Parameter Sniffing
Up until a few years ago, I’ll admit to having never been bitten by the parameter sniffing performance issue and if I am honest, I had not even heard of it. I got bitten by this yesterday
First a little insight for those who have not come across this previously.
When a stored procedure is executed for the first time or no longer exists in cache, a query plan will need to be generated. To do this the optimizer will scan the queries and also the input parameters (parameter sniffing) to find the most efficient route way to to data. It then caches the plan, executes and returns the results.
When the stored procedure is next called, it will use the cached query plan. It doesn’t matter that the parameter values may be different. This is fine if the parameters are very similar. However, if the dataset has changed dramatically, let’s say a large bulk insert or data deletion, then the query plan may no longer be appropriate causing performance issues. For example, assume a 10m row table that has a query plan that uses an index seek but now the table is 20m rows and the query plan uses an index scan.
So what happened to me and my database? We have a stored procedure which is called every 2 minutes to refresh the main screen. It performs a look up over the last 2 minutes to obtain any new quotes. It normally runs in about 3-7ms and around 20-30 logical reads.
When the application starts up, it loads a drop down box full of names and immediately fires off this stored procedure but now the search date is the 1st January 2000. The stored procedure took anywhere between 50s and 70s and incurred 70m (yes, million) logical reads. It also meant the GUI just sat there for over a minute with that rather annoying hourglass!
By changing the stored procedure to use local variables rather than use the input variables within the queries, it now executes in 1-3ms and has 20 logical reads and the GUI is ready to use as soon as it is fired up.
This is actually the first time I had come across this issue. Maybe I have come across it before and not realised and re-worked the code to improve the performance. I’m going to spend some time researching this a little more and will post an article with some examples at a later date.