In this post I’m gong to show some T-SQL that will show you the compression state of tables and how to quickly find objects that are worth compressing.
Last week brentozar.com ran one of their Technology Triage Tuesdays on Compression in SQL Server. The video does not appear to be up yet, however it provided good insight, certainly better than the Technet pages on the same subject. As we have a multi-terabyte data warehouse at work, which is on an Enterprise licensed instance of SQL Server 2012, I’m familiar with the subject of data compression in SQL. Until recently however, another one of our other SQL Servers was on a Standard license. Recently this instance was upgraded to Enterprise, and so I was able to compress the database. Although not as large as our data warehouse, it was well worth considering compressing some of the larger tables.
Below is a script to check for all objects that are currently compressed.
--script to identify compressed tables SELECT st.name, st.object_id, ss.name AS [Schema Name], sp.partition_id, sp.partition_number, sp.data_compression, sp.data_compression_desc FROM sys.partitions SP INNER JOIN sys.tables ST ON st.object_id = sp.object_id INNER JOIN sys.schemas SS ON SS.schema_id = ST.schema_id WHERE data_compression <> 0
If you want to see which objects are not compressed, just change the ‘<>’ to ‘=’
--script to identify uncompressed tables SELECT st.name, st.object_id, ss.name AS [Schema Name], sp.partition_id, sp.partition_number, sp.data_compression, sp.data_compression_desc FROM sys.partitions SP INNER JOIN sys.tables ST ON st.object_id = sp.object_id INNER JOIN sys.schemas SS ON SS.schema_id = ST.schema_id WHERE data_compression = 0
But it’s still not a great way to find out how much space, or what the most appropriate compression is. Fortunately there’s two stored procs you can run here that will help you out. The names are fairly self explanatory so I won’t insult your intelligence by telling you what they do, other than in the 2nd one you can replace ‘PAGE’ with ‘ROW’.
EXEC sp_spaceused N'Value.Currency' EXEC sp_estimate_data_compression_savings 'Value', 'Currency', NULL, NULL, 'PAGE' ;
Although useful, some databases can contain many tables. You may know your workload and the largest tables in your database, but it’s still a laborious process to go through them all, and some may save you more space than others. So I wrote a script that will go through and print out the commands to run the first stored proc for all tables on your database. I prefer the print out method because you can check the result, and you can batch the executes into as many or as little as you like; although not a taxing sp, I felt it worth checking first! Also if you do choose to batch the entire process, you can then export the results to Excel.
The script also has a bit flag that will switch the execution to the 2nd sp. Again, this uses the print out method, as this sp is considerably more taxing than the first, though still not as demanding as the actual act of compression.
Both of the printouts store the results in a temp table, and from there is possible to then query the data better (more of this below). Notice that there are some inline comments that will be printed out to drop the temp tables; don’t forget to run these when you are finished with them!
SET NOCOUNT OFF declare @schema varchar (50), @table varchar (50), @tempTable varchar (500), @i bit, @tab nvarchar (12), @sql nvarchar (max) set @i = 0 if @i = 0 BEGIN set @tempTable = 'CREATE TABLE #FOO ( [name] nvarchar (50) ,[rows] int ,[reserved] varchar (20) ,[data] varchar (20) ,[index_size] varchar (20) ,[unused] varchar (20) ) GO --SELECT * FROM #FOO -- DROP TABLE #FOO ' END ELSE set @tempTable = 'CREATE TABLE #BAR ( [object_name] nvarchar (100) ,[schema_name] nvarchar (100) ,[index_id] bigint ,[partition_number] bigint ,[size_with_current_compression_settings(KB)] bigint ,[size_with_requested_compression_settings(KB)] bigint ,[sample_size_with_current_compression_settings(KB)] bigint ,[sample_size_with_requested_compression_settings(KB)] bigint ) GO --SELECT * FROM #BAR --DROP TABLE #BAR ' PRINT @tempTable DECLARE DB_cursor CURSOR FAST_FORWARD FOR SELECT [name] from AdventureWorks.sys.tables OPEN DB_cursor FETCH NEXT FROM DB_cursor into @table WHILE @@FETCH_STATUS = 0 BEGIN SELECT @schema = MAX (ss.name) FROM sys.partitions SP INNER JOIN sys.tables ST ON st.object_id = sp.object_id INNER JOIN sys.schemas SS ON SS.schema_id = ST.schema_id WHERE data_compression = 0 and st.name = @table SELECT @table = MAX (st.name) FROM sys.partitions SP INNER JOIN sys.tables ST ON st.object_id = sp.object_id INNER JOIN sys.schemas SS ON SS.schema_id = ST.schema_id WHERE data_compression = 0 and st.name = @table --insert into @dbTableVar values (@schema, @table) if @i = 0 BEGIN set @tab = 'INSERT #FOO' END ELSE set @tab = 'INSERT #BAR' if @i = 0 BEGIN set @sql = 'EXEC sp_spaceused N''' + @schema + '.' + @table + '''' END ELSE set @sql = 'EXEC sp_estimate_data_compression_savings ''' + @schema+'''' +','''+ @table+ ''', ' + 'NULL, '+ 'NULL, '+ ''+'PAGE;' print @tab print @sql FETCH NEXT FROM db_cursor INTO @table END CLOSE db_cursor DEALLOCATE db_cursor SET NOCOUNT ON
Once you have executed the stored procs, you will have your temp tables of data, you can organize the data into finding the largest tables, like in the example below. It requires a bit of trimming and casting, but then you’re able to find which of your tables are worth considering for compression.
select name, cast( left([reserved], LEN ([reserved]) -2) as decimal) as size_KB from #foo order by size_KB desc
So let’s say for the sake of an argument that we’re only interested in compressing objects larger than 100,000KB, roughly 100MB ( if we want the database to be in memory completely, maybe we have to think this small). With the 2nd table, you can run a query like the one below where there is a high value in the compression of any table that is greater than or equal to the smallest table you are interested in compressing by dividing the size of the table with the current compression against the projected size of the table. The higher the value then clearly the higher the value of compression.
select [object_name],[schema_name], Case when cast([size_with_requested_compression_settings(KB)] as int) = 0 then NULL else cast ([size_with_current_compression_settings(KB)] as decimal)/ cast([size_with_requested_compression_settings(KB)] as int) end as [space_savings] from #BAR where [size_with_current_compression_settings(KB)] >= 106896 order by space_savings desc
With a few queries we have identified the objects that we would benefit the most from compressing. I’m going to assume that you’ll want to compress using PAGE compression as it includes all that ROW compression offers plus more. If you have a server where CPU is the bottleneck, you may not want to follow this strategy but try to see if there is a win from using just ROW compression. Compression is applied using the sp below. I was tempted to expand the script to print out the commands similar to above. However compressing tables requires a considerable amount of effort on the part of the server, and you probably don’t want to batch the process. Plus, you probably don’t have hundreds of tables to compress, and if you do, then you can simply mod the script provided above.
ALTER TABLE Value.Currency REBUILD WITH (DATA_COMPRESSION = PAGE);
As mentioned in the webinar, it’s probably not a good idea to compress larger objects with considerable space saving optimizations during a busy period. So if you want a db maintenance window, you can use that Excel spreadsheet I mentioned earlier to send to the boss to show how much space you can save in a pretty chart to justify the downtime :D