Databases Reference
In-Depth Information
might be possible to improve the I/O performance situation by adding more spindles, changing the
RAID controller cache policy, or changing the RAID level. You also might consider moving some of
your database i les to other drives if possible.
Now, using the query shown in Listing 15-18, you are going to see which user databases on the
instance are using the most memory.
LISTING 15-18:
Total buf er usage by database
-- Get total buffer usage by database for current instance
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
-- Tells you how much memory (in the buffer pool)
-- is being used by each database on the instance
This query will list the total buffer usage for each user database running on the current instance.
Especially if you are seeing signs of internal memory pressure, you are going to be interested in know-
ing which database(s) are using the most space in the buffer pool. One way to reduce memory usage
in a particular database is to ensure that you don't have a lot of missing indexes on large tables that
are causing a large number of index or table scans. Another way, if you have SQL Server Enterprise
Edition, is to start using SQL Server data compression on some of your larger indexes (if they are
good candidates for data compression). The ideal candidate for data compression is a large static table
that is highly compressible because of the data types and actual data in the table. A bad candidate for
data compression is a small, highly volatile table that does not compress well. A compressed index will
stay compressed in the buffer pool, unless any data is updated. This means that you may be able to
save many gigabytes of space in your buffer pool under ideal circumstances.
Next, you will take a look at which user databases on the instance are using the most processor time
by using the query shown in Listing 15-19.
LISTING 15-19:
CPU usage by database
-- Get CPU utilization by database
WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
Search WWH ::
Custom Search