MANNING
Ian W. Stirk
Better queries with
Dynamic Management Views
www.it-ebooks.info
SQL Server DMVs in Action
www.it-ebooks.info
www.it-ebooks.info
SQL Server
DMVs in Action
BETTER QUERIES WITH
DYNAMIC MANAGEMENT VIEWS
IAN W. STIRK
MANNING
Shelter Island
www.it-ebooks.info
For online information and ordering of this and other Manning books, please visit
www.manning.com. The publisher offers discounts on this book when ordered in quantity.
For more information, please contact
Special Sales Department
Manning Publications Co.
20 Baldwin Road
PO Box 261
Shelter Island, NY 11964
Email: [email protected]
©2011 by Manning Publications Co. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in
any form or by means electronic, mechanical, photocopying, or otherwise, without prior written
permission of the publisher.
Many of the designations used by manufacturers and sellers to distinguish their products are
claimed as trademarks. Where those designations appear in the book, and Manning

7 ■ Common Language Runtime DMVs 174
8 ■ Resolving transaction issues 196
9 ■ Database-level DMVs 226
10 ■ The self-healing database 257
11 ■ Useful scripts 285
www.it-ebooks.info
www.it-ebooks.info
ix
contents
preface xix
acknowledgements xx
about this book xxii
PART 1 STARTING THE JOURNEY 1
1
The Dynamic Management Views gold mine 3
1.1 What are Dynamic Management Views? 4
A glimpse into SQL Server’s internal data 7
Aggregated results 8
■
Impact of running DMVs 8
Part of SQL Server 2005 onward 8
1.2 The problems DMVs can solve 9
Diagnosing problems 9
■
Performance tuning 10
Monitoring 10
1.3 DMV examples 13
Find your slowest queries 14
■
Find those

2.1 Reducing blocking 32
2.2 Using CROSS APPLY 32
2.3 Restricting output to a given database 32
2.4 Restricting output by using the TOP command 33
2.5 Creating an empty temporary table structure 34
2.6 Looping over the databases on a server 35
2.7 Retrieving a query’s cached plan and SQL text 37
2.8 Extracting the Individual Query from the
Parent Query 38
2.9 Identifying the database used by ad hoc queries 39
2.10 Calculating DMV changes 40
2.11 Reading cached plans 43
Targeting the area of concern 43
■
Things to look out for 44
2.12 Building dynamic SQL 47
2.13 Printing the content of large variables 48
2.14 Common terms and acronyms 49
2.15 Known problems that may affect the scripts 50
SQL Server compatibility level set to below 2005 50
An OFFLINE database 50
2.16 Summary 51
PART 2 DMV DISCOVERY 53
3
Index DMVs 55
3.1 The importance of indexes 56
Types of index 56
■
Types of index access 57
Factors affecting index performance 58

The importance of knowing which indexes are used 81
3.8 Databases with most missing indexes 83
Finding which databases have the most missing indexes 84
The importance of other databases 84
3.9 Completely unused indexes 85
Finding which indexes aren’t used at all 85
■
The importance
of unused indexes 87
3.10 Your statistics 87
Finding the state of your statistics 88
■
The importance
of statistics 89
3.11 A holistic approach to managing indexes 90
3.12 Summary 91
4
Improving poor query performance 92
4.1 Understanding executed queries 93
Aggregated results 93
■
Clearing the cached plans 93
4.2 Finding a cached plan 94
How to find a cached plan 94
www.it-ebooks.info
CONTENTS
xii
4.3 Finding where a query is used 97
Identifying where a query is used 97
4.4 Long-running queries 98

Finding implicit data conversions 125
■
Finding disparate
column data types 125
5.4 Finding queries that have table scans 127
5.5 Slower-than-normal queries 127
Finding queries that are running slower than normal 128
The importance of queries that are running slower
than normal 131
5.6 Unused stored procedures (2008 only) 132
Finding unused stored procedures 133
www.it-ebooks.info
CONTENTS
xiii
5.7 Looking for SQL queries run during a
specific interval 134
What runs over a given time period? 134
5.8 Relationships between DMV snapshot deltas 137
Amalgamated DMV snapshots 137
5.9 Currently running queries 142
What’s running now? 142
5.10 Recompiled queries 144
Finding the most-recompiled queries 144
5.11 Summary 146
6
Operating system DMVs 147
6.1 Understanding server waits 148
6.2 Identifying your most common waits 148
Why are you waiting? 149
■

7.3 .NET Framework performance concerns 181
7.4 Time-consuming CLR queries 183
Finding the queries that spend the most time in the CLR 183
Impact of time-consuming CLR queries 185
7.5 Queries spending the most time in the CLR
(snapshot version) 186
Finding queries that spend the most time in the CLR
(snapshot version) 186
7.6 Relationships between CLR DMVs and other DMVs 188
7.7 Getting information about SQL Server CLR
integration 192
7.8 Getting information about your SQL CLR
assemblies 193
7.9 Summary 195
8
Resolving transaction issues 196
8.1 Transaction overview 197
8.2 A simple transaction-based case study 198
8.3 Locks, blocks, and deadlocks 200
Locks 200
■
Blocks 202
■
Deadlocks 203
8.4 The ACID properties of transactions 205
8.5 Transaction isolation levels 205
8.6 Sessions, connections, and requests 206
8.7 Finding locks 207
8.8 Identifying the contended resources 209
Contended resources—basic version 209

Total, free, and used space
in tempdb 228
■
Tempdb total space usage by object type 230
9.2 Session usage in tempdb 231
Session usage of tempdb space 232
■
Space used and not reclaimed
in tempdb by session 233
9.3 Task usage in tempdb 235
Space used by running SQL queries 235
■
Space used and not
reclaimed by active SQL queries 237
9.4 Tempdb recommendations 239
9.5 Index contention 240
Indexes under row-locking pressure 241
Escalated indexes 243
■
Unsuccessful index-lock
promotions 244
■
Indexes with the most
page splits 245
■
Indexes with most latch contention 247
Indexes with most page I/O-latch contention 248
■
Indexes under
row-locking pressure—snapshot version 249

Using the generic performance test harness 288
11.3 Determining the impact of a system upgrade 290
Quantifying system upgrade impact 290
11.4 Estimating the finishing time of system jobs 293
Estimating when a job will end 294
11.5 Get system information from within SQL Server 295
11.6 Viewing enabled Enterprise features (2008 only) 297
11.7 Who’s doing what and when? 298
11.8 Finding where your query really spends its time 300
Locating where your queries are spending their time 300
11.9 Memory usage per database 303
Determining the memory used per database 303
11.10 Memory usage by table or index 304
Determining the memory used by tables and indexes 304
11.11 Finding I/O waits 307
I/O waits at the database level 307
■
I/O waits at the
file level 308
■
Average read/write times per file,
per database 310
www.it-ebooks.info
CONTENTS
xvii
11.12 A simple lightweight trace utility 311
11.13 Some best practices 313
11.14 Where to start with performance problems 314
Starting with a slow server or database 314
■

DMVs aren’t more widely used. I hope this book will help correct this situation.
www.it-ebooks.info
xx
acknowledgements
I’d like to start off by thanking the whole Manning team, in particular Katharine
Osborne, Michael Stephens, Marjan Bace, Mary Piergies, Janet Vail, Linda Reckten-
wald, Katie Tennant, and Dennis Dalinnik. Thank you, Katharine, for your profession-
alism and steering me in the right direction, and thank you Michael and Marjan for
believing the subject of DMVs could make an important contribution to improving
SQL performance. I’m indebted to the Manning production team, Mary, Janet, Linda,
Katie, and Dennis, for guiding me through the production process and helping make
this a better book.
I’d like to express my thanks to Elvira Chierkoet, for checking and reading every
sentence and helping ensure my ideas were sensible.
To the technical reviewers, I want to thank you for your feedback and for making
this a more accurate book: Tariq Ahmed, Christian Siegers, Nikander Bruggeman,
Margriet Bruggeman, Amos Bannister, Richard Siddaway, Sumit Pal, Dave Corun, and
Sanchet Dighe, and special thanks to the main technical reviewer, Deepak Vohra.
I want to give a special thank-you to Karen Stirk, Catherine Stirk, and Charlie for
their support and encouragement. A special thank-you is owed to my grandparents,
Joan and Bill Bridgewater, and the rest of the Bridgewater family (Karen, Timmy,
Brenda, Caroline, Kenny, Patty, Jenny, Mary, Jacky, David, and Diane). And thanks also
to my old chemistry teacher, Jim Galbraith. Without these people, I would have
turned out a lesser person.
I’ve been lucky enough to know some interesting and helpful people, both as
friends and colleagues, and I’d like to thank you all: Tim Noakes, Dave Starkey,
www.it-ebooks.info
ACKNOWLEDGEMENTS
xxi
Mark Hadley, Gerald Hemming, Albert Morris, Martin Gunning, Chris Conn, Roy

ciently as possible will find this book very useful. The following groups of people in
particular will find this book valuable: database administrators (DBAs), developers
working with SQL Server, and administrators of SharePoint servers, CRM systems, and
similar servers.
When a new version of a software product appears, for example, Microsoft Word or
SQL Server, new features are typically added to the existing core. Microsoft Word is
still primarily used to enter and store text; this core functionality hasn’t changed,
despite the numerous version releases. Similarly, although this book is written primarily
www.it-ebooks.info
ABOUT THIS BOOK
xxiii
for SQL Server 2005 and 2008, the core functionality of the DMVs is unlikely to
change in future versions (for example, SQL Server 2011), and so it should be applica-
ble to future versions too.
DBAs need to ensure the databases under their command are running as efficiently
as possible. Running the code snippets provided in this book will identify any problem
areas and help provide solutions to these problems.
Developers need to ensure their applications can retrieve and store data efficiently.
Using the supplied code snippets, developers will be able to ensure appropriate
indexes are being used, the data is being retrieved efficiently, and any changes are
tested for defined improvement.
Increasingly, SharePoint servers, CRM servers, and similar servers that have SQL
Server as their underlying database are being installed in organizations with little
thought for ongoing maintenance. With time, the performance of these unattended
servers can degrade. Applying the code snippets included in this book will identify
areas where performance can be improved.
One final point: Often organizations install third-party applications on their SQL
Servers. Although it’s usually not possible to change the code in these applications, it
is possible to run the code snippets in this book against these databases, with a view to
either applying any missing indexes (if this is allowed) or providing feedback to the

you to investigate performance problems from differing viewpoints.
Chapter 5 is an extension of chapter 4, discussing further aspects of how to
improve the performance of your queries.
Chapter 6 relates to operating system DMVs. It discusses why your queries, as a
whole, are not able to run, what resources they’re waiting for, and how these resources
can be improved to give faster queries. Windows performance counters are also exam-
ined in relation to these collective queries.
Chapter 7 focuses on the Common Language Runtime DMVs. The use of the CLR
within SQL Server is illustrated with a CLR class that provides regular expression func-
tionality for use within your own SQL queries.
Chapter 8 opens with a look at transactions, locking, blocking, and deadlocks. A
small case study is provided to illustrate the transaction-based DMV code snippets.
Ways of reducing both blocking and deadlocking are explored.
Chapter 9 discusses database-related DMVs. The first section discusses the impor-
tance of tempdb and shows how to examine its usage when space problems arise. The
second section examines various aspects of index usage that can help you diagnose
and improve your queries.
Chapter 10 contains code snippets that can be used to automatically improve the
performance of your SQL Server databases. Snippets include intelligently updating
statistics, recompiling slow routines, and implementing missing indexes.
Chapter 11 has useful snippets that don’t fit into any of the other chapters. The
snippets include a generic test harness, estimating the finishing time of jobs, how
memory is used by your database, and a simple lightweight DMV trace utility.
Code conventions and downloads
All source code in listings or set off from the text is in a
fixed-width

font

like