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

For Day 23 of this series, we start out with Query #52<\/strong>, which is SP Logical Writes. 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 cached stored procedures that have the highest cumulative total logical writes in the current database. Query #52 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Top Cached SPs By Total Logical Writes (Query 52) (SP Logical Writes)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- Logical writes relate to both memory and disk I\/O pressure <\/span><\/pre>\n
<\/p>\n
   3:<\/span> SELECT<\/span> TOP<\/span>(25) p.name AS<\/span> [SP Name], qs.total_logical_writes AS<\/span> [TotalLogicalWrites], <\/pre>\n
<\/p>\n
   4:<\/span> qs.total_logical_writes\/qs.execution_count AS<\/span> [AvgLogicalWrites], qs.execution_count,<\/pre>\n
<\/p>\n
   5:<\/span> ISNULL(qs.execution_count\/DATEDIFF(Minute<\/span>, qs.cached_time, GETDATE()), 0) AS<\/span> [Calls\/Minute<\/span>],<\/pre>\n
<\/p>\n
   6:<\/span> qs.total_elapsed_time, qs.total_elapsed_time\/qs.execution_count AS<\/span> [avg_elapsed_time], <\/pre>\n
<\/p>\n
   7:<\/span> qs.cached_time<\/pre>\n
<\/p>\n
   8:<\/span> FROM<\/span> sys.procedures AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> INNER<\/span> JOIN<\/span> sys.dm_exec_procedure_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span> ON<\/span> p.[object_id] = qs.[object_id]<\/pre>\n
<\/p>\n
  11:<\/span> WHERE<\/span> qs.database_id = DB_ID()<\/pre>\n
<\/p>\n
  12:<\/span> AND<\/span> qs.total_logical_writes > 0<\/pre>\n
<\/p>\n
  13:<\/span> ORDER<\/span> BY<\/span> qs.total_logical_writes DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  14:<\/span>  <\/pre>\n
<\/p>\n
  15:<\/span> -- This helps you find the most expensive cached stored procedures from a write I\/O perspective<\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- You should look at<\/span> this if<\/span> you see signs of<\/span> I\/O pressure or<\/span> of<\/span> memory pressure<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #52 SP Logical Writes<\/strong><\/p>\n
This query lets you see which cached stored procedures have the highest number cumulative logical writes in this database. This helps you see which stored procedures are causing the most write I\/O pressure for this database. If you are seeing any signs of high write I\/O latency on your instance of SQL Server (and if this database is causing a lot of I\/O activity, as shown in Query #31), then the results of this query can help you figure out which stored procedures are the biggest offenders.<\/p>\n
 <\/p>\n
Query #53<\/strong> is Top IO Statements. This query retrieves information from the sys.dm_exec_query_stats<\/a> dynamic management and the sys.dm_exec_sql_text<\/a> dynamic management function about the cached query statements that have the highest average I\/O activity in the current database. Query #53 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Lists the top statements by average input\/output usage for the current database  (Query 53) (Top IO Statements)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> TOP<\/span>(50) OBJECT_NAME(qt.objectid, dbid) AS<\/span> [SP Name],<\/pre>\n
<\/p>\n
   3:<\/span> (qs.total_logical_reads + qs.total_logical_writes) \/qs.execution_count AS<\/span> [Avg<\/span> IO], qs.execution_count AS<\/span> [Execution Count<\/span>],<\/pre>\n
<\/p>\n
   4:<\/span> SUBSTRING<\/span>(qt.[text][\/text],qs.statement_start_offset\/2, <\/pre>\n
<\/p>\n
   5:<\/span>     (CASE<\/span> <\/pre>\n
<\/p>\n
   6:<\/span>         WHEN<\/span> qs.statement_end_offset = -1 <\/pre>\n
<\/p>\n
   7:<\/span>      THEN<\/span> LEN(CONVERT<\/span>(nvarchar(max<\/span>), qt.[text][\/text])) * 2 <\/pre>\n
<\/p>\n
   8:<\/span>         ELSE<\/span> qs.statement_end_offset <\/pre>\n
<\/p>\n
   9:<\/span>      END<\/span> - qs.statement_start_offset)\/2) AS<\/span> [Query Text]    <\/pre>\n
<\/p>\n
  10:<\/span> FROM<\/span> sys.dm_exec_query_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  11:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(qs.sql_handle) AS<\/span> qt<\/pre>\n
<\/p>\n
  12:<\/span> WHERE<\/span> qt.[dbid] = DB_ID()<\/pre>\n
<\/p>\n
  13:<\/span> ORDER<\/span> BY<\/span> [Avg<\/span> IO] DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  14:<\/span>  <\/pre>\n
<\/p>\n
  15:<\/span> -- Helps you find the most expensive statements for<\/span> I\/O by<\/span> SP<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #53 Top IO Statements<\/strong><\/p>\n
This query shows you which query statements (which are often inside of stored procedures are causing the highest average I\/O activity in the current database. Again, if you are under internal memory pressure, or if you are seeing high I\/O latency for reads or for writes, the results of this query can point you in the right direction for further investigation.<\/p>\n
Perhaps you have a query that is doing a clustered index scan because it is missing a useful non-clustered index. Perhaps a query is pulling back more rows or columns of data than it really needs (although can be hard for you to confirm this as a DBA). Perhaps the table(s) that are involved in this query might have indexes that would be good candidates for SQL Server Data Compression. There are many, many possible issues and actions that you can investigate in this area!<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 23 of this series, we start out with Query #52, which is SP Logical Writes. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest cumulative total logical writes in the current database. Query #52 is shown in […]<\/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-1051","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 23 - 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-23\/\" \/>\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 23 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 23 of this series, we start out with Query #52, which is SP Logical Writes. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest cumulative total logical writes in the current database. Query #52 is shown in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-23T22:51:24+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-23\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 23 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-23T22:51:24+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-23\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/#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 23\"}]},{\"@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 23 - 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-23\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 23 - Glenn Berry","og_description":"For Day 23 of this series, we start out with Query #52, which is SP Logical Writes. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the cached stored procedures that have the highest cumulative total logical writes in the current database. Query #52 is shown in […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-23T22:51:24+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-23\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 23 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-23T22:51:24+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-23\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-23\/#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 23"}]},{"@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\/1051","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=1051"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1051\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}