\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":"\n