DBA vs Developer
-
I just wanted to share this. (Disclaimer: This conversation was between a junior developer and a senior DBA).
Developer: You haven't configured tempdb correctly. I keep getting an error that it is running out of space.
DBA: It must be your code, tempdb is configured correctly. Let me see the error message.
Developer: It can't be my code. Here is the error.
DBA: It is definitely your code. You are spilling like crazy. Let me see the estimated execution plan.
Developer: The what?
DBA: Let me show you.
-
Hmm, 4,85 x 10^14, that would be one row for each mile of blood vessel in every human on earth:w00t:
😎
-
You mean you didn't flip the infinity switch on your tempdb? That developer is right, you have it configured incorrectly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi Grant,
This is the first time I see about infinity switch. Does it mean unlimited growth in TempDB? If not, would you pleasee explain and how can I switch it?
Thank you in advance.
JJ
-
jarupan (7/18/2014)
Hi Grant,This is the first time I see about infinity switch. Does it mean unlimited growth in TempDB? If not, would you pleasee explain and how can I switch it?
Thank you in advance.
JJ
You need to request it to Microsoft. It's an option exclusive to MVPs that have written more than one book.
Seriously, that was a joke from him. :hehe:
-
Grant people take you seriously... Be considerate... 😀
Cheers
-
jarupan (7/18/2014)
Hi Grant,This is the first time I see about infinity switch. Does it mean unlimited growth in TempDB? If not, would you pleasee explain and how can I switch it?
Thank you in advance.
JJ
Apologies.
That was humor. The OP was illustrating a semi-funny situation with the large estimate and his developers response to the problem. I responded in kind. Sorry for the confusion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
For the fun of it you wish you could increase the TEMPDB to 93000TB and then watch how the developer handles the resultset in the application. He probably will loop through each row to get to the single one row he needs....
Edit: forgot to type the 0's with the required size. I'm not used to deal with such high volumes...
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
That is funny. Think about it - 92045 TB? Since I don't work for the government, so I can't fathom spending that kind of money on hardware. Then again, it might help if they used a petabyte SAN built with SSDs. What's next? Naturally, make it a memory-optimized table. 😀
PS: This is also intended as humor.
-
Thank Grant for verify it, do not take it seriously. I can not assume that I know everything.
For example in 2003, I worked as consultant in Title company in Ohio. One DBA wanted me to help him for his usp_ that took ~ 5 minutes to run, I made it to ~ 1 minute to run but it called one system stored procedure, manager happy about that. But VP did not like that , he called Microsoft and change that system stored procedure, after change the job took only 15 seconds to run and that opened my eye. Up until now, I still do not know how he changed it but it was changed . From that time, I open myself to any info.
Some kind of naive but I learn something new everyday.
Thank again
JJ
-
At least 92,045 terabytes is below the maximum database size of 524,272 terabytes, but you would need to have at least 5,753 files in tempdb at a maximum of 16 terabytes per file.
Using 2 TB drives at 480 drives per rack, you would need 95 racks to hold the drives for tempdb, and a few more if you use raid.
-
Any idea what query was causing that?
The Redneck DBA
-
Sean, do you have the actual execution plan for comparison? I've seen obscene row estimations before, I suspect caused by the optimiser thinking that a one-to-many join was a many-to-many in some cases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (8/1/2014)
Sean, do you have the actual execution plan for comparison? I've seen obscene row estimations before, I suspect caused by the optimiser thinking that a one-to-many join was a many-to-many in some cases.No, I don't have it. The cause was bad join logic so I didn't bother trying to get it to run through for accurate row counts and just sent the developer off to fix it. There were about 20 joins but when I asked the developer what uniquely identified the row he couldn't tell me. Eish. Besides, all the joins were left joins and the developer couldn't tell me why.
-
Sean Pearce (8/2/2014)
GilaMonster (8/1/2014)
Sean, do you have the actual execution plan for comparison? I've seen obscene row estimations before, I suspect caused by the optimiser thinking that a one-to-many join was a many-to-many in some cases.No, I don't have it. The cause was bad join logic so I didn't bother trying to get it to run through for accurate row counts and just sent the developer off to fix it. There were about 20 joins but when I asked the developer what uniquely identified the row he couldn't tell me. Eish. Besides, all the joins were left joins and the developer couldn't tell me why.
Sounds like a report development, seen few of these, normally preceded by a question on database configuration/optimization. Unfortunately not running Grant's magical :w00t: SQL Server version so no infinity switches or pixy dust available.
😎
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply