By: Tim Smith | Comments (1) | Related: > Compression
Problem
For some projects, we may disable a database from growing and archive data out of it after a set point (often measured in time, though this may vary depending on data). With these databases, I've seen some clients use compression for tables and indexes as an approach when facing size restrictions and it's helpful to be able to obtain compression information quickly to either (1) compress tables and indexes that aren't, or (2) compress a table or index, depending on its use and size.
Solution
Compression is not a free operation to the environment, in that enabling it will come with costs, though these costs may be little compared to the benefits. For instance, according to Microsoft, compression saves data space used and can assist in improving IO since queries read data from fewer pages, though to compress the data for storage and decompress the data when using will require additional CPU. In addition, when we look for compressed objects, especially in an environment where we may have partitioned tables or indexes, not every partition may be compressed while other partitions may be; I evaluate a partitioned object in the below script differently than a non-partitioned object. Consider a partitioned table with every stock from every stock exchange on Earth; given the current frequency of trading and inquiry in the US markets (which won't always be a given), we may have a US market partition which wouldn't have compression enabled, while a partition for Hong Kong stocks would have compression enabled. Just because we find a non-compressed partition doesn't mean we should compress it (unless we must), and in this example, we might find the US markets partition that's not compressed and recognize why this is. When testing compression, consider that while you may not see high costs for reads, the costs of writes, especially if you have a concurrent demand for reads at the same time, may be enormous.
In the below script, we will loop through all the databases on a server, passing in the server as a parameter ($server), pass in the location of the SMO library ($smo), and the folder where we want a CSV with the results saved ($fold). For tables and indexes, we'll get the name of the database and the name of the objects (see this tip for a suggestion about naming conventions that demarcate objects), with the row count reported for tables, and the partition number reported for partitioned tables and indexes. Note that when looking at the report, indexes follow their respective tables. If the script is unable to find one table or index not partitioned, it removes the created file, so that if we looped through twenty servers, and only one found tables and indexes without compression, we'd only get that one as a result.
Function Find-NonCompressed ($server, $smo, $fold)
{
$nl = [Environment]::NewLine
Add-Type -Path $smo
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$dt = Get-Date -uFormat "%Y%m%d"
$csv = $fold + $server.Replace("\","") + "_CompressedObjects_" + $dt + ".csv"
New-Item $csv -ItemType file
Add-Content $csv "DatabaseName,ObjectName,ObjectRowCount,ObjectPartition_No"
$cnt = 0
foreach ($d in $srv.Databases | Where-Object {$_.IsSystemObject -eq $false})
{
$dn = $d.Name
$db = $srv.Databases["$dn"]
$tables = $db.Tables
foreach ($t in $tables)
{
if ($t.IsPartitioned -eq "True")
{
foreach ($tp in $t.PhysicalPartitions)
{
if ($tp.DataCompression -eq "None")
{
$cp = $dn + "," + $t.Parent + "," + $tp.RowCount.ToString() + "," + $tp.PartitionNumber
Add-Content $csv $cp
$cnt++
}
}
}
else
{
if ($t.PhysicalPartitions[0].DataCompression -eq "None")
{
$ct = $dn + "," + $t.Name + "," + $t.RowCount.ToString() + ","
Add-Content $csv $ct
$cnt++
}
}
foreach ($i in $t.Indexes)
{
if ($i.IsPartitioned -eq "True")
{
foreach ($ip in $i.PhysicalPartitions)
{
if ($ip.DataCompression -eq "None")
{
$cp = $dn + "," + $i.Parent + ",," + $ip.PartitionNumber
Add-Content $csv $cp
$cnt++
}
}
}
else
{
if ($i.PhysicalPartitions[0].DataCompression -eq "None")
{
$ct = $dn + "," + $i.Name + ",,"
Add-Content $csv $ct
$cnt++
}
}
}
}
}
if ($cnt -eq 0)
{
Remove-Item $csv
}
}
## Note that I am saving the file to the path "C:\Administration\Recon\" and this can be changed to your preferred path
Find-NonCompressed -server "OURSERVER\OURINSTANCE" -smo "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -fold "C:\Administration\Recon\"The results from the CSV (opened in Excel) in this example:
In most situations, I like to obtain recon information about whether the tables and indexes are compressed, evaluate how they're used, and compress (or not) in accordance with the findings. To automate the next step, I adjust the script to match T-SQL's syntax for altering objects and compressing them (using tables as an example with page compression), by taking the properties from each object that the syntax requires (name of the object and partition number for partitioned objects). I can copy and paste the output (if using PowerShell ISE), or save it as a string and execute the string as a SQL Command:
## Partitioned tables
if ($t.IsPartitioned -eq "True")
{
foreach ($tp in $t.PhysicalPartitions)
{
if ($tp.DataCompression -eq "None")
{
#$cp = $dn + "," + $t.Parent + "," + $tp.RowCount.ToString() + "," + $tp.PartitionNumber
#Add-Content $csv $cp
#$cnt++
## Note PAGE compression:
Write-Host "ALTER TABLE " + $t.Name + " REBUILD PARTITION = " + $tp.PartitionNumber + " WITH (DATA_COMPRESSION = PAGE)" + $nl
}
}
}
else
{
if ($t.PhysicalPartitions[0].DataCompression -eq "None")
{
#$ct = $dn + "," + $t.Name + "," + $t.RowCount.ToString() + ","
#Add-Content $csv $ct
#$cnt++
## Note PAGE compression:
Write-Host "ALTER TABLE " + $t.Name + " WITH (DATA_COMPRESSION = PAGE)" + $nl
}
}That being shown above this, I still prefer to avoid compressing all objects by default unless the environment calls for it and compressing the objects that need compression based on how they're used. The same may or may not be true for your environment. If you wonder whether you should compress your objects, or if it will matter, I'd highly suggest testing queries and evaluating how performance and space are impacted. Derek Colley provides an excellent tip showing the effects of using data compression with the results of his experiments.
Next Steps
- Use the above script to get compression information.
- If applicable, what tables and (or) indexes in your environment shouldn't be compressed?
- Review all SQL Server Compression tips.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
