What do you think?
Rate this book
Performance Tuning with SQL Server Dynamic Management Views
Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions. Why, then, aren't all DBAs using them? Why do many DBAs continue to ignore them in favour of "tried and trusted" tools such as sp_who2, DBCC OPENTRAN, and so on, or make do with the "ready made" reports built into SSMS? Why do even those that do use the DMVs speak wistfully about "good old sysprocesses"? There seem to be two main factors at work. Firstly, some DBAs are simply unaware of the depth and breadth of the information that is available from the DMvs, or how it might help them troubleshoot common issues. This book investigates all of the DMVs that are most frequently useful to the DBA in investigating query execution, index usage, session and transaction activity, disk IO, and how SQL Server is using or abusing the operating system. Secondly, the DMVs have a reputation of being difficult to use. In the process of exposing as much useful data as possible, sysprocesses has been de-normalized, and many new views and columns have been added. This fact, coupled with the initially-baffling choices of what columns will be exposed where, has lead to some DBAs to liken querying DMVs to "collecting mystic spells." In fact, however, once you start to write your own scripts, you'll see the same tricks, and similar join patterns, being used time and again. As such, a relatively small core set of scripts can be readily adapted to suit any requirement. This book is here to de-mystify the process of collecting the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own systems, including how to: * Root out the queries that are causing memory or CPU pressure on your system * Investigate caching, and query plan reuse * Identify index usage patterns * Track fragmentation in clustered indexes and heaps * Get full details on blocking and blocked transactions, including the exact commands being executed, and by whom. * Find out where SQL Server is spending time waiting for resources to be released, before proceeding * Monitor usage and growth of tempdb The DMVs don't make existing, built-in, performance tools obsolete. On the contrary, they complement these tools, and offer a flexibility, richness and granularity that are simply not available elsewhere. Furthermore, you don't need to master a new GUI, or a new language in order to use them; it's all done in a language all DBAs know and mostly love: T-SQL.
344 pages, Paperback
First published September 1, 2010
8 people are currently reading
27 people want to read
Ratings & Reviews
Friends & Following
Create a free account to discover what your friends think of this book!
Community Reviews
Displaying 1 - 3 of 3 reviews
March 31, 2011
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.
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.
October 11, 2019
Took me awhile to soldier through this book. Still don't understand a lot of it, but a good resource on SQL Server Dynamic Management Objects (DMOs). It tries to distill them down to most important DMOs and information that can be gleaned from them.
October 11, 2014
A well written and suitable illustrated with TSQL report on performance issues in SQL Server.
Displaying 1 - 3 of 3 reviews