One of the most important tools for your toolbox is some mechanism to see what’s happening in the engine. Who is running what in my system, and how is it behaving? I liken this to “Task Manager” or “Resource Monitor” in Windows. SQL Server has “Activity Monitor” as a graphical tool within SQL Server Management Studios, but I’ve found that to be a bit limiting.
There are many other fantastic tools for such diagnostics… Off the top of my head, I’d recommend looking at sp_Blitz from Brent Ozar and team or sp_WhoIsActive from Adam Machanic. I have not used them a lot myself, but I’ve heard nothing but great things about each tool.
I have developed the following script over the course of several years. I have iterated over it and updated it hundreds of times. I used it daily, and I’m never done changing it. So why write my own? A few reasons (none of them may be particularly compelling to you):
- It’s been a great learning experience to dive in and discover SQL’s DMVs
- It’s extremely customized for my needs (what can I say- I like what I like)
- It’s a no-trace solution… meaning that it’s just a SELECT statement, and no database objects are created, nor are any settings or configurations changed
I believe the only permission you’ll need on your SQL instance is VIEW SERVER STATE. It should also be compatible with at least SQL 2008 – SQL 2017. Furthermore, it’s written with dynamic SQL so it will return more information with newer versions of SQL, as Microsoft has augmented DMVs over time.
Keep in mind, I never claimed this code was pretty 😉
Without further ado, here it is:
/*
Written by: Ryan Snee (http://www.sqlsnee.com)
*/
DECLARE @ShowSleepingThreads BIT;
DECLARE @SkipQP BIT;
DECLARE @V varchar(50);
DECLARE @V1 INT;
DECLARE @V2 INT;
DECLARE @V3 INT;
/*<Setable Parameters>*/
SET @ShowSleepingThreads=0;
SET @SkipQP=0; --Sometimes this query will be blocked when querying the sys.dm_exec_query_plan DMF
/*</Setable Parameters>*/
select @V=cast(SERVERPROPERTY ('ProductVersion') as varchar(50));
SELECT @V1=CAST(substring(@V,1,charindex('.',@V)-1) as int)
,@V2=CAST(substring(@V,charindex('.',@V)+1,(charindex('.',@V,charindex('.',@V)+1))-(charindex('.',@V)+1)) as int)
,@V3=CAST(substring(@V, charindex('.',@V,CHARINDEX('.',@V)+1)+1,(charindex('.',@V,charindex('.',@V,CHARINDEX('.',@V)+1)+1))-(charindex('.',@V,CHARINDEX('.',@V)+1)+1)) as int);
declare @sql nvarchar(max);
SET @sql= CAST('' AS nvarchar(max))
+N'SELECT
cast(s.session_id as varchar(10))
+ case when (r.blocking_session_id=0 or r.blocking_session_id is null) and LB.LeadBlocker=1 then ''*'' else '''' end
AS [Session]
,'
+CASE
WHEN @V1 >= 11 THEN N'concat(case when dbt.database_transaction_status2=258 then ''ROLLBACK'' ELSE s.status END,'':''+r.status)'
ELSE N'case when dbt.database_transaction_status2=258 then ''ROLLBACK'' ELSE (s.status) END --This column is undocumented by MS, and this is not guranteed to be accurate!!!'
END+N' as [Status]
,CASE
WHEN ((datediff(day,s.last_request_start_time,getdate())>28) AND (r.session_id IS NOT NULL))
THEN cast(datediff(day,s.last_request_start_time,getdate()) as varchar(50))+''d+''
WHEN [r].[total_elapsed_time] > 86400000 --Days, hours, and minutes
THEN cast([r].[total_elapsed_time]/86400000 as varchar(50)) +''d ''+cast(([r].[total_elapsed_time]/3600000)%24 as varchar(50)) +''h ''+cast(([r].[total_elapsed_time]/60000)%60 as varchar(50)) +''m''
WHEN [r].[total_elapsed_time] > 3600000 --Hours, minutes and seconds
THEN cast([r].[total_elapsed_time]/3600000 as varchar(50)) +''h ''+cast(([r].[total_elapsed_time]/60000)%60 as varchar(50)) +''m ''+cast(([r].[total_elapsed_time]/1000)%60 as varchar(50))+''s''
WHEN [r].[total_elapsed_time] > 60000 --Minutes, seconds, and milliseconds
THEN cast([r].[total_elapsed_time]/60000 as varchar(50)) +''m ''+cast(([r].[total_elapsed_time]/1000)%60 as varchar(50))+''s ''+cast(([r].[total_elapsed_time])%1000 as varchar(50))+''ms''
WHEN [r].[total_elapsed_time] > 1000 --Seconds and milliseconds
THEN cast([r].[total_elapsed_time]/1000 as varchar(50))+''s ''+cast(([r].[total_elapsed_time])%1000 as varchar(50))+''ms''
ELSE cast([r].[total_elapsed_time] as varchar(50))+''ms'' --Milliseconds
END AS [ElapsedTime]
,coalesce(nullif(s.host_name+'' '',''''),''(''+c.client_net_address+'')'') as Client
,
case
when s.login_name <> s.original_login_name
THEN s.login_name +'' '' +quotename(s.original_login_name ,''('')
ELSE s.login_name
END AS [login_name]
,coalesce(s.program_name,'''')+ coalesce('' (PID:''+cast(s.host_process_id as varchar(20))+'')'','''') as [ClientProgram]
,'+CASE
WHEN @V1 >= 11 THEN N'DB_NAME(COALESCE(r.database_id, s.database_id))'
ELSE N'DB_NAME(r.database_id)'
END+N' AS [Database Name]
,nullif(r.percent_complete,0) AS [%Complete]
,nullif(
case
WHEN(
select count(distinct scheduler_id)
FROM sys.dm_os_tasks ost
where r.request_id=ost.request_id and s.session_id=ost.session_id
--and ost.task_state = ''RUNNING''
and scheduler_id < 1000000
) > 1
THEN(
select count(distinct scheduler_id)
FROM sys.dm_os_tasks ost
where r.request_id=ost.request_id and s.session_id=ost.session_id
--and ost.task_state = ''RUNNING''
and scheduler_id < 1000000
)-1
ELSE(
select count(distinct scheduler_id)
FROM sys.dm_os_tasks ost
where r.request_id=ost.request_id and s.session_id=ost.session_id
--and ost.task_state = ''RUNNING''
and scheduler_id < 1000000
)
END
,0) as [Tasks]
,'+CASE
WHEN @V1 >= 11 THEN N'format(coalesce(r.cpu_time,s.cpu_time),''#,###'')'
ELSE N'coalesce(r.cpu_time,s.cpu_time)'
END+N' AS [CPU(ms)]
,CASE
WHEN eqmg.granted_memory_kb >= 1048576
THEN cast(cast(eqmg.granted_memory_kb/1048576.0 as decimal(18,2)) as varchar(30))+ '' GB''
WHEN eqmg.granted_memory_kb >= 1024
THEN cast(cast(eqmg.granted_memory_kb/1024.0 as decimal(18,2)) as varchar(30))+ '' MB''
ELSE
cast(eqmg.granted_memory_kb as varchar(30))+ '' KB''
END AS [granted_mem]
,CASE
WHEN ([dtdt].[TLogBytesUsed]) > 1073741824000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' TB''
WHEN ([dtdt].[TLogBytesUsed]) > 1048576000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' GB''
WHEN ([dtdt].[TLogBytesUsed]) > 1024000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' MB''
WHEN ([dtdt].[TLogBytesUsed]) > 1000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' KB''
ELSE CAST(CAST((([dtdt].[TLogBytesUsed]) ) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' B''
END as [TLogBytesUsed]
,'+case WHEN @V1 >= 11 THEN N'format(try_cast(eqmg.query_cost as decimal(38,0)),''#,###'')' else 'cast(eqmg.query_cost as decimal(38,0))' END +' AS [estQueryCost]
,cast((cast(coalesce(r.logical_reads,s.logical_reads) as bigint)*8)/1024.0/1024. as decimal(18,2)) as [LogicalReads(GB)]
,CASE
WHEN (r.logical_reads IS NOT NULL AND r.reads IS NOT NULL AND r.logical_reads <> 0)
THEN CAST(
(1-(CAST(r.reads as decimal)/cast(r.logical_reads as decimal)))*100
as decimal(18,2))
WHEN s.logical_reads <> 0
THEN CAST((1-(CAST(s.reads as decimal)/cast(s.logical_reads as decimal)))*100 as decimal(18,2))
ELSE
NULL
END AS [BPHitRate]
,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_duration_ms, r.wait_time) ELSE r.wait_time END AS [wait_time(ms)]
,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_type, r.wait_type)+''*'' ELSE r.wait_type END AS [wait_type]
,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_resource, r.wait_resource) ELSE r.wait_resource END AS [wait_resource]'+
case when @V1 >= 13
THEN N'
,SesWaits.wait_type + '' (''+CAST(SesWaits.wait_time_ms as varchar(10))+'')'' as [BiggestSessionWait (ms)]'
ELSE N''
END +N'
,nullif(r.blocking_session_id, 0) AS [Blocker]
,r.last_wait_type
,r.command
,CASE
WHEN s.transaction_isolation_level=1 THEN ''*Read Uncommitted*''
WHEN s.transaction_isolation_level=2 THEN ''Read Committed''
WHEN s.transaction_isolation_level=3 THEN ''*Repeatable Read*''
WHEN s.transaction_isolation_level=4 THEN ''*Serializable*''
WHEN s.transaction_isolation_level=5 THEN ''*Snapshot*''
ELSE ''Unspecified''
END AS [Transaction Isolation]
,c.auth_scheme
,c.net_transport
,c.encrypt_option
,CASE
WHEN at.transaction_state=0 THEN ''The transaction has not been completely initialized yet.''
WHEN at.transaction_state=1 THEN ''The transaction has been initialized but has not started.''
WHEN at.transaction_state=2 THEN ''The transaction is active.''
WHEN at.transaction_state=3 THEN ''The transaction has ended. This is used for read-only transactions.''
WHEN at.transaction_state=4 THEN ''The commit process has been initiated on the distributed transaction. This is for DTs only. The DT is still active but further processing cannot take place.''
WHEN at.transaction_state=5 THEN ''The transaction is in a prepared state and waiting resolution.''
WHEN at.transaction_state=6 THEN ''The transaction has been committed.''
WHEN at.transaction_state=7 THEN ''The transaction is being rolled back.''
WHEN at.transaction_state=8 THEN ''The transaction has been rolled back.''
ELSE ''Unspecified''
END AS [Transaction_State_desc]
,at.transaction_begin_time
,coalesce(st.open_transaction_count ,0) as OpenTranCount
,case WHEN st.is_local =1
THEN cast(at.transaction_id as varchar(50))
ELSE cast(at.transaction_id as varchar(50))+''*''
END AS transaction_id
,N''(''+cast('+
case when @V1 > 11
THEN N'FORMAT('
ELSE N''
END +
N'r.statement_start_offset'+
CASE WHEN @V1 > 11
THEN N', ''#,###'')'
ELSE N''
END +
N' as nvarchar(max))+N'') ''+txt.text AS [BatchExecuting]
,'+CASE WHEN
(
(@V1 >= 13)
OR (@V1 = 12
AND (
(@V2 > 0)
OR (@V2=0 AND @V3 >= 5000)
)
)
) --13 or > 12.0.5000
THEN N'buf.event_info'
ELSE N'''DBCC INPUTBUFFER(''+cast(s.session_id as varchar(10))+'');'''
END +N'
AS [InputBuffer]
'+CASE WHEN ((@V1 >= 13) OR (@V1 = 12 AND ((@V2 > 0) OR (@V2=0 AND @V3 >= 5000)))) --13 or > 12.0.5000
THEN N',buf.parameters AS BufParameters'
ELSE N''
END +N'
--Greg Larsen
,SUBSTRING(txt.text,
CASE WHEN r.statement_start_offset = 0
OR r.statement_start_offset IS NULL
THEN 1
ELSE r.statement_start_offset/2 + 1 END,
CASE WHEN r.statement_end_offset = 0
OR r.statement_end_offset = -1
OR r.statement_end_offset IS NULL
THEN LEN(txt.text)
ELSE r.statement_end_offset/2 END -
CASE WHEN r.statement_start_offset = 0
OR r.statement_start_offset IS NULL
THEN 1
ELSE r.statement_start_offset/2 END + 2
) AS [Statement]
,qs.creation_time as [PlanCompiled]
,CASE
WHEN r.plan_handle =0x0
THEN ''''
ELSE ''DBCC FREEPROCCACHE (''+convert(varchar(200),r.plan_handle,1)+'');''
END as [RemovePlanFromCacheCmd]
'+case when @SkipQP=0 THEN ' ,qp.query_plan' ELSE '' END +'
,[txt2].text AS [MostRecentQuery]
,s.login_time
,CASE c.endpoint_id
WHEN 1
THEN 1
ELSE 0
END AS [isDAC]
,quotename(db_name(txt.dbid))+''.''+quotename(object_schema_name(txt.objectid,cast(txt.dbid as int)))+''.''+quotename(object_name(txt.objectid,cast(txt.dbid as int))) AS [ObjectName]
,s.row_count as [RowsReturnedLastRequest]
,r.row_count as [RequestRowCount]
,m.text as [LastErrorEncountered]
,wlg.name AS [WorkloadGroup]
,rp.name AS [ResourcePool]
,s.client_interface_name
,net_packet_size
,CASE
WHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1073741824
THEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)/1073741824.0 as decimal(18,2)) as varchar(30))+ '' GB''
WHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1048576
THEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)/1048576.0 as decimal(18,2)) as varchar(30))+ '' MB''
WHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1024
THEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)/1024.0 as decimal(18,2)) as varchar(30))+ '' KB''
ELSE
cast((cast(c.num_reads as bigint)*net_packet_size) as varchar(30))+ '' B''
END AS [NetworkBytesIngress]
,CASE
WHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1073741824
THEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)/1073741824.0 as decimal(18,2)) as varchar(30))+ '' GB''
WHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1048576
THEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)/1048576.0 as decimal(18,2)) as varchar(30))+ '' MB''
WHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1024
THEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)/1024.0 as decimal(18,2)) as varchar(30))+ '' KB''
ELSE
cast((cast(c.num_writes as bigint)*net_packet_size) as varchar(30))+ '' B''
END AS [NetworkBytesEgress]
,s.last_request_start_time
,getdate() as [TimeRun]
from sys.dm_exec_sessions [s]
LEFT JOIN sys.dm_exec_requests [r]
ON s.session_id=r.session_id
OUTER APPLY (
SELECT top 1 *
FROM sys.dm_exec_connections con
where s.session_id=con.session_id
--Grab a connection even if we don''t have a corresponding request
and (r.connection_id=con.connection_id OR r.connection_id is null)
--In that case, pick any connection related to this session that has a SQL handle... no good way to determine most recent
ORDER BY CASE WHEN con.most_recent_sql_handle = 0x0 THEN 0 ELSE 1 END DESC
) AS c
OUTER APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS [txt2]
left JOIN sys.dm_exec_query_stats AS [qs]
ON qs.plan_handle=r.plan_handle
AND r.statement_start_offset=qs.statement_start_offset
AND r.statement_end_offset=qs.statement_end_offset
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS [txt]
'+case when @SkipQP=0 THEN '
--Including exec_query_plan can cause this query to be blocked
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS [qp]' ELSE '' END+ '
LEFT JOIN sys.dm_tran_session_transactions [st]
ON s.session_id=st.session_id
LEFT JOIN sys.dm_tran_active_transactions [at]
ON st.transaction_id=at.transaction_id
outer apply(SELECT TOP 1 [database_transaction_status2] FROM sys.dm_tran_database_transactions dbt WHERE st.transaction_id=dbt.transaction_id and database_transaction_status2=258) dbt
LEFT JOIN sys.dm_resource_governor_workload_groups wlg
on s.group_id=wlg.group_id
LEFT JOIN sys.dm_resource_governor_resource_pools rp
on wlg.pool_id=rp.pool_id
LEFT JOIN sys.messages m
on s.prev_error=m.message_id
AND language_id=1033 --English
OUTER APPLY (SELECT TOP 1 wait_type ,wait_duration_ms,wait_resource from sys.dm_os_waiting_tasks a where a.session_id=s.session_id ORDER BY CASE WHEN wait_type=''CXPACKET'' THEN 2 when wait_type=''SLEEP_TASK'' THEN 1 ELSE 0 END, wait_duration_ms desc) ParallelWait
LEFT JOIN sys.dm_exec_query_memory_grants eqmg
ON s.session_id=eqmg.session_id
AND r.request_id=eqmg.request_id
OUTER APPLY (SELECT sum(database_transaction_log_bytes_used) as TLogBytesUsed FROM sys.dm_tran_database_transactions tr WHERE tr.transaction_id= at.transaction_id) AS dtdt
'
+CASE WHEN ((@V1 >= 13) OR (@V1 = 12 AND ((@V2 > 0) OR (@V2=0 AND @V3 >= 5000)))) --13 or > 12.0.5000
THEN N'
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) buf'
ELSE N''
END+
CASE WHEN @V1>=13
THEN N'
--Biggest wait type (by time) besides CXPACKET
OUTER APPLY (SELECT top 1 * FROM sys.dm_exec_session_wait_stats desws WHERE desws.session_id=s.session_id AND wait_time_ms > 0 ORDER BY case when wait_type=''CXPACKET'' THEN 1 ELSE 0 END, wait_time_ms desc) SesWaits'
ELSE N''
END+
N'
OUTER APPLY(select top 1 1 AS LeadBlocker from sys.dm_exec_requests r2 where s.session_id=r2.blocking_session_id) LB
WHERE 1=1
AND (s.is_user_process = 1 OR command IN (''DB STARTUP'', ''ROLLBACK TRANSACTION'',''ALTER DATABASE ENCRYPTION KEY''))
AND s.session_id <> @@SPID
AND coalesce(s.program_name, '''') NOT LIKE ''%IntelliSense''
--AND transaction_begin_time is not null
AND ((r.wait_type IS NULL) OR (r.wait_Type <> ''SP_SERVER_DIAGNOSTICS_SLEEP''))'+
CASE WHEN @ShowSleepingThreads=0 THEN N'
AND ((s.status <> ''sleeping'') OR(transaction_state IS NOT NULL) OR command IN (''DB STARTUP'', ''ROLLBACK TRANSACTION'',''ALTER DATABASE ENCRYPTION KEY''))'
ELSE N'' END+N'
--AND s.login_name LIKE ''%%''
--AND '+CASE WHEN @V1 >= 11 THEN N'DB_NAME(COALESCE(r.database_id, s.database_id))' ELSE N'DB_NAME(r.database_id)'END+' LIKE ''%%''
ORDER BY
--transaction_begin_time asc,
dbt.database_transaction_status2 asc,
s.status asc,
CASE when r.status=''running'' THEN 1
WHEN r.status=''runnable'' THEN 2
WHEN r.status=''suspended'' THEN 3
ELSE 4 END,
CASE WHEN s.status=''sleeping''
THEN s.session_id
ELSE -1
END DESC,
s.last_request_start_time desc
'
exec sp_executesql @sql
As a fun aside, it is possible to create this as a stored procedure, possibly with with filters (like limiting it to seeing sessions from a specific login), and allow somebody rights to execute just your pre-defined version of this query. This can be done without granting them the broad VIEW SERVER STATE, and can be accomplished through code-signing. While this is out-of-scope for this blog post, I may do one on that topic in the future.
The full script can be found here: SessionsAndRequests.txt.
Please let me know if you found this script useful or if you have any suggestions to improve it.
LLAP,
Ryan
As always, please note the waive of liability published on the About page.
The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee. You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.