estimatecomp2.sql

-- SQL Server 2008, 2008 R2 and 2012 Data Compression Estimation Queries
    -- This may take some time to run, depending on your hardware infrastructure and table size
    -- Glenn Berry 
    -- April 2013
    -- http://www.sqlskills.com/blogs/glenn/
    -- http://glennberrysqlperformance.spaces.live.com/
    -- Twitter: GlennAlanBerry
 
    -- Get estimated data compression savings and other index info for every index in the specified table
    SET NOCOUNT ON;
    DECLARE @SchemaName sysname = N'dbo';                                -- Specify schema name
    DECLARE @TableName sysname = N'SpecObjAll';                        -- Specify table name
    DECLARE @FullName sysname = @SchemaName + '.' + @TableName;
    DECLARE @IndexID int = 1;
    DECLARE @CompressionType nvarchar(60) = N'PAGE';                    -- Specify desired data compression type (PAGE, ROW, or NONE)
    SET @FullName = @SchemaName + '.' + @TableName;
 
    -- Get Table name, row count, and compression status for clustered index or heap table
    SELECT OBJECT_NAME(object_id) AS [Object Name], 
    SUM(Rows) AS [RowCount], data_compression_desc AS [Compression Type]
    FROM sys.partitions WITH (NOLOCK)
    WHERE index_id < 2
    AND OBJECT_NAME(object_id) = @TableName
    GROUP BY object_id, data_compression_desc
    ORDER BY SUM(Rows) DESC;
 
    -- Breaks down buffers used by current table in this database by object (table, index) in the buffer pool
    -- Shows you which indexes are taking the most space in the buffer cache, so they might be possible candidates for data compression
    SELECT OBJECT_NAME(p.[object_id]) AS [Object Name],
    p.index_id, COUNT(*)/128 AS [Buffer size(MB)],  COUNT(*) AS [Buffer Count], 
    p.data_compression_desc AS [Compression Type]
    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 = 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 [Buffer Count] DESC;
 
    -- Get the 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 i.index_id
        FROM sys.indexes AS i WITH (NOLOCK)
        INNER JOIN sys.tables AS t WITH (NOLOCK)
        ON i.[object_id] = t.[object_id]
        WHERE t.type_desc = N'USER_TABLE'
        AND OBJECT_NAME(t.[object_id]) = @TableName
        ORDER BY i.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 this 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 WITH (NOLOCK)
    INNER JOIN sys.indexes AS i WITH (NOLOCK)
    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 @FullName;
    
	-- Individual File Sizes and space available for current database  
    SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
    CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB],
    CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2))
    AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
    FROM sys.database_files AS f WITH (NOLOCK)
    LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
    ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);