Consolidated DMV Scripts – Part 2 – Day 79 – One DMV a Day

Hello Geeks and welcome to the Day 79 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMV s in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMV s and some scenarios where these DMV s will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have seen the consolidated DMV scripts – part 1. Today I will cover the 2^nd part of the consolidated scripts.

Execution related DMV s:

Exec connections: know more…

SELECT session_id,
	most_recent_session_id,
	connection_id,
	connect_time,
	net_transport,
	protocol_type,
	encrypt_option,
	auth_scheme,
	node_affinity,
	num_reads,
	num_writes,
	last_read,
	last_write,
	net_packet_size,
	client_net_address,
	client_tcp_port,
	most_recent_sql_handle
FROM sys.dm_exec_connections

Exec Session: know more…

SELECT session_id,
	login_time,
	host_name,
	program_name,
	host_process_id,
	client_interface_name,
	login_name,
	original_login_name,
	status,
	cpu_time,
	memory_usage,
	total_scheduled_time,
	total_elapsed_time,
	last_request_start_time,
	last_request_end_time,
	reads,
	writes,
	logical_reads,
	is_user_process,
	transaction_isolation_level,
	open_transaction_count,
	database_id,
	authenticating_database_id
FROM sys.dm_exec_sessions
WHERE session_id > 50

Check blocking using exec requests: know more…

;WITH b AS
(
	SELECT distinct blocking_session_id AS blockers
	FROM sys.dm_exec_requests
)
SELECT session_id,
	0 AS blocking_session_id,
	Block_Desc = 'Lead'
FROM sys.dm_exec_sessions es
INNER JOIN b
	ON es.session_id = b.blockers
WHERE NOT EXISTS(SELECT 1 FROM sys.dm_exec_requests er WHERE es.session_id = er.session_id)
	OR EXISTS(SELECT 1 FROM sys.dm_exec_requests er WHERE es.session_id = er.session_id AND er.blocking_session_id = 0)
 
UNION ALL
 
SELECT session_id,
	blocking_session_id,
	Block_Desc = 'Victim'
FROM sys.dm_exec_requests
WHERE blocking_session_id != 0

Check running statement: know more…

SELECT est.*
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE session_id = <spid>
GO

DBCC INPUTBUFFER(<spid>)

Check query plan: know more…

SELECT er.session_id, est.text, eqp.query_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE session_id = <spid>

Check text query plan: know more…

SELECT er.session_id,
	SUBSTRING(est.text, statement_start_offset/2+1,
	(((CASE WHEN er.statement_end_offset = -1
		THEN datalength(est.text)
		ELSE er.statement_end_offset END) - er.statement_start_offset)/2) + 1)
		AS current_stmnt,
	text AS batch,
	CAST(etqp.query_plan AS XML) AS stmnt_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) etqp
WHERE session_id = <spid>

Collecting procedure stats: know more…

SELECT database_id,
	object_id,
	execution_count AS Exec_Cnt,
	total_worker_time AS Exec_Time,
	total_physical_reads AS Disk_Reads,
	total_logical_reads AS Mem_Reads,
	total_logical_writes AS Total_Writes,
	total_elapsed_time AS Total_Time
FROM sys.dm_exec_procedure_stats
WHERE database_id NOT IN(1,2,3,4,32767)
AND type = 'P'

Exec query stats: know more…

SELECT sql_handle,
	statement_start_offset,
	statement_end_offset,
	plan_handle,
	execution_count AS Exec_Cnt,
	total_worker_time AS Exec_Time,
	total_physical_reads AS Disk_Reads,
	total_logical_reads AS Mem_Reads,
	total_logical_writes AS Total_Writes,
	total_clr_time AS CLR_Time,
	total_elapsed_time AS Total_Time,
	total_rows AS Total_Rows
FROM sys.dm_exec_query_stats

Exec trigger stats: know more…

SELECT database_id,
	object_id,
	execution_count AS Exec_Cnt,
	total_worker_time AS Exec_Time,
	total_physical_reads AS Disk_Reads,
	total_logical_reads AS Mem_Reads,
	total_logical_writes AS Total_Writes,
	total_elapsed_time AS Total_Time
FROM sys.dm_exec_trigger_stats
WHERE database_id NOT IN(1,2,3,4,32767)

Cursor details: know more…

SELECT session_id,
	cursor_id,
	name,
	properties,
	creation_time,
	is_open,
	is_async_population,
	is_close_on_commit,
	fetch_status,
	fetch_buffer_size,
	fetch_buffer_start,
	ansi_position,
	worker_time/1000 as worker_ms,
	reads,
	writes
FROM sys.dm_exec_cursors(<spid>)

Background job queue: know more…

SELECT time_queued,
	job_id,
	database_id,
	object_id1,
	object_id2,
	object_id3,
	object_id4,
	error_code,
	request_type,
	retry_count,
	in_progress,
	session_id
FROM sys.dm_exec_background_job_queue

Background job queue stats: know more…

SELECT queue_max_len,
	enqueued_count,
	started_count,
	ended_count,
	failed_lock_count,
	failed_other_count,
	failed_giveup_count,
	enqueue_failed_full_count,
	enqueue_failed_duplicate_count,
	elapsed_avg_ms,
	elapsed_max_ms
FROM sys.dm_exec_background_job_queue_stats

Exec cached plans: know more…

SELECT DB_NAME(CAST(db.value AS INT)) AS DBName,
	OBJECT_NAME(CAST(obj.value AS INT)) AS objName,
	plan_handle,
	refcounts,
	usecounts,
	pool_id,
	cacheobjtype,
	bucketid
FROM sys.dm_exec_cached_plans cp(NOLOCK)
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) db
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) obj
WHERE db.attribute = 'dbid'
	AND db.value = DB_ID('AdventureWorks2012')
	AND obj.attribute = 'objectid'
	AND obj.value = OBJECT_ID('uspGetEmployeeManagers')

Exec plan attributes: know more…

SELECT * FROM sys.dm_exec_plan_attributes(<plan handle>)

Exec plan dependent objects: know more…

SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(<plan handle>)

Exec query memory grants: know more…

SELECT * FROM sys.dm_exec_query_memory_grants

Exec query resource semaphores: know more…

select * from sys.dm_exec_query_resource_semaphores

Exec describe first result set: know more…

SELECT * FROM  sys.dm_exec_describe_first_result_set(
N'SELECT * FROM <table1>;
SELECT * FROM <table2>', NULL, 0)

Transaction related DMV s:

Tran locks: know more…

SELECT Resource_type,
	Resource_database_id,
	Resource_description,
	Resource_associated_entity_id,
	Resource_lock_partition,
	Request_mode,
	Request_type,
	Request_status,
	Request_session_id,
	Request_request_id,
	Request_owner_type,
	Request_owner_id,
	Lock_owner_address
FROM sys.dm_tran_locks
WHERE request_session_id = <spid>

Tran database transactions: know more…

SELECT transaction_id,
	DB_NAME(database_id) AS DBName,
	database_transaction_begin_time,
	CASE database_transaction_type
		WHEN 1 THEN 'Read/Write'
		WHEN 2 THEN 'Read-Only'
		WHEN 3 THEN 'System'
		ELSE 'Unknown Type - ' + convert(VARCHAR(50), database_transaction_type)
		END AS TranType,
	CASE database_transaction_state
		WHEN 1 THEN 'Uninitialized'
		WHEN 3 THEN 'Not Started'
		WHEN 4 THEN 'Active'
		WHEN 5 THEN 'Prepared'
		WHEN 10 THEN 'Committed'
		WHEN 11 THEN 'Rolled Back'
		WHEN 12 THEN 'Comitting'
		ELSE 'Unknown State - ' + convert(VARCHAR(50), database_transaction_state)
		END AS TranState,
	database_transaction_log_record_count AS LogRecords,
	database_transaction_replicate_record_count AS RelLogRcrds,
	database_transaction_log_bytes_reserved AS LogResrvd,
	database_transaction_log_bytes_used AS LogUsed,
	database_transaction_log_bytes_reserved_system AS SysLogResrvd,
	database_transaction_log_bytes_used_system AS SysLogUsed
FROM sys.dm_tran_database_transactions
WHERE database_id NOT IN(1, 2, 3, 4, 32767)

Tran session transactions: know more…

SELECT session_id,
	transaction_id,
	transaction_descriptor,
	enlist_count,
	is_user_transaction,
	is_local,
	is_enlisted,
	is_bound,
	open_transaction_count
FROM sys.dm_tran_session_transactions
WHERE session_id = <spid> --Change session id as needed

Tran active transactions: know more…

SELECT transaction_id,
	name,
	transaction_begin_time,
	case transaction_type
		when 1 then 'Read/Write'
		when 2 then 'Read-Only'
		when 3 then 'System'
		when 4 then 'Distributed'
		else 'Unknown - ' + convert(varchar(20), transaction_type)
		end as tranType,
	case transaction_state
		when 0 then 'Uninitialized'
		when 1 then 'Not Yet Started'
		when 2 then 'Active'
		when 3 then 'Ended (Read-Only)'
		when 4 then 'Committing'
		when 5 then 'Prepared'
		when 6 then 'Committed'
		when 7 then 'Rolling Back'
		when 8 then 'Rolled Back'
		else 'Unknown - ' + convert(varchar(20), transaction_state)
		end as tranState,
	case dtc_state
		when 0 then NULL
		when 1 then 'Active'
		when 2 then 'Prepared'
		when 3 then 'Committed'
		when 4 then 'Aborted'
		when 5 then 'Recovered'
		else 'Unknown - ' + convert(varchar(20), dtc_state)
		end as dtcState,
	transaction_uow
FROM sys.dm_tran_active_transactions

Tran current transactions: know more…

SELECT transaction_id AS [tranId],
	transaction_sequence_num AS [seqNo],
	transaction_is_snapshot AS [isSnap],
	first_snapshot_sequence_num AS [1stSnapSeqNo],
	last_transaction_sequence_num AS [lastSeqNo],
	first_useful_sequence_num AS [1stUsefulSeqNo]
FROM sys.dm_tran_current_transaction

Tran version store: know more…

SELECT * FROM sys.dm_tran_version_store
WHERE database_id = <DBID>
 
SELECT COUNT(1) AS VerRcrds,
	SUM(record_length_first_part_in_bytes + record_length_second_part_in_bytes)/1024 AS VerSizeKB
FROM sys.dm_tran_version_store

Tran top version store generators: know more…

SELECT * FROM sys.dm_tran_top_version_generators
 
USE <DatabaseName>
GO
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS TableName
FROM sys.partitions p
INNER JOIN sys.objects o
	ON o.object_id = p.object_id
WHERE hobt_id = <rowset_id from sys.dm_tran_top_version_generators>

Rest of them will be continued tomorrow.

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook