Performance Tuning with SQL Server Dynamic Management Views

1 High Performance SQL Server Performance Tuning with SQL Server Dynamic Management Views Louis Davidson and Tim Ford Performance Tuning using SQL Server Dynamic Management Views By Louis Davidson and Tim Ford First published by Simple Talk Publishing 2010. Copyright Louis Davidson and Tim Ford 2010. ISBN 978-1-906434-46-5. The right of Louis Davidson and Tim Ford to be identified as the authors of this work has been asserted by them in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act in relation to this publication may be liable to criminal prosecution and civil claims for damages.

2 This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold, hired out, or otherwise circulated without the publisher's prior consent in any form other than that in which it is published and without a similar condition including this condition being imposed on the subsequent publisher. Editor: Tony Davis Technical Review and Additional Material: Glenn Berry Cover Image: Tim Ford Typeset & Designed: Matthew Tye & Gower Associates Table of Contents 12. Code 15. Chapter 1: using Dynamic Management 16. Compatibility Views , Catalog Views , and 17. DMO Security and 21. Performance Tuning with Navigating through the 24. Point-in-time versus cumulative 26. Beware of the watcher 29. using DMOs with other Performance 29. 32. Chapter 2: Connections, Sessions and 33. Sysprocesses versus 35. Connections and 37. Who is connected?

3 42. Who is connected by SSMS?.. 44. Session-level Logins with more than one 47. Identify sessions with context 48. Identify inactive 48. Identify idle sessions with orphaned 51. 52. Overview of 53. Overview of Returning the SQL text of ad hoc Isolating the executing statement within a SQL Investigating work done by 64. Dissecting user 66. 71. Chapter 3: Query Plan 72. Why Cached Plan Analysis with DMOs?.. 73. An Overview of DMOs for Query Plan 75. Flushing the Cache?..77. Viewing the Text of Cached Queries and Query Returning the plan using Dissecting the SQL Returning the plan using Cached Query Plan 86. The 87. Investigating plan Query Plan Gathering Query Execution 100. Overview of 101. Putting to Investigating Expensive Cached Stored Getting Aggregate Query Optimization Statistics for All 110. Chapter 4: What is a transaction, anyway?..115. Investigating Locking and DMOs, Activity Monitor and 119.

4 An overview of the Investigating Blocking analysis using and Analyzing Transactional 138. Transactional DMOs vs. DBCC 138. 139. 140. 143. Assessing transaction log Snapshot Isolation and the tempdb Version SNAPSHOT and READ_COMMITTED_SNAPSHOT Investigating snapshot Chapter 5: Indexing Strategy and The Indexing System Catalog 173. using the Indexing 178. Index 178. Investigating index usage (index_usage_stats)..182. Determine usage patterns of current indexes (index_operational_stats).. 190. Find missing 199. Index Maintenance (index_physical_stats).. 207. A brief overview of index Fragmentation statistics (index_physical_stats)..210. Detecting and fixing 212. 215. Chapter 6: Physical Disk Statistics and Minimizing 218. Tuning the Disk I/O 221. Getting Physical Statistics on your Tables and Size and Investigating 230. Diagnosing I/O An overview of using Investigating physical I/O and I/O Viewing pending I/O 247.

5 Finding the Read:Write 249. Amount of data read versus 251. Number of read and write 253. Number of reads and writes at the table Getting Stats about tempdb 257. 260. Chapter 7: OS and Hardware Wait A brief overview of Finding the most common 267. Finding the longest cumulative 270. Investigating locking 271. Investigating CPU SQL Server Performance Directly usable counter Per second Average number of 286. Monitoring Machine 288. Investigating CPU An overview of CPU 295. Insufficient 297. Context 297. Is NUMA enabled?..299. CPU utilization 300. Investigating Memory 301. System-wide memory Process memory Memory use in the buffer 306. Memory clerks and memory 308. Investigate memory using cache Investigating 319. About the Authors Louis Davidson Louis has been in the IT industry for 16 years as a corporate database developer and architect. He has been a SQL Server Microsoft MVP for six years and has written four books on database design.

6 He is currently the Data Architect and sometimes DBA for the Christian Broadcasting Network supporting offices in Virginia Beach, Virginia and Nashville, Tennessee. He graduated from the University of Tennessee at Chattanooga with a Bachelor's degree in Computer Science, with a minor in mathematics. For more information, visit his website at or email him at Tim Ford Tim is a SQL Server MVP, and has been working with SQL Server for over ten years. He is the co-founder of SQL Cruise, LLC, a training company for SQL Server specializing in deep-dive sessions for small groups, hosted in exotic and alternative locations throughout the world. He is also a Senior SQL Server Professional for Next Wave Logistics, Inc. and is the owner and Lead Consultant for B-Side Consulting, LLC. He's been writing about technology since 2007 for a variety of websites and maintains his own blog at http://.

7 Covering SQL as well as telecommuting and professional development topics. Tim is an established SQL Server Community Speaker and long-term volunteer in the technical community, having held positions in the Professional Association for SQL. viii Server (PASS) since 2002. He has also been leading the West Michigan SQL Server User Group (WMSSUG) since 2008. When not neck-deep in technology, Tim spends his time travelling with his wife, Amy and sons, Austen and Trevor, be it to Alaska or simply to one of Trevor's tennis practices or Austen's Boy Scout meetings. Tim is passionate about photography, cooking, music, gaming, and exercise, and either experiences or writes about them often. Glenn Berry (technical review and additional material). Glenn Berry works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests.

8 He is also an Adjunct Faculty member at University College, University of Denver, where he has been teaching since 2000, and he has completed the Master Teacher Program. His blog is at and he is GlennAlanBerry on Twitter. ix Acknowledgements Louis Davidson To acknowledge all the people who made my part of this book possible would take forever (plus, I don't think I could just cut and paste the entire list of SQL Server MVPs, the Microsoft employees, and all of the bloggers out there who blogged about DMVs in here, and get away with it). However, I do want to mention three particular people on the technical side. First, Tony Davis who is both one of the best editors around and one of the most annoying (you take that back! Ed). The results he gets from an amateur like me never cease to amaze me, even if his super attention to detail makes him "fun" to work with . Next, Glenn Berry, who is a heck of a guy and has done some awesome work with the DMVs on his website, during his technical review of this book, and in his contributions to the text and scripts herein.

9 Finally, Tim Ford, without whom I doubt I would ever have finished this book. It was our conversations with Tony at the 2008 PASS conference that really got the book started on the path to completion. On the less computer-oriented side, I want to thank my wife and daughter for just being there. After five books, I don't think they even notice I am doing this anymore, but they do give me strength to keep going at times. And my granddaughter, who didn't even exist when I first started this book, three years ago; and boy, does that put it all into perspective! x Tim Ford I dedicate this book to Amy, Austen, and Trevor. I could (and should) have spent with you all the hours devoted to working on this book missing sporting events, school activities, and just those quiet moments that add up to make a life. Thank you for your patience, love, and understanding in letting me fulfill a commitment made and a goal I had set for myself since I was 13 years old: to write a book.

10 Unfortunately, there are no aliens, car chases, or explosions in the pages that follow; no dashing young man that gets the girl. (To my adolescent self I offer my sincerest apologies.). To my co-author, Louis, who said, "I told you it would be great if you wrote a book, not if you write a book!" while I was in the midst of writer's block, and to our editor Tony Davis: to think this all started with a fondness for a decent pint of beer! To Dad, Pop, Grandma Conrad, and Grandma Ford. I miss not being able to share this and my many other proud moments with you. xi Introduction with the advent of the Dynamic Management Objects (DMOs) in SQL Server 2005, Microsoft vastly expanded the range and depth of metadata that could be exposed regarding the connections, sessions, transactions, statements, and processes that are, or have been, executing against a database instance.