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 «««3,8983,8993,9003,9013,902»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 7,100, Visits: 6,927
Evil Kraig F (2/20/2013)
Hey folks, can I get some help here: http://www.sqlservercentral.com/Forums/Topic1421888-392-1.aspx#bm1421965

A very pleasant newbie... I just don't understand what he's trying to say. New eyes on the problem might make sense of what he's attempting.


I posted a suggestion, don't know if it will help though



Far away is close at hand in the images of elsewhere.

Anon.

Post #1422434
Posted Thursday, February 21, 2013 2:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
Right now, I'm feeling embarrassingly stupid.

I was trying to put together a submission for QotD. But I thought I'd seen too many comments recently where people had run code where really if running that code was necessary to get the answer they need a very basic remedial SQL course, so I wanted to forulate the code in such a way that they couldn't reasonably run it because it would run for too long (or at least restrict the ability to run it to people who had enough personal resources to run it without getting fed up with waiting for a result, rather than running it on company machines because it would take a nasty chunk of machine power). At the same time, I wanted to be able to run it to make sure that my answer was correct. So I put together the first part of the code, which would generate data for a table or CTE (hadn't decided yet) that would take a silly amount of both CPU and disc activity to generate, in a form that I thought I could sensibly run on my rather underpowered machine and leave to complete while I went off to make and drink some coffee, make and eat some sandwiches, and pour and swallow a couple of jars.
I came back to my machine and found that the code had finished in less than two minutes - and failed horribly: the error message was
Msg 1101, Level 17, State 10, Line 12
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. 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.

The problem was incredibly stupid (). An intermediate result had to be either explicitly materialised as a (temp) table or spooled by the optimiser (the latter was the case in the version of the set-up that I tried). I had only a few 10s of gigabytes to spare. The intermediate result, if the machine had managed to generate and store it, would have had - as I discovered by doing some straightforwards arithmetic, which I ought to have done before setting it running - a little more than 2**48 (256 trillion - counting 1024 instead of 1000 as thousand) rows (actually 281475010265089, which is 1+2**25+2**48).
Maybe that possible QotD will never see the day - I'll have to see if I can make it smaller without turning it into trivia (the crux was going to be a MERGE statement with three merge clauses, all of them with clause search conditions - maybe I should try to curb my liking for bizarre questions).


Tom
Post #1422792
Posted Thursday, February 21, 2013 2:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 5,601, Visits: 25,000
L' Eomot Inversé (2/21/2013)
Right now, I'm feeling embarrassingly stupid.

I was trying to put together a submission for QotD. But I thought I'd seen too many comments recently where people had run code where really if running that code was necessary to get the answer they need a very basic remedial SQL course, so I wanted to forulate the code in such a way that they couldn't reasonably run it because it would run for too long (or at least restrict the ability to run it to people who had enough personal resources to run it without getting fed up with waiting for a result, rather than running it on company machines because it would take a nasty chunk of machine power). At the same time, I wanted to be able to run it to make sure that my answer was correct. So I put together the first part of the code, which would generate data for a table or CTE (hadn't decided yet) that would take a silly amount of both CPU and disc activity to generate, in a form that I thought I could sensibly run on my rather underpowered machine and leave to complete while I went off to make and drink some coffee, make and eat some sandwiches, and pour and swallow a couple of jars.
I came back to my machine and found that the code had finished in less than two minutes - and failed horribly: the error message was
Msg 1101, Level 17, State 10, Line 12
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. 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.

The problem was incredibly stupid (). An intermediate result had to be either explicitly materialised as a (temp) table or spooled by the optimiser (the latter was the case in the version of the set-up that I tried). I had only a few 10s of gigabytes to spare. The intermediate result, if the machine had managed to generate and store it, would have had - as I discovered by doing some straightforwards arithmetic, which I ought to have done before setting it running - a little more than 2**48 (256 trillion - counting 1024 instead of 1000 as thousand) rows (actually 281475010265089, which is 1+2**25+2**48).
Maybe that possible QotD will never see the day - I'll have to see if I can make it smaller without turning it into trivia (the crux was going to be a MERGE statement with three merge clauses, all of them with clause search conditions - maybe I should try to curb my liking for bizarre questions).


DON'T give up...... I have had to "dumb down" many a QOD -
AND remember one of the main purposes of a QOD is to TEACH.
So you could/can relate your above experience in the discussion that normally follows any QOD. That experience is good teaching.



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1422800
Posted Thursday, February 21, 2013 5:23 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: Today @ 5:37 AM
Points: 3,627, Visits: 5,275
L' Eomot Inversé (2/21/2013)
a little more than 2**48 (256 trillion - counting 1024 instead of 1000 as thousand) rows (actually 281475010265089, which is 1+2**25+2**48).


Now that's a test harness! Jeff take notice.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1422828
Posted Friday, February 22, 2013 12:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 PM
Points: 6,250, Visits: 7,409
Heh, this seems to be becoming thematic for my posts in the thread...

Anyone around here worked much with Extended Events? It's way out of my scope, I only know the name.

If you have, would you mind poking at here: http://www.sqlservercentral.com/Forums/Topic1421470-3077-1.aspx



- 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 #1423214
Posted Friday, February 22, 2013 2:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
dwain.c (2/21/2013)
L' Eomot Inversé (2/21/2013)
a little more than 2**48 (256 trillion - counting 1024 instead of 1000 as thousand) rows (actually 281475010265089, which is 1+2**25+2**48).


Now that's a test harness! Jeff take notice.


BWAA-HAAA!!!! Tom claims to have an "underpowered" machine but he has the HD space to store 1/4 Quadrillion rows? "Underpowered" my patooti!

If his machine is as underpowered as my desktop, it will take approximately 40 minutes to gen a billion rows. I know because I tried it a couple of years ago. That means that it would take approximately 19.469 years to generate that many rows on his machine and only if he could avoid the log file from exploding by building the rows in sets of a mere billion or so.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423268
Posted Friday, February 22, 2013 2:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
Evil Kraig F (2/22/2013)
Heh, this seems to be becoming thematic for my posts in the thread...

Anyone around here worked much with Extended Events? It's way out of my scope, I only know the name.

If you have, would you mind poking at here: http://www.sqlservercentral.com/Forums/Topic1421470-3077-1.aspx


I've not had the opportunity to work on Extended Events nor to even read up on them. My only comment is one of shear shock and mortification... "REALLY???!!!!! XML???!!!!!" What the hell were they thinking when they wrote the spec on that little pearl? Even a CSV would have been better so they could avoid the tag-bloat! What's next? A bloody Ribbon-Bar????


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423270
Posted Friday, February 22, 2013 3:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
Jeff Moden (2/22/2013)
dwain.c (2/21/2013)
L' Eomot Inversé (2/21/2013)
a little more than 2**48 (256 trillion - counting 1024 instead of 1000 as thousand) rows (actually 281475010265089, which is 1+2**25+2**48).


Now that's a test harness! Jeff take notice.


BWAA-HAAA!!!! Tom claims to have an "underpowered" machine but he has the HD space to store 1/4 Quadrillion rows? "Underpowered" my patooti!

No, it never had to store that many rows as it never managed to generate them (because it ran out of space while trying).
If his machine is as underpowered as my desktop, it will take approximately 40 minutes to gen a billion rows. I know because I tried it a couple of years ago.
I'll generate a billion row table and see how long it takes on my (definitely underpowered) machine.


Tom
Post #1423280
Posted Friday, February 22, 2013 4:45 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
Brandie Tarvin (2/13/2013)
Grant Fritchey (2/13/2013)
I think we're back to the Roadhouse rule set here. Be nice.


And always keep a set of spare tires in the trunk.


SO long as it is a semi beatup muscle car.




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 #1423292
Posted Friday, February 22, 2013 4:49 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: Friday, August 29, 2014 8:34 AM
Points: 3,796, Visits: 1,152
OK, so you're aware, this is post #39,000 on this thread.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.
Post #1423294
« Prev Topic | Next Topic »

Add to briefcase «««3,8983,8993,9003,9013,902»»»

Permissions Expand / Collapse