A Better sp_Spaceused

Question

A Better sp_Spaceused

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
November 5, 2016 at 8:30 pm

#75752

Comments posted to this topic are about the item A Better sp_Spaceused
November 7, 2016 at 12:04 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

Geez... it would have been SOOOOOOOO simple for them to make the "improvement" so much more worthwhile. I don't know why they even bothered.

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

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

So they actually did work on it and yet the space "numbers" are still varchar(18). Sigh.

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Ed Wagner (11/6/2016)
So they actually did work on it and yet the space "numbers" are still varchar(18). Sigh.

The good part is that they didn't use FORMAT to format the numbers as VARCHAR. Heh... and notice how they made it return ALL tables and indexed views and with an object type?. 😉

This is almost as bad as a splitter that returns nothing if you send it a NULL and doesn't return ordinal positions of the split-out elements. 😉

What's next? I hate to even think about it. :sick:

I just can't imagine what they're thinking or not thinking about when they do this stuff. If this is being pushed down from a "Release to Azure first" mantra, then we're all in deep Kimchi.

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Jeff Moden (11/6/2016)
Geez... it would have been SOOOOOOOO simple for them to make the "improvement" so much more worthwhile. I don't know why they even bothered.

They had to add the support for the stretched objects, hence the new sys.sp_spaceused_remote_data_archive extended procedure.

😎

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

Eirikur Eiriksson (11/7/2016)
Jeff Moden (11/6/2016)
Geez... it would have been SOOOOOOOO simple for them to make the "improvement" so much more worthwhile. I don't know why they even bothered.
They had to add the support for the stretched objects, hence the new sys.sp_spaceused_remote_data_archive extended procedure.
😎

I knew the reason (it's pretty obvious in BOL/MSDN/TECHNET) but whatever happened to "continuous improvement"? Yeah... I know the answer to that... schedule. It's a real shame that such urgent "clutch cargo fixes" are the norm. It's like that bloody splitter function they came out with... designed for marketing, not for what is needed. Heh... PIVOT, FORMAT, the original release of aggregate()OVER, and the newer temporal datatypes are all great examples of that, as well.

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

Jeff Moden (11/7/2016)
Eirikur Eiriksson (11/7/2016)
Jeff Moden (11/6/2016)
Geez... it would have been SOOOOOOOO simple for them to make the "improvement" so much more worthwhile. I don't know why they even bothered.
They had to add the support for the stretched objects, hence the new sys.sp_spaceused_remote_data_archive extended procedure.
😎
I knew the reason (it's pretty obvious in BOL/MSDN/TECHNET) but whatever happened to "continuous improvement"? Yeah... I know the answer to that... schedule. It's a real shame that such urgent "clutch cargo fixes" are the norm. It's like that bloody splitter function they came out with... designed for marketing, not for what is needed. Heh... PIVOT, FORMAT, the original release of aggregate()OVER, and the newer temporal datatypes are all great examples of that, as well.

I guessed I wasn't telling you anything new:-D

😎

The SQL Server T-SQL code base has some (read zillions) lines of ancient and very bad code, functionalities with bad implementation etc. etc. which certainly could use some makeover but it looks like after the box is ticked then the case is closed forever.

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

Nice question, thanks.

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

Eirikur Eiriksson (11/7/2016)
Jeff Moden (11/7/2016)
Eirikur Eiriksson (11/7/2016)
Jeff Moden (11/6/2016)
Geez... it would have been SOOOOOOOO simple for them to make the "improvement" so much more worthwhile. I don't know why they even bothered.
They had to add the support for the stretched objects, hence the new sys.sp_spaceused_remote_data_archive extended procedure.
😎
I knew the reason (it's pretty obvious in BOL/MSDN/TECHNET) but whatever happened to "continuous improvement"? Yeah... I know the answer to that... schedule. It's a real shame that such urgent "clutch cargo fixes" are the norm. It's like that bloody splitter function they came out with... designed for marketing, not for what is needed. Heh... PIVOT, FORMAT, the original release of aggregate()OVER, and the newer temporal datatypes are all great examples of that, as well.
I guessed I wasn't telling you anything new:-D
😎
The SQL Server T-SQL code base has some (read zillions) lines of ancient and very bad code, functionalities with bad implementation etc. etc. which certainly could use some makeover but it looks like after the box is ticked then the case is closed forever.

Possibly being my own contrarian, it's still some absolutely amazing software.

I wrote a "dedicated" database (one to solve a specific problem) in Turbo Basic (a Basic Compiler) back in the '90s before I knew of SQL. It included what we now know as a "Clustered Index" and "Non-Clustered Indexes" that were formed by a sort routine that I created based on the old IBM card sorter. I'd also setup "date serial number" functions so that I could easily solve the "days between dates" problem and a binary search engine, all of which was nasty fast for the time and the desktop box it was done on (Intel P4).

The reason I'm telling you that is because, although a ton of fun, it was also a PITA. All those bloody loops, substrings, pointers, etc, etc. Knowing all of what I went through, I continue to be astonished at how well SQL Server/T-SQL works (especially the optimizer) and am amazed that they've made data interrogation so bloody simple. If you don't think so, try writing code that will recognize a file as a "table" with different datatypes and the ability to selectively return fields from the file as columns in the output just by using the keyword SELECT and a list of column names. The code and engineering behind any database engine, especially relational databases, is simply remarkable.

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