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

For Day 13 of this series, we start out with Query #30<\/strong>, which is CPU Usage by Database. This query retrieves information from the sys.dm_exec_query_stats<\/a> dynamic management view and from the sys.dm_exec_plan_attributes<\/a> dynamic management function about total CPU usage by database for cached query plans. Query #30 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get CPU utilization by database (Query 30) (CPU Usage by Database)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> WITH<\/span> DB_CPU_Stats<\/pre>\n
<\/p>\n
   3:<\/span> AS<\/span><\/pre>\n
<\/p>\n
   4:<\/span> (SELECT<\/span> pa.DatabaseID, DB_Name(pa.DatabaseID) AS<\/span> [Database<\/span> Name], SUM<\/span>(qs.total_worker_time\/1000) AS<\/span> [CPU_Time_Ms]<\/pre>\n
<\/p>\n
   5:<\/span>  FROM<\/span> sys.dm_exec_query_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   6:<\/span>  CROSS<\/span> APPLY (SELECT<\/span> CONVERT<\/span>(int<\/span>, value<\/span>) AS<\/span> [DatabaseID] <\/pre>\n
<\/p>\n
   7:<\/span>               FROM<\/span> sys.dm_exec_plan_attributes(qs.plan_handle)<\/pre>\n
<\/p>\n
   8:<\/span>               WHERE<\/span> attribute = N'dbid'<\/span>) AS<\/span> pa<\/pre>\n
<\/p>\n
   9:<\/span>  GROUP<\/span> BY<\/span> DatabaseID)<\/pre>\n
<\/p>\n
  10:<\/span> SELECT<\/span> ROW_NUMBER() OVER<\/span>(ORDER<\/span> BY<\/span> [CPU_Time_Ms] DESC<\/span>) AS<\/span> [CPU Rank],<\/pre>\n
<\/p>\n
  11:<\/span>        [Database<\/span> Name], [CPU_Time_Ms] AS<\/span> [CPU Time<\/span> (ms)], <\/pre>\n
<\/p>\n
  12:<\/span>        CAST<\/span>([CPU_Time_Ms] * 1.0 \/ SUM<\/span>([CPU_Time_Ms]) OVER<\/span>() * 100.0 AS<\/span> DECIMAL<\/span>(5, 2)) AS<\/span> [CPU Percent<\/span>]<\/pre>\n
<\/p>\n
  13:<\/span> FROM<\/span> DB_CPU_Stats<\/pre>\n
<\/p>\n
  14:<\/span> WHERE<\/span> DatabaseID <> 32767 -- ResourceDB<\/span><\/pre>\n
<\/p>\n
  15:<\/span> ORDER<\/span> BY<\/span> [CPU Rank] OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  16:<\/span>  <\/pre>\n
<\/p>\n
  17:<\/span> -- Helps determine which database<\/span> is<\/span> using<\/span> the most CPU resources on<\/span> the instance<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #30 CPU Usage by Database<\/strong><\/p>\n
Simply speaking, this query shows you which databases are using the most CPU resources on the instance, at least as far as their cached query plans are concerned. If you are seeing any signs of CPU pressure, this query can help point you at the correct databases to investigate further, to see what queries are using the most CPU resources. There are several other queries in this complete set that can help you find the most expensive cached stored procedures and queries.<\/p>\n
 <\/p>\n
Query #31<\/strong> is IO Usage by Database. This query retrieves information from the sys.dm_io_virtual_file_stats<\/a> dynamic management function about your total cumulative I\/O usage by database since SQL Server was last started. Query #31 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get I\/O utilization by database (Query 31) (IO Usage By Database)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> WITH<\/span> Aggregate_IO_Statistics<\/pre>\n
<\/p>\n
   3:<\/span> AS<\/span><\/pre>\n
<\/p>\n
   4:<\/span> (SELECT<\/span> DB_NAME(database_id) AS<\/span> [Database<\/span> Name],<\/pre>\n
<\/p>\n
   5:<\/span> CAST<\/span>(SUM<\/span>(num_of_bytes_read + num_of_bytes_written)\/1048576 AS<\/span> DECIMAL<\/span>(12, 2)) AS<\/span> io_in_mb<\/pre>\n
<\/p>\n
   6:<\/span> FROM<\/span> sys.dm_io_virtual_file_stats(NULL<\/span>, NULL<\/span>) AS<\/span> [DM_IO_STATS]<\/pre>\n
<\/p>\n
   7:<\/span> GROUP<\/span> BY<\/span> database_id)<\/pre>\n
<\/p>\n
   8:<\/span> SELECT<\/span> ROW_NUMBER() OVER<\/span>(ORDER<\/span> BY<\/span> io_in_mb DESC<\/span>) AS<\/span> [I\/O Rank], [Database<\/span> Name], io_in_mb AS<\/span> [Total I\/O (MB)],<\/pre>\n
<\/p>\n
   9:<\/span>        CAST<\/span>(io_in_mb\/ SUM<\/span>(io_in_mb) OVER<\/span>() * 100.0 AS<\/span> DECIMAL<\/span>(5,2)) AS<\/span> [I\/O Percent<\/span>]<\/pre>\n
<\/p>\n
  10:<\/span> FROM<\/span> Aggregate_IO_Statistics<\/pre>\n
<\/p>\n
  11:<\/span> ORDER<\/span> BY<\/span> [I\/O Rank] OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  12:<\/span>  <\/pre>\n
<\/p>\n
  13:<\/span> -- Helps determine which database<\/span> is<\/span> using<\/span> the most I\/O resources on<\/span> the instance<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #31 IO Usage Usage by Database<\/strong><\/p>\n
The figures that this query collects are cumulative since SQL Server last started, and they include all file activity against your database data files and log files. This includes your normal database workload, plus things like index maintenance, DBCC CHECKDB activity, database backups, and any log reader activity. Because of all this, the numbers you see here might be different than you expect.<\/p>\n
Query #32<\/strong> is Total Buffer Usage by Database. This query retrieves information from the sys.dm_os_buffer_descriptors<\/a> dynamic management view about your current total buffer usage by database. Query #32 is shown in Figure 3.<\/p>\n
\n
\n
   1:<\/span> -- Get total buffer usage by database for current instance  (Query 32) (Total Buffer Usage by Database)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- This make take some time to run on a busy instance<\/span><\/pre>\n
<\/p>\n
   3:<\/span> WITH<\/span> AggregateBufferPoolUsage<\/pre>\n
<\/p>\n
   4:<\/span> AS<\/span><\/pre>\n
<\/p>\n
   5:<\/span> (SELECT<\/span> DB_NAME(database_id) AS<\/span> [Database<\/span> Name],<\/pre>\n
<\/p>\n
   6:<\/span> CAST<\/span>(COUNT<\/span>(*) * 8\/1024.0 AS<\/span> DECIMAL<\/span> (10,2))  AS<\/span> [CachedSize]<\/pre>\n
<\/p>\n
   7:<\/span> FROM<\/span> sys.dm_os_buffer_descriptors WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> WHERE<\/span> database_id <> 32767 -- ResourceDB<\/span><\/pre>\n
<\/p>\n
   9:<\/span> GROUP<\/span> BY<\/span> DB_NAME(database_id))<\/pre>\n
<\/p>\n
  10:<\/span> SELECT<\/span> ROW_NUMBER() OVER<\/span>(ORDER<\/span> BY<\/span> CachedSize DESC<\/span>) AS<\/span> [Buffer Pool Rank], [Database<\/span> Name], CachedSize AS<\/span> [Cached Size<\/span> (MB)],<\/pre>\n
<\/p>\n
  11:<\/span>        CAST<\/span>(CachedSize \/ SUM<\/span>(CachedSize) OVER<\/span>() * 100.0 AS<\/span> DECIMAL<\/span>(5,2)) AS<\/span> [Buffer Pool Percent<\/span>]<\/pre>\n
<\/p>\n
  12:<\/span> FROM<\/span> AggregateBufferPoolUsage<\/pre>\n
<\/p>\n
  13:<\/span> ORDER<\/span> BY<\/span> [Buffer Pool Rank] OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  14:<\/span>  <\/pre>\n
<\/p>\n
  15:<\/span> -- Tells you how much memory (in the buffer pool) <\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- is<\/span> being used by<\/span> each<\/span> database<\/span> on<\/span> the instance<\/pre>\n
<\/div>\n<\/div>\n
Figure 3: Query #32 Total Buffer Usage Usage by Database<\/strong><\/p>\n
This query shows you which databases are using the most space in the SQL Server Buffer Pool. If you see a database that is using a large amount of memory in the buffer pool, you might be able to improve the situation by doing some query or index tuning, or by using SQL Server Data Compression on some of your indexes. It may be that you just have a large database that has a lot of activity, so it has a lot of data in the buffer pool, by design.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 13 of this series, we start out with Query #30, which is CPU Usage by Database. This query retrieves information from the sys.dm_exec_query_stats dynamic management view and from the sys.dm_exec_plan_attributes dynamic management function about total CPU usage by database for cached query plans. Query #30 is shown in Figure 1. 1: — Get […]<\/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":[270],"class_list":["post-1036","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 13 - 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-13\/\" \/>\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 13 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 13 of this series, we start out with Query #30, which is CPU Usage by Database. This query retrieves information from the sys.dm_exec_query_stats dynamic management view and from the sys.dm_exec_plan_attributes dynamic management function about total CPU usage by database for cached query plans. Query #30 is shown in Figure 1. 1: -- Get […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-13T16:10:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:47+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=\"4 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-13\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 13 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-13T16:10:42+00:00\",\"dateModified\":\"2017-04-13T19:28:47+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-13\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/#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 13\"}]},{\"@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 13 - 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-13\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 13 - Glenn Berry","og_description":"For Day 13 of this series, we start out with Query #30, which is CPU Usage by Database. This query retrieves information from the sys.dm_exec_query_stats dynamic management view and from the sys.dm_exec_plan_attributes dynamic management function about total CPU usage by database for cached query plans. Query #30 is shown in Figure 1. 1: -- Get […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-13T16:10:42+00:00","article_modified_time":"2017-04-13T19:28:47+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 13 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-13T16:10:42+00:00","dateModified":"2017-04-13T19:28:47+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-13\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-13\/#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 13"}]},{"@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\/1036","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=1036"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1036\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1036"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1036"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1036"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}