A couple of weeks ago, I was working on a Microsoft PDW proof-of-concept (POC) and had to measure compression ratios. In order to do this, I fired up SSMS and wrote a little script. The script will iterate through all tables in a database and run the sp_estimate_data_compression_savings stored procedure. This will only work in SQL Server 2008+ versions running Enterprise edition.
If you’re not familiar with this stored procedure, it basically will tell you what effect PAGE or ROW compression will have on your table/index/partition, etc. There are pro’s and con’s with compression. What I’ve tended to see is that compression has very positive results on space, IO, and query duration, with a negative impact on CPU and write speed. Like most things, it’s a trade-off and the results will vary by environment, so I recommend you do some testing before you apply compression to all tables. I tend to use compression mostly for my historical tables and partitions and leave my recent data uncompressed. And, back to the script, I use this stored procedure to estimate the impact of compression and to determine whether to use PAGE or ROW compression. PAGE is a higher level of compression, which means it’s also more expensive in terms of CPU, so if the difference between the two results is negligible, I’m more apt to just use ROW compression.
Now that my impromptu compression discussion is done, let’s get to the actual script. One final word of caution, however. This is an IO intensive process, so you may want to run it after peak business hours.
SET NOCOUNT ON; DECLARE @printOnly BIT = 0 -- change to 1 if you don't want to execute, just print commands , @tableName VARCHAR(256) , @schemaName VARCHAR(100) , @sqlStatement NVARCHAR(1000) , @tableCount INT , @statusMsg VARCHAR(1000); IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#tables%') DROP TABLE #tables; CREATE TABLE #tables ( database_name sysname , schemaName sysname NULL , tableName sysname NULL , processed bit ); IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#compression%') DROP TABLE #compressionResults; IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#compression%') BEGIN CREATE TABLE #compressionResults ( objectName varchar(100) , schemaName varchar(50) , index_id int , partition_number int , size_current_compression bigint , size_requested_compression bigint , sample_current_compression bigint , sample_requested_compression bigint ); END; INSERT INTO #tables SELECT DB_NAME() , SCHEMA_NAME([schema_id]) , name , 0 -- unprocessed FROM sys.tables; SELECT @tableCount = COUNT(*) FROM #tables; WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0) BEGIN SELECT TOP 1 @tableName = tableName , @schemaName = schemaName FROM #tables WHERE processed = 0; SELECT @statusMsg = 'Working on ' + CAST(((@tableCount - COUNT(*)) + 1) AS VARCHAR(10)) + ' of ' + CAST(@tableCount AS VARCHAR(10)) FROM #tables WHERE processed = 0; RAISERROR(@statusMsg, 0, 42) WITH NOWAIT; SET @sqlStatement = 'EXECUTE sp_estimate_data_compression_savings ''' + @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''PAGE'';' -- ROW, PAGE, or NONE IF @printOnly = 1 BEGIN SELECT @sqlStatement; END ELSE BEGIN INSERT INTO #compressionResults EXECUTE sp_executesql @sqlStatement; END; UPDATE #tables SET processed = 1 WHERE tableName = @tableName AND schemaName = @schemaName; END; SELECT * FROM #compressionResults; |
Here’s a way to script it on a server in powershell. This script can easily be changed to run against all servers either from a servers.txt or from Central Management server call to msdb. Here’s another example at this link that I wrote to identify any tables that are not compressed http://artemervits.blogspot.com/2011/01/identify-uncompressed-tables-in-sql.html
I’m still working on converting your index defrag script into Powershell
.
cls
#$hostname = Get-Content “D:\NYP\CheckSQLServer\Staging\test.txt”
echo “–Please Enter Server Name:”
$hostname = Read-Host
echo “–Please Enter Compression to estimate savings, valid input is ‘row’ or ‘page’ with no single quote”
$compression = Read-Host
Write-Host “–Servername: ” $hostname
Write-Host “–Compression to calculate is: ” $compression
[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null
$sqlserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $hostname
foreach($db in $sqlserver.Databases)
{
if($db.ID -gt 4 -and $db.Name -ne “ReportServer” -and $db.Name -ne “ReportServerTempDB”)
{
foreach($table in $db.Tables)
{
foreach($schema in $table.Schema)
{
if($table.HasCompressedPartitions -ne $true)
{
$use = “USE ” + $db.Name
$go = “GO”
$sql = “EXEC sp_estimate_data_compression_savings [" + $schema + "]” + “, ” + “[" + $table.Name + "]” + “, NULL, NULL, ” + $compression
echo $use
echo $go
echo $sql
echo $go
}
}
}
}
}
Write-Host “—————————-”
Here’s the output from the script above:
–Please Enter Server Name:
–Please Enter Compression to estimate savings, valid input is ‘row’ or ‘page’ with no single quote
–Servername: E3802XX6951
–Compression to calculate is: PAGE
USE AdventureWorks
GO
EXEC sp_estimate_data_compression_savings [dbo], [AWBuildVersion], NULL, NULL, PAGE
GO
USE AdventureWorks
GO
EXEC sp_estimate_data_compression_savings [dbo], [DatabaseLog], NULL, NULL, PAGE
GO
USE AdventureWorks
GO
EXEC sp_estimate_data_compression_savings [dbo], [ErrorLog], NULL, NULL, PAGE
GO
USE AdventureWorks
GO
EXEC sp_estimate_data_compression_savings [HumanResources], [Department], NULL, NULL, PAGE
GO
etc. I should’ve clarified that this version only scripts the T-SQL, at some point I will convert it into true powershell script so that it would calculate on the fly.
Pingback: Determine compression on SQL Server database tables | Technobabble by Klee
Hi
Pingback: Something for the Weekend – SQL Server Links 17/06/11
Can you share what compression ratios you obtained for your POC ?
Hi, I only get 7 tables that your script runs on… Any Ideia ?
THX
Thanks for the useful script!
James
Hey Michelle,
I was just looking to find a script for this and I come by my old colleague.
Thanx Michelle!
Pingback: notes on SQL data compression | becknspace