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


Table space usage 2


Table space usage 2

Author
Message
SKYBVI
SKYBVI
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1563 Visits: 3239
I would surely be a scientist, If i could perform that much calculations :-P

I sincerely hope that Rest of the QOD questions does'nt have calculations like this much...

Regards,
SKYBVI
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28703 Visits: 9671
Just to add to the non-fire of the lack of space.

If your default FF is not 100, then you get to run out of space even earlier Whistling!
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14172 Visits: 12197
Hugo,

I really like this kind of question :-) - it helps keep my brain from atrophying (I hope :-D). More please!

I plumped for the error message, because I reckoned an index with 200000 leaf pages would need about 3GB for the rebuild operation, so saw no need to calculate the size of the non-leaf part of the index. Was surprised to be told it was wrong, but not much bothered: mistakes happen.

edit: I didn't even notice the switch from 200000 to 20000 in the explanation (which apart from that was an excellent description of how to calculate the final size); can't imagine it would confuse anyone who did notice it.

Tom

Mike Dougherty-384281
Mike Dougherty-384281
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 Visits: 944
The question is sound. My attention span is not.

I would hope those who are paid to hold the title of DBA are able to do these maths without too much difficulty.

I knew I had only a 1 in 7 chance of guessing correctly. I didn't care about getting the point, I just wanted to read the explanation. Smile

As far as feedback on whether you should post the rest of the series: please do. Your posts are consistently high-quality and I'm sure you've put appreciable effort into your QotD series too. If I continue to incorrectly answer every on of them at least I know the ensuring discussions will be worthwhile reading.
Salmon Hunter
Salmon Hunter
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1504 Visits: 1887
Hugo,

As others have said before me, I also like this type of question. The question certainly made my brain work hard, something it's not used to doing, and the explanation of the answer was very clear despite the typo that I didn't spot until someone else pointed it out.

Having come up with two wrong answers I have learned something today which seems to me to be the whole point of QOD.

Thank you for making me think.
jeff.mason
jeff.mason
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1455 Visits: 2136
Hugo Kornelis (11/16/2011)
vk-kirov (11/16/2011)
...but the question has a minor issue. When I created the QOD database with the DemoTable table and tried to rebuild the index, I got the following error message:
The statement has been terminated.
Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'QOD' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

#$%#$^& !!!

You won't believe the time I spent going over my calculations again and again, to make sure I didn't mess this one up - and then I forget that rebuilding the index will temporarily require double the size. In a few hours, my mailbox will flood with ne wpost notifications of the "I want my point back" type. (kicks self)



OK, so I am not going crazy then. I calculated that it would be over 1 GB for the data size, realized that there was no way that a reindex on that would work, and picked the error. When I saw it was wrong, I scratched my head. Oh, well, mistakes happen, and in principle this is a very useful exercise as all forms of DBAs, developers, infrastructure, production, all of us need to be able to calculate consumed space. So consider this an encouraging vote.
Tony++
Tony++
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2364 Visits: 2028
I'd like more questions like this. One of the biggest technical leaps I made as a DBA was reading the "Inside" book series & learning HOW sql server does it's storage magic. It really helped me think better about the impact of my designs and queries, even for simple things like why using appropriate datatypes will/may results in fewer pages, and why this matters when saving & querying.
It's a big topic, but it's something I think is really helpful to know.

These types of questions are a great refresher to me on the calculations and thought process, and I think it will help others understand the guts of sql better.
OzYbOi d(-_-)b
OzYbOi d(-_-)b
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1346 Visits: 778
jeff.mason (11/16/2011)
Hugo Kornelis (11/16/2011)
vk-kirov (11/16/2011)
...but the question has a minor issue. When I created the QOD database with the DemoTable table and tried to rebuild the index, I got the following error message:
The statement has been terminated.
Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'QOD' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

#$%#$^& !!!

You won't believe the time I spent going over my calculations again and again, to make sure I didn't mess this one up - and then I forget that rebuilding the index will temporarily require double the size. In a few hours, my mailbox will flood with ne wpost notifications of the "I want my point back" type. (kicks self)



OK, so I am not going crazy then. I calculated that it would be over 1 GB for the data size, realized that there was no way that a reindex on that would work, and picked the error. When I saw it was wrong, I scratched my head. Oh, well, mistakes happen, and in principle this is a very useful exercise as all forms of DBAs, developers, infrastructure, production, all of us need to be able to calculate consumed space. So consider this an encouraging vote.


Ditto on what Jeff said! ;-)

Hugo - thank you for this question. like many others here this morning, I enjoyed this question and although I too selected the ERROR for the answer, I totally get what your intentions were and have benefited from the experience today.

Cheers mate! :-)
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1572 Visits: 1046
[quote]Hugo Kornelis (11/16/2011)
Quick message to those who arrive here: I am aware that this question involves a lot of calculations - much more hard work than normal questions. So my compliments on all your hard work, and I hope it paid off by earning you a point.
[quote]
I do seriously like the spirit of the question. Just suprised at the lack of detail in certain aspects. Considering how much attention to detail Hugo usually pays to others QOD's I was suprised. Others have pointed out that some of your statement also contradict ones the articles they reference as do the numbers you have in your math. I can see around that though becuase a SQL data page has been the same size for a long time and your table rows fit.

The primary thing this question is missing from a real world size calculations perspective is if the DB was created with FULL or BULK as the option for logging. Was the log file set to auto-grow? How often is the tran log is backed up?
The calculations on size requirements the DB recovery option, Tran log backup policy, and the size of the logged transactions are even more important that the ones Hugo brings up when determining the amount of space needed to support an OLTP database with 2 million rows in a single table, a char row of random values for a primary key (instead of a finding a candidate key in the data), and other items that would cuase this DB to be costly.

Number one disk space abuser in my world is Databases created with Full recovery model and no Tran log backup or DB backup jobs. It is amazing to me that I have yet to find an environment that does not have at least one database like this.

Also one last note: Not certain if Auto create Statistics was left on or off so I will believe it was the default or on. The tables Primary Key Clustered Index is not at all related to the data stored in the table or to the 2 Million new rows being inserted into it. The stats that would exist before and after the Index rebuild would require more disc space than a good Non-Clustered index. A proper Table Primary Key created from two or more candidate key columns would also create a Clustered Index that could reduce the true space required by this table and all the data structures that it has. The sys.dm_db_missing_index_details management view can be used after the initial data insert is completed to find out exactly what a good non-clustered index would need to be. If you want to find what the table candidate keys should be so you can have the best of both worlds use the sp_special_columns to find information about the column that uniquely identifies rows in the table. This would only work if you had a version of the table with data in it that did not have the current random char(10) value as a Primary Key.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10809 Visits: 11966
Again, thanks for all the nice and encouraging comments. I have now decided to continue with the series. Sorry for those who did not like it and would rather not see it continue; based on the feedback so far, you are in the minority.


SanDroid (11/16/2011)
[quote][b]The main thing this question is missing is if the DB was created with FULL or BULK as the option for logging.

How exactly is that relevant for the answer? No errors due to a full log are possible, as the log file is specified to be "sufficently large".


Some of your statements contradict ones the articles they reference.

Ouch, that is a very embarassing mistake. Can you please elaborate on which of my statements you mean, and which articles they contradict? I thought I checked everything very carefully; if I was wrong, I'd like to know before I start working on the next similar question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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