November 16, 2011 at 2:36 pm
Hugo Kornelis (11/16/2011)
However, technically speaking they are not really correct. The message you get when running out of space in an index rebuild is different from the message I gave in the answer options (which, if I recall correctly, I copied from SSMS after forcing SQL Server to run out of space while inserting rows).
Now, see, I didn't even check for the correct error. I played by the rules and pulled up the calculator and got row size and got your first set of numbers (page size per row) and figured out how big the table was, and looked for "error" and stopped. I didn't even check that it was the exact error you'd get. So that'd be one on me then!
November 16, 2011 at 2:42 pm
jeff.mason (11/16/2011)
Now, see, I didn't even check for the correct error. I played by the rules and pulled up the calculator and got row size and got your first set of numbers (page size per row) and figured out how big the table was, and looked for "error" and stopped. I didn't even check that it was the exact error you'd get. So that'd be one on me then!
To clarify my previous reply, I think you (and everyone else who selected the error option) deserve the point. Not getting a point for a nitty gritty difference in the error message is, in my opinion, not in the spirit of the QotD. The question should test your understanding of SQLL Server, not your reading skills.
FYI, I have sent Steve a PM requesting him to award points back to everyone who chose the error option, and to change the question to specify a 3.5 GB data file (so that now the error message is no longer correct).
November 16, 2011 at 2:49 pm
Thank you Hugo, now I understand better not only the sql exercise of the qotd
but also the exercise of making a good question.
Well done! I hope to see more questions like this one.
Iulian
November 16, 2011 at 2:53 pm
and I am sorry about your mater db 🙂
November 16, 2011 at 2:59 pm
Edit: Whatever...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 3:35 pm
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 FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 17, 2011 at 1:19 am
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.
November 17, 2011 at 5:01 am
good question hugo - looks like Steve has fixed it up.
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
November 17, 2011 at 5:47 am
Keep em coming. They are tough questions but that's how your learn.
http://brittcluff.blogspot.com/
November 17, 2011 at 2:02 pm
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
November 21, 2011 at 1:21 pm
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. :w00t:
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2011 at 2:49 pm
Excellent question, keep 'em coming.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 23, 2011 at 5:17 am
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. 🙂
February 17, 2012 at 4:06 pm
Thanks for tough question though I liked it alot since it covers the index topic and always an important concept to grasp.
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply