locked
Performance improvement with Data Compression RRS feed

  • 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

    • Moved by Kalman Toth Thursday, August 22, 2013 8:41 PM Not db design
    Wednesday, August 21, 2013 9:34 PM

Answers

  • 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

    • Marked as answer by SukhjeetSingh Friday, August 23, 2013 1:16 PM
    Thursday, August 22, 2013 11:02 PM

All replies

  • 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

    Thursday, August 22, 2013 4:39 AM
  • 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

    Thursday, August 22, 2013 6:34 AM
    Answerer
  • 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.

    • Proposed as answer by Naomi N Thursday, August 22, 2013 9:17 PM
    Thursday, August 22, 2013 6:53 AM
  • 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


    Thursday, August 22, 2013 8:40 PM
  • 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

    Thursday, August 22, 2013 8:58 PM
  • 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

    • Marked as answer by SukhjeetSingh Friday, August 23, 2013 1:16 PM
    Thursday, August 22, 2013 11:02 PM