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

For Day 25 of this series, we start out with Query #57<\/strong>, which is Buffer Usage. This query retrieves information from the sys.allocation_units<\/a> object catalog view, the sys.dm_os_buffer_descriptors<\/a> dynamic management view and the sys.partitions<\/a> object catalog view about buffer pool usage in the current database. Query #57 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Breaks down buffers used by current database by object (table, index) in the buffer cache  (Query 57) (Buffer Usage)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- Note: This query could take some time on a busy instance<\/span><\/pre>\n
<\/p>\n
   3:<\/span> SELECT<\/span> OBJECT_NAME(p.[object_id]) AS<\/span> [Object<\/span> Name], p.index_id, <\/pre>\n
<\/p>\n
   4:<\/span> CAST<\/span>(COUNT<\/span>(*)\/128.0 AS<\/span> DECIMAL<\/span>(10, 2)) AS<\/span> [Buffer size<\/span>(MB)],  <\/pre>\n
<\/p>\n
   5:<\/span> COUNT<\/span>(*) AS<\/span> [BufferCount], p.Rows<\/span> AS<\/span> [Row<\/span> Count<\/span>],<\/pre>\n
<\/p>\n
   6:<\/span> p.data_compression_desc AS<\/span> [Compression Type]<\/pre>\n
<\/p>\n
   7:<\/span> FROM<\/span> sys.allocation_units AS<\/span> a WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> INNER<\/span> JOIN<\/span> sys.dm_os_buffer_descriptors AS<\/span> b WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> ON<\/span> a.allocation_unit_id = b.allocation_unit_id<\/pre>\n
<\/p>\n
  10:<\/span> INNER<\/span> JOIN<\/span> sys.partitions AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  11:<\/span> ON<\/span> a.container_id = p.hobt_id<\/pre>\n
<\/p>\n
  12:<\/span> WHERE<\/span> b.database_id = CONVERT<\/span>(int<\/span>,DB_ID())<\/pre>\n
<\/p>\n
  13:<\/span> AND<\/span> p.[object_id] > 100<\/pre>\n
<\/p>\n
  14:<\/span> GROUP<\/span> BY<\/span> p.[object_id], p.index_id, p.data_compression_desc, p.[Rows<\/span>]<\/pre>\n
<\/p>\n
  15:<\/span> ORDER<\/span> BY<\/span> [BufferCount] DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  16:<\/span>  <\/pre>\n
<\/p>\n
  17:<\/span> -- Tells you what tables and indexes are using the most memory in the buffer cache<\/span><\/pre>\n
<\/p>\n
  18:<\/span> -- It can help identify possible candidates for<\/span> data<\/span> compression<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #57 Buffer Usage<\/strong><\/p>\n
This query shows you which tables and indexes are using the most buffer pool space in the current database. This is very important information to understand if you are under internal memory pressure, or you are seeing high read latency for your data file(s). The query also displays the data compression status for the index.<\/p>\n
If you see an index that is using a lot of space in the SQL Server buffer pool (because it shows up near the top of this query), then I would investigate whether SQL Server data compression might make sense for that index. What you want to look for are indexes that are highly compressible, on relatively static data, at least as far as UPDATES are concerned. <\/p>\n
 <\/p>\n
Query #58<\/strong> is Table Sizes. This query retrieves information from the the sys.partitions<\/a> object catalog view about the table sizes and clustered index (or heap) data compression status in the current database. Query #58 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get Table names, row counts, and compression status for clustered index or heap  (Query 58) (Table Sizes)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(object_id) AS<\/span> [ObjectName], <\/pre>\n
<\/p>\n
   3:<\/span> SUM<\/span>(Rows<\/span>) AS<\/span> [RowCount<\/span>], data_compression_desc AS<\/span> [CompressionType]<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.partitions WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   5:<\/span> WHERE<\/span> index_id < 2 --ignore<\/span> the partitions from<\/span> the non-clustered<\/span> index<\/span> if<\/span> any<\/span><\/pre>\n
<\/p>\n
   6:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'sys%'<\/span><\/pre>\n
<\/p>\n
   7:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'queue_%'<\/span> <\/pre>\n
<\/p>\n
   8:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'filestream_tombstone%'<\/span> <\/pre>\n
<\/p>\n
   9:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'fulltext%'<\/span><\/pre>\n
<\/p>\n
  10:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'ifts_comp_fragment%'<\/span><\/pre>\n
<\/p>\n
  11:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'filetable_updates%'<\/span><\/pre>\n
<\/p>\n
  12:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'xml_index_nodes%'<\/span><\/pre>\n
<\/p>\n
  13:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'sqlagent_job%'<\/span>  <\/pre>\n
<\/p>\n
  14:<\/span> AND<\/span> OBJECT_NAME(object_id) NOT<\/span> LIKE<\/span> N'plan_persist%'<\/span>  <\/pre>\n
<\/p>\n
  15:<\/span> GROUP<\/span> BY<\/span> object_id, data_compression_desc<\/pre>\n
<\/p>\n
  16:<\/span> ORDER<\/span> BY<\/span> SUM<\/span>(Rows<\/span>) DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  17:<\/span>  <\/pre>\n
<\/p>\n
  18:<\/span> -- Gives you an idea of<\/span> table<\/span> sizes, and<\/span> possible data<\/span> compression opportunities<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #58 Table Sizes<\/strong><\/p>\n
This query simply shows you the row counts and data compression status for the clustered index or heap for each table in the current database. I use this query to look for tables that might be good candidates of SQL Server data compression. Again, what you are looking for are large tables, that are relatively static, that compress well.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 25 of this series, we start out with Query #57, which is Buffer Usage. This query retrieves information from the sys.allocation_units object catalog view, the sys.dm_os_buffer_descriptors dynamic management view and the sys.partitions object catalog view about buffer pool usage in the current database. Query #57 is shown in Figure 1. 1: — Breaks […]<\/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-1053","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 25 - 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-25\/\" \/>\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 25 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 25 of this series, we start out with Query #57, which is Buffer Usage. This query retrieves information from the sys.allocation_units object catalog view, the sys.dm_os_buffer_descriptors dynamic management view and the sys.partitions object catalog view about buffer pool usage in the current database. Query #57 is shown in Figure 1. 1: -- Breaks […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-25T19:28:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:52+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-25\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 25 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-25T19:28:34+00:00\",\"dateModified\":\"2017-04-13T19:28:52+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-25\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/#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 25\"}]},{\"@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 25 - 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-25\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 25 - Glenn Berry","og_description":"For Day 25 of this series, we start out with Query #57, which is Buffer Usage. This query retrieves information from the sys.allocation_units object catalog view, the sys.dm_os_buffer_descriptors dynamic management view and the sys.partitions object catalog view about buffer pool usage in the current database. Query #57 is shown in Figure 1. 1: -- Breaks […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-25T19:28:34+00:00","article_modified_time":"2017-04-13T19:28:52+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-25\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 25 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-25T19:28:34+00:00","dateModified":"2017-04-13T19:28:52+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-25\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-25\/#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 25"}]},{"@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\/1053","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=1053"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1053\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}