Performance Tuning with SQL Server Dynamic Management Views

Performance Tuning with SQL Server Dynamic Management Views By Louis Davidson and Tim Ford


I’m not 100% certain what it was that I was expecting from this book.  From one review I had read on amazon, I was halfway expecting an in-depth performance tuning book that would teach a lot about background, benchmarking and baselines, etc.  Unfortunately, this text isn’t really that in depth.  Fortunately, this text IS a very good review of the various Dynamic Management Objects (DMOs, or as they are often referred to, DMVs) and a collection of scripts which you can use to investigate issues with your system.  If I hadn’t come into this with high expectations I would have more than likely been blown away. 

The book is written in a fairly consistent style, is easy to read and conveys a lot of good information that can be used immediately.  I’d recommend it for the entry to mid level DBA or developer as a good start to learning about DMOs and how they can be used to investigate metrics associated with your server. 

I read this book in March of 2011 after I had a fair amount of experience with DMOs. 

Chapter 01 – Using Dynamic Management Objects

The first chapter deals a little with the DMOs and how it is that they relate to the older system catalogs and compatibility views.  It also delves into what permissions you will need in order to view the DMOs and explains a bit about the groupings and types of DMOs available. 

Chapter 02 – Connections, Sessions and Requests

Here we start to get into the meat of the product with the introduction of several key objects.  We use dm_exec_requests, dm_exec_sessions, dm_exec_connections, dm_exec_query_plan and dm_exec_sql_text in an attempt to find out who is connected, what they are running and the text or plan of the queries that are being executed.  In addition, we find out how it is that you can detect idle sessions with orphaned transactions, users with multiple connections, etc.  All in all, these DMOs are the basis for scripts which I run several times a day to get a feel for my servers activity.  They are extremely important to understand and to use in order to effectively monitory your servers, especially during times where you are experiencing performance problems.

Chapter 03 – Query Plan Metadata

Who would want to know what type of queries are being run against their environments?  Would you also like to start to investigate what type of plans are cached for your stored procedures?  How about which of your stored procedures are the most resource intensive?  If so, look no further.  In this chapter the authors delve into the dm_exec_query_plan, dm_exec_sql_text, dm_exec_cached_plan, dm_exec_query_stats and dm_exec_procedure_stats objects, all of which are here to help in the tasks outlined above.

Chapter 04 – Transactions

Perhaps this chapter should have been called Locking and Blocking or Concurrency Effects or something similar.  Transactions just sounds so… simple?  I don’t know, but this chapter provides a LOT of information about what is happening in your system by investigating the dm_tran_locks, dm_tran_session_transactions, dm_tran_active_transactions, dm_tran_database_transactions, dm_tran_active_snapshot_database_transactions, dm_tran_current_snapshot, dm_tran_transactions_snapshot, dm_tran_version_store and dm_tran_version_generators DMOs.  This chapter, more so than the rest of the book, gets into details about the theory behind transactions, isolation levels, concurrency, dirty data, and the newish snapshot isolation levels.

Chapter 05 – Indexing Strategy and Maintenance

In this chapter the authors’ investigate how to examine the indexes that you have and that indexes that you don’t.   For those you do have, they attempt to help determine how useful they are by examining the usage count versus the update counts, the space used, how fragmented the indexes are, etc.  For those indexes which you don’t have they explain why it is that they are present within the missing indexes DMOs and attempt to guide you in a direction for picking those indexes which you SHOULD be including in your database design.  The chapter includes a few hints on using the DMOs for maintenance and provides links to prefabricated scripts that are a good choice for use as regularly scheduled maintenance in your systems.

Chapter 06 – Physical Disk Statistics and Utilization

With the physical disk chapter I was hoping to have a great big enormous “AH-HA!” moment with some piece of arcane lore that I had overlooked.  Of course, I was hoping this would be the case since at least one company for which I have worked has had I/O problems that were at times hard to pin down.  Unfortunately that didn’t happen.  Not the authors fault, I think it’s just a topic that will never generate a “magic bullet” that will solve all I/O problems.  I knew this going in, it was just a hope that I had deep down that I knew was probably not to be.  Oh well.  As is, in this chapter we do receive a lot of good information that will tell you the I/O statistics of each of the files in your system as well as cumulative stall (wait) metrics, the read versus write ratios of your files and some fun statistics about your tempdb database.  Don’t let my diatribe above fool you, this was a chapter that is well worth the read.

Chapter 07 – OS and Hardware Interaction

This is the chapter I probably learned the most from.  With this chapter I was reminded of DMOs which I rarely see and even more rarely use.  It’s not that they aren’t important, it’s just that they don’t seem to pop up as often for me.  I was also show other DMOs which I use regularly (hello mr. dm_os_wait_stats).  We are introduced to the counters that are exposed to Perfmon and it is explained how we can use them correctly (they use different methods to collect the metrics and report them differently by counter class) through the dm_os_performance_counters object.  We also look at the schedulers, the memory usage, the threads, etc.  All in all, good information.

This entry was posted in Books and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s