SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compression estimates


Compression estimates

Author
Message
Evgeny Garaev
Evgeny Garaev
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5148 Visits: 1560
Comments posted to this topic are about the item Compression estimates
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872870 Visits: 47502
It'll be interesting to see someone's face when they run this code even on a giant database right after a service bounce. Wink 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evgeny Garaev
Evgeny Garaev
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5148 Visits: 1560
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. Wink A little embedded documentation would go a long way.

I have run it on 2Tb database. Tongue 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
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872870 Visits: 47502
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. Wink A little embedded documentation would go a long way.

I have run it on 2Tb database. Tongue 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evgeny Garaev
Evgeny Garaev
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5148 Visits: 1560
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
mike_the_dba
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 8
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
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872870 Visits: 47502
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mike_the_dba
mike_the_dba
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 8
Probably not a 'god' thing - lol.
Any thoughts on how to make this code behave better, i.e., no RBAR processing?

Mike
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872870 Visits: 47502
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
OnePunchDBA
OnePunchDBA
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 5
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search