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 12Could 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).

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 12Could 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.

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.

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."

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."