I’ll be blogging about some very useful dynamic management views that can be used to debug very specific problems in SQL Server.
First, a background on what a dynamic management view is.
Dynamic management views were introduced in SQL Server 2005 to give information about the internals of SQL Server. They include information from memory allocations, executing requests, thread wait times, etc… Needless to say, they provide a wealth of information. However with a wealth of information comes into question, how to use it all.
The first dynamic management view I’m going to talk about is the dm_exec_session_requests view. It has been incredibly useful for me.
This view gives a list of outstanding queries that are running against a server. For example, let’s say your CPU usage on the server spikes to 100%. This view will let you know what queries are running.
SELECT text,req.* FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS text
Notice the CROSS APPLY here. This is very common to do with the execution related dynamic management views either to determine the request text, or the SQL plan for a particular request. The sys.dm_exec_sql_text gives the sql text, likewise the sys.dm_exec_query_plan view can be used to give the query plan for a query.
I’ve used this view a few different ways.
First, while performance testing applications, I often group by the text from the dm_exec_sql_text cross apply to get an idea of how many times a specific query is being run at any given instant. It gives me a real time look into what the system is doing.
Second, if there is some sort of problem query for example, one causing excessive CPU usage, I can use the cpu_time to pick this out.
Third, sometimes certain queries will execute a long time. I’ll use the start_time column to narrow down what queries have been executing for the longest amount of time.
There are many other ways this view can be used. See the MSDN library reference here for additional information on this view: http://msdn.microsoft.com/en-us/library/ms177648.aspx