I have been asked recently what is parameter sniffing in an interview. Well, we all know how Optimizer in SQL server works to retrieving the data from the tables. We do also know that Optimizer reads the statistics and internal pre-compiled plans instead generating a new plan for parameterized Queries and Store procedures. Here we are, how Optimizer knows we are executing a similar query as it searching for pre-compiled plan for us because it was reading the parameters we passed to the stored procedure or a parameterized query and it is called “sniffing”.
What it counts for Performance, Good or bad?
“It depends “Yes “It depends” – I know we use this word many times in regards to databases
By default, Parameter sniffing is enabled and SQL Server caches the plans and reads the parameters those passed to the stored procedures or parameterized SQL Queries. We benefit from it as Optimizer will go and search for the plan it compiled earlier in cache when we execute the same query again. Optimizer will not need to re-create the plan to execute the same query multiple times.
This is all good only when there are good statics are maintained and typical parameters are used. What changes day by day is the requirement for using the new parameters with different words and optimizer will tries to use the same old plan in cache and may not be good for the new parameters as data has grown a lot and statistics have not updated yet and may be the plan for new parameter was doing table scans.
- Until we see a performance issue with this parameter sniffing it is better to leave it by default and we really benefit from it.
- When we see lot of performance issue, we could disable it using a trace flag 4136, this will disable the parameter sniffing at the server level.
- We could use it to disable only for specific query then we could use the recompile query hint