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