July 28, 2009 at 2:13 pm
I came across a weird thing while trying to figure out what the most expensive queries are in our overnight loads (SQL 2008 SP1, x64 on Win 2008 x64 in a vitualised environment on quite a powerful machine used as a Dev environment only). I was trying to analyze what the actual query plan was for the most expensive one query, which has 7 joins, of which 3 use UDF's in the JOIN statement (recursive ones since it has to traverse a hierarchy of sectors within a stock exchange sector hierarchy, not necessarily a smart thing to do) which takes about 1 1/2 minutes to run, not too bad since it is executed just once a night,. But I was curious as to what the query plan really was. When I asked to include the actual query plan from SSMS it took more than 11 minutes for the query to run before it came back with an 'Out of space in tempdb' message (don't have the actual message text here). Tempdb is set to allow unlimited growth for both the data and log file and preset to 10GB with 10GB of free space available on the drive they are located on. I watched the file system, and neither file ever grows beyond their preallocated sizes. Does anyone have any idea what may cause this? Can requesting an actual query plan really fill up tempdb or am I missing something here?
July 28, 2009 at 3:03 pm
I could be mistaken, but I think the XML for the query plan is built in tempdb. So, yeah, it could. Doesn't sound like it should in your case, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 6:54 am
It sounds like it was building out execution plans for all the recursions (guess). Instead of trying to get the execution plan by running the query in SSMS, try querying the cache through the DMF sys.dm_exec_query_plan.
Multi-statement table valued UDF's (which I assume is what you've got) are VERY bad candidates for JOINs within queries under the best of circumstances. Have you tried converting them to a recursive CTE?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2009 at 7:33 am
Grant Fritchey (7/29/2009)
It sounds like it was building out execution plans for all the recursions (guess). Instead of trying to get the execution plan by running the query in SSMS, try querying the cache through the DMF sys.dm_exec_query_plan.Multi-statement table valued UDF's (which I assume is what you've got) are VERY bad candidates for JOINs within queries under the best of circumstances. Have you tried converting them to a recursive CTE?
Thanks guys.
Yes, they are indeed multi-statement UDF's, but they return scalar values (two return ints and the other one a bit). Already got rid of one of them because it was invariant anyhow. Called it just once, assigned it to a variable, then used that in the main query. Didn't make a difference though, except that it ran out of space a minute earlier :w00t:
Will try converting it to use CTE's when I get a chance, or even better, get my colleague to do it who wrote the code in the first place. Once I have some results I'll get back to you. But as I said, it is not too crucial and very low on my priority list.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply