Data Compression and Snapshot Isolation

Question

Data Compression and Snapshot Isolation

SQLMajor

Mr or Mrs. 500

Points: 576
More actions
October 2, 2014 at 12:28 am

#293907

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

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

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 355 More actions · Answer 6

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.