|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedure Cache QuestionI'm having a problem that confuses me. We use MSMQ to scale our database calls. We have a .NET listener that grabs items out the queue and processes them calling a stored proc. This process has backed up significantly lately and we've been looking for a cause. Yesterday for an unrelated reason we recompiled the stored procedure that this process calls and immediately the 120 item queue was flushed out in a matter of about 2 minutes. This queue had been backed up for 36 hours and was cleared immediately. It hasn't backed up again yet. It's been 18 hours. This made me think about a problem we had recently where a stored procedure was working very fast (processing 20,000 rows in a matter of a few seconds), but if you ran it throughout the day it would eventually slow down to a crawl. However recompiling it would speed it up immediately. We came to the conclusion that, as data changed throughout the day, the execution plan for the procedure was not getting updated because it was called so frequently and remained in the cache indefinitely. I understand the procedure cache to be a first in first out type queue that moves items tot he top when they are called subsequent times. My question is as follows. The solution we have come up with is to create a job that runs at a specified interval that would recompile the procedure, this updating it's execution plan. Is this the best solution? Is there a way to manage the cache so that items will only remain in the cache for [x] amount of time regardless of how many times they are called? Thanks in advance! Anthony Sullivan The "with recompile" option allows you to recompile
everytime an SP is run i think you can schedule a job to run and recreate the sp during off peak hours but i'm not sure of its adverse effect when an application calls the sp during its recreation method its going to throw an exemption -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba MCP #2324787 "Anthony Sullivan" wrote: > Hey Folks, > > I'm having a problem that confuses me. We use MSMQ to scale our database > calls. We have a .NET listener that grabs items out the queue and processes > them calling a stored proc. This process has backed up significantly lately > and we've been looking for a cause. Yesterday for an unrelated reason we > recompiled the stored procedure that this process calls and immediately the > 120 item queue was flushed out in a matter of about 2 minutes. This queue > had been backed up for 36 hours and was cleared immediately. It hasn't > backed up again yet. It's been 18 hours. > > This made me think about a problem we had recently where a stored procedure > was working very fast (processing 20,000 rows in a matter of a few seconds), > but if you ran it throughout the day it would eventually slow down to a > crawl. However recompiling it would speed it up immediately. We came to the > conclusion that, as data changed throughout the day, the execution plan for > the procedure was not getting updated because it was called so frequently > and remained in the cache indefinitely. > > I understand the procedure cache to be a first in first out type queue that > moves items tot he top when they are called subsequent times. > > My question is as follows. The solution we have come up with is to create a > job that runs at a specified interval that would recompile the procedure, > this updating it's execution plan. Is this the best solution? Is there a way > to manage the cache so that items will only remain in the cache for [x] > amount of time regardless of how many times they are called? > > Thanks in advance! > > Anthony Sullivan > > > One thing that you might consider is to update statistics on the affected
table(s) instead. Not only will this ensure that your stored procedure is using the correct exection plan but will also force a recompile of the procedure (they will recompile any time statistics update on one of the tables that they use). Check sp_updatestats in Books Online. Of course, this depends on the size of the table in question. It's possible that updating the statistics regularly could cause an even bigger bottleneck. If you're doing a lot of data manipulation then statistics should be updated automatically (unless you've shut off auto-update stats) so you might also look up AUTO_UPDATE_STATISTICS. "Anthony Sullivan" <t***@nebulasys.net> wrote in message <snip>news:%231w6VGkAGHA.3268@TK2MSFTNGP10.phx.gbl... > My question is as follows. The solution we have come up with is to create </snip>> a job that runs at a specified interval that would recompile the > procedure, this updating it's execution plan. Is this the best solution? > Is there a way to manage the cache so that items will only remain in the > cache for [x] amount of time regardless of how many times they are called?  |
|||||||||||||||||||||||
Other interesting topics