SQL Server Stored Procedure runs slow sometimes

Sometimes, you might face a very weird issue with a stored procedure on SQL Server 2008 R2. Sometimes, about once every month, some procedures that becomes very slow, takes about 6sec to run instead of a few milliseconds.

Possible reasons:

When the sp is compiled, it is cached and this cache is reused every time I call it, and this cached version gets corrupted for some reason.

Possible Solutions:


1. When you first compile a stored procedure, its execution plan gets cached.

If the sproc has parameters whose definitions can significantly change the contained query's execution plan like index scans vs seeks, the stored procedure's cached plan may not work best for all parameter definitions.

One way to avoid this is to include a RECOMPILE clause with the CREATE PROCEDURE statement.

Example:

CREATE PROCEDURE SP
@myParam
WITH RECOMPILE
AS
BEGIN
 -- SP Body
END
GO


By doing this, a new plan will be generated each time the procedure is called. If recompile time < time lost by its using the wrong cached plan, this is worth implementing. In your case, it will also save you the time/planning required to manually recompile this procedure every time you notice it is executing slowly.

2. Update your statistics:

EXEC sp_updatestats

sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.

3. Parameter sniffing
Parameter sniffing is a technique the SQL Server optimizer uses to try to figure out parameter values/ranges so it can choose the best execution plan for your query. In some instances SQL Server does a poor job at parameter sniffing & doesn't pick the best execution plan for the query.
With Parameter sniffing Sql Server uses to optimze the query execution plan for a stored procedure. When you first call the SP, Sql Server looks at the given parameter values of your call and decides which indices to use based on the parameter values.