SQL Server: Working with Dynamic Management Objects
Dynamic Management Objects help you manage and monitor workload details in SQL Server, which is essential for performance tuning.
Excerpted from “SQL Server DMV Starter Pack,” published by Red Gate Books (2010).
Glenn Berry, Louis Davidson and Tim Ford
Dynamic Management Objects (DMOs) are a set of SQL Server objects stored in the system schema. They provide you with a window into the activities being performed on your various SQL Server instances, and the resources that these activities are consuming.
In other words, DMOs expose valuable information concerning the connections, sessions, transactions, SQL statements and processes executing against a database instance, the resultant workload generated on the server, how it’s distributed, where the pressure points are and so on. Having revealed a particular bottleneck or pressure point, you can then take appropriate steps to alleviate the problem—perhaps by tuning a query, adding an index or simply killing a blocking session.
The term “dynamic” refers to the fact that the information stored in DMOs is generated dynamically from a vast range of instrumentation points. These are in-memory structures throughout the SQL Server engine. This data is then exposed in tabular form in the sys database schema. The data is exposed either in views, in which case they’re referred to as Dynamic Management Views (DMVs), or in table-values functions, in which case they’re referred to as Dynamic Management Functions (DMFs).
DMVs and DMFs are essentially system views and system functions. Use them as you would use any other view and function within SQL Server: querying them, joining, passing parameters, and ultimately returning a single result set containing the data you need to investigate a particular issue regarding the state or health of your SQL Server instance.
Performance Tuning with DMVs
DMOs expose a sometimes-dizzying array of information. The original sysprocesses system view has essentially been de-normalized, and many new DMOs have been added. Many new data columns are now available for querying. As the database engine becomes better instrumented, the amount of data available about the engine, and the work it’s doing, will continue to grow.
The added complexity of stitching together data from a disparate array of DMOs, coupled with the initially baffling choices of what columns will be exposed where, has lead some DBAs to liken querying DMOs to collecting mystic spells.
However, the de-normalization process has, in many ways, made the data that DMOs return much easier to analyze and understand. 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 many requirements.
In some ways, working through DMOs for the diagnostic data you need a process of peeling back layers. At the outer layer, we can find out who’s connected to our SQL Server instances and how they’re connected; what sessions are running against them; and what requests are being performed by these sessions. We can find out the details of the SQL statements being executed by these requests, the query plans that are being used to run them and so on.
Dropping down a layer, we have the transaction level, where we can find out what locks are being held as a result of these transactions, investigate any potential blocking and so on. Moving down another layer, we can find how the workload represented by the submitted requests translates into actual work in the OS. We can determine, for example:
- What actual tasks (threads) are being executed in order to fulfill the requests
- What work they’re performing in terms of I/O, CPU and memory usage
- How I/O is distributed among the various files
- How long threads spend waiting, unable to proceed and why
It’s your job to assemble all the pieces of data from the various different layers to provide the results needed to highlight the specific problems in the system.
Point in Time vs. Cumulative
As noted, we can query data held on DMOs just as we would any other table, view or function. However, always remember that the data you’re seeing is “dynamic” in nature. It’s collected from a range of different structures in the database engine and represents a point-in-time “snapshot” of the activity that was occurring on your server at the time you ran the DMO query.
Sometimes, this is exactly what you want. You have a performance issue, and want to find out what queries are running on the server that could be causing it. Sometimes, though, you may find it quite difficult to query the data in these point-in-time DMOs in the hope that the problem will simply “jump out at you.”
If, for example, you have a performance problem and want to check for any “unusual” locking patterns, then it’s unlikely that a “select [columns] from [locking DMV]” will tell you much, unless you’re very familiar with what “normal” locking looks like on your system, and you can easily spot anomalies.
Bear in mind that the point-in-time data can and likely will change each time you query it, as the state of the server changes. You should expect to occasionally see anomalous or non-representative results, and you may need to run a script many times to get a true picture of activity on your instance.
In other cases, DMOs are cumulative. In other words, the data in a given column is accumulative and incremented every time a certain event occurs. For example, every time a session waits a period of time for a resource to become available, this is recorded in a column of the sys.dm_os_wait_stats DMV. When querying such a DMV, you’ll see, for example, the total amount of time spent waiting for various resources, across all sessions, since SQL Server was started or restarted—unless a database consistency checking , or DBCC, command was run to manually clear out the stored statistics.
While this will give you a broad overview of where time has been spent waiting, over a long period, it will make it hard to see the smaller details. If you want to measure the impact of a certain change to the database (a new index, for example), you’ll need to take a baseline measurement, make the change and then measure the difference.
Finally, always bear in mind that much of the data you’re seeing in such DMOs is aggregate data, collected across many sessions, many requests and many transactions. The previously mentioned wait_stats DMV, for example, will show you at an instance level where SQL Server spent time waiting, aggregated across all sessions. You can’t track the wait times at an individual session level—unless, of course, you’re working on an isolated server.
.png "Glenn Berry")
.png "Louis Davidson")
.png "Tim Ford")
Glenn Berry works as a database architect at NewsGator Technologies in Denver, Colo. 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.
Louis Davidson 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. Currently he is the data architect and sometimes DBA for the Christian Broadcasting Network, supporting offices in Virginia Beach, Va., and Nashville, Tenn.
Timothy Ford is a SQL Server MVP and has been working with SQL Server for more than 10 years. He is the primary DBA and subject-matter expert for the SQL Server platform for Spectrum Health. He’s been writing about technology since 2007 for a variety of Web sites and maintains his own blog at thesqlagentman.com, covering SQL as well as telecommuting and professional development topics.
Learn more about “SQL Server DMV Starter Pack” at red-gate.com/our-company/about/book-store.