How to stop CPU Spikes as a result of parameter sniffing while the MS SQL stored procedure is executed?

With one of our clients that we provide Remote DBA services, we recently encountered a situation where one of the frequently running stored procedures suddenly started consuming 100% of the CPU on the server.

It was clear that something went wrong with the execution plan of the query. This procedure accepts parameters and it is very likely that for some of the specific set of parameters, the SQL Server optimizer created an inefficient execution plan.

Most likely we faced an issue called “parameter sniffing.” In short, parameter sniffing is when the optimizer creates an execution plan that sniffs the parameter values. This is not an issue; in fact, it is needed to build the best execution plan.

The problem arises when a query uses a previously generated execution plan which was optimized for a different data distribution.

In most cases, the database workload is homogeneous, parameter sniffing will not be a problem; on a small number of cases it can be a problem with dramatic outcomes.

Fortunately, we used a new feature introduced in SQL Server 2016 named Query Store. It really helps us identify what is going on, what queries are executed slowly, what their execution plan is, as well as many other features available in this tool.

As mentioned above, it was clearly a parameter sniffing issue and we had a few options to solve it. First of all, I tried OPTION (RECOMPILE) hint. The problem is that the procedure is being called frequently, and each time it was needed to re-compile a new plan. Having said that, we still noticed the CPU spikes and users keep complaining about degrading performance.

Finally, I implemented a simple technique to "disable" parameter sniffing by using local variables. This solution is based on assigning the stored procedure parameters to local variables and then using the local variables in the query. SQL Server is not sniffing local variables; it uses the local variables in place of parameters to enforce a plan generated based on statistics. In general, make sure to keep statistics up-to-date, and this will work. How to Update Statistics in MS SQL Server is a topic we may discuss in future articles.

To simplify, please see below the example with the stored procedure:

After compiling this stored procedure, we do not see any more CPU spikes and as a result, there have been no user complaints regarding database performance issues.

Our SQL consultants solved that problem very quickly and the client is very happy.