April 8, 2010 at 12:13 pm
Greetings all 😀
I have a stored procedure that wipes out and reloads a table each morning. Looking historically this always runs between 3 and 5 seconds...cool no problem. Today however I needed to go in and modify the code and ran into some rather strange behavior.
I noticed that attempting to execute the SELECT part of the INSERT statement ran indefinitely (I let it go for 10 minutes before I gave up, no results returned at all). Note I had not yet made any changes but was simply running the select statement to refamiliarize myself with the results the query already returned (About 38k rows of data). After a few hours of playing around I decided to throw an OPTION (RECOMPILE) at the bottom of the query, it completed in 2 seconds! any time I attempt to run this query without the recompile it just runs without returning results (or at least not within a reasonable amount of time!). Seeing as I am running a query with no variables, just a straight foward select statement, should this not be using a cached plan? The query has not changed in 6 months.
I am using SQL Server 2008 enterprise 64bit version.
The only non-normal piece to this query is that I create a temp table that uses the row_number function and it is recursively used within the select statement.
I can post the code if necessary but I wanted to see if there was a simple answer/solution first before I go through the hassle of editing my code for posting 🙂
Link to my blog http://notyelf.com/
April 8, 2010 at 12:23 pm
The query hasn't changed, but the underlying data, index fragmentation, statistics accuracy etc. all has. It could also have just had a "bad" plan.
I've had 1 particular query that spazzed out like that on occasion. 99.9% of the time it ran in 3 or 4 ms. .01% of the time it ran for an indefinite period of time until I killed it. I could never duplicate the behavior when I tried though, so I never did get to the bottom of it. I have a pretty good guess as to what it's doing when it does that, but no idea *why* it chose to do so.
April 8, 2010 at 12:50 pm
That makes sense, the latter sound like a lock occuring that .01% of the time.
I figured out the solution. The database in question has forced parameterization, I decided to change it to simple to see if that would make a difference and it did.
Im assuming that the stored procedure has a good plan and is not forcing the literal values into parameters as it states in the online books description of forced parameterization. I do have several calculated literal values within the query itself so I am thinking this is causing the engine to have a plausible 'sniffing' issue. Of course I am no master of this so I could be completely wrong, but something in my brain had the right idea 🙂
Link to my blog http://notyelf.com/
April 9, 2010 at 6:52 am
shannonjk (4/8/2010)
The only non-normal piece to this query is that I create a temp table that uses the row_number function and it is recursively used within the select statement.
#temp table or table variable? How many rows would it typically contain?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 9, 2010 at 10:26 am
A #temp table. Typcially containing 50k rows and 5 columns.
Link to my blog http://notyelf.com/
April 9, 2010 at 9:49 pm
Have you turned off automatic statistics updates in tempdb? Or is async update stats on?
I'm just guessing at possible reasons here - it may be that your query just is not optimized well, for whatever good reason. If you could post an estimated execution plan without the RECOMPILE hint, and an actual execution plan with the RECOMPILE hint (as *.sqlplan files) it will be easier to identify the reason.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply