|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DBCC FREEPROCCACHE
SQL2K, Enterprise Edition. On a heavily loaded OLTP system, I have a stored procedure running tremendously quickly. Nice. Query Profiler mostly reports 0 or 16 msec. Then, for some reason, with the system under the same load, it all of a sudden starts taking multiple *seconds*. Then I run DBCC FREEPROCCACHE. It helps, reducing the run time to around 1 second, but not back down to 0 or 16 msec. Then I run DBCC FREEPROCCACHE another time. It returns the performance to normal, back down to 0 or 16 msec. Can anyone shed some light as to this behaviour, first where a sproc runs just fine, then all of a sudden performance dies. Then, FREEPROCCACHE sort of works, then a subsequent FREEPROCCACHE completely clears the problem. I'm guessing query plan, statistics, which index gets chosen, but these are only guesses. I've seen this behaviour on two seaparate systems - one test, one production. I had been guessing locking/blocking, but now I'm off that bandwagon - how would locking/blocking behaviour change with a FREEPROCCACHE? Are there any DBCC commands I can use to get SQL Server to tell me what the exact query plan is, that is cached in SQL Server's processor cache? Any trace flags I could run, or Profiler trace events I could be capturing? Ideas would be appreciated, Cheers, Steve. Freeproccache drops all existing query plans in cache so the next time you
run the proc it must generate a new plan. Plans for sps are generated using the values passed in the first time they are run or when they recompile. If the values of the parameters are such that they represent the majority of the values it will generate an optimal plan for it. If you first passed in a value that was atypical and a scan was best for that value it will use a scan for all other values as well. Instead of freeprocache you can do the same thing by recompiling the individual sp. Check out sp_recompile in BOL. But you may also be suffering from a condition of blocking if you are not owner qualifying the sp. Make sure you call the sp with the owner such as dbo.yoursp not just yoursp. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "news.microsoft.com" <steve.cockayne@nospamplease.com> wrote in message news:unjdkxhSGHA.4608@tk2msftngp13.phx.gbl... > > Hi all. > > SQL2K, Enterprise Edition. > > On a heavily loaded OLTP system, I have a stored procedure running > tremendously quickly. Nice. Query Profiler mostly reports 0 or 16 msec. > > Then, for some reason, with the system under the same load, it all of a > sudden starts taking multiple *seconds*. > > Then I run DBCC FREEPROCCACHE. It helps, reducing the run time to around 1 > second, but not back down to 0 or 16 msec. > > Then I run DBCC FREEPROCCACHE another time. It returns the performance to > normal, back down to 0 or 16 msec. > > Can anyone shed some light as to this behaviour, first where a sproc runs > just fine, then all of a sudden performance dies. Then, FREEPROCCACHE sort > of works, then a subsequent FREEPROCCACHE completely clears the problem. > > I'm guessing query plan, statistics, which index gets chosen, but these > are only guesses. I've seen this behaviour on two seaparate systems - one > test, one production. > > I had been guessing locking/blocking, but now I'm off that bandwagon - how > would locking/blocking behaviour change with a FREEPROCCACHE? > > Are there any DBCC commands I can use to get SQL Server to tell me what > the exact query plan is, that is cached in SQL Server's processor cache? > Any trace flags I could run, or Profiler trace events I could be > capturing? > > Ideas would be appreciated, > > Cheers, > > Steve. > > > > > > Hi
Another thought that I don't think Andrew included.. if your procedure branches, split it into multiple procedures. John Show quoteHide quote "news.microsoft.com" <steve.cockayne@nospamplease.com> wrote in message news:unjdkxhSGHA.4608@tk2msftngp13.phx.gbl... > > Hi all. > > SQL2K, Enterprise Edition. > > On a heavily loaded OLTP system, I have a stored procedure running > tremendously quickly. Nice. Query Profiler mostly reports 0 or 16 msec. > > Then, for some reason, with the system under the same load, it all of a > sudden starts taking multiple *seconds*. > > Then I run DBCC FREEPROCCACHE. It helps, reducing the run time to around 1 > second, but not back down to 0 or 16 msec. > > Then I run DBCC FREEPROCCACHE another time. It returns the performance to > normal, back down to 0 or 16 msec. > > Can anyone shed some light as to this behaviour, first where a sproc runs > just fine, then all of a sudden performance dies. Then, FREEPROCCACHE sort > of works, then a subsequent FREEPROCCACHE completely clears the problem. > > I'm guessing query plan, statistics, which index gets chosen, but these > are only guesses. I've seen this behaviour on two seaparate systems - one > test, one production. > > I had been guessing locking/blocking, but now I'm off that bandwagon - how > would locking/blocking behaviour change with a FREEPROCCACHE? > > Are there any DBCC commands I can use to get SQL Server to tell me what > the exact query plan is, that is cached in SQL Server's processor cache? > Any trace flags I could run, or Profiler trace events I could be > capturing? > > Ideas would be appreciated, > > Cheers, > > Steve. > > > > > >  |
|||||||||||||||||||||||
Other interesting topics