{"id":1048,"date":"2016-01-20T09:07:47","date_gmt":"2016-01-20T17:07:47","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1048"},"modified":"2017-04-13T12:28:51","modified_gmt":"2017-04-13T19:28:51","slug":"sql-server-diagnostic-information-queries-detailed-day-20","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 20"},"content":{"rendered":"

For Day 20 of this series, we start out with Query #46<\/strong>, which is Query Execution Counts. This query retrieves information from the sys.dm_exec_query_stats<\/a> dynamic management view, the sys.dm_exec_sql_text<\/a> dynamic management function, and the sys.dm_exec_query_plan<\/a> dynamic management function about the most frequently executed cached queries in the current database. Query #46 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get most frequently executed queries for this database (Query 46) (Query Execution Counts)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> TOP<\/span>(50) LEFT<\/span>(t.[text][\/text], 50) AS<\/span> [Short Query Text], qs.execution_count AS<\/span> [Execution Count<\/span>],<\/pre>\n
<\/p>\n
   3:<\/span> qs.total_logical_reads AS<\/span> [Total Logical Reads<\/span>],<\/pre>\n
<\/p>\n
   4:<\/span> qs.total_logical_reads\/qs.execution_count AS<\/span> [Avg<\/span> Logical Reads<\/span>],<\/pre>\n
<\/p>\n
   5:<\/span> qs.total_worker_time AS<\/span> [Total Worker Time<\/span>],<\/pre>\n
<\/p>\n
   6:<\/span> qs.total_worker_time\/qs.execution_count AS<\/span> [Avg<\/span> Worker Time<\/span>], <\/pre>\n
<\/p>\n
   7:<\/span> qs.total_elapsed_time AS<\/span> [Total Elapsed Time<\/span>],<\/pre>\n
<\/p>\n
   8:<\/span> qs.total_elapsed_time\/qs.execution_count AS<\/span> [Avg<\/span> Elapsed Time<\/span>], <\/pre>\n
<\/p>\n
   9:<\/span> qs.creation_time AS<\/span> [Creation Time<\/span>]<\/pre>\n
<\/p>\n
  10:<\/span> --,t.[text][\/text] AS<\/span> [Complete Query Text], qp.query_plan AS<\/span> [Query Plan<\/span>] -- uncomment out these columns if not copying results to Excel<\/span><\/pre>\n
<\/p>\n
  11:<\/span> FROM<\/span> sys.dm_exec_query_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  12:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(plan_handle) AS<\/span> t <\/pre>\n
<\/p>\n
  13:<\/span> CROSS<\/span> APPLY sys.dm_exec_query_plan(plan_handle) AS<\/span> qp <\/pre>\n
<\/p>\n
  14:<\/span> WHERE<\/span> t.dbid = DB_ID()<\/pre>\n
<\/p>\n
  15:<\/span> ORDER<\/span> BY<\/span> qs.execution_count DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #46 Query Execution Counts<\/strong><\/p>\n
This query shows you which cached queries (which might be part of a stored procedure or not) are being called the most often. This is useful as a part of understanding the nature of your workload. Keep in mind that just because a query is called a lot does not necessarily mean that it is a key part of your workload. It might be, but it could be that it is not actually that expensive for individual calls or cumulatively. You will need to look at the other metrics for that query to determine that.<\/p>\n
You may notice that I have one line of this query commented out. This is because Excel does not deal very well with large quantities of text or XML. If you are working with this in real time, you should probably uncomment that line, so you see the extra information that it retrieves.<\/p>\n
 <\/p>\n
Query #47<\/strong> is SP Execution Counts. This query retrieves information from the sys.procedures<\/a> object catalog view and the sys.dm_exec_procedure_stats<\/a> dynamic management view about the most frequently executed cached stored procedures in the current database. Query #47 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Top Cached SPs By Execution Count (Query 47) (SP Execution Counts)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> TOP<\/span>(100) p.name AS<\/span> [SP Name], qs.execution_count,<\/pre>\n
<\/p>\n
   3:<\/span> ISNULL(qs.execution_count\/DATEDIFF(Minute<\/span>, qs.cached_time, GETDATE()), 0) AS<\/span> [Calls\/Minute<\/span>],<\/pre>\n
<\/p>\n
   4:<\/span> qs.total_worker_time\/qs.execution_count AS<\/span> [AvgWorkerTime], qs.total_worker_time AS<\/span> [TotalWorkerTime],  <\/pre>\n
<\/p>\n
   5:<\/span> qs.total_elapsed_time, qs.total_elapsed_time\/qs.execution_count AS<\/span> [avg_elapsed_time],<\/pre>\n
<\/p>\n
   6:<\/span> qs.cached_time<\/pre>\n
<\/p>\n
   7:<\/span> FROM<\/span> sys.procedures AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> INNER<\/span> JOIN<\/span> sys.dm_exec_procedure_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> ON<\/span> p.[object_id] = qs.[object_id]<\/pre>\n
<\/p>\n
  10:<\/span> WHERE<\/span> qs.database_id = DB_ID()<\/pre>\n
<\/p>\n
  11:<\/span> ORDER<\/span> BY<\/span> qs.execution_count DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  12:<\/span>  <\/pre>\n
<\/p>\n
  13:<\/span> -- Tells you which cached stored procedures are called the most often<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- This helps you characterize and<\/span> baseline your workload<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #47 SP Execution Counts<\/strong><\/p>\n
This query shows you which stored procedures with cached query plans are being called the most often. This helps you understand the nature and magnitude of your workload. Ideally, you should have a general idea of what your normal workload looks like, in terms of how many calls\/minute or per second you are seeing for your top stored procedures. <\/p>\n
If this rate suddenly changes, you would want to investigate further to understand what might have happened. Understanding which stored procedures are called the most often, can also help you identify possible candidates for middle-tier caching.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 20 of this series, we start out with Query #46, which is Query Execution Counts. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function, and the sys.dm_exec_query_plan dynamic management function about the most frequently executed cached queries in the current database. Query #46 is shown in Figure […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1048","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 20 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 20 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 20 of this series, we start out with Query #46, which is Query Execution Counts. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function, and the sys.dm_exec_query_plan dynamic management function about the most frequently executed cached queries in the current database. Query #46 is shown in Figure […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-20T17:07:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:51+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 20 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-20T17:07:47+00:00\",\"dateModified\":\"2017-04-13T19:28:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 20\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 20 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 20 - Glenn Berry","og_description":"For Day 20 of this series, we start out with Query #46, which is Query Execution Counts. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function, and the sys.dm_exec_query_plan dynamic management function about the most frequently executed cached queries in the current database. Query #46 is shown in Figure […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-20T17:07:47+00:00","article_modified_time":"2017-04-13T19:28:51+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 20 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-20T17:07:47+00:00","dateModified":"2017-04-13T19:28:51+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-20\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 20"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1048"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1048\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}