Databases Reference
In-Depth Information
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
-- Helps you connect missing indexes to specific stored procedures or queries
-- This can help you decide whether to add them or not
This query returns information about cached execution plans that have “missing index” warnings.
It will give you the stored procedure name, the query plan, and the use count for that cache execu-
tion plan. This can help you decide whether a particular “missing index” is really important or
not. You should use this query along with the query shown in Listing 15-46 to help determine
whether you should add any new indexes to a particular table.
Next, using the query shown in Listing 15-48, you can i nd out which tables and indexes are using
the most space in the SQL Server buffer pool.
LISTING 15-48:
Buf er usage by table and index
-- Breaks down buffers used by current database
-- by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [BufferCount],
p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC OPTION (RECOMPILE);
-- Tells you what tables and indexes are
-- using the most memory in the buffer cache
This query indicates which indexes and tables in the current database are using the most memory
in the SQL Server buffer pool. It also shows you whether the index is using any form of data
compression. If you see an index that is using a large amount of space in the buffer pool, you should
investigate whether that index might be a good candidate for SQL Server data compression,
assuming that you have SQL Server 2008 or later Enterprise Edition.
An ideal data compression candidate would be a large, static table that has highly compressible
data. In such a case, you might see as much as a 10:1 compression ratio, meaning the compressed
index would take up far less space in the buffer pool, and in the data i le on disk. In my experience,
I have typically seen anywhere from 2:1 up to 4:1 for average compression ratios. A poor data com-
pression candidate would be a smaller, highly volatile table containing data that does not compress
very well. In that case, you would most likely be better off without using data compression.
Next, you will i nd out the size (in terms of row counts) and the data compression status of all the
tables in this database, using the query shown in Listing 15-49.
Search WWH ::
Custom Search