{"id":33386,"date":"2023-04-11T09:42:02","date_gmt":"2023-04-11T09:42:02","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=33386"},"modified":"2023-04-11T09:42:05","modified_gmt":"2023-04-11T09:42:05","slug":"sql-server-queries-to-gather-information-about-the-workload","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/sql-server-queries-to-gather-information-about-the-workload\/","title":{"rendered":"SQL SERVER queries to gather information about the workload"},"content":{"rendered":"\n

To get the top queries that are taking the longest time to execute:<\/strong><\/p>\n\n\n

SELECT TOP 50<\/span>\n[rs].[sql_handle],\n[rs].[execution_count],\n[rs].[total_worker_time] AS<\/span> [Total CPU Time],\n[rs].[total_elapsed_time] AS<\/span> [Total Elapsed Time],\n[rs].[total_logical_reads] AS<\/span> [Total Logical Reads],\n[rs].[total_logical_writes] AS<\/span> [Total Logical Writes],\n[st].[text] AS<\/span> [Query Text]\nFROM\nsys.dm_exec_query_stats AS<\/span> [rs]\nCROSS APPLY sys.dm_exec_sql_text([rs].[sql_handle]) AS<\/span> [st]\nWHERE\n[rs].[total_elapsed_time] >= 1000<\/span> -- Show only queries that have taken more than 1<\/span> second to execute\nORDER BY\n[rs].[total_elapsed_time] DESC;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
<\/p>\n\n\n\n
To get the top resource-intensive queries:<\/strong><\/p>\n\n\n
SELECT TOP 50<\/span>\n[rs].[sql_handle],\n[rs].[execution_count],\n[total_worker_time] AS<\/span> [Total CPU Time],\n[total_logical_reads] AS<\/span> [Total Logical Reads],\n[total_logical_writes] AS<\/span> [Total Logical Writes],\n[total_physical_reads] AS<\/span> [Total Physical Reads],\n[total_elapsed_time] AS<\/span> [Total Elapsed Time],\n[query_plan],\n[st].[text] AS<\/span> [Query Text]\nFROM\nsys.dm_exec_query_stats [rs]\nCROSS APPLY\nsys.dm_exec_sql_text([rs].[sql_handle]) [st]\nCROSS APPLY\nsys.dm_exec_query_plan([rs].[plan_handle]) [qp]\nWHERE\n[rs].[total_elapsed_time] >= 1000<\/span> -- Show only queries that have taken more than 1<\/span> second to execute\nORDER BY\n[total_worker_time] DESC;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
<\/p>\n\n\n\n
To get the top wait types:<\/strong><\/p>\n\n\n
SELECT TOP 10<\/span>\nwait_type,\nwait_time_ms \/ 1000.0<\/span> AS<\/span> wait_time_seconds,\nwait_time_ms \/ (1000.0<\/span> * 60.0<\/span>) AS<\/span> wait_time_minutes,\nwait_time_ms \/ (1000.0<\/span> * 60.0<\/span> * 60.0<\/span>) AS<\/span> wait_time_hours,\n(wait_time_ms \/ 1000.0<\/span>) \/ (wait_time_ms \/ 1000.0<\/span> + signal_wait_time_ms \/ 1000.0<\/span>) AS<\/span> wait_time_percent,\nwaiting_tasks_count,\nwait_time_ms,\nsignal_wait_time_ms\nFROM\nsys.dm_os_wait_stats\nWHERE\nwait_type NOT LIKE '%SLEEP%'<\/span> -- Exclude the SLEEP wait type\nAND<\/span> wait_type NOT LIKE 'CLR_%'<\/span> -- Exclude CLR wait types\nAND<\/span> wait_type NOT LIKE 'BROKER_%'<\/span> -- Exclude Broker wait types\nAND<\/span> wait_type NOT LIKE 'XE_%'<\/span> -- Exclude Extended Events wait types\nORDER BY\nwait_time_ms DESC;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>","protected":false},"excerpt":{"rendered":"
To get the top queries that are taking the longest time to execute: To get the top resource-intensive queries: To get the top wait types:<\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[5197,6989],"class_list":["post-33386","post","type-post","status-publish","format-standard","hentry","category-uncategorised","tag-azure","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/33386","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=33386"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/33386\/revisions"}],"predecessor-version":[{"id":33387,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/33386\/revisions\/33387"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=33386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=33386"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=33386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}