November 27, 2009 at 3:47 am
Hi all, hope some can assist me.
I hav a production server that has recently started giving me grief.
DELL quad code 2.5Ghz
16Gb RAM + many disks (log, data,tempdb etc). It is SQL 2005 SP3 (9.0.4226). It is in a VMWare ESX environment but is the only VM on that host.
Ever few days or so the CPU spikes to 99% (SQL proc @ 80-90% - all cores neally maxing out). Until today I want unable to resolve the issue without having to restart the SQL Server service.
We have a stored proc that normally runs in under 2 seconds (the proc has no case statements), granted it looks @ table that have 1 million + records but imo the queries are pretty well optimized and the correct indexes are been use.
During this 80%+ CPU util I notice that the sed SP was causing major blocking which was extremely unusual. I orginally thought that it was blocking because of high CPU and SQL not been able to service... But I recomiled the SP and CPU dropped. What I dont understand is why the proc cache would be "messed" up?
I have attached some CPU and Mem stats... MemStats.xls is from the Idera tool that displays some interesting info with regards to the Proc Cache droping... Currently it is @ 700Mb
Thanks in advance
November 27, 2009 at 4:29 am
What about the table that the procedure reads from? Does it change frequently? Some times when data changes enormously, the statistics becomes stale, the execution plan also becomes less suitable.
November 27, 2009 at 5:08 am
Joe
Thanks for your reply...
Indexes and Stats are updated on these table every night.
JL
November 27, 2009 at 5:46 am
When the procedure executes for the first time it's execution plan is stored in the procedure cache. Now, that execution plan is only really relevant for the parameters that were passed at that point in time.
If differing values are passed through to the stored procedure, then the execution plan that was generated originally won't necessarily be optimal for the current execution.
Also, as already mentioned, if the data in the table (and subsequently statistics) change then this could knock out the cached execution plan. It's all well and good that you update statistics each night, but if a large amount of changes occur during the day then your cached plans might be sub-optimal until the next time your update stats, indexes, etc.
You could create the stored procedure with the recompile option so that it always recompiles when it runs. Often the benefits of having a stored execution plan are outweighed by recompiling if the data changes sufficiently.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply