locked
Compression on indexes, how to check performance improvement ? RRS feed

  • Question

  • Hi all,

    I have some questions regarding rebuilding the indexes.

    I read that it is a good practice to also compress indexes. In our case, I executed the following query

    exec sp_estimate_data_compression_savings 'inv','L_INVENTORY',NULL,NULL,'PAGE'
    exec sp_estimate_data_compression_savings 'inv','L_INVENTORY',NULL,NULL,'ROW'

    Result:

    As you can see, the PAGE compression on the index saves up quite some space. However, I also read that compressing indexes is not always a benefit, because it can decrease performance?

    In my situation, the index is in Data Vault, this means that INSERT happen, updates happen but also SELECT queries on the tables when filling the Data Marts. To note, the inserts are usually not much (+- 1/2 mil rows) but the select query from EDWH - DM is quite big.

    This makes me wonder, when chosing Page compression, the select query seems to be better, but the inserts are taking a lot longer. How can I really know what is the best in my situation? How is it usually tested? 

    Change the compression to page and monitor the load for a few days? What do you guys suggest in my situation concerning index compression?

    Also, probably my main question, does compressing the index makes rebuilding it faster?

    Thanks!



    • Edited by Yvanlathem Wednesday, April 13, 2016 9:34 AM
    Wednesday, April 13, 2016 9:28 AM

Answers

  • I can suggest you to document which has formula to calculate before hand as to whether to go for page or row compression and whether actually it would be beneficial.

    Data Compression Strategy and Planning

    Look at section "deciding when to compress". The Sp you used to calculate will only give you space saving not the benefit if any. The document above is complete and thorough.


    Cheers,

    Shashank

    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 Wiki Articles

    MVP

    • Marked as answer by Yvanlathem Wednesday, April 13, 2016 9:54 AM
    Wednesday, April 13, 2016 9:40 AM
    Answerer

All replies

  • I can suggest you to document which has formula to calculate before hand as to whether to go for page or row compression and whether actually it would be beneficial.

    Data Compression Strategy and Planning

    Look at section "deciding when to compress". The Sp you used to calculate will only give you space saving not the benefit if any. The document above is complete and thorough.


    Cheers,

    Shashank

    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 Wiki Articles

    MVP

    • Marked as answer by Yvanlathem Wednesday, April 13, 2016 9:54 AM
    Wednesday, April 13, 2016 9:40 AM
    Answerer
  • Thanks a lot!

    I noticed that compressed indexes takes a lot longer to rebuild. In my case, I have very low U and very high S, suggesting to go for PAGE COMPRES.

    However, if I look at our environment 

    Most time is lost due to rebuilding the indexes. In my case, I should just leave the indexes uncompressed since the inserts are going very fast as you can tell by the logging.

    Wednesday, April 13, 2016 9:55 AM
  • Thanks a lot!

    I noticed that compressed indexes takes a lot longer to rebuild. In my case, I have very low U and very high S, suggesting to go for PAGE COMPRES.

    However, if I look at our environment 

    Most time is lost due to rebuilding the indexes. In my case, I should just leave the indexes uncompressed since the inserts are going very fast as you can tell by the logging.

    Its totally your call I would suggest. But its not necessary you compress all tables in a database like for table which you are saying is access frequently and insert are fast when page is not compressed leave it as is search for table which is least accessed like history table and compress it. Its not going to affect performance much and will give you space saving. Identifying tables which can be compressed and would actually perform satisfactory is what you have to do also remember not all tables can be compressed and would give you better performance.



    Cheers,

    Shashank

    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 Wiki Articles

    MVP

    Wednesday, April 13, 2016 10:15 AM
    Answerer