contents
preface
acknowledgements
about this book
Part 1 Starting the journey
- Chapter 1 The Dynamic Management Views gold mine
- What are Dynamic Management Views?
- The problems DMVs can solve
- DMV examples
- Preparing to use DMVs
- DMV companions
- Working with DMVs
- Summary
- Chapter 2 Common patterns
- Reducing blocking
- Using CROSS APPLY
- Restricting output to a given database
- Restricting output by using the TOP command
- Creating an empty temporary table structure
- Looping over the databases on a server
- Retrieving a query’s cached plan and SQL text
- Extracting the Individual Query from the Parent Query
- Identifying the database used by ad hoc queries
- Calculating DMV changes
- Reading cached plans
- Building dynamic SQL
- Printing the content of large variables
- Common terms and acronyms
- Known problems that may affect the scripts
- Summary
Part 2 DMV discovery
- Chapter 3 Index DMVs
- The importance of indexes
- Costly missing indexes
- Unused indexes
- High-maintenance indexes
- Most-frequently used indexes
- Fragmented indexes
- Indexes used by a given routine
- Databases with most missing indexes
- Completely unused indexes
- Your statistics
- A holistic approach to managing indexes
- Summary
- Chapter 4 Improving poor query performance
- Understanding executed queries
- Finding a cached plan
- Finding where a query is used
- Long-running queries
- Queries that spend a long time being blocked
- CPU-intensive queries
- I/O-hungry queries
- Frequently executed queries
- The last run of a query
- Summary
- Chapter 5 Further query improvements
- Queries with missing statistics
- Finding queries that have missing indexes
- Finding queries that have implicit data type conversions
- Finding queries that have table scans
- Slower-than-normal queries
- Unused stored procedures (2008 only)
- Looking for SQL queries run during a specific interval
- Relationships between DMV snapshot deltas
- Currently running queries
- Recompiled queries
- Summary
- Chapter 6 Operating system DMVs
- Understanding server waits
- Identifying your most common waits
- Identifying your most common waits—snapshot version
- Identifying why queries wait
- Queries that are waiting
- Finding what’s blocking running SQL
- SQL Server performance counters
- Effect of running SQL queries on the performance counters
- How performance counters and wait states relate
- SQL queries and how they change the performance counters and wait states
- Correlating wait states and performance counters
- Capturing DMV data periodically
- Summary
- Chapter 7 Common Language Runtime DMVs
- Introducing the CLR
- A simple CLR example
- .NET Framework performance concerns
- Time-consuming CLR queries
- Queries spending the most time in the CLR (snapshot version)
- Relationships between CLR DMVs and other DMVs
- Getting information about SQL Server CLR integration
- Getting information about your SQL CLR assemblies
- Summary
- Chapter 8 Resolving transaction issues
- Transaction overview
- A simple transaction-based case study
- Locks, blocks, and deadlocks
- The ACID properties of transactions
- Transaction isolation levels
- Sessions, connections, and requests
- Finding locks
- Identifying the contended resources
- Identifying inactive sessions with open transactions
- Waiting due to transaction locks
- Queries waiting for more than 30 seconds
- Lock escalation
- How to reduce blocking
- How to reduce deadlocks
- Summary
- Chapter 9 Database-level DMVs
- Space usage in tempdb
- Session usage in tempdb
- Task usage in tempdb
- Tempdb recommendations
- Index contention
- Summary
- Chapter 10 The self-healing database
- Self-healing database
- Recompiling slow routines
- Automatically rebuild and reorganize indexes
- Intelligently update statistics
- Automatically updating a routine’s statistics
- Automatically implement missing indexes
- Automatically disable or drop unused indexes
- Summary
- Chapter 11 Useful scripts
- Viewing everyone’s last-run SQL query
- A generic performance test harness
- Determining the impact of a system upgrade
- Estimating the finishing time of system jobs
- Get system information from within SQL Server
- Viewing enabled Enterprise features (2008 only)
- Who’s doing what and when?
- Finding where your query really spends its time
- Memory usage per database
- Memory usage by table or index
- Finding I/O waits
- A simple lightweight trace utility
- Some best practices
- Where to start with performance problems
- Summary
-
index
