1104
Part VIII: Performance Tuning and Optimization
■ (^) The Data Compression Wizard, found in Object Explorer (Context
menu ➪ Storage ➪ Manage Compression) opens with the current compression
selected.
To see the current compression setting for every object in the database, run this query:
SELECT O.object_id, S.name AS [schema], O.name AS [Object],
I.index_id AS Ix_id, I.name AS IxName, I.type_desc AS IxType,
P.partition_number AS P_No, P.data_compression_desc AS Compression
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'
ORDER BY S.name, O.name, I.index_id ;
Abbreviated result when executed in the AdventureWorks database:
object_id schema Object ix_id ixName ixType P_No Comp
1509580416 Person Person 1 PK_Person_Busines... CLUSTERED 1 NONE
1509580416 Person Person 2 IX_Person_LastName... NONCLUSTERED 1 NONE
1509580416 Person Person 3 AK_Person_rowguid NONCLUSTERED 1 NONE
...
Estimating Data Compression
Because every object can yield a different compression ratio, you need to have some idea of
how much compression is possible before actually performing the compression. Toward this
end, SQL 2012 includes the ability to pre-estimate the potential data reduction of data com-
pression using the sp_estimate_data_compression_savings system stored procedure.
Specifi cally, this system stored procedure can be used to copy 5 percent of the data to be
compressed into tempdb and compress it. The 5 percent is not a random sample but every
twentieth page, so it should give consistent results:
EXEC sp_estimate_data_compression_savings
@schema_name = 'Production',
@object_name = 'BillOfMaterials',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'page';
c48.indd 1104c48.indd 1104 7/31/2012 10:24:09 AM7/31/2012 10:24:09 AM
http://www.it-ebooks.info