Often when a server seems to work slow or if I want to see what my store procedure is doing now, I use the “Running sessions” script.<\/p>\n\n\n\n
It helps me to get a fast overview of what are the SQL Server doing right now. Also blocking sessions are showed and if I uncomment the two commented lines, I can often get the SQL Plan which can help me to give a hint to an missing an index.<\/p>\n\n\n\n
USE master\nGO\n\nSELECT SPID = er.session_id\n ,STATUS = ses.STATUS\n ,[Login] = ses.login_name\n ,Host = ses.host_name\n ,BlkBy = er.blocking_session_id\n ,DBName = DB_Name(er.database_id)\n ,CommandType = er.command\n ,ObjectName = OBJECT_NAME(st.objectid)\n ,CPUTime = er.cpu_time\n ,StartTime = er.start_time\n ,TimeElapsed = CAST(GETDATE() - er.start_time AS TIME)\n ,SQLStatement = st.text\n FROM sys.dm_exec_requests er\n OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st\n LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id\n LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id\n WHERE st.text IS NOT NULL\n AND er.session_Id NOT IN (@@SPID)\n\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\n\nSELECT er.session_Id AS [Spid]\n ,sp.ecid\n ,er.start_time\n ,DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds]\n ,sp.nt_username\n ,er.status\n ,er.wait_type\n ,SUBSTRING (qt.text, (er.statement_start_offset\/2) + 1, ((\n CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2\n ELSE er.statement_end_offset END - er.statement_start_offset)\/2) + 1) AS [Individual Query]\n ,qt.text AS [Parent Query]\n ,sp.program_name\n ,sp.Hostname\n ,sp.nt_domain\n ,er.plan_handle\n-- ,qp.query_plan\n FROM sys.dm_exec_requests er\n JOIN sys.sysprocesses sp ON er.session_id = sp.spid\n CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt\n-- CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp\n WHERE session_Id > 50\n AND session_Id NOT IN (@@SPID)\n ORDER BY session_Id, ecid\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Often when a server seems to work slow or if I want to see what my store procedure is doing now, I use the “Running sessions” script. It helps me to get a fast overview of what are the SQL Server doing right now. Also blocking sessions are showed and if I uncomment the two… L\u00e6s mere Running sessions<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[7],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","tag-dm_exec_requests","entry"],"_links":{"self":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":2,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"predecessor-version":[{"id":58,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/posts\/56\/revisions\/58"}],"wp:attachment":[{"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oraclas.dk\/index.php\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}