February 1, 2013 at 8:56 am
Hi All,
I have a stored procedure that has always ran fine without any problem, the param passed to it is a date, so it gets called daily with the date param increased daily, for some reason it just started to become stuck for hours for a particular day, the previous date worked fine. Now none seems to work. In the past we have had bad plans being used for this proc, we have also had issues in the past with scalar functions, which were later re-written to use a TVF, ironically this has been running fine for 18 months, but now the TVF seems to be the bottleneck now, if the TVF is taken out the query returns in no time.
When its stuck, it’s actually got a CXPACKET wait type, and checking sys.dm_os_waiting tasks for that session returns about 300 rows, it’s just rather strange. I have changed it to use MAXDOP = 4 which doesn’t use a parallel plan, however it’s still stuck as well. I have updated stats with fullscan, used recompile and also cleared cache. The proc, wouldn’t simply run.
February 1, 2013 at 10:24 am
btw, you do know what nolock does?
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 10:31 am
Tried breaking it up? Insert the tvf into a temp table, join the temp table.
Can't tell much about the row count, the plans are estimated, but SQL thinks it's dealing with several millions of rows and it's doing multiple table scans. I'd suggest looking at indexing for the tables involved in this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 10:38 am
Thats what I done in the end recently, I first of all populated the temp table, then updated the columns that use the function seperately, what I dont understand is that within the space of a day, it simply went wrong and ever since it has gone wrong, so its a case of working up till a certain day, and after that day never works again. Nothing has also changed from a schema perspective.
February 1, 2013 at 11:43 am
Either the stats got sufficiently out of date (see referenced blog post) or the data volume increase and passed a tipping point where the plan changed dramatically
There's definitely some room for optimisation, I saw multiple million row+ table and index scans in the plan
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 12:01 pm
The query is a bit suspect to be honest, but since it's a end of day batch. It's not expected to run in seconds, but 4 hrs plus is too long.I have also updated stats with full scan so regarding the blog post, I have taken its recommendations.
Recompiled
Updated stats
Cleared cache
So Im sure it's not paramilitary sniffing, could well be that it has passed that tipping point
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply