none
Use of SQL server table compression RRS feed

  • Question

    1. What is the use of the table compression?
    2. when do we need to compress the table ?
    3. If i compress the table what will be the performance impact


    Kamal

    Tuesday, September 30, 2014 8:55 AM

Answers

  • 1. What is the use of the table compression?

    Save disk space and sometimes also gain performance.

    2. when do we need to compress the table ?

    Need is maybe not the best word, but when we want to reduce disk space and/or make the performance gains, we would consider compression.

    And, not to forget, if we are on Enterprise Edition. Compression is not available in other editions.

    3. If i compress the table what will be the performance impact

    There are two levels of compression: ROW and PAGE. ROW is basically a different storage format, which gives a more compact format for most data profiles. Not the least if you have plenty of fixed-length columns that are often NULL. ROW compression has a fairly low CPU overhead. Since compression means that the data takes up less space, this means a scan of the full table will be faster. This is why you may gain performance.

    Page compression is more aggressive and uses a dictionary. You can make a bigger gain in disk space, but the CPU overhead is fairly considerable, so it is less likely that you will make a net gain.

    To find out how your system is affected, there is a stored procedure, of which I don't recall the name right now, which can give you estimated space savings. But if you also want to see the performance effects, you will need to run a test with your workload.

    There is also columnstore, which also is a form a compression, and which for data warehouses can give enormous performance gains.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, September 30, 2014 9:03 AM
  • 1. For compression the table data.
    2. When ever we want to compress the table data.
    3. basically, more CPU, and less storage place

    check this link:

    http://msdn.microsoft.com/en-us/library/cc280449.aspx


    [Personal Site]  [Blog]  [Facebook]
    signature

    Tuesday, September 30, 2014 9:05 AM
    Moderator
    1. What is the use of the table compression?
    2. when do we need to compress the table ?
    3. If i compress the table what will be the performance impact


    Kamal

    Kamal,

    Please read below Link it has answer to all your question

    http://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx

    Compression is also there in Developer edition just to make things more clear. Compression is not all Unicorns and rainbows it can and cannot have benefit and it totally depends on workload data types and query you run. It is quite possible that you might just need row compression and not page compression because page compression causes more CPU overhead than row and is not guaranteed to give performance benefit which is notable.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Tuesday, September 30, 2014 9:37 AM
  • Table compression means: You need fewer pages on disk to store your data. Thus IO is reduced. This can lead to a performance gain, when you need to retrieve these pages often from disk (cold buffers).
    Tuesday, September 30, 2014 9:44 AM
    1. What is the use of the table compression? Reduction in DiskSpace as well as reduction in I/O happening across memory and CPU.
    2. when do we need to compress the table ? If your table column contains lot of wide character strings (char/varchar) columns, then you will get the best compression. Can go for Row/Page level compression, Page level has highest level of compression.
    3. If i compress the table what will be the performance impact No, in most of the cases. But you will get good perf because of reduction in I/O, because many times I/O is the main culprit. CPUs being more powerful can compress/uncompress data within no time compared to the time taken by I/O.

    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Tuesday, September 30, 2014 9:57 AM

All replies

  • 1. What is the use of the table compression?

    Save disk space and sometimes also gain performance.

    2. when do we need to compress the table ?

    Need is maybe not the best word, but when we want to reduce disk space and/or make the performance gains, we would consider compression.

    And, not to forget, if we are on Enterprise Edition. Compression is not available in other editions.

    3. If i compress the table what will be the performance impact

    There are two levels of compression: ROW and PAGE. ROW is basically a different storage format, which gives a more compact format for most data profiles. Not the least if you have plenty of fixed-length columns that are often NULL. ROW compression has a fairly low CPU overhead. Since compression means that the data takes up less space, this means a scan of the full table will be faster. This is why you may gain performance.

    Page compression is more aggressive and uses a dictionary. You can make a bigger gain in disk space, but the CPU overhead is fairly considerable, so it is less likely that you will make a net gain.

    To find out how your system is affected, there is a stored procedure, of which I don't recall the name right now, which can give you estimated space savings. But if you also want to see the performance effects, you will need to run a test with your workload.

    There is also columnstore, which also is a form a compression, and which for data warehouses can give enormous performance gains.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, September 30, 2014 9:03 AM
  • 1. For compression the table data.
    2. When ever we want to compress the table data.
    3. basically, more CPU, and less storage place

    check this link:

    http://msdn.microsoft.com/en-us/library/cc280449.aspx


    [Personal Site]  [Blog]  [Facebook]
    signature

    Tuesday, September 30, 2014 9:05 AM
    Moderator
    1. What is the use of the table compression?
    2. when do we need to compress the table ?
    3. If i compress the table what will be the performance impact


    Kamal

    Kamal,

    Please read below Link it has answer to all your question

    http://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx

    Compression is also there in Developer edition just to make things more clear. Compression is not all Unicorns and rainbows it can and cannot have benefit and it totally depends on workload data types and query you run. It is quite possible that you might just need row compression and not page compression because page compression causes more CPU overhead than row and is not guaranteed to give performance benefit which is notable.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Tuesday, September 30, 2014 9:37 AM
  • Table compression means: You need fewer pages on disk to store your data. Thus IO is reduced. This can lead to a performance gain, when you need to retrieve these pages often from disk (cold buffers).
    Tuesday, September 30, 2014 9:44 AM
    1. What is the use of the table compression? Reduction in DiskSpace as well as reduction in I/O happening across memory and CPU.
    2. when do we need to compress the table ? If your table column contains lot of wide character strings (char/varchar) columns, then you will get the best compression. Can go for Row/Page level compression, Page level has highest level of compression.
    3. If i compress the table what will be the performance impact No, in most of the cases. But you will get good perf because of reduction in I/O, because many times I/O is the main culprit. CPUs being more powerful can compress/uncompress data within no time compared to the time taken by I/O.

    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Tuesday, September 30, 2014 9:57 AM