In 1993 the alternative rock group, 4 Non Blondes, were climbing the charts all over the world screaming at the top of their lungs “What’s going on?”. Almost 12 years later, the SQL Server team came with the answer – the Dynamic Management Views and Functions. If you care what’s going on with your server, read on and I’ll try in 10 minutes of your time to get you started.
Dynamic Management Views and Functions expose internal state of SQL Server and also statistical data. Don’t be scared by this phrase. In plain English, you can see through DMVs current sessions, requests, locks, transactions, memory allocation, tempdb usage, index usage and so on. This is so called dynamic metadata that is not persisted physically on disk and lives somewhere in your server memory. You can guess from that what happens if your server gets restarted.
Let’s see them at work by checking the list of the current session. You will find the output similar with the output of sp_who or sp_who2 stored procedures (or sysprocesses virtual table):
Example:
SELECT
s.session_id
, DB_NAME(r.database_id) as DBName
, s.login_name
, r.blocking_session_id as BlkBy
, r.open_transaction_count
, r.status
, s.host_name
, s.program_name
, r.command
, (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), text)) * 2 ELSE statement_end_offset END -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText
FROM
sys.dm_exec_sessions s JOIN
sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE
s.is_user_process = 1
Let me explain this first example more detailed. You already met two DMVs (sys.dm_exec_sessions, sys.dm_exec_requests) and one DMF (sys.dm_exec_sql_text). The first information you can get from this objects come from their name. They belong to the sys schema (like other system objects), then the dm prefix is common for all DMVs (and DMFs). The prefix is followed by a category that groups related DMVs – exec in this particular case for execution related DMVs. The last part of the name will tell you what the DMV does, in this example you will get a list of current sessions and associated requests (and the Query Text respectively from the sys.dm_exec_sql_text DMF).
The rows returned by the query look almost identical with the output of sp_who stored procedure so it won’t be a mystery if you are familiar with SQL Server 2000 but I do have some remarks here. The former SPID (Server Process ID) became the session id and to get only the user sessions you have to use the is_user_process column (the old approach – testing for spids greater than 51 is no longer guaranteed). For more information about these DMVs and all the others that will follow please refer to Books Online as I am trying only to whet your appetite here.
Before I move on, I will specify one more use of the sys.dm_exec_requests DMV – progress reporting for some DBCC commands such as DBCC CHECKDB, DBCC CHECKFILEGROUP and DBCC CHECKTABLE. You have two columns for that - the percent_complete column (for the percent of work completed) and the command column (for reporting the current phase of the DBCC command). To test this feature, use the following example:
Example:
-- run this in one window
-- of SQL Server Management Studio
-- or Query Analyzer
DBCC CHECKDB ('AdventureWorks')
GO
-- run this in another window
SELECT
s.session_id
,r.percent_complete
,r.command
FROM
sys.dm_exec_sessions s join
sys.dm_exec_requests r on s.session_id = r.session_idsession_id
WHERE
s.is_user_process = 1
Optionally you can improve the example by including a specific session id in your WHERE clause.
Another DMV that I want to introduce to you is sys.dm_os_memory_clerks. It displays the memory allocation for server components.
Just run a SELECT * FROM sys.dm_os_memory_clerks to get a feel of it. In time if you investigate further you will know that this DMV is just the top of the iceberg and if you need to you can get the memory allocation at the object level. More than that, you can peek at internal memory notifications and so on. Anyway, don’t worry about that for now.
How about indexes? The existing DMVs (and when I say DMVs I mean also DMFs) can tell you if your indexes are used and how, their fragmentation and even if your performance can benefit from additional indexes. The next example will display the fragmentation of your indexes from AdventureWorks database:
Example:
USE AdventureWorks;
GO
SELECT
OBJECT_NAME(s.object_id) Table_Name
,i.[name] Index_Name
,avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'),NULL,NULL,NULL,'SAMPLED') s
JOIN
sys.indexes i ON i.[object_id]=s.[object_id]
AND
i.index_id=s.index_id
WHERE
OBJECTPROPERTY(s.[object_id],'IsUserTable')=1
My last example will concentrate on performance troubleshooting. I found out in my work that the Pareto's Principle (the 80-20 Rule) really works. In the performance area if I will tune 20 percent of the queries that run on my server I will gain the maximum effect (that 80 percent improvement). But how to find that 20 percent of queries? With a DMV of course:
Example:
SELECT
SQ.*
,(SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), text)) * 2 ELSE statement_end_offset END -statement_start_offset)/2)) AS QueryText
FROM
(
SELECT TOP 20 PERCENT
sql_handle
,SUM(total_worker_time) AS Total_CPU_Time
,SUM(execution_count) AS Total_Execution_Count
,COUNT(*) AS Nmb_Statements
,MIN(statement_start_offset) AS statement_start_offset
,MIN(statement_end_offset) AS statement_end_offset
FROM
sys.dm_exec_query_stats
GROUP BY sql_handle
ORDER BY Total_CPU_Time DESC
) AS SQ
CROSS APPLY sys.dm_exec_sql_text(SQ.sql_handle) AS T
Don’t close this page yet – there’s an explanation for the above query. I find out first in a sub-query the most executed offending queries for me by means of the total CPU time used. I also display the number of times they were executed because it does matter and also the query text.
I will stop for now and I will continue in future articles (and hopefully in a book). If you are not convinced by now that you should at least consider using them, I’ll try one more approach. Think at your server as being a car. What do you do if something seems to be wrong? You open the hood, don’t you? For SQL Server the equivalent is the DMVs. You may find a work-around to avoid them in some cases but why would you do that? So pop-up that hood and open Books Online and see what those DMVs are capable of. Thank you!
Published in Culminis Compass
Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro