Managing Index Fragmentation and Compression

For today, I recycle bits from a couple of excellent authors in the SQL community.  The first script is for optimizing compression.  I “borrowed” this from Paul Nielson, author of the SQL Bible.  You can visit his SQL Server bible site at http://www.sqlserverbible.com/.   It includes a link for sample scripts, which is where I pulled this from.  I’m also including a script for defragmenting indexes, which I borrowed from http://sqlfool.com/2010/04/index-defrag-script-v4-0/.  These scripts can be used together.  What I am doing is on a monthly basis running the compression script to make adjustments to my compression options based on data, and then doing the defragmentation weekly.

Based on my testing and testing on several other sites, compression should be the norm rather than the exception.  Unless you have heavy OLTP processing, most of the time the savings in memory, cache, and ultimately disk I/O because more data is stored in less space is greater than the CPU costs.  Along the same lines, backup compression should almost always be used as it will run faster than non-compressed for both backup and restore purposes.  They really got compression right in SQL 2008.

Below is the script almost as originally written, but with a couple of modifications.  Paul’s version had only 1 threshold, rather than separate for page and row, which I’ve added.  In his version, he uses page compression if it is more effective than row and row compression if it is more effective than page.   However almost always page is better than row because it incorporates row.  I’ve decided to take a slightly different approach and allow a threshold for row and another for page.  Page requires more CPU processing than row, so the idea is that we only want page if the compression benefits are significantly better than row.   On the other hand, row has a lower CPU cost.  By default, I am using 15 and 25%.  By default, I’m employing page compression if it has a benefit of 25% savings and row if it has a benefit of 15%, you can pass in different values if you want to be more conservative or aggressive.

Read more: Microsoft Bob

Posted via email from jasper22's posterous