SQL Server DMVs in Action
Better Queries with Dynamic Management Views
Ian W. Stirk
  • May 2011
  • ISBN 9781935182733
  • 352 pages
  • printed in black & white

Essential reference for SQL Server Administrators.

Dave Corun, Avanade

SQL Server DMVs in Action is a practical guide that shows you how to obtain, interpret, and act on the information captured by DMVs to keep your system in top shape. The samples provided in this book will help you master DMVs and also give you a tested, working, and instantly reusable SQL code library.

Table of Contents show full


acknowledgements about this book

Part 1 Starting the journey

1. Chapter 1 The Dynamic Management Views gold mine

1.1. What are Dynamic Management Views?

1.2. The problems DMVs can solve

1.3. DMV examples

1.4. Preparing to use DMVs

1.5. DMV companions

1.6. Working with DMVs

1.7. Summary

2. Chapter 2 Common patterns

2.1. Reducing blocking

2.2. Using CROSS APPLY

2.3. Restricting output to a given database

2.4. Restricting output by using the TOP command

2.5. Creating an empty temporary table structure

2.6. Looping over the databases on a server

2.7. Retrieving a query’s cached plan and SQL text

2.8. Extracting the Individual Query from the Parent Query

2.9. Identifying the database used by ad hoc queries

2.10. Calculating DMV changes

2.11. Reading cached plans

2.12. Building dynamic SQL

2.13. Printing the content of large variables

2.14. Common terms and acronyms

2.15. Known problems that may affect the scripts

2.16. Summary

Part 2 DMV discovery

3. Chapter 3 Index DMVs

3.1. The importance of indexes

3.2. Costly missing indexes

3.3. Unused indexes

3.4. High-maintenance indexes

3.5. Most-frequently used indexes

3.6. Fragmented indexes

3.7. Indexes used by a given routine

3.8. Databases with most missing indexes

3.9. Completely unused indexes

3.10. Your statistics

3.11. A holistic approach to managing indexes

3.12. Summary

4. Chapter 4 Improving poor query performance

4.1. Understanding executed queries

4.2. Finding a cached plan

4.3. Finding where a query is used

4.4. Long-running queries

4.5. Queries that spend a long time being blocked

4.6. CPU-intensive queries

4.7. I/O-hungry queries

4.8. Frequently executed queries

4.9. The last run of a query

4.10. Summary

5. Chapter 5 Further query improvements

5.1. Queries with missing statistics

5.2. Finding queries that have missing indexes

5.3. Finding queries that have implicit data type conversions

5.4. Finding queries that have table scans

5.5. Slower-than-normal queries

5.6. Unused stored procedures (2008 only)

5.7. Looking for SQL queries run during a specific interval

5.8. Relationships between DMV snapshot deltas

5.9. Currently running queries

5.10. Recompiled queries

5.11. Summary

6. Chapter 6 Operating system DMVs

6.1. Understanding server waits

6.2. Identifying your most common waits

6.3. Identifying your most common waits—snapshot version

6.4. Identifying why queries wait

6.5. Queries that are waiting

6.6. Finding what’s blocking running SQL

6.7. SQL Server performance counters

6.8. Effect of running SQL queries on the performance counters

6.9. How performance counters and wait states relate

6.10. SQL queries and how they change the performance counters and wait states

6.11. Correlating wait states and performance counters

6.12. Capturing DMV data periodically

6.13. Summary

7. Chapter 7 Common Language Runtime DMVs

7.1. Introducing the CLR

7.2. A simple CLR example

7.3. NET Framework performance concerns

7.4. Time-consuming CLR queries

7.5. Queries spending the most time in the CLR (snapshot version)

7.6. Relationships between CLR DMVs and other DMVs

7.7. Getting information about SQL Server CLR integration

7.8. Getting information about your SQL CLR assemblies

7.9. Summary

8. Chapter 8 Resolving transaction issues

8.1. Transaction overview

8.2. A simple transaction-based case study

8.3. Locks, blocks, and deadlocks

8.4. The ACID properties of transactions

8.5. Transaction isolation levels

8.6. Sessions, connections, and requests

8.7. Finding locks

8.8. Identifying the contended resources

8.9. Identifying inactive sessions with open transactions

8.10. Waiting due to transaction locks

8.11. Queries waiting for more than 30 seconds

8.12. Lock escalation

8.13. How to reduce blocking

8.14. How to reduce deadlocks

8.15. Summary

9. Chapter 9 Database-level DMVs

9.1. Space usage in tempdb

9.2. Session usage in tempdb

9.3. Task usage in tempdb

9.4. Tempdb recommendations

9.5. Index contention

9.6. Summary

10. Chapter 10 The self-healing database

10.1. Self-healing database

10.2. Recompiling slow routines

10.3. Automatically rebuild and reorganize indexes

10.4. Intelligently update statistics

10.5. Automatically updating a routine’s statistics

10.6. Automatically implement missing indexes

10.7. Automatically disable or drop unused indexes

10.8. Summary

11. Chapter 11 Useful scripts

11.1. Viewing everyone’s last-run SQL query

11.2. A generic performance test harness

11.3. Determining the impact of a system upgrade

11.4. Estimating the finishing time of system jobs

11.5. Get system information from within SQL Server

11.6. Viewing enabled Enterprise features (2008 only)

11.7. Who’s doing what and when?

11.8. Finding where your query really spends its time

11.9. Memory usage per database

11.10. Memory usage by table or index

11.11. Finding I/O waits

11.12. A simple lightweight trace utility

11.13. Some best practices

11.14. Where to start with performance problems

11.15. Summary

© 2014 Manning Publications Co.

About the Technology

Every action in SQL Server leaves a set of tiny footprints. SQL Server records that valuable data and makes it visible through Dynamic Management Views, or DMVs. You can use this incredibly detailed information to significantly improve the performance of your queries and better understand what's going on inside your SQL Server system.

About the book

SQL Server DMVs in Action shows you how to obtain, interpret, and act on the information captured by DMVs to keep your system in top shape. The over 100 code examples help you master DMVs and give you an instantly reusable SQL library. You'll also learn to use Dynamic Management Functions (DMFs), which provide further details that enable you to improve your system's performance and health.

What's inside

  • Many practical solutions
  • How to correct missing indexes
  • What's slowing down your queries
  • What's compromising concurrency
  • Much more

About the reader

This book is written for DBAs and developers.

About the author

Ian Stirk is a freelance consultant based in London. He's an expert in SQL Server performance and a fierce advocate for DMVs.

combo $64.99 pBook + eBook
eBook $51.99 pdf + ePub + kindle

FREE domestic shipping on three or more pBooks

Arm yourself with an arsenal of DMV knowledge.

Tariq Ahmed, Amcom Technology

Lifts the hood on SQL Server performance.

Richard Siddaway, Serco

The examples alone are worth twice the price of the book!

Nikander and Margriet Bruggeman, Lois & Clark IT Services