Performance improvement with Data Compression

Question

I have a question:

What will be impact on execution plan or query cost (execution plan - Estimated & Actual) if data is  compressed (for example, less IO and more CPU Cost involved here)?
As I have compared the execution plans for few queries, before and after data compression but did not find difference in execution plans, though there is improvement in query performance after data compression.

Thanks,
Sukhjeet

Answer 1

Hello Sukhjeet,

Can you read this Whitepaper for more details,it has all your answers i think.

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

Also some QA related to Compression  by Paul Randal

http://www.sqlskills.com/blogs/paul/conference-questions-pot-pourri-9-qa-around-compression-features/

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 2

It depends on what/how you did a compression... We do see performance improvement after did PAGE compression on 30 million table...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

I do not think you may get a good picture of your performance improvement looking at Execution plan here.

You may do a performance test on the module and find the response time. You also need to look at the CPU utilization(ideally a bit of more usage)/IO(better) to get more picture in detail.

Please refer the below article:

http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

The speedup maybe related that more rows fit to a page after compression.

I am moving it to T-SQL.

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 5

Get some of the tech papers from IBM and their DB2 compression improvements.  As usual Microsoft is behind the curve. The execution plan is not where things happen. There is more data transferred per read. It is much faster to decompress data in main storage or SSD than to read it from a conventional disk. DB2 EXPLAIN figures this in its estimates; T-SQL does not. They will catch up. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

To echo what Joe said, compression should affect the CPU cost estimates (higher) and IO cost estimates (lower) compared to operators in the same query plan that are not affected by compression.

In the end, the query plan is just the plan of action. It will say things like "seek the first row and scan the rest" or "scan the entire index", etc. Then it is up to the engine to execute the plan. The runtime conditions determine how many pages are actually read, (depending on the type of compression) how many rows on the page actually have to be decompressed, or how expensive it is to decompress the particular page.

In many cases the query plan (as in: the steps the engine has to perform, and the order in which to do it) will be exactly the same for compressed and uncompressed data sources. But you still might get great benefits.

---

Gert-Jan