Compression In SQL Server

Overview

The database is a crucial part of any organization. The main challenge is actually how to manage space because of the ever growing MDF and LDF size just keeps on growing day by day, and the more data the more the SQL Server engine has to perform day in and day out.

Consider a scenario where there are n million rows and size of the database is 10 TB  -- now just consider how much load and SQL Server engine performance.

Example

Consider lots of books on your bookshelf. Now just pick one book --  and even that book has some empty pages. Similarly SQL Server works the same way; the empty pages may contain bits of data but consider the whole book and calculate it and you will find plenty of space available.

Introduction

SQL Server 2008 and later provides an option of Data Compression.

As there are two types of compression, Page and Row compression.

• Row Compression: Row compression is nothing but performing compression on rows,  here it’s much more stable as compared to page level compression. I/O has less buffer time and is easy to implement. Row level compression is suitable for indexes.
  
  
• Page Level Compression: Page level Compression is different from row level compression. Here, in page level compression, the entire page is compressed instead of the row compressed. Here it’s not stable as compared to row level compression because while it inserts or updates there are some performance impacts.

Let’s Start:

We will be using Local PC. Try it on UAT Server where there is lot of Data, and then you will abel to see the difference in both:

• Create Table.
  
  
  
  
• Lets Insert value.
  
  
  
  
• Lets type a command EXEC SP_SPACEUSED to determine how much space its accumulating.
  
  
  
  
• Start with Row level.
  
  
  
  You will see data size reduced.
  
  Now let's see with Page level.
  
  
  
  You can see the difference.
  
  Try it on Server level you will able to see the difference.

Let's Look at the GUI.



A popup will open.



Click On next.



Click on Row level and Click On Calculate Button it will estimate the Size in MB.



Just Click Next.



Click Next and See the details.



Click On Finish.



A Script will get generated; just run that script.



Same with Page level.

Conclusion

This was Compression on SQL Server . Kindly let me know your queries pertaining to this article.