Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Table space usage 2 Expand / Collapse
Author
Message
Posted Wednesday, November 16, 2011 6:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 6:14 AM
Points: 1,200, Visits: 3,237
I would surely be a scientist, If i could perform that much calculations

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

Regards,
SKYBVI
Post #1206609
Posted Wednesday, November 16, 2011 6:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 12:48 AM
Points: 21,388, Visits: 9,604
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 !
Post #1206610
Posted Wednesday, November 16, 2011 6:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 8,577, Visits: 9,090
Hugo,

I really like this kind of question - it helps keep my brain from atrophying (I hope ). 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
Post #1206617
Posted Wednesday, November 16, 2011 7:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:28 AM
Points: 257, Visits: 902
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. :)

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.
Post #1206704
Posted Wednesday, November 16, 2011 7:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:40 AM
Points: 1,006, Visits: 1,524
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.
Post #1206715
Posted Wednesday, November 16, 2011 7:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:13 PM
Points: 989, Visits: 1,821
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.
Post #1206719
Posted Wednesday, November 16, 2011 8:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 1,475, Visits: 1,587
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.
Post #1206762
Posted Wednesday, November 16, 2011 8:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, 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!
Post #1206831
Posted Wednesday, November 16, 2011 8:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
[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.
Post #1206844
Posted Wednesday, November 16, 2011 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 5,930, Visits: 8,181
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
Post #1206875
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse