<\/p>\n\n\n\n
sp_whoisactive: https:\/\/www.brentozar.com\/responder\/log-sp_whoisactive-to-a-table\/\n\nCPU Real-Time SQL Utilization\n\nEXEC dbo.sp_WhoIsActive\n@get_transaction_info=0,\n@output_column_list ='[session_id][start_time]\n [cpu][status][context_switches][wait_info][program_name]\n [database_name][sql_text][host_name][open_tran_count]', \n@sort_order='[CPU]DESC'\n\n\/*delta*\/\nEXEC dbo.sp_WhoIsActive\n@delta_interval=5, @get_task_info = 2,\n@output_column_list ='[session_id][start_time][context switches]\n [CPU_delta][reads_delta][writes_delta][tempdb_writes_delta]\n [tempdb_reads_delta][tempdb_current_delta]\n [database_name][host_name][login_name]', \n@sort_order='[CPU_delta]DESC'\n\nQuery History Log:\n\nSELECT --TOP 1000 \n[dd hh:mm:ss.mss]\n ,[session_id]\n ,[sql_text]\n ,[sql_command]\n ,[login_name]\n ,[wait_info]\n ,[CPU]\n ,[tempdb_allocations]\n ,[tempdb_current]\n ,[blocking_session_id]\n ,[blocked_session_count]\n ,[reads]\n ,[writes]\n ,[physical_reads]\n ,[used_memory]\n ,[status]\n ,[open_tran_count]\n ,[percent_complete]\n ,[host_name]\n ,[database_name]\n ,[program_name]\n ,[start_time]\n ,[login_time]\n ,[request_id]\n ,[collection_time]\n FROM [dbwhoisactive].[dbo].[tablelogwhoisactive]\n ORDER BY [collection_time] DESC\n\nSimplified CPU Real-Time SQL Utilization:\n\nselect\n r.session_id,\n r.cpu_time,\n ((r.total_elapsed_time\/1000.0)\/60.0) as MinutesRunning,\n s.login_name,\n c.client_net_address,\n s.program_name\n ,sjj.name as Job\n ,sj.step_name as 'Step Job'\n ,st.text as query\nfrom sys.dm_exec_requests r\ninner join sys.dm_exec_sessions s\non r.session_id = s.session_id\nleft join sys.dm_exec_connections c\non r.session_id = c.session_id\nouter apply sys.dm_exec_sql_text(r.sql_handle) st\nleft JOIN msdb.dbo.sysjobsteps sj\n ON SUBSTRING(s.[program_name],30,34) =\n CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1)\nleft JOIN msdb.dbo.sysjobs sjj\nON sj.job_id = sjj.job_id\nwhere s.login_name <> 'sa' \nand r.session_id <> @@spid\norder by r.cpu_time desc, ((r.total_elapsed_time\/1000.0)\/60.0) desc\n\nBlocking Sessions SQL:\n\nSELECT DISTINCT \n[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),\n[Host Name Block] = ISNULL(ws.host_name, N''), \n[Login Block] = ws.login_name, \n[Database Block] = ISNULL(db_name(wp.dbid), N''), \n[Application Block] = ISNULL(ws.program_name, N''),\n[Session ID] = s.session_id,\n[Host Name] = ISNULL(s.host_name, N''), \n[Login] = s.login_name, \n[Database] = ISNULL(db_name(p.dbid), N''), \n[Application] = ISNULL(s.program_name, N''),\n[SQL block] = ISNULL(wst.text,''),\n[SQL] = ISNULL(st.text,'')\nFROM sys.dm_exec_sessions s\nLEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) \nLEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id) \nLEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id\nAND r.request_id = t.request_id) \nLEFT OUTER JOIN ( \nSELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num \nFROM sys.dm_os_waiting_tasks ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 \nLEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)\nLEFT OUTER JOIN sys.dm_exec_sessions ws ON w.session_id=ws.session_id\nLEFT OUTER JOIN sys.sysprocesses wp ON (ws.session_id = wp.spid)\nLEFT OUTER JOIN sys.dm_exec_requests wr ON (ws.session_id = wr.session_id)\nouter apply sys.dm_exec_sql_text(r.sql_handle) st\nouter apply sys.dm_exec_sql_text(wr.sql_handle) wst\nWHERE NOT w.blocking_session_id IS NULL\nORDER BY 1 desc;<\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_seo_schema_type":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[15],"tags":[24,21],"class_list":["post-87","post","type-post","status-publish","format-standard","hentry","category-notes","tag-performance-analysis","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/notbotnotes.com\/index.php?rest_route=\/wp\/v2\/posts\/87","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/notbotnotes.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/notbotnotes.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/notbotnotes.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/notbotnotes.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=87"}],"version-history":[{"count":0,"href":"https:\/\/notbotnotes.com\/index.php?rest_route=\/wp\/v2\/posts\/87\/revisions"}],"wp:attachment":[{"href":"https:\/\/notbotnotes.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/notbotnotes.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/notbotnotes.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}