{"id":224,"date":"2019-06-25T16:05:51","date_gmt":"2019-06-25T21:05:51","guid":{"rendered":"http:\/\/blog.sqlsnee.com\/?p=224"},"modified":"2020-02-18T18:31:03","modified_gmt":"2020-02-19T00:31:03","slug":"whats-happening-in-sql-server","status":"publish","type":"post","link":"https:\/\/blog.sqlsnee.com\/?p=224","title":{"rendered":"What’s Happening in SQL Server?"},"content":{"rendered":"\n

One of the most important tools for your toolbox is some mechanism to see what’s happening in the engine. Who <\/em>is running what <\/em>in my system, and how is it behaving<\/em>? 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. <\/p>\n\n\n\n

There are many other fantastic<\/em> tools for such diagnostics… Off the top of my head, I’d recommend looking at sp_Blitz<\/a> from Brent Ozar and team or sp_WhoIsActive<\/a> from Adam Machanic. I have not used them a lot myself, but I’ve heard nothing but great things about each tool.<\/p>\n\n\n\n

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):<\/p>\n\n\n\n

  1. It’s been a great learning experience to dive in and discover SQL’s DMVs<\/li>
  2. It’s extremely customized for my needs (what can I say- I like what I like)<\/li>
  3. 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<\/li><\/ol>\n\n\n\n

    I believe the only permission you’ll need on your SQL instance is VIEW SERVER STATE. It should also be compatible with at least <\/em>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.<\/p>\n\n\n\n

    Keep in mind, I never claimed this code was pretty \ud83d\ude09<\/p>\n\n\n\n

    Without further ado, here it is:<\/p>\n\n\n\n

    \/*\nWritten by: Ryan Snee (http:\/\/www.sqlsnee.com)\n\n*\/\n\nDECLARE @ShowSleepingThreads BIT;\nDECLARE @SkipQP BIT;\nDECLARE @V  varchar(50);\nDECLARE @V1 INT;\nDECLARE @V2 INT;\nDECLARE @V3 INT;\n\n\n\n\/*<Setable Parameters>*\/\nSET @ShowSleepingThreads=0;\nSET @SkipQP=0;\t\t\t\t\t--Sometimes this query will be blocked when querying the sys.dm_exec_query_plan DMF\n\/*<\/Setable Parameters>*\/\n\n\n\n\nselect @V=cast(SERVERPROPERTY ('ProductVersion')  as varchar(50));\n\nSELECT @V1=CAST(substring(@V,1,charindex('.',@V)-1) as int)\n,@V2=CAST(substring(@V,charindex('.',@V)+1,(charindex('.',@V,charindex('.',@V)+1))-(charindex('.',@V)+1)) as int)\n,@V3=CAST(substring(@V,\tcharindex('.',@V,CHARINDEX('.',@V)+1)+1,(charindex('.',@V,charindex('.',@V,CHARINDEX('.',@V)+1)+1))-(charindex('.',@V,CHARINDEX('.',@V)+1)+1)) as int);\n\n\ndeclare @sql nvarchar(max);\nSET @sql= CAST('' AS nvarchar(max))\n+N'SELECT\n\tcast(s.session_id  as varchar(10))\n\t\t+ case when (r.blocking_session_id=0 or r.blocking_session_id is null) and LB.LeadBlocker=1 then ''*'' else '''' end\n\tAS [Session]\n\t,'\n\t+CASE\n\tWHEN @V1 >= 11 THEN N'concat(case when dbt.database_transaction_status2=258 then ''ROLLBACK'' ELSE s.status END,'':''+r.status)'\n\tELSE 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!!!' \n\tEND+N' as [Status]\n\t\n\t\n\t,CASE\n\t\n\t\tWHEN ((datediff(day,s.last_request_start_time,getdate())>28) AND (r.session_id IS NOT NULL))\n\t\t\tTHEN cast(datediff(day,s.last_request_start_time,getdate()) as varchar(50))+''d+''\n\t\tWHEN [r].[total_elapsed_time] > 86400000 --Days, hours, and minutes\n\t\t\tTHEN 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''\n\t\tWHEN [r].[total_elapsed_time] > 3600000 --Hours, minutes and seconds\n\t\t\tTHEN 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''\n\t\tWHEN [r].[total_elapsed_time] > 60000 --Minutes, seconds, and milliseconds\n\t\t\tTHEN 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''\n\t\tWHEN [r].[total_elapsed_time] > 1000 --Seconds and milliseconds\n\t\t\tTHEN cast([r].[total_elapsed_time]\/1000 as varchar(50))+''s ''+cast(([r].[total_elapsed_time])%1000 as varchar(50))+''ms''\n\t\tELSE cast([r].[total_elapsed_time] as varchar(50))+''ms'' --Milliseconds\n\n\tEND AS [ElapsedTime]\n\t\n\t\n\t\n\t,coalesce(nullif(s.host_name+'' '',''''),''(''+c.client_net_address+'')'') as Client\n\t\n\t,\n\n\tcase\n\t\twhen s.login_name <> s.original_login_name\n\t\t\tTHEN s.login_name +'' '' +quotename(s.original_login_name ,''('')\n\t\tELSE s.login_name\n\tEND AS [login_name]\n\t\n\t,coalesce(s.program_name,'''')+ coalesce('' (PID:''+cast(s.host_process_id as varchar(20))+'')'','''') as [ClientProgram]\n\t\n\t,'+CASE\n\tWHEN @V1 >= 11 THEN N'DB_NAME(COALESCE(r.database_id, s.database_id))'\n\tELSE N'DB_NAME(r.database_id)'\n\tEND+N' AS [Database Name]\n\t\n\t\n\t,nullif(r.percent_complete,0) AS [%Complete]\n\t\n\n\t\n\n\t,nullif(\n\t\tcase\n\t\t\tWHEN(\n\t\t\t\tselect count(distinct scheduler_id)\n\t\t\t\tFROM sys.dm_os_tasks ost\n\t\t\t\twhere r.request_id=ost.request_id and s.session_id=ost.session_id\n\t\t\t\t--and ost.task_state = ''RUNNING''\n\t\t\t\tand scheduler_id < 1000000\n\t\t\t) > 1\n\t\t\tTHEN(\n\t\t\t\tselect count(distinct scheduler_id)\n\t\t\t\tFROM sys.dm_os_tasks ost\n\t\t\t\twhere r.request_id=ost.request_id and s.session_id=ost.session_id\n\t\t\t\t--and ost.task_state = ''RUNNING''\n\t\t\t\tand scheduler_id < 1000000\n\t\t\t)-1\n\t\t\tELSE(\n\t\t\t\tselect count(distinct scheduler_id)\n\t\t\t\tFROM sys.dm_os_tasks ost\n\t\t\t\twhere r.request_id=ost.request_id and s.session_id=ost.session_id\n\t\t\t\t--and ost.task_state = ''RUNNING''\n\t\t\t\tand scheduler_id < 1000000\n\t\t\t)\n\t\tEND\n\t,0) as [Tasks]\n\t,'+CASE\n\tWHEN @V1 >= 11 THEN N'format(coalesce(r.cpu_time,s.cpu_time),''#,###'')'\n\tELSE N'coalesce(r.cpu_time,s.cpu_time)'\n\tEND+N' AS [CPU(ms)]\n\t\n\t\n\t,CASE\n\t\tWHEN eqmg.granted_memory_kb >= 1048576\n\t\t\tTHEN cast(cast(eqmg.granted_memory_kb\/1048576.0 as decimal(18,2)) as varchar(30))+ '' GB''\n\t\tWHEN eqmg.granted_memory_kb >= 1024\n\t\t\tTHEN cast(cast(eqmg.granted_memory_kb\/1024.0 as decimal(18,2)) as varchar(30))+ '' MB''\n\t\tELSE\n\t\t\tcast(eqmg.granted_memory_kb as varchar(30))+ '' KB''\n\t END AS [granted_mem]\n\t,CASE\n\t\tWHEN ([dtdt].[TLogBytesUsed]) > 1073741824000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) \/ 1024.0\/1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' TB''\n\t\tWHEN ([dtdt].[TLogBytesUsed]) > 1048576000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) \/ 1024.0\/1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' GB''\n\t\tWHEN ([dtdt].[TLogBytesUsed]) > 1024000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) \/ 1024.0\/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' MB''\n\t\tWHEN ([dtdt].[TLogBytesUsed]) > 1000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) \/ 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' KB''\n\t\tELSE CAST(CAST((([dtdt].[TLogBytesUsed]) ) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' B''\n\tEND as [TLogBytesUsed]\n\n,'+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]\t\n\t\n\n\t\n\n\n\t,cast((cast(coalesce(r.logical_reads,s.logical_reads) as bigint)*8)\/1024.0\/1024. as decimal(18,2)) as [LogicalReads(GB)]\n\t,CASE\n\tWHEN (r.logical_reads IS NOT NULL AND r.reads IS NOT NULL AND r.logical_reads <> 0)\n\t\tTHEN CAST(\n\t\t\t(1-(CAST(r.reads as decimal)\/cast(r.logical_reads as decimal)))*100\n\t\t\n\t\tas decimal(18,2))\n\tWHEN s.logical_reads <> 0\n\t\tTHEN CAST((1-(CAST(s.reads as decimal)\/cast(s.logical_reads as decimal)))*100 as decimal(18,2))\n\tELSE\n\t\tNULL\n\tEND AS [BPHitRate]\n\t\n\t\n\n\t,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_duration_ms, r.wait_time)  ELSE r.wait_time END AS [wait_time(ms)]\n\t,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_type, r.wait_type)+''*''  ELSE r.wait_type END AS [wait_type]\n\t,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_resource, r.wait_resource)  ELSE r.wait_resource END AS [wait_resource]'+\n\tcase when @V1 >= 13\n\tTHEN N'\n\t,SesWaits.wait_type + '' (''+CAST(SesWaits.wait_time_ms as varchar(10))+'')'' as [BiggestSessionWait (ms)]'\n\tELSE N''\n\tEND +N'\n\n\n\n\n\t,nullif(r.blocking_session_id, 0) AS [Blocker]\n\t,r.last_wait_type\n\t\n\t\n\t\n\t,r.command\n\n\n\n\t,CASE\n\t\tWHEN s.transaction_isolation_level=1 THEN ''*Read Uncommitted*''\n\t\tWHEN s.transaction_isolation_level=2 THEN ''Read Committed''\n\t\tWHEN s.transaction_isolation_level=3 THEN ''*Repeatable Read*''\n\t\tWHEN s.transaction_isolation_level=4 THEN ''*Serializable*''\n\t\tWHEN s.transaction_isolation_level=5 THEN ''*Snapshot*''\n\t\tELSE ''Unspecified''\n\tEND AS [Transaction Isolation]\n\t\n\t,c.auth_scheme\n\t,c.net_transport\n\t,c.encrypt_option\n\t\n\t,CASE\n\t\tWHEN at.transaction_state=0 THEN ''The transaction has not been completely initialized yet.''\n\t\tWHEN at.transaction_state=1 THEN ''The transaction has been initialized but has not started.''\n\t\tWHEN at.transaction_state=2 THEN ''The transaction is active.''\n\t\tWHEN at.transaction_state=3 THEN ''The transaction has ended. This is used for read-only transactions.''\n\t\tWHEN 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.''\n\t\tWHEN at.transaction_state=5 THEN ''The transaction is in a prepared state and waiting resolution.''\n\t\tWHEN at.transaction_state=6 THEN ''The transaction has been committed.''\n\t\tWHEN at.transaction_state=7 THEN ''The transaction is being rolled back.''\n\t\tWHEN at.transaction_state=8 THEN ''The transaction has been rolled back.''\n\t\tELSE ''Unspecified''\n\tEND AS [Transaction_State_desc]\n\n\t\n\t\n\t,at.transaction_begin_time\n\t,coalesce(st.open_transaction_count ,0) as OpenTranCount\n\t,case WHEN st.is_local =1\n\tTHEN cast(at.transaction_id as varchar(50))\n\tELSE cast(at.transaction_id as varchar(50))+''*''\n\tEND AS transaction_id\n\t\n\n\t\n\t,N''(''+cast('+\n\tcase when @V1 > 11\n\tTHEN N'FORMAT('\n\tELSE N''\n\tEND +\n\tN'r.statement_start_offset'+\nCASE WHEN @V1 > 11\nTHEN N', ''#,###'')'\nELSE N''\nEND +\n\n\tN' as nvarchar(max))+N'') ''+txt.text AS [BatchExecuting]\n\t\n\t,'+CASE WHEN\n\t\n\t(\n\t\t(@V1 >= 13)\n\t\tOR (@V1 = 12\n\t\t\tAND (\n\t\t\t\t(@V2 > 0)\n\t\t\t\tOR (@V2=0 AND  @V3 >= 5000)\n\t\t\t)\n\t\t)\n\t) --13 or > 12.0.5000\n\tTHEN N'buf.event_info'\n\tELSE N'''DBCC INPUTBUFFER(''+cast(s.session_id as varchar(10))+'');'''\n\tEND +N'\n\t AS [InputBuffer]\n\t\n\t'+CASE WHEN ((@V1 >= 13) OR (@V1 = 12 AND ((@V2 > 0) OR (@V2=0 AND  @V3 >= 5000)))) --13 or > 12.0.5000\n\tTHEN N',buf.parameters AS BufParameters'\n\tELSE N''\n\tEND +N'\n\t \n\n\t--Greg Larsen\n\t,SUBSTRING(txt.text,\n\t\tCASE WHEN r.statement_start_offset = 0\n\t\t\tOR r.statement_start_offset IS NULL\n\t\t\t\tTHEN 1\n\t\t\t\tELSE r.statement_start_offset\/2 + 1 END,\n\t\tCASE WHEN r.statement_end_offset = 0\n\t\t\t\tOR r.statement_end_offset = -1\n\t\t\t\tOR r.statement_end_offset IS NULL\n\t\t\t\t\tTHEN LEN(txt.text)\n\t\t\t\t\tELSE r.statement_end_offset\/2 END -\n\t\t \tCASE WHEN r.statement_start_offset = 0\n\t\t\t\tOR r.statement_start_offset IS NULL\n\t\t\t\t\tTHEN 1\n\t\t\t\t\tELSE r.statement_start_offset\/2 END + 2\n\t) AS [Statement]\n\t\n\t,qs.creation_time as [PlanCompiled]\n\t,CASE\n\t\tWHEN r.plan_handle =0x0\n\t\t\tTHEN ''''\n\t\tELSE ''DBCC FREEPROCCACHE (''+convert(varchar(200),r.plan_handle,1)+'');''\n\tEND as [RemovePlanFromCacheCmd]\n\t\n\t'+case when @SkipQP=0 THEN ' ,qp.query_plan' ELSE '' END +'\n\t\n\t,[txt2].text AS [MostRecentQuery]\n\t,s.login_time\n\t,CASE c.endpoint_id\n\t\tWHEN 1\n\t\tTHEN 1\n\t\tELSE 0\n\tEND AS [isDAC]\t\n\t\n\n\t\n\t\n\n\t,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]\n\t\n\t\n\t,s.row_count as [RowsReturnedLastRequest]\n\t,r.row_count as [RequestRowCount]\n\t,m.text as [LastErrorEncountered]\n\t\n\t,wlg.name AS [WorkloadGroup]\n\t,rp.name AS [ResourcePool]\n\t\n\t\n\t,s.client_interface_name\n\t,net_packet_size\n\t,CASE\n\t\tWHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1073741824\n\t\t\tTHEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)\/1073741824.0 as decimal(18,2)) as varchar(30))+ '' GB''\n\t\tWHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1048576\n\t\t\tTHEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)\/1048576.0 as decimal(18,2)) as varchar(30))+ '' MB''\n\t\tWHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1024\n\t\t\tTHEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)\/1024.0 as decimal(18,2)) as varchar(30))+ '' KB''\n\t\tELSE\n\t\t\tcast((cast(c.num_reads as bigint)*net_packet_size) as varchar(30))+ '' B''\n\t END AS [NetworkBytesIngress]\n\t\n\t,CASE\n\t\tWHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1073741824\n\t\t\tTHEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)\/1073741824.0 as decimal(18,2)) as varchar(30))+ '' GB''\n\t\tWHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1048576\n\t\t\tTHEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)\/1048576.0 as decimal(18,2)) as varchar(30))+ '' MB''\n\t\tWHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1024\n\t\t\tTHEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)\/1024.0 as decimal(18,2)) as varchar(30))+ '' KB''\n\t\tELSE\n\t\t\tcast((cast(c.num_writes as bigint)*net_packet_size) as varchar(30))+ '' B''\n\t END AS [NetworkBytesEgress]\n\t,s.last_request_start_time\n\t,getdate() as [TimeRun]\n\t\nfrom sys.dm_exec_sessions [s]\n\tLEFT JOIN sys.dm_exec_requests [r]\n\t\tON s.session_id=r.session_id\n\n\tOUTER APPLY (\n\t\tSELECT top 1 *\n\t\tFROM sys.dm_exec_connections con\n\t\twhere s.session_id=con.session_id\n\t\t--Grab a connection even if we don''t have a corresponding request\n\t\t\tand (r.connection_id=con.connection_id OR r.connection_id is null)\n\t\t--In that case, pick any connection related to this session that has a SQL handle... no good way to determine most recent\n\t\tORDER BY CASE WHEN con.most_recent_sql_handle = 0x0 THEN 0 ELSE 1 END DESC\n\t\t\n\t) AS c\n\t\t\n\tOUTER APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS [txt2]\n\t\n\tleft JOIN sys.dm_exec_query_stats AS [qs]\n\t\tON qs.plan_handle=r.plan_handle\n\t\t\tAND r.statement_start_offset=qs.statement_start_offset\n\t\t\tAND r.statement_end_offset=qs.statement_end_offset\n\tOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS [txt]\n\n\t'+case when @SkipQP=0 THEN '\n\t--Including exec_query_plan can cause this query to be blocked\n\tOUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS [qp]' ELSE '' END+ '\n\n\tLEFT JOIN sys.dm_tran_session_transactions [st]\n\t\tON s.session_id=st.session_id\n\tLEFT JOIN sys.dm_tran_active_transactions [at]\n\t\tON st.transaction_id=at.transaction_id\n\t\n\t\n\t\n\touter 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\n\t\n\tLEFT JOIN sys.dm_resource_governor_workload_groups wlg\n\t\ton s.group_id=wlg.group_id\n\tLEFT JOIN sys.dm_resource_governor_resource_pools rp\n\t\ton wlg.pool_id=rp.pool_id\n\n\tLEFT JOIN sys.messages m\n\t\ton s.prev_error=m.message_id\n\t\t\tAND language_id=1033 --English\n\t\n\tOUTER 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\n\tLEFT JOIN sys.dm_exec_query_memory_grants eqmg\n\t\tON s.session_id=eqmg.session_id\n\t\tAND r.request_id=eqmg.request_id\n\tOUTER 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\n\t'\n\t+CASE WHEN ((@V1 >= 13) OR (@V1 = 12 AND ((@V2 > 0) OR (@V2=0 AND  @V3 >= 5000)))) --13 or > 12.0.5000\n\tTHEN N'\n\tOUTER APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) buf'\n\tELSE N''\n\tEND+\n\n\tCASE WHEN @V1>=13\n\tTHEN N'\n\t--Biggest wait type (by time) besides CXPACKET\n\tOUTER 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'\n\tELSE N''\n\tEND+\n\t\n\n\tN'\n\tOUTER APPLY(select top 1 1 AS LeadBlocker from sys.dm_exec_requests r2 where s.session_id=r2.blocking_session_id) LB\nWHERE 1=1\n\tAND (s.is_user_process = 1 OR command IN (''DB STARTUP'', ''ROLLBACK TRANSACTION'',''ALTER DATABASE ENCRYPTION KEY''))\n\tAND s.session_id <> @@SPID\n\tAND coalesce(s.program_name, '''') NOT LIKE ''%IntelliSense''\n\t--AND transaction_begin_time is not null\n\tAND ((r.wait_type IS NULL) OR (r.wait_Type <> ''SP_SERVER_DIAGNOSTICS_SLEEP''))'+\n\t\tCASE WHEN @ShowSleepingThreads=0 THEN N'\n\tAND ((s.status <> ''sleeping'') OR(transaction_state IS NOT NULL) OR  command IN (''DB STARTUP'', ''ROLLBACK TRANSACTION'',''ALTER DATABASE ENCRYPTION KEY''))'\n\tELSE N'' END+N'\n\t--AND s.login_name LIKE ''%%''\n\t--AND '+CASE\tWHEN @V1 >= 11 THEN N'DB_NAME(COALESCE(r.database_id, s.database_id))'\tELSE N'DB_NAME(r.database_id)'END+' LIKE ''%%''\nORDER BY\n\t--transaction_begin_time asc,\n\tdbt.database_transaction_status2 asc,\n\ts.status asc,\n\tCASE when r.status=''running'' THEN 1\n\tWHEN r.status=''runnable'' THEN 2\n\tWHEN r.status=''suspended'' THEN 3\n\tELSE 4 END,\n\t\n\t\n\tCASE WHEN s.status=''sleeping''\n\t\tTHEN s.session_id\n\tELSE -1\n\tEND DESC,\n\ts.last_request_start_time desc\n\t'\n\n\t\n\nexec sp_executesql @sql\n\t<\/pre>\n\n\n\n
    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.<\/p>\n\n\n\n
    The full script can be found here: SessionsAndRequests.txt<\/a>.<\/p>\n\n\n\n
    Please let me know if you found this script useful or if you have any suggestions to improve it.<\/p>\n\n\n\n
    LLAP,
    Ryan<\/p>\n\n\n\n
    \n\n\n\n
    As always, please note the waive of liability<\/em> published on the About<\/a> page.<\/p>\n\n\n\n
    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<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 \u2026  Continue reading → <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[26,25],"class_list":["post-224","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-requests","tag-sessions"],"_links":{"self":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=224"}],"version-history":[{"count":10,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/224\/revisions"}],"predecessor-version":[{"id":250,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/224\/revisions\/250"}],"wp:attachment":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}