Compression estimates

  • Evgeny Garaev

    SSCertifiable

    Points: 6602

    Comments posted to this topic are about the item Compression estimates

  • Jeff Moden

    SSC Guru

    Points: 996673

    It'll be interesting to see someone's face when they run this code even on a giant database right after a service bounce. 😉  A little embedded documentation would go a long way.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Evgeny Garaev

    SSCertifiable

    Points: 6602

    Jeff Moden - Wednesday, January 17, 2018 5:20 PM

    It'll be interesting to see someone's face when they run this code even on a giant database right after a service bounce. 😉  A little embedded documentation would go a long way.

    I have run it on 2Tb database. 😛 But you are right it make sense to execute it on a UAT/Test database (if it is an exact copy of a production database) instead of a production database.

  • Jeff Moden

    SSC Guru

    Points: 996673

    Evgeny Garaev - Wednesday, January 17, 2018 8:23 PM

    Jeff Moden - Wednesday, January 17, 2018 5:20 PM

    It'll be interesting to see someone's face when they run this code even on a giant database right after a service bounce. 😉  A little embedded documentation would go a long way.

    I have run it on 2Tb database. 😛 But you are right it make sense to execute it on a UAT/Test database (if it is an exact copy of a production database) instead of a production database.

    I was talking about the fact that it appears that the first temporary table will have little to nothing available because the usage stats (sys.dm_db_index_usage_stats) are cleared on restart of the service.  I also wonder why a lot of people don't comment their code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Evgeny Garaev

    SSCertifiable

    Points: 6602

    Jeff Moden - Wednesday, January 17, 2018 10:44 PM

    I was talking about the fact that it appears that the first temporary table will have little to nothing available because the usage stats (sys.dm_db_index_usage_stats) are cleared on restart of the service.  I also wonder why a lot of people don't comment their code.

    Thanks. I see what you mean, I have another version with left outer join...

  • mike_the_dba

    Valued Member

    Points: 66

    Jeff Moden - funny, I was thinking about how you'd react if you saw this code, and BAM, there you are!

    Evgeny - great code.  I'm trying to think if there's a way to run this without the RBAR.  I guess you could dynamically create the INSERT... EXEC statements, but that's essentially the same thing.

    I do have a couple of questions: 

    1. Why the 'ORDER BY IndexSizeKB DESC' on the initial SELECT statement?  It doesn't matter which order the rows are processed and inserted into the table since you don't have to order your results coming out of the cursor.
    2. Secondly, you could run 'SELECT * INTO #estimates_tmp_r FROM #estimates_tmp_p WHERE 1 = 1' instead of a second CREATE TABLE statement.  That probably won't make much of a difference in resources, creation time, etc.  In fact, the execution plan shows the CREATE TABLE statement as taking 0% of the query cost when run along with the SELECT * INTO...  

    Does anyone else have any comments on my ideas here?

  • Jeff Moden

    SSC Guru

    Points: 996673

    Mike Stuart-386150 - Thursday, February 15, 2018 3:58 PM

    Jeff Moden - funny, I was thinking about how you'd react if you saw this code, and BAM, there you are!

    Is that a god thing or a bad thing?  :pinch:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mike_the_dba

    Valued Member

    Points: 66

    Probably not a 'god' thing - lol.
    Any thoughts on how to make this code behave better, i.e., no RBAR processing?

    Mike

  • Jeff Moden

    SSC Guru

    Points: 996673

    mike_the_dba - Thursday, February 15, 2018 4:48 PM

    Probably not a 'god' thing - lol.
    Any thoughts on how to make this code behave better, i.e., no RBAR processing?

    Mike

    Heh... no pun intended but I hate today's damned keyboards.  They provide no tactical feedback as to if the key-press was deep enough to register.

    The only way to make this non-RBAR would be to rewrite the functionality provided by sp_estimate_data_compression_savings, which is loaded with RBAR to begin with.  I don't know why Microsoft continues to write single object stored procedures other than it's an easy thing to do and control loops (as opposed to row loops) aren't that expensive and so they leave such things up to the users.

    I rewrote sp_spaceused for my own purposes when I needed single result set returns for many objects (worked really well for seeing the meta-data "guts" of partitioned tables all at once, as well).  You can also see the code for sp_estimate_data_compression_savings using sp_helptext, so someone could rewrite it to be less RBAR in nature but that probably won't be me because I don't actually have a need to do so and the number of times that someone might actually use it are pretty small.

    There are other scripts similar to the one posted out there that will return compression estimates even if compression is already in play.  That's kind of important if you're into this kind of thing because it just may be that the original type of compression isn't the best and it would be nice to see if compressing or even decompressing might be a better thing to do.  They also don't depend on data that might not be there after a service bounce.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • OnePunchDBA

    SSC Rookie

    Points: 38

    Good work but looks like you have reinvented the wheel, which does happen very often in the scripts area.
    Estimate compression for all tables and indexes with both Row and Page

  • Jeff Moden

    SSC Guru

    Points: 996673

    OnePunchDBA - Wednesday, February 21, 2018 7:13 AM

    Good work but looks like you have reinvented the wheel, which does happen very often in the scripts area.
    Estimate compression for all tables and indexes with both Row and Page

    Heh... re-inventing the wheel is sometimes what's needed.  I'm really glad than today's vehicles have something other that thick rubber bands on a wooden spoked  wheel. 😉  Conversely, I'm really glad that we're not using Reuleaux Triangles for wheels even though they are constant diameter. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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