Mar 28 2009
Compression and performance tricks for SQL Server 2008
At the SQLBits Goes Forth conference in Manchester today, Ramesh Meyyappan demonstrated impacts of some new SQL Server 2008 features on query performance. One topic he tackled in particular is compression, offering advice when to apply it and when not to apply it.
Meyyappan used three tables with the same set of data, containing several hundreds of thousands of rows. One table was uncompressed, taking 450MB on the disk. The second was row-compressed, having about 350 MB on the disk. The third was page compressed, taking 250 MB on the disk. (Meyyappan pointed out that these ratios are not global and that the compression rate depends on the data itself). He executed the same query on each table, effectively demonstrating that when data is processed from the disk there is a significant performance increase moving from uncompressed to row compressed to page compressed tables (because of less IO). However, that should not be taken as a signal to just compress the whole database. Using the option to compress the whole database, in his opinion, is bad as that needs to be considered for each table individually. Running the queries again, with the data now in the page cache, the results were actually inverse: the uncompressed table performed significantly better than row compressed which in turn did better than page compressed (because compressed pages are kept compressed in memory, so reading from page cache still requires decompression). Meyyappan advised compressing tables that will mostly be processed using data on the disk and be IO-bound. Frequently used tables, with pages which will be in the cache, should not be compressed.
Another interesting example he demonstrated was running a query on a large table which has text fields. He used char(2000) and varchar(2000) as data types for two tests and compared the execution, leading to the conclusion that varchar types cause more IO so queries run slower. As an interesting trick how to reduce CPU load and still have good storage usage, he advised using row compression and the char type, effectively getting optimised storage and fast query processing.
 |
 |
