/*
-----------------------------------------------------------------
Index Compression Estimations
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
USE DatabaseNameHere;
GO
-- Get estimated data compression savings and other index info
-- for every index in the specified table
SET NOCOUNT ON;
DECLARE @SchemaName SYSNAME = N'SchemaNameHere'; -- Specify schema name
DECLARE @TableName SYSNAME = N'TableNameHere'; -- Specify table name
DECLARE @IndexID INT = 1;
DECLARE @CompressionType NVARCHAR(60) = N'CompressionTypeHere' -- Specify data compression type (PAGE, ROW, or NONE)
-- Get table name, row count, and compression status
-- for clustered index or heap table
SELECT OBJECT_NAME([object_id]) AS [ObjectName],
SUM([Rows]) AS [RowCount],
data_compression_desc AS [CompressionType]
FROM sys.partitions
WHERE index_id < 2 -- ignore the partitions from the non-clustered index if any
AND OBJECT_NAME([object_id]) = @TableName
GROUP BY [object_id], data_compression_desc
ORDER BY SUM([Rows]) DESC;
-- Breaks down buffers used by current database by object (table, index) in the buffer pool
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
INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND OBJECT_NAME(p.[object_id]) = @TableName
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC;
-- Shows you which indexes are taking the most space in the buffer cache
-- Get current and estimated size for every index in specified table
DECLARE curIndexID CURSOR
FAST_FORWARD
FOR
-- Get list of index IDs for this table
SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE OBJECT_NAME(s.[object_id]) = @TableName
AND s.database_id = DB_ID()
ORDER BY s.index_id;
OPEN curIndexID;
FETCH NEXT
FROM curIndexID
INTO @IndexID;
-- Loop through every index in the table and run sp_estimate_data_compression_savings
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get current and estimated size for specified index with specified compression type
EXEC sp_estimate_data_compression_savings @SchemaName, @TableName, @IndexID, NULL, @CompressionType;
FETCH NEXT
FROM curIndexID
INTO @IndexID;
END
CLOSE curIndexID;
DEALLOCATE curIndexID;
-- Index Read/Write stats for a single table
SELECT OBJECT_NAME(s.[object_id]) AS [TableName],
i.name AS [IndexName],
i.index_id,
SUM(user_seeks) AS [User Seeks],
SUM(user_scans) AS [User Scans],
SUM(user_lookups)AS [User Lookups],
SUM(user_seeks + user_scans + user_lookups)AS [Total Reads],
SUM(user_updates) AS [Total Writes]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND OBJECT_NAME(s.[object_id]) = @TableName
GROUP BY OBJECT_NAME(s.[object_id]), i.name, i.index_id
ORDER BY [Total Writes] DESC, [Total Reads] DESC;
-- Get basic index information (does not include filtered indexes or included columns)
--EXEC sp_helpindex @TableName;
-- Get size and available space for files in current database
SELECT name AS [File Name],
physical_name AS [Physical Name],
size / 128.0 AS [Total Size in MB],
size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Available Space In MB],
[file_id]
FROM sys.database_files;
Enjoy!
Chris
No comments:
Post a Comment