asked
Stored procedure with recompile causing CPU spike
Stored procedure with recompile causing CPU spike There is a heavily queried stored procedure that uses a bad plan once in few days. As the number of execution grows, one of the bad plans causes a 100% CPU spike. The temporary solution We chose was to run DBCC FREEPROCCACHE for that specific Stored Procedure which brings the CPU utilization to acceptable range. In order to give a permanent solution, we have changed the procedure to do a recompile every time it executes. Now, the CPU usage stayed beyond 90% constant. • What is a better way to deal with this problem? • Is it possible to set up a job on SQL agent to do a recompile of the stored procedure only if CPU hits >80%, • The table it runs against has already more than 15 indexes on it and a lot of records, which makes indexing out of the solution list, • Our statistics is up to date; weekly and full scan, • @@VERSION: 2008R2 (SP3) CPU - 16 core processor, Memory... Box--96GB RAM, assigned memory for SQL - Max 75 GB & Min 55 GB • Approximate call /day for the store procedure> 100,000/day • The stored procedure is called by a vendor app, thus there is little room for tweaking the stored proc.
One thing to understand when using WITH RECOMPILE on a stored procedure is that the procedure has to be compiled in a way which fulfills every possible set of input parameters, which is not likely to be the optimal plan for any set of parameters if the procedure is somewhat complex. An option would be to identify which individual statement inside the procedure is causing the intermittent CPU spikes, and add option recompile on that individual Query. That will make it recompile every time and create an (almost) optimal plan every time. The recompile in itself is somewhat resource intensive of course, so choose carefully when to use recompile. If the procedure has programmatic logic (IF/ELSE etc), you might gain some by putting each IF/ELSE-block in its own stored procedure. That way, the main procedure only needs a plan for the IF/ELSE-logic, and each block gets its own plan (because it is in its own procedure).
17 People are following this question.
