Data Compression and Snapshot Isolation

  • SQLMajor

    Mr or Mrs. 500

    Points: 576

    Comments posted to this topic are about the item Data Compression and Snapshot Isolation

  • Alan Burstein

    SSC Guru

    Points: 61039

    Good article, very interesting.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • TheSQLGuru

    SSC Guru

    Points: 134017

    I wonder if it is page compression at issue here. Unless you are getting a MASSIVE amount of regular insert/update/delete activity with this table the VAST majority of data you hit for OLTP and especially reporting will be normal old pages from the table - which will be compressed and which will result in lower IO. They won't be from tempdb and the version store.

    I wonder if the lack of performance improvement you expected to happen either:

    a) The table is so small (only 12M rows or so, even fat ones, fits in most server's memory these days) that you don't do IO when you hit it in which case if the table is always in RAM

    or

    b) It is the "narly" entity attribute value design I am guessing exists based on the naming scheme you presented. Unless you jump through some specific hoops those are death on a hotplate in SQL Server. :w00t:

    What does the query plan for the report look like?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Vishal Singh

    SSCertifiable

    Points: 5830

    I echo here with @TheSQLGuru as Enabling compression only changes the physical storage format of the data that is associated with a data type but not its syntax or semantics (BOL states the same).

    It also adds up to CPU which needs to be utilized in compression and decompression.

    I would have checked the causes of this bottle neck like post enabling the compression the pages are might be read from disk instead of RAM hence the I/O should be equally efficient.:-P

    In overall aspect compression is to save on space and not to boost performance. 🙂

    People may argue that reducing the size will boost the performance but the internally doesn't works that way as it works decompress the data before operating it.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • SQLMajor

    Mr or Mrs. 500

    Points: 576

    Hi Guys,

    Thanks for your replies!

    I do find articles like http://sqlmag.com/database-performance-tuning/practical-data-compression-sql-server that indicate in addition to storage savings, you may see a performance improvement. Which actually makes sense if your server usage is heavy I/O, but not maxing memory and CPU running at about 10% capacity. I was willing to take the CPU hit to reduce I/O.

    If I have time one day, I would like to rerun the test without snapshot isolation.

    Even the Microsoft PFE said there is a potential performance gain on I/O using data compression. 🙂

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Even the Microsoft PFE said there is a potential performance gain on I/O using data compression

    1) POTENTIAL is just that. It isn't guaranteed, or even likely in some scenarios. Performance can also be WORSE.

    2) I have known some MS PFEs that I wouldn't trust to troubleshoot a single user DEV SQL Server. :w00t: Of course there are plenty that REALLY know their stuff too though!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Amit Pandey - Scalability Engineers

    Old Hand

    Points: 345

    Useful information in the article.

    Compressed pages are persisted as compressed on disk and stay compressed when read into memory.

    There is no in-memory, decompressed copy of the compressed page.

    The savings in logical and physical I/O is largest when tables or indexes are scanned. When singleton lookups (for read or write) are performed, I/O savings from compression are smaller

    As per my implementations on data compression in some of the large reporting environments where scans are inevitable compression do help. I see performance gain of as much as 20-30%. Take an example of the report pulling the records from the order and order details table where most of the orderIDs are joined thus they result in an index scan.

    So the workload is an important factor here to understand whether compression will help or not.

    A pure reporting workload where the OLTP and Reporting are separated typically does get the benefit of compression on a standard storage. Regarding the work load it is important to understand the U and S percentages.

    U: Percent of Update Operations on the Object

    S: Percent of Scan Operations on the Object

    I feel if you look at the execution plan of the query that can tell you a lot as @TheSQLGuru said.

    So next time when you test it, pls test it on a workload that is more towards S. Run some of the larger reports where scans happen.

    I feel you will see performance benefit even if the snapshot is being turned on.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply