5
I’ve heard of Parameter sniffing. Something to do with SQL Server, stored procedures, and parameters. But what exactly is?
1 answer
8
When a stored Procedure will be run for the first time, SQL Server compiles the procedure and generates a action plan, which is stored in the cache, and is used in subsequent calls Procedure.
This is done in this way to prevent the process of compiling the Procedure is repeated every call, as it is a task that consumes many CPU resources.
However, there is one important detail: in this process of compiling Procedure, SQL Server seeks to mount the best possible action plan. The action plan great. In assembling this better plan, the compiler considers the parameter value - that’s where the Parameter sniffing: is the compiler of procedures of SQL Server monitoring the contents of the parameters sent to the Procedure in order to develop and build the best action plan to be stored in cache and reused after.
This describes how SQL Server normally operates.
The issue of Parameter sniffing becomes important when it occurs that the "best plan of action" for a given Procedure differs considerably according to the value of the parameter. This has the potential to generate the unpleasant situation: in the first call, when the compilation takes place, a parameter can be used that generates an action plan that can be optimal for that parameter. However, in a later call, it can happen that a different value for the parameter has a terrible performance using that action plan that was best for the another parameter value, previously passed.
The developer needs to know this mechanism of Parameter sniffing to guard against the situation mentioned. That is, it is a mechanism that at first serves to optimize performance, but that may result in its opposite, impairing performance.
Source: https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
(In the material used as source, the solutions to avoid the problems generated by Parameter sniffing are addressed.)
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
In addition to J. Bruni’s reply, I suggest reading the article Introduction to Parameter sniffing in SQL Server, didactically explaining the subject. -> https://portosql.wordpress.com/2020/09/03/introducao-parameter-sniffing/
– José Diz