Frequently used DMV's in SQL Server


What is Dynamic Management Objects (DMOs): Dynamic Management Views are views and functions introduced in SQL server 2005 for monitoring and tuning SQL server performance.

Dynamic Management Views (DMVs): These are defined as views introduce in SQL 2005 as one more monitoring tool.

Dynamic Management Functions (DMFs): These are defined as functions introduced in SQL 2005 as one more monitoring tool. This required input parameters like function

Click here: List of Frequently Used Queries


• change data capture
• Common language runtime
• Database mirroring
• Database
• Execution
• Full-text search
• I/O
• Index
• Object
• Query notifications
• Replication
• Resource governor
• SQL Operating System

Frequently used types of DMVs: Frequently used dmv’s are based on operations one perform like if some one works lot on mirroring , mirroring dmv’s will be frequent one similarly who works on performance issues , he must be using execution and OS dmv’s. Below DMV’s are those which every

DBA must know

• Database
• Execution
• IO
• Index
• SQL operating system

You need different permission to run these as per their scope
           Server scoped --> view server state
           Database scoped --> view database state

Majorly used DMV’s are :

Execution Related

• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_cached_plans
• sys.dm_exec_query_plans
• sys.dm_exec_sql_text
• sys.dm_exec_query_stats

Index Related

• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_columns

SQL Server Operating System

• sys.dm_os_performance_counters
• sys.dm_os_schedulers
• sys.dm_os_nodes
• sys.dm_os_waiting_tasks
• sys.dm_os_wait_stats

I/O Related

• sys.dm_io_virtual_file_stats
• sys.dm_io_pending_io_requests
• sys.dm_io_cluster_shared_drives

/*****************Database*************************/

Example :
-------------------For page and row count-------------------
select object_name(object_id) as objname, * from sys.dm_db_partition_stats

------------------------- Space in database-------------------------
if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'
SELECT [name], fileid, filename, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sysfiles
else
SELECT [name], file_id, physical_name, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sys.database_files

/*******************************IO***********************************/

---------- Can be run when you think that io can be a bottleneck------------
Example :
 select * sys.dm_io_pending_io_requests

------------Shows io stats for data and log files-------------------
Example :
select * from sys.dm_io_virtual_file_stats (null,null)
select db_name(database_id), * from sys.dm_io_virtual_file_stats(null,null)

/************************Index operations************************/

-------- Returns counts of different types of index operations and the time each type of operation was last performed.-------------------
Example :
select * from sys.dm_db_index_usage_stats

-------------------To analyze a common access pattern to the table or index and to analyze statistics of physical I/Os on an index or heap partition. Shows io, locking and access information such as inserts, deletes, updates-------------------
Example :
SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)

-------------------This dynamic management function replaces the DBCC SHOWCONTIG statement. Shows index storage and fragmentation info-------------------
 avg_fragmentation_in_percent
 avg_page_space_used_in_percent
Example :
 SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED/DETAILED');

-----------These are used to find missing indexes during performance issues------------
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_columns

/********************SQL Operating System**************************/

---------------Information about processors, Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor-------------------
Example :
SELECT
scheduler_id,
cpu_id,
parent_node_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count
FROM sys.dm_os_schedulers;
WHERE scheduler_id < 255

-- Info about computer and about resources available to and consumed by sql server -----
Example :
select * from sys.dm_os_sys_info

----How memory is used overall on the server, and how much memory is available ------- 
Example :
  select * from sys.dm_os_sys_memory

------ Returns information about the waits encountered by threads that executed ------------
Example :  select * from sys.dm_os_wait_stats

------ Info about all data pages that are currently in the sql server buffer pool --------------
Example :
---------Returning cached page count for each database----------
SELECT COUNT(*)AS cached_pages_count
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;

---------Returns a row per performance counter maintained by the server ---------------
select * from sys.dm_os_performance_counters

/*********************Execution**************************/
------------------- when sql server is restart everything is reset

sys.dm_exec_sessions: Info about all active user connections and internal tasks, This DMV will give information on each session connected to SQL Server. This DMV is similar to running sp_who2 or querying Master..sysprocesses table ---------------
Example :
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM
sys.dm_exec_sessions
WHERE session_id >= 51 –- All user Sessions

sys.dm_exec_connections: Info about connections established, This DMV shows all the connection to SQL Server. The below query uses sys.dm_exec_connections DMV to get connection information. This view returns one row for each user connection (Sessionid > =51).
Example :
SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM
sys.dm_exec_connections

sys.dm_exec_requests: Info about each request that is executing (including all system processes), This DMV will give details on what each connection is actually performing in SQL Server.
Example :
SELECT
session_id,status,
command,sql_handle,database_id
FROM
sys.dm_exec_requests
WHERE
session_id >= 51

Query plans:

sys.dm_exec_sql_text: Returns text of sql batch, This dynamic management function returns the text of a SQL statement given a SQL handle.
Example :
SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 51

sys.dm_exec_query_plan: Returns showplan in xml
sys.dm_exec_query_stats: Returns stats for cached query plans
sys.dm_exec_cached_plans: Each query plan that is cached

Example Query:
-- -- -- -- -- -- -- This returns Connection details along with query-- -- -- -- -- -- -- -- -- --
select
*
from
sys.dm_exec_connections cross apply
sys.dm_exec_sql_text(most_recent_sql_handle)

-- --  -- --This returns spid status along with query and waits -- -- -- -- -- --
select
*
from
sys.dm_exec_requests cross apply
sys.dm_exec_sql_text(sql_handle)

-- -- -- -- This returns query detail along with query and execution plan -- -- -- -- --
Select
T.[text],
p.[query_plan],
s.[program_name],
s.host_name,
s.client_interface_name,
s.login_name,
r.*
from
sys.dm_exec_requests r inner join
sys.dm_exec_sessions S ON s.session_id = r.session_id cross apply
sys.dm_exec_sql_text(r.sql_handle) T cross apply
sys.dm_exec_query_plan (plan_handle) P

-- -- -- --This returns query's in plan cache and there status -- --  -- -- --

select
usecounts,
cacheobjtype,
objtype,
text
from
sys.dm_exec_cached_plans cross apply
sys.dm_exec_sql_text(plan_handle)
where
usecounts > 1
order by usecounts desc

-- -- -- -- -- -- --Current Running Transaction -- -- -- -- -- -- -- -- -- --

use master
SELECT
SPID,ER.percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN
sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST

- -- --  --Detailed blocking information with query information -- -- -- --

SELECT
owt.session_id AS waiting_session_id,
owt.blocking_session_id,
DB_NAME(tls.resource_database_id) AS database_name,
(SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
(CASE WHEN ers.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE ers.statement_end_offset
END
- ers.statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
CASE WHEN owt.blocking_session_id > 0
THEN (
SELECT
est.[text] FROM sys.sysprocesses AS sp
CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
WHERE sp.spid = owt.blocking_session_id)
ELSE
NULL
END AS blocking_query_text,
(CASE tls.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
ELSE (SELECT OBJECT_NAME(pat.[object_id], tls.resource_database_id)
FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)
END
) AS object_name,
owt.wait_duration_ms,
owt.waiting_task_address,
owt.wait_type,
tls.resource_associated_entity_id,
tls.resource_description AS local_resource_description,
tls.resource_type,
tls.request_mode,
tls.request_type,
tls.request_session_id,
owt.resource_description AS blocking_resource_description,
qp.query_plan AS waiting_query_plan
FROM sys.dm_tran_locks AS tls
INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
GO

-- -- -- Run following query to find longest running query using T-SQL- -- -- --

SELECT DISTINCT TOP 3
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
--ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn--,
--ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
-- -- -- -- -- -- -- --Top 5 worst performing Queries -- -- -- -- -- -- -- -- --
SELECT
TOP 5 obj.name, max_logical_reads, max_elapsed_time
FROM
sys.dm_exec_query_stats a CROSS APPLY
sys.dm_exec_sql_text(sql_handle) hnd INNER JOIN
sys.sysobjects obj on hnd.objectid = obj.id
ORDER BY
max_logical_reads DESC

-- -- -- -- --TOP 5 CPU-CONSUMING STATEMENTS -- -- -- --
SELECT TOP 5
qs.total_worker_time/(qs.execution_count*60000000) as [Avg CPU Time in mins],
qs.execution_count,
qs.min_worker_time/60000000 as [Min CPU Time in mins],
--qs.total_worker_time/qs.execution_count,
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
dbname=db_name(qt.dbid),
object_name(qt.objectid) as [Object name]
FROM
sys.dm_exec_query_stats qs cross apply
sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time in mins] DESC


-- -- -- -- --Transaction causing log space filled most-- -- -- -- --
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
where DB_NAME (tdt.database_id) = 'tempdb'
ORDER BY [Log Bytes Used] DESC;

 -- -- -- -- Sleeping Sessions from last 30  mins---- -- -- -- ---- -- -- --
select
 s.spid,t.text, s.last_batch,
 s.login_time, s.open_tran,
 s.nt_username, s.hostname,s.status
from
 sys.sysprocesses s cross apply
 sys.dm_exec_sql_text(sql_handle) t
where
 s.status not in ( 'runnable', 'suspended' , 'running')
 AND datediff (mi,last_batch, getdate()) > 120 -- inactive for 30 mins
 and  not(nt_username like 'svc%') -- do not kill svc account
 and  not(nt_username like 'adm%') -- do not kill adm accounts
 and  s.spid >=50 -- do not kill SQL system generated spids
 
/******************************************************************/




References:

http://msdn.microsoft.com/en-us/library/ms178621.aspx
http://msdn.microsoft.com/en-us/library/ms176083.aspx
http://msdn.microsoft.com/en-us/library/ms187974.aspx
http://msdn.microsoft.com/en-us/library/ms190314.aspx

10 comments:
Write comments
  1. Nice collection of dmv querys, Please try to add more

    ReplyDelete
  2. thanks, just what I was looking for

    ReplyDelete
  3. excellent categorization of the DMVs with scenario based supporting examples

    ReplyDelete
  4. queries are lot of use.. thanks for the post.

    ReplyDelete
  5. Nice collection Saurabh . Thanks for the post

    ReplyDelete
  6. It is very good blog and useful for students and developer ,

    Sql server DBA Online Course Bangalore

    ReplyDelete

Please do not enter spam links

Meet US

Services

More Services