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 CPU utilization by database (Query 30) (CPU Usage by Database)
2: WITH DB_CPU_Stats
3: AS
4: (SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
5: FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
6: CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
7: FROM sys.dm_exec_plan_attributes(qs.plan_handle)
8: WHERE attribute = N'dbid') AS pa
9: GROUP BY DatabaseID)
10: SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
11: [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
12: CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
13: FROM DB_CPU_Stats
14: WHERE DatabaseID <> 32767 -- ResourceDB
15: ORDER BY [CPU Rank] OPTION (RECOMPILE);
16:
17: -- Helps determine which database is using the most CPU resources on the instance
Figure 1: Query #30 CPU Usage by Database
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.
Query #31 is IO Usage by Database. This query retrieves information from the sys.dm_io_virtual_file_stats 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.
1: -- Get I/O utilization by database (Query 31) (IO Usage By Database)
2: WITH Aggregate_IO_Statistics
3: AS
4: (SELECT DB_NAME(database_id) AS [Database Name],
5: CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
6: FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
7: GROUP BY database_id)
8: SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name], io_in_mb AS [Total I/O (MB)],
9: CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
10: FROM Aggregate_IO_Statistics
11: ORDER BY [I/O Rank] OPTION (RECOMPILE);
12:
13: -- Helps determine which database is using the most I/O resources on the instance
Figure 2: Query #31 IO Usage Usage by Database
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.
Query #32 is Total Buffer Usage by Database. This query retrieves information from the sys.dm_os_buffer_descriptors dynamic management view about your current total buffer usage by database. Query #32 is shown in Figure 3.
1: -- Get total buffer usage by database for current instance (Query 32) (Total Buffer Usage by Database)
2: -- This make take some time to run on a busy instance
3: WITH AggregateBufferPoolUsage
4: AS
5: (SELECT DB_NAME(database_id) AS [Database Name],
6: CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
7: FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
8: WHERE database_id <> 32767 -- ResourceDB
9: GROUP BY DB_NAME(database_id))
10: SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
11: CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
12: FROM AggregateBufferPoolUsage
13: ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);
14:
15: -- Tells you how much memory (in the buffer pool)
16: -- is being used by each database on the instance
Figure 3: Query #32 Total Buffer Usage Usage by Database
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.