My favorite technical session at the PASS Summit conference last month was provided by Grant Fritchey, the “Scary DBA.” The subject was Dynamic Management Views (DMVs) and how to use them for troubleshooting under the title “SQL Server DMV’s as a Shortcut to Procedure Tuning.” This Wednesday session was also on “SQL Kilt” day so Grant obliged by wearing a camouflaged kilt. Now there was one Scary DBA!
Grant quickly renamed the session to Dynamic Management Objects (DMOs) because technically there are Dynamic Management Views and Functions so DMO is a better inclusive term. DMOs allow you to inspect the internal state of SQL Server from a performance point of view in a real-time sense. There are literally hundreds of DMOs available so the big question is always “which ones are the best to use?” Of course, the answer is “it depends” but Grant did a great job highlighting his favorites.
DMOs are either server scoped or database scoped. To use a Server scoped DMO you will need VIEW SERVER STATE permission. To use a Database scoped DMO you will need VIEW DATABASE STATE permission. Grant recommended giving these rights to your developers so they can do their own performance analysis. This caused a bit of a stir (“give our developers what???”) but it makes sense if you wish to empower your developers and reduce your workload. Maybe not, if you don’t.
Most DMOs can be joined with others to reveal more information. For instance, you can join DM_EXEC_REQUESTS and DM_TRAN_ACTIVE_TRANSACTIONS on Transaction_ID. This may uncover transactions that are being blocked since DM_EXEC_REQUESTS includes a BLOCKING_SESSION_ID. You could then join this with DM_OS_WAITING_TASKS on Session_ID to reveal waiting information such as WAIT_DURATION_MS, the wait time in milliseconds.
The SSMS Activity Monitor actually uses similar DMOs to display its information graphically but you can get this information quickly through your own scripts. You could also set up a SQL Profiler trace to capture exactly what DMOs the Activity Monitor is accessing on your behalf. If you are lucky, there may even be some comments in there provided by thoughtful Microsoft developers.
The flexibility of your own scripts means you can focus on what’s important to you. Also, since these are standard objects, you can reuse scripts from other helpful folks like the Scary DBA.