SQL 2008 saw the introduction of data compression to SQL Server. (I’m referring specifically to data compression, not backup file compression.) This Enterprise-only feature allows SQL Server to store data on disk and in memory in a compressed format. SQL can perform three types of data compression – no compression, row compression, or page compression. I’m not going to go into the details of each method, but in a nutshell (and seriously over-simplified), row compression changes fixed-length fields into variable length fields and page compression includes row compression and also compresses data that is common across fields in all the rows on a page. The Books Online Entry can be found here.
How do you know if you can benefit from data compression? Microsoft provides a stored procedure that will give you an estimate of how much space you can save using the various compression methods: sp_estimate_data_compression_savings. Before you go enabling data compression, there are a few things to keep in mind.
- This is an Enterprise Edition (or Developer Edition) only feature. If you enable data compression in a database, you will only be able to restore that database to a SQL Server running Enterprise or Developer edition. Keep this in mind when designing your disaster recovery plan.
- Likewise, if you have any servers that will be accessing the compressed databases as a linked server, they will also need to be Enterprise or Developer editions. *UPDATE* This may not be true. See my test in the comments below.
- Compression comes with a price. SQL will store the data in memory and on disk in compresed form, so you gain performance by having fewer disk I/Os and being able to cache more data in memory. The flipside, however, is you’ll need some extra CPU cycles to work with that data.
- SQL data compression is completely transparent to the user or application accessing the database.
If you’ve evaluated the pros and cons of data compression and decided it’s something you want to implement, how do you go about doing it? SQL Server does not compress an entire database. Rather, it compresses tables and indexes (and indexed views and some other things – see the BOL link above for more details.) Enabling compression on an object requires rebuilding the object, much like an index rebuild. The TSQL commands are ALTER TABLE or ALTER INDEX, depending on which you are compressing. You include the DATA_COMPRESSION clause in the command.
“But Shaun,” I hear you howling, “my database has hundreds of tables!!! Are you telling me I have to manually compress each one?” Unfortunately, yes. Furthermore, there is no way to tell SQL Server that all future objects should be compressed, so if your database has tables being created frequently, you’ll need to look for uncompressed objects on a regular basis and compress them.
Luckily, compression can be performed in an online operation, meaning the objects are still available to users while the compression is happening. Unfortunately, if your table is a heap, the online compression will be single threaded, meaning it will be S-L-O-W. Multi-threaded compression for heaps only happens when SQL can have exclusive access to the table.
But back to that one-object-at-a-time issue. As all DBAs know, anything that has to be done one object at a time can be scripted and, thus, somewhat automated. I’m about to give you a script that will do this for you.
This script will created a stored procedure called db_compression. It is based on a script first written by Paul Nielsen several years ago. I’ve modified it a bit. The procedure takes three input paramters: @minCompression, @MaxRunTimeInMinutes, and @ExecuteCompressCommand. @minCompression is the minimum compression ratio threshold you want to hit before an object is compressed, expressed as a decimal. For example, if you only want to compress objects where you can achieve 25% compression, use a value of .25. @MaxRunTimeInMinutes is the maximum time the procecure will spend compressing objects. Note that this does NOT include the time it takes to perform the initial scan of all objects and calculate their estimated savings from compression. It is also not a hard and fast limit. The script only checks this time before each compress command is started, so it’s possible a large table may make the procedure run longer than the limit entered here. Finally, the last option is a flag that tells the script if it should actually execute the compression commands or just print them out.
How it works
When first run, the script will create a list of all user tables and indexes in the database. (System tables cannot be compressed.) It writes this list to a table called dbEstimate. There is a flag for each record to indicate if the row has been processed or not and it also notes if the object currently has some sort of compression enabled. Once this table is populated, the script will execute the sp_estimate_data_compression_savings procedure against it. It will run this two times for each record – to get the estimated sizes for the two compression settings the object currently does not have. For example, if a table already has page compression enabled, the script will get estimated sizes for row compression and no compression. This portion of the script is not limited by the @MaxRunTimeInMinutes parameter.
The next portion of the script does the actual work. The dbEstimate table is looped through with a cursor. The cursor will only look at objects that contain data and where the compression ratio exceeds the value specified in the @minCompression parameter. If the @ExecuteCompressCommand parameter is 1, the script will execute a command to compress the object using whichever method gives the greatest compression ratio. If the @ExecuteCompressCommand is 0, it will not perform the compression and simply output the command instead. The output also includes a count of how many objects were compressed.
What happens if all the objects selected for compression do not get compressed before the allotted time is up? If you re-run the procedure, it will detect that the dbEstimate table already exists and, if there are still items than have not been processed, it will pick up compressing those, skipping the initial scan portion (which is often time consuming). If, however, all the items in the table have been processed, the script will wipe out the table and re-scan the entire database. This is useful if you have many tables and / or indexes being created.
If an object is already compressed, the script will not change the object from the existing compression method to the other method (i.e. from page to row or vice-versa), even if the other method may result in greater compression. (This might be something to look into for the next iteration of the script, but I’m not sure if this situation would occur often enough to make the effort worthwhile.)
The procedure is hard-coded to use the SORT_IN_TEMPDB option. It will also start compressing the smallest objects first. It does not compress using the ONLINE option, mainly due to the single-threaded limitation with heaps mentioned above. If you want this to run in online mode, add that option to lines 415 and 421 below. You can use this procedure to compress a large database over multiple days by running it for a couple hours overnight or during times of minimal usage.
Compressing the database will not automatically decrease the size of the data file on the disk. If you want to do that, you’ll need to shrink the database (and perform the appropriate defragmentation process afterwards).
CREATE PROC [dbo].[db_compression] ( @minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */ ,@MaxRunTimeInMinutes INT = 60 ,@ExecuteCompressCommand BIT = 0 /* 1 to execute command */ ) AS SET nocount ON; /* Original script by Paul Nielsen www.SQLServerBible.com March 13, 2008 Modified by Shaun J. Stuart www.shaunjstuart.com February 27, 2013 Sets compression for all objects and indexs in the database needing adjustment If estimated gain is equal to or greater than min compression parameter then enables row or page compression, whichever gives greater space savings If row and page have same gain then, enables row compression If estimated gain is less than min compression parameter, then compression is set to none - SJS 2/27/13 - Added @MaxRunTimeInMinutes to limit run length (checked afer each command, so may run longer) Note: This timer only applies to the actual compression process. It does not limit the time it takes to perform a scan of all the tables / indexes and estimate compression savings. - Sorted compression cursor to compress smallest tables first - Added flag to execute compression command or not - Added sort in tempdb option (always) - Re-wrote slightly to persist initial scan results. Will only re-scan after all tables have been processed */ DECLARE @CompressedCount INT; SET @CompressedCount = 0; DECLARE @StartTime DATETIME2; SET @StartTime = CURRENT_TIMESTAMP; CREATE TABLE #ObjEst ( PK INT IDENTITY NOT NULL PRIMARY KEY ,object_name VARCHAR(250) ,schema_name VARCHAR(250) ,index_id INT ,partition_number INT ,size_with_current_compression_setting BIGINT ,size_with_requested_compression_setting BIGINT ,sample_size_with_current_compression_setting BIGINT ,sample_size_with_requested_compresison_setting BIGINT ); IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbEstimate]') AND type IN (N'U') ) BEGIN CREATE TABLE dbEstimate ( PK INT IDENTITY NOT NULL PRIMARY KEY ,schema_name VARCHAR(250) ,object_name VARCHAR(250) ,index_id INT ,ixName VARCHAR(255) ,ixType VARCHAR(50) ,partition_number INT ,data_compression_desc VARCHAR(50) ,None_Size INT ,Row_Size INT ,Page_Size INT ,AlreadyProcessed BIT ); END /* If all objects have been processed, rescan and start fresh. Useful for catching added objects since last scan and / or finding objects that were compressed initially with one method but now would do better with another method. But beware - this is I/O intensive and can take a while. */ IF NOT EXISTS ( SELECT 1 FROM dbEstimate WHERE AlreadyProcessed = 0 ) BEGIN DELETE FROM dbEstimate; INSERT INTO dbEstimate (schema_name ,object_name ,index_id ,ixName ,ixType ,partition_number ,data_compression_desc ,AlreadyProcessed) SELECT S.name ,o.name ,I.index_id ,I.name ,I.type_desc ,P.partition_number ,P.data_compression_desc ,0 AS AlreadyProcessed FROM sys.schemas AS S JOIN sys.objects AS O ON S.schema_id = O.schema_id JOIN sys.indexes AS I ON o.object_id = I.object_id JOIN sys.partitions AS P ON I.object_id = P.object_id AND I.index_id = p.index_id WHERE O.TYPE = 'U'; -- Determine Compression Estimates DECLARE @PK INT ,@Schema VARCHAR(150) ,@object VARCHAR(150) ,@DAD VARCHAR(25) ,@partNO INT ,@indexID INT ,@ixName VARCHAR(250) ,@SQL NVARCHAR(MAX) ,@ixType VARCHAR(50) ,@Recommended_Compression VARCHAR(10); DECLARE cCompress CURSOR FAST_FORWARD FOR SELECT schema_name ,object_name ,index_id ,partition_number ,data_compression_desc FROM dbEstimate WHERE AlreadyProcessed = 0; OPEN cCompress; FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD; WHILE @@Fetch_Status = 0 BEGIN /* evaluate objects with no compression */ IF @DAD = 'none' BEGIN /* estimate Page compression */ INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'page'; UPDATE dbEstimate SET none_size = O.size_with_current_compression_setting ,page_size = O.size_with_requested_compression_setting FROM dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number; DELETE #ObjEst; -- estimate Row compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'row'; UPDATE dbEstimate SET row_size = O.size_with_requested_compression_setting FROM dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number; DELETE #ObjEst; END /* end evaluating objects with no compression */ /* evaluate objects with row compression */ IF @DAD = 'row' BEGIN /* estimate Page compression */ INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'page'; UPDATE dbEstimate SET row_size = O.size_with_current_compression_setting ,page_size = O.size_with_requested_compression_setting FROM dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number; DELETE #ObjEst; /* estimate None compression */ INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'none'; UPDATE dbEstimate SET none_size = O.size_with_requested_compression_setting FROM dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number; DELETE #ObjEst; END /* end evaluating objects with row compression */ /* evalutate objects with page compression */ IF @DAD = 'page' BEGIN /* estimate Row compression */ INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'row'; UPDATE dbEstimate SET page_size = O.size_with_current_compression_setting ,row_size = O.size_with_requested_compression_setting FROM dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number; DELETE #ObjEst; /* estimate None compression */ INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'none'; UPDATE dbEstimate SET none_size = O.size_with_requested_compression_setting FROM dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number; DELETE #ObjEst; END /* end evaluating objects with page compression */ FETCH NEXT FROM cCompress INTO @Schema, @object, @indexID, @partNO, @DAD END CLOSE cCompress DEALLOCATE cCompress PRINT 'Initial scan complete.' END /* End evaluating compression savings. Now do the actual compressing. */ PRINT 'Beginning compression.'; /* Do not process objects that do not meet our compression criteria */ UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE (1 - (CAST(Row_Size AS FLOAT) / None_Size)) < @minCompression AND (Row_Size <= Page_Size) AND None_Size > 0; UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE (1 - (CAST(Page_Size AS FLOAT) / None_Size)) < @minCompression AND (Page_Size <= Row_Size) AND None_Size > 0; /* Do not set compression on empty objects */ UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE None_size = 0; /* Do not process objects that are already using an acceptable compression method */ UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE (1 - (CAST(Page_Size AS FLOAT) / none_Size)) >= @minCompression AND (Page_Size <= Row_Size) AND None_Size > 0 AND data_compression_desc = 'PAGE'; UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE (1 - (CAST(Row_Size AS FLOAT) / None_Size)) >= @minCompression AND (Row_Size <= Page_Size) AND None_Size > 0 AND data_compression_desc = 'ROW'; /* Ignore tables that have not had their sizes calculated. This can occur if the procedure execution was cancelled before the initial scan completed. */ UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE None_Size IS NULL OR Row_Size IS NULL OR Page_Size IS NULL; -- set the compression DECLARE cCompress CURSOR FAST_FORWARD FOR SELECT schema_name ,object_name ,partition_number ,ixName ,ixType ,CASE WHEN (1 - (CAST(Row_Size AS FLOAT) / none_Size)) >= @minCompression AND (Row_Size <= Page_Size) THEN 'Row' WHEN (1 - (CAST(page_Size AS FLOAT) / none_Size)) >= @minCompression AND (Page_Size <= row_Size) THEN 'Page' ELSE 'None' END AS Recommended_Compression ,PK FROM dbEstimate WHERE None_Size <> 0 AND (CASE WHEN (1 - (CAST(Row_Size AS FLOAT) / none_Size)) >= @minCompression AND (Row_Size <= Page_Size) THEN 'Row' WHEN (1 - (CAST(page_Size AS FLOAT) / none_Size)) >= @minCompression AND (Page_Size <= row_Size) THEN 'Page' ELSE 'None' END <> data_compression_desc) AND AlreadyProcessed = 0 ORDER BY None_Size ASC; /* start with smallest tables first */ OPEN cCompress FETCH cCompress INTO @Schema, @object, @partNO, @ixName, @ixType, @Recommended_Compression, @PK -- prime the cursor; WHILE @@Fetch_Status = 0 BEGIN IF @ixType = 'Clustered' OR @ixType = 'heap' SET @SQL = 'ALTER TABLE ' + @Schema + '.' + @object + ' Rebuild with (data_compression = ' + @Recommended_Compression + ', SORT_IN_TEMPDB=ON)'; ELSE SET @SQL = 'ALTER INDEX ' + @ixName + ' on ' + @Schema + '.' + @object + ' Rebuild with (data_compression = ' + @Recommended_Compression + ',SORT_IN_TEMPDB=ON)'; IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes BEGIN IF @ExecuteCompressCommand = 1 BEGIN PRINT 'Compressing table/index: ' + @Schema + '.' + @object; EXEC sp_executesql @SQL; UPDATE dbEstimate SET AlreadyProcessed = 1 WHERE PK = @PK; SET @CompressedCount = @CompressedCount + 1; END ELSE BEGIN PRINT 'Command execution not enabled. Command is:' + @SQL; END END ELSE BEGIN PRINT 'Max runtime reached. Exiting...'; BREAK END FETCH cCompress INTO @Schema, @object, @partNO, @ixName, @ixType, @Recommended_Compression, @PK; END CLOSE cCompress; DEALLOCATE cCompress; PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount); DROP TABLE #ObjEst; RETURN
Update: I realized the above script calls a secondary stored procedure, db_compression_estimate, that was not provided. Here it is.
CREATE PROC [dbo].[db_compression_estimate] AS SET nocount ON -- estimates the row and page compression gain for every object and index in the database -- Paul Nielsen -- www.SQLServerBible.com -- March 13, 2008 -- to do: -- [ ] transaction error from insert...Exec sp_estimate_data_compression_savings -- [ ] filter objects to only those eligible for compression CREATE TABLE #ObjEst ( PK INT IDENTITY NOT NULL PRIMARY KEY ,object_name VARCHAR(250) ,schema_name VARCHAR(250) ,index_id INT ,partition_number INT ,size_with_current_compression_setting BIGINT ,size_with_requested_compression_setting BIGINT ,sample_size_with_current_compression_setting BIGINT ,sample_size_with_requested_compresison_setting BIGINT ) CREATE TABLE #dbEstimate ( PK INT IDENTITY NOT NULL PRIMARY KEY ,schema_name VARCHAR(250) ,object_name VARCHAR(250) ,index_id INT ,ixName VARCHAR(255) ,ixType VARCHAR(50) ,partition_number INT ,data_compression_desc VARCHAR(50) ,None_Size INT ,Row_Size INT ,Page_Size INT ) INSERT INTO #dbEstimate (schema_name ,object_name ,index_id ,ixName ,ixType ,partition_number ,data_compression_desc) SELECT S.name ,o.name ,I.index_id ,I.name ,I.type_desc ,P.partition_number ,P.data_compression_desc FROM sys.schemas AS S JOIN sys.objects AS O ON S.schema_id = O.schema_id JOIN sys.indexes AS I ON o.object_id = I.object_id JOIN sys.partitions AS P ON I.object_id = P.object_id AND I.index_id = p.index_id WHERE O.TYPE = 'U' -- Determine Compression Estimates DECLARE @PK INT ,@Schema VARCHAR(150) ,@object VARCHAR(150) ,@DAD VARCHAR(25) ,@partNO INT ,@indexID INT ,@SQL NVARCHAR(MAX) DECLARE cCompress CURSOR FAST_FORWARD FOR SELECT schema_name ,object_name ,index_id ,partition_number ,data_compression_desc FROM #dbEstimate OPEN cCompress FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD -- prime the cursor WHILE @@Fetch_Status = 0 BEGIN IF @DAD = 'none' BEGIN -- estimate Page compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'page' UPDATE #dbEstimate SET none_size = O.size_with_current_compression_setting ,page_size = O.size_with_requested_compression_setting FROM #dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number DELETE #ObjEst -- estimate Row compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'row' UPDATE #dbEstimate SET row_size = O.size_with_requested_compression_setting FROM #dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number DELETE #ObjEst END -- none compression estimate IF @DAD = 'row' BEGIN -- estimate Page compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'page' UPDATE #dbEstimate SET row_size = O.size_with_current_compression_setting ,page_size = O.size_with_requested_compression_setting FROM #dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number DELETE #ObjEst -- estimate None compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'none' UPDATE #dbEstimate SET none_size = O.size_with_requested_compression_setting FROM #dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number DELETE #ObjEst END -- row compression estimate IF @DAD = 'page' BEGIN -- estimate Row compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'row' UPDATE #dbEstimate SET page_size = O.size_with_current_compression_setting ,row_size = O.size_with_requested_compression_setting FROM #dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number DELETE #ObjEst -- estimate None compression INSERT #ObjEst (object_name ,schema_name ,index_id ,partition_number ,size_with_current_compression_setting ,size_with_requested_compression_setting ,sample_size_with_current_compression_setting ,sample_size_with_requested_compresison_setting) EXEC sp_estimate_data_compression_savings @Schema_name = @Schema ,@object_name = @object ,@index_id = @indexID ,@partition_number = @partNO ,@data_compression = 'none' UPDATE #dbEstimate SET none_size = O.size_with_requested_compression_setting FROM #dbEstimate D JOIN #ObjEst O ON D.Schema_name = O.Schema_Name AND D.Object_name = O.object_name AND D.index_id = O.index_id AND D.partition_number = O.partition_number DELETE #ObjEst END -- page compression estimate FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD END CLOSE cCompress DEALLOCATE cCompress -- report findings SELECT schema_name + '.' + object_name AS [Object] ,index_id ,ixName ,ixType ,partition_number ,data_compression_desc AS Current_Compression ,CAST((1 - (CAST(Row_Size AS FLOAT) / none_Size)) * 100 AS INT) AS RowGain ,CAST((1 - (CAST(page_Size AS FLOAT) / none_Size)) * 100 AS INT) AS PageGain ,CASE WHEN (1 - (CAST(Row_Size AS FLOAT) / none_Size)) >= .25 AND (Row_Size <= Page_Size) THEN 'Row' WHEN (1 - (CAST(page_Size AS FLOAT) / none_Size)) >= .25 AND (Page_Size <= row_Size) THEN 'Page' ELSE 'None' END AS Recommended_Compression FROM #dbEstimate WHERE None_Size <> 0 ORDER BY [Object] RETURN
Hi Shaun,
Good article. I have been trying to track down the effects of data compression when sending data across a linked server connection.
You say, “Likewise, if you have any servers that will be accessing the compressed databases as a linked server, they will also need to be Enterprise or Developer editions.”
Assuming I have two Enterprise Servers, one in Australia and One in the UK, and enable data compression on my table, would the data compression also lead to reduced traffic across the Linked server Connection?
Thanks
P.S. I have been trying unsuccessfully to find an answer online and have posted on the MS SQL Server forums here:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d6e3fc9-b3b3-42e8-b8eb-ed7f42c48eab/data-compression-over-linked-server-connections-sql-server-2012-enterprise-developer-editions
I believe it will give reduced network traffic, but I have nothing to back up that claim. Perhaps a test is in order?
*EDIT* I’m changing my answer here. I think the data will be sent uncompressed over the network. I ran the following test: I have database with a compressed table on an Enterprise 2012 SQL Server. On a 2008 R2 Standard SQL Server, I created a linked server entry to the Enterprise server. On the Standard server, I was able to do a SELECT * from the compressed table on the Enterprise server via the linked server entry. This would imply that the data is sent uncompressed over the network because Standard edition does not support compressed data.
Thanks Shaun!
Your out-of-the-box do-it-all (and even without looking) scripts were very welcome for reducing the footprint on my developer notebook. For me, space savings clearly outweigh performance concerns.
Regards,
Reiner