Estimating Vardecimal

Question

Post reply

Estimating Vardecimal

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
April 28, 2016 at 8:54 pm

#77477

Comments posted to this topic are about the item Estimating Vardecimal
April 28, 2016 at 11:51 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

TjhomasH6610 SSCommitted Points: 1690 More actions · Answer 1

TjhomasH6610

SSCommitted

Points: 1690

April 29, 2016 at 12:56 am

#1875476

Nice one Steve 🙂

paul s-306273 SSChampion Points: 10730 More actions · Answer 2

paul s-306273

SSChampion

Points: 10730

April 29, 2016 at 1:53 am

#1875478

Nice QOTD.

HappyGeek SSCoach Points: 18729 More actions · Answer 3

HappyGeek

SSCoach

Points: 18729

April 29, 2016 at 3:04 am

#1875492

Good question thanks.

...

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Nice way to end the week. I learned something new, which is always nice. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 5

Thanks for this question. Perhaps even note that the vardecimal storage format is deprecated

and sp_estimated_rowsize_reduction_for_vardecimal is available in SQL Server Enterprise Edition only.

Ken SSCoach Points: 17191 More actions · Answer 6

Thanks for the question Steve. I incorrectly figured it was a trick question since vardecimal was deprecated in favor of row_compression. I wonder how many more versions of SQL Server need to be produced before they finally remove the option?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

It's still available in SQL 2016. My guess is it's not ever going away

The reason the procedure only exists in Enterprise is compression is an Enterprise only feature.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Interesting question.

Is the correct answer really correct? It's rather naive. All the sp gives you is an estimate of average row size reduction and a count of rows, and that only gives you and estimate of how much less space will be occupied by rows. That's not necessarily space saved - it may just be additional empty space in non-empty pages; maybe some of it will be space saved, because some rows may be small enough to fit into the newly unused space - but without additional data you can't make an estimate of space saved out of that. You would need an estimate of the variance of row sizes in the table before compression, an estimate of the variance of row sizes after compression, and estimates of the mean and variance of the amount of empty space in pages before compression before you could get a good answer. So the SP gives you less than half of what you need to get a decent estimate of the saving (or loss) of space caused by applying decimal compression.

Even with that extra information you might want to look at whether the clustering of the table is biased towards rows being roughly ordered by length and if so to what extent is the bias significant - of course it's very unlikely that there's an intentional bias of that sort, but if someone has invented a classification system for objects in the table and the classification is the first element of the clustering key the application designers may have accidentally introduced such a bias without even being aware that they have done so.

Despite that, I don't think that any of the other options is as good an answer as the correct answer, so I guess it's right to call it correct even though it doesn't cover all the nasty nitty gritty stuff that somethimes intervenes in the real world when people attept to estimate things based on insufficient data.

Tom

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

May 2, 2016 at 11:59 am

#1875977

Thanks for the question.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Steve Jones - SSC Editor (4/29/2016)
It's still available in SQL 2016. My guess is it's not ever going away

I would add that you can't enable or disable vardecimal either. It's there and enabled by default. I would also agree that it won't be going away because it is a foundation feature needed by other features (working theory, not fact).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Steve Jones - SSC Editor (4/28/2016)
Comments posted to this topic are about the item <A HREF="/questions/T-SQL/140426/">Estimating Vardecimal</A>

Heh... based on the fact that the Vardecimal format is deprecated, I estimate that you won't need to estimate it for very long. 😛

--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.

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 12

Nice and easy question. Just need to pass the values as below,

USe [DB]

GO

exec sp_estimated_rowsize_reduction_for_vardecimal

@table_name = 'Config'

Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP