SP execution plans going bad?

• Alan Clemmer

  Valued Member

  Points: 57

  More actions

  March 11, 2005 at 12:28 pm

  #85567

  Hi folks:

  We have a couple of stored procedures that are causing problems. It looks like their execution plans are getting corrupted or something like that. After compiling the SPs, they run fine for a while. Then at some point something goes wrong with them and their performance slows to a crawl, causing user delays & timeouts. Recompiling them brings performance back to what it should be, at least until it happens again. A job has been set up to recompile them daily, which (a) is insufficient for the problem and (b) is not a persistent fix anyway.

  Any thoughts/ideas? TIA!!

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  March 11, 2005 at 2:55 pm

  #546742

  See the thread on "Stored Proc vs Query" http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=166363#bm166984

  What may be happening is:

  1. SP executes for first time with particular parameter values that make the optimal plan use an index.

  2. SP cache manager need memory so the SP execution plan is discarded.

  3. SP executes again with particular parameter values that make the optimal plan a table scan.

  4. SP executes again with a different set of parameter values and the cached plan with a table scan is used.

  To confirm this, run the SQL Profilier, filter and capture the statistic for "Stored Procedures", "SP:Recompile".

  To get the optimal plan for each execution of the stored procedure, add the "WITH RECOMPILE" option.

  Also see Mike Metcalf's article titled "Hits and Misses" at

  http://qa.sqlservercentral.com/columnists/mmetcalf/hitsandmisses.asp

  SQL = Scarcely Qualifies as a Language