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 «««23456

Table space usage 2 Expand / Collapse
Author
Message
Posted Wednesday, November 16, 2011 3:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 3,436, Visits: 1,683
rashton (11/16/2011)
Admittedly, getting it right may have biased my opinion, but I have to say that this is possibly the best QotD I've tried so far so please, more like this Hugo!

I would also mention that having done the calculations by hand, I double-checked them by running the code (I used the numbers 1 to 1000000 from a tally table in another DB I keep on hand for the PK column values) then using this query:
SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID('DemoTable'),
NULL,
NULL,
'DETAILED'
);

Since you specified SQL Server 2005 and up in the question, I figured it was a good way of highlighting how the Dynamic Management Views can help us work these things out.


Absolutely awesome method of getting the answer. I agree on the previous points, great question, great discussion, don't mind seeing more.

I will say I got it wrong because I did a rough calculation, saw it was well over 1GB and just assumed you were testing knowledge that the reindex would require double the space. I certainly didn't mind getting it wrong though, and really enjoyed the answer and discussion.

Kenneth

Kenneth


Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1207177
Posted Thursday, November 17, 2011 1:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:02 AM
Points: 1,652, Visits: 2,075
Hugo Kornelis (11/16/2011)
My question is: would you like a few more questions of this kind, or would submitting the rest of the questions I was planning expose me to death threats, stale fruit, or lots of boo-ing and hissing?
This is a serious question. If the majority feels that the QotD should not involve that much work, I will not submit the other similar questions I was planning.


It's a good question and I would like to see more of the same.
Thanks.
Post #1207279
Posted Thursday, November 17, 2011 5:01 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 20,462, Visits: 14,088
good question hugo - looks like Steve has fixed it up.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1207395
Posted Thursday, November 17, 2011 5:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
Keep em coming. They are tough questions but that's how your learn.

http://brittcluff.blogspot.com/
Post #1207428
Posted Thursday, November 17, 2011 2:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:52 PM
Points: 1,380, Visits: 2,681
I like hard questions like this. Please submit others.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #1207903
Posted Monday, November 21, 2011 1:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
Brain hurts. I'm playing catchup a few days late so I didn't run into most of the concerns the others did. My math just sucks and I actually did a full rebuild of the problem and didn't look into all the angles.

More please.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1209620
Posted Tuesday, November 22, 2011 2:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 1,194, Visits: 2,666
Excellent question, keep 'em coming.

----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1210567
Posted Wednesday, November 23, 2011 5:17 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: Monday, April 14, 2014 3:48 AM
Points: 987, Visits: 858
Great question Hugo.
I enjoyed doing the math, even though I got it wrong through a silly calculation error, but it was fun having a dive into the Math behind the Tables. :)



Post #1210885
Posted Friday, February 17, 2012 4:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 9:09 AM
Points: 483, Visits: 242
Thanks for tough question though I liked it alot since it covers the index topic and always an important concept to grasp.
Post #1254234
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse