{"id":712,"date":"2013-04-12T12:30:24","date_gmt":"2013-04-12T19:30:24","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=712"},"modified":"2013-04-16T12:37:09","modified_gmt":"2013-04-16T19:37:09","slug":"estimating-data-compression-savings-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/estimating-data-compression-savings-in-sql-server-2012\/","title":{"rendered":"Estimating Data Compression Savings in SQL Server 2012"},"content":{"rendered":"

SQL Server Data Compression (which was first introduced in SQL Server 2008, and is an Enterprise Edition only feature) lets you compress individual indexes with either ROW or PAGE compression. It can be a huge win for many SQL Server workloads, by letting you reduce both your required disk I\/O and your memory usage at the cost of some added CPU usage in some scenarios.<\/p>\n

By design, there is no \u201cCompress Entire Database\u201d command in SQL Server 2012. Instead, you need to evaluate individual indexes, based on their size, estimated compression savings and volatility. The ideal case is a large table that shows very good compression savings that is read-only. A bad candidate is a small table, that does not show much compression savings, with very volatile data.<\/p>\n

I long ago got tired of manually running the sp_estimate_data_compression_savings system stored procedure with hard-coded parameters for each index in a database, so I decided to write some T-SQL that would somewhat automate the process.\u00a0 If you set the schema name, table name, and desired data compression type in the variable declarations at the top of the script, you will get some pretty detailed information about all of the indexes in that table.<\/p>\n

You should run the entire query at once, after you have supplied your own values. It may take some time to run, depending on your hardware and on how large your tables are.\u00a0 You could also wrap part of this in a stored procedure that you could call for each table in a database, and have it write the results out to a table that you could easily query later.<\/p>\n

\n
<\/pre>\n
    -- SQL Server 2008, 2008 R2 and 2012 Data Compression Estimation Queries<\/span>\r\n    -- This may take some time to run, depending on your hardware infrastructure and table size<\/span><\/pre>\n
    -- Glenn Berry <\/span>\r\n    -- April 2013<\/span><\/pre>\n
    -- https:\/\/www.sqlskills.com\/blogs\/glenn\/<\/span>\r\n    -- http:\/\/glennberrysqlperformance.spaces.live.com\/<\/span><\/pre>\n
    -- Twitter: GlennAlanBerry<\/span><\/pre>\n
\u00a0\r\n    -- Get estimated data compression savings and other index info for every index in the specified table<\/span><\/pre>\n
    SET<\/span> NOCOUNT ON<\/span>;<\/pre>\n
    DECLARE<\/span> @SchemaName sysname = N'dbo'<\/span>;                                -- Specify schema name<\/span>\r\n    DECLARE<\/span> @TableName sysname = N'ActivityEvent'<\/span>;                        -- Specify table name<\/span><\/pre>\n
    DECLARE<\/span> @FullName sysname = @SchemaName + '.'<\/span> + @TableName;\r\n    DECLARE<\/span> @IndexID int<\/span> = 1;<\/pre>\n
    DECLARE<\/span> @CompressionType nvarchar(60) = N'PAGE'<\/span>;                    -- Specify desired data compression type (PAGE, ROW, or NONE)<\/span><\/pre>\n
    SET<\/span> @FullName = @SchemaName + '.'<\/span> + @TableName;<\/pre>\n
\u00a0\r\n    -- Get Table name, row count, and compression status for clustered index or heap table<\/span><\/pre>\n
    SELECT<\/span> OBJECT_NAME(object_id) AS<\/span> [Object<\/span> Name], \r\n    SUM<\/span>(Rows<\/span>) AS<\/span> [RowCount<\/span>], data_compression_desc AS<\/span> [Compression Type]<\/pre>\n
    FROM<\/span> sys.partitions WITH<\/span> (NOLOCK)\r\n    WHERE<\/span> index_id < 2<\/pre>\n
    AND<\/span> OBJECT_NAME(object_id) = @TableName\r\n    GROUP<\/span> BY<\/span> object_id, data_compression_desc<\/pre>\n
    ORDER<\/span> BY<\/span> SUM<\/span>(Rows<\/span>) DESC<\/span>;<\/pre>\n
\u00a0\r\n    -- Breaks down buffers used by current table in this database by object (table, index) in the buffer pool<\/span><\/pre>\n
    -- Shows you which indexes are taking the most space in the buffer cache, so they might be possible candidates for data compression<\/span>\r\n    SELECT<\/span> OBJECT_NAME(p.[object_id]) AS<\/span> [Object<\/span> Name],<\/pre>\n
    p.index_id, COUNT<\/span>(*)\/128 AS<\/span> [Buffer size<\/span>(MB)],  COUNT<\/span>(*) AS<\/span> [Buffer Count<\/span>], \r\n    p.data_compression_desc AS<\/span> [Compression Type]<\/pre>\n
    FROM<\/span> sys.allocation_units AS<\/span> a WITH<\/span> (NOLOCK)\r\n    INNER<\/span> JOIN<\/span> sys.dm_os_buffer_descriptors AS<\/span> b WITH<\/span> (NOLOCK)<\/pre>\n
    ON<\/span> a.allocation_unit_id = b.allocation_unit_id\r\n    INNER<\/span> JOIN<\/span> sys.partitions AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
    ON<\/span> a.container_id = p.hobt_id\r\n    WHERE<\/span> b.database_id = DB_ID()<\/pre>\n
    AND<\/span> OBJECT_NAME(p.[object_id]) = @TableName\r\n    AND<\/span> p.[object_id] > 100<\/pre>\n
    GROUP<\/span> BY<\/span> p.[object_id], p.index_id, p.data_compression_desc\r\n    ORDER<\/span> BY<\/span> [Buffer Count<\/span>] DESC<\/span>;<\/pre>\n
<\/pre>\n
\u00a0\r\n    -- Get the current and estimated size for every index in specified table<\/span><\/pre>\n
    DECLARE<\/span> curIndexID CURSOR<\/span> FAST_FORWARD\r\n    FOR<\/span><\/pre>\n
        -- Get list of index IDs for this table<\/span>\r\n        SELECT<\/span> i.index_id<\/pre>\n
        FROM<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)\r\n        INNER<\/span> JOIN<\/span> sys.tables AS<\/span> t WITH<\/span> (NOLOCK)<\/pre>\n
        ON<\/span> i.[object_id] = t.[object_id]\r\n        WHERE<\/span> t.type_desc = N'USER_TABLE'<\/span><\/pre>\n
        AND<\/span> OBJECT_NAME(t.[object_id]) = @TableName\r\n        ORDER<\/span> BY<\/span> i.index_id;<\/pre>\n
\u00a0\r\n    OPEN<\/span> curIndexID;<\/pre>\n
\u00a0\r\n    FETCH<\/span> NEXT<\/span> FROM<\/span> curIndexID INTO<\/span> @IndexID;<\/pre>\n
\u00a0\r\n    -- Loop through every index in the table and run sp_estimate_data_compression_savings<\/span><\/pre>\n
    WHILE<\/span> @@FETCH_STATUS<\/span> = 0\r\n        BEGIN<\/span><\/pre>\n
            -- Get current and estimated size for specified index with specified compression type<\/span>\r\n            EXEC<\/span> sp_estimate_data_compression_savings @SchemaName, @TableName, @IndexID, NULL<\/span>, @CompressionType;<\/pre>\n
\u00a0\r\n            FETCH<\/span> NEXT<\/span><\/pre>\n
            FROM<\/span> curIndexID\r\n            INTO<\/span> @IndexID;<\/pre>\n
        END<\/span><\/pre>\n
    CLOSE<\/span> curIndexID;\r\n    DEALLOCATE<\/span> curIndexID;<\/pre>\n
<\/pre>\n
    -- Index Read\/Write stats for this table<\/span>\r\n    SELECT<\/span> OBJECT_NAME(s.[object_id]) AS<\/span> [TableName],<\/pre>\n
    i.name AS<\/span> [IndexName], i.index_id,\r\n    SUM<\/span>(user_seeks) AS<\/span> [User<\/span> Seeks], SUM<\/span>(user_scans) AS<\/span> [User<\/span> Scans],<\/pre>\n
    SUM<\/span>(user_lookups)AS<\/span> [User<\/span> Lookups],\r\n    SUM<\/span>(user_seeks + user_scans + user_lookups)AS<\/span> [Total Reads<\/span>],<\/pre>\n
    SUM<\/span>(user_updates) AS<\/span> [Total Writes]     \r\n    FROM<\/span> sys.dm_db_index_usage_stats AS<\/span> s WITH<\/span> (NOLOCK)<\/pre>\n
    INNER<\/span> JOIN<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)\r\n    ON<\/span> s.[object_id] = i.[object_id]<\/pre>\n
    AND<\/span> i.index_id = s.index_id\r\n    WHERE<\/span> OBJECTPROPERTY(s.[object_id],'IsUserTable'<\/span>) = 1<\/pre>\n
    AND<\/span> s.database_id = DB_ID()\r\n    AND<\/span> OBJECT_NAME(s.[object_id]) = @TableName<\/pre>\n
    GROUP<\/span> BY<\/span> OBJECT_NAME(s.[object_id]), i.name, i.index_id\r\n    ORDER<\/span> BY<\/span> [Total Writes] DESC<\/span>, [Total Reads<\/span>] DESC<\/span>;<\/pre>\n
<\/pre>\n
    -- Get basic index information (does not include filtered indexes or included columns)<\/span>\r\n    EXEC<\/span> sp_helpindex @FullName;<\/pre>\n
<\/pre>\n
    -- Individual File Sizes and space available for current database  <\/span>\r\n    SELECT<\/span> f.name AS<\/span> [File<\/span> Name] , f.physical_name AS<\/span> [Physical Name],<\/pre>\n
    CAST<\/span>((f.size<\/span>\/128.0) AS<\/span> decimal<\/span>(15,2)) AS<\/span> [Total Size<\/span> in<\/span> MB],\r\n    CAST<\/span>(f.size<\/span>\/128.0 - CAST<\/span>(FILEPROPERTY(f.name, 'SpaceUsed'<\/span>) AS<\/span> int<\/span>)\/128.0 AS<\/span> decimal<\/span>(15,2))<\/pre>\n
    AS<\/span> [Available Space<\/span> In<\/span> MB], [file_id], fg.name AS<\/span> [Filegroup Name]\r\n    FROM<\/span> sys.database_files AS<\/span> f WITH<\/span> (NOLOCK)<\/pre>\n
    LEFT<\/span> OUTER<\/span> JOIN<\/span> sys.data_spaces AS<\/span> fg WITH<\/span> (NOLOCK) \r\n    ON<\/span> f.data_space_id = fg.data_space_id OPTION<\/span> (RECOMPILE);<\/pre>\n<\/div>\n