Make sure you are updating statistics regularly, at least on the table(s) referenced in this stored procedure that are causing the CPU spike. If the bad cached plan / parameter-sniffing issue only happens "once every few days", then that implies one or both of the following:Īn infrequently used value that has a widely different amount of rows in the table/index than the more frequently used values Without more detailed info it is difficult to be more specific regarding what should be done, but I think I could say the following:Īn increase in CPU is to be expected when recompiling a frequently executed stored procedure upon each execution.įor the moment, if it were me, I would remove the WITH RECOMPILE because an occasional 100% CPU utilization that can be fixed with a recompile is preferable to a sustained 90% CPU utilization.
Recompile a stored procedure full#
The stats are updated with full scan if the indexes are not rebuilt. The third-party tool I use, CONFIO, has executions during peak spike of up to 130,000. The number of execution that I mentioned was the estimated one when the stored proc is created/modified. There are no unused stats in the tables used by the stored procedures.There is one table with some 200,000 records that the compiler uses clustered index scan, every time it has a spike.Approximate calls /day for the store procedure - 5000.Statistics updated weekly for all user databases.Memory - Box 96GB RAM, assigned SQL memory Max 75 GB, Min 55 GB.How would I do a rollback for an immediate solution? What is a better way to deal with this problem?
Now, the CPU usage stayed beyond 90% constant.
In order to mitigate this, we have changed the procedure to do a recompile every time it executes. As the number of executions grows, one of the bad plans causes a 100% CPU spike. There is a heavily queried stored procedure that uses a bad plan once in few days.
0 kommentar(er)
