{"id":104,"date":"2009-07-02T13:38:24","date_gmt":"2009-07-02T17:38:24","guid":{"rendered":"http:\/\/pattersoncprod.wpengine.com\/?p=104"},"modified":"2009-07-02T13:38:24","modified_gmt":"2009-07-02T17:38:24","slug":"how-to-identify-slow-running-queries-in-sql-server","status":"publish","type":"post","link":"https:\/\/pattersonc.com\/?p=104","title":{"rendered":"How to identify slow running queries in Sql Server"},"content":{"rendered":"<p>Recently, I was trying to pin point the queries in an application that were causing Sql Server to spike the CPU at 100%. This query was helpful in finding the top offenders.<\/p>\n<pre class=\"brush: csharp;\">SELECT  creation_time\n        ,last_execution_time\n        ,total_physical_reads\n        ,total_logical_reads\n        ,total_logical_writes\n        ,execution_count\n        ,total_worker_time\n        ,total_elapsed_time\n        ,total_elapsed_time \/ execution_count avg_elapsed_time\n        ,SUBSTRING(st.text, (qs.statement_start_offset\/2) + 1\n        ,((CASE statement_end_offset\n          WHEN -1 THEN DATALENGTH(st.text)\n          ELSE qs.statement_end_offset END\n          - qs.statement_start_offset)\/2) + 1) AS statement_text\nFROM sys.dm_exec_query_stats AS qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st\nORDER BY total_elapsed_time \/ execution_count DESC;<\/pre>\n<p>&#160;<\/p>\n<p><span><span>See this <\/span><a href=\"http:\/\/www.sql-server-performance.com\/articles\/per\/tsql_statement_performance_p1.aspx\" mce_href=\"http:\/\/www.sql-server-performance.com\/articles\/per\/tsql_statement_performance_p1.aspx\">post <\/a><span>for more information.<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently, I was trying to pin point the queries in an application that were causing Sql Server to spike the CPU at 100%. This query was helpful in finding the top offenders. SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes ,execution_count ,total_worker_time ,total_elapsed_time ,total_elapsed_time \/ execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset\/2) + 1 ,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_block_theme_hide_title":false,"footnotes":""},"categories":[3],"tags":[8,9],"class_list":{"0":"post-104","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"hentry","6":"category-code","7":"tag-performance","8":"tag-sqlserver","10":"without-featured-image"},"featured_image_src":null,"featured_image_src_square":null,"author_info":{"display_name":"Christopher Patterson","author_link":"https:\/\/pattersonc.com\/?author=1"},"_links":{"self":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=104"}],"version-history":[{"count":0,"href":"https:\/\/pattersonc.com\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions"}],"wp:attachment":[{"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pattersonc.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}