Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?

share|improve this question
2  
Which RDBMS are you talking about here? Oracle? SQL Server? Something else? –  Mike Fal Feb 28 '13 at 21:15
    
SQL Server 2008 –  Liton Uddin Feb 28 '13 at 21:25
add comment

1 Answer

up vote 9 down vote accepted

In SQL Server, shrinking a database/datafile and compression are not the same. Shrinking a file (which, by the way, isn't really recommended) is the process of removing unused space from data files in your database. When files are created, SQL Server "reserves" space by sizing files out (depending on how the file is created), even if it doesn't actually have data to fill that space up. The idea is that you'll eventually put something in there (but not always). Shrinking removes this empty space.

Data compression, on the other hand, compresses your objects to reduce the space within the data file they take up. There are a couple different types of compression, but what they all end up doing is reducing the physical space required by your tables and indexes.

Consider you have a 1 GB data file with 600 MB worth of actual data in it. The two operations will have the following affects:

  • Shrink: You will resize the data file down to 600 MB. This is the limit, because you still need capacity for the data.
  • Compress: You will resize your data from 600 MB to some amount less (depending on how good the algorithms can compress it), but your data file will remain 1 GB.

To your second question, compression is more CPU intensive(it has to uncompress the data to make use of it), but uses less disk IO (fewer bits to pull off the disk). It might affect your query performance if you are CPU constrained. The only way to properly evaluate is to test.

share|improve this answer
3  
+1 In short: Shrinking just frees and moves pages. Compression actually changes the data on each page. And both of these options are different from index defragmentation too, which is only slightly related to disk defragmentation, and from index rebuilding - both of which can free space, make I/O more efficient, or both. –  Aaron Bertrand Feb 28 '13 at 21:42
2  
Also 99% of systems out there that aren't on Fusion-IO or really good SSD configs are I/O-bound, not CPU-bound, and unless the system is vastly underpowered, compression is almost always worth it. To know for sure, though, as with a lot of other things, you need to test. –  Aaron Bertrand Feb 28 '13 at 21:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.