Microsoft® SQL Server® 2012 Bible

1108

Part VIII: Performance Tuning and Optimization

FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

JOIN sys.objects o ON o.object_id = i.object_id

JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

+ i.leaf_delete_count + leaf_update_count

+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND o.type = 'U'

ORDER BY [Percent_Scan] DESC

After you execute these queries, use the returned values along with the estimated space

savings to determine whether to page or row compress.

Summary

Data compression was the sleeper feature when it was introduced. With both row compres-

sion and page compression, including both prefi x and dictionary compression, SQL Server

offers the granularity to tune data compression. Using data compression carefully, you can

push the envelope for an I/O bound, high-transaction database.

The next chapter continues the thread of technologies used for highly scalable database

design with a look at several types of partitioning.

c48.indd 1108c48.indd 1108 7/31/2012 10:24:10 AM7/31/2012 10:24:10 AM

http://www.it-ebooks.info