Contents
What is a parameter sniffing?
Parameter Sniffing is the process of looking to the first passed parameters values when compiling the stored procedure in order to create an optimal execution plan that fits these parameters values and use it for all values.
How do you deal with parameter sniffing?
Workarounds for SQL Server Parameter Sniffing
- Create SQL Server Stored Procedures using the WITH RECOMPILE Option.
- Use the SQL Server Hint OPTION (RECOMPILE)
- Use the SQL Server Hint OPTION (OPTIMIZE FOR)
- Use Dummy Variables on SQL Server Stored Procedures.
- Disable SQL Server Parameter Sniffing at the Instance Level.
What is parameter sniffing in SQL?
Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed. The important point for us is that those parameters passed are used to determine how SQL Server will process the query.
What is Sp_recompile?
sp_recompile looks for an object in the current database only. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.
Which is better parameter sniffing or parameter embedding?
Parameter Sniffing. Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache. These are all good things, provided the query being parameterized really ought to use the same cached execution plan for different parameter values.
How to optimize parameter sniffing in SQL Server?
SQL Server provides a range of query hints and other options to tune the behaviour of parameter sniffing: The OPTIMIZE FOR (@parameter = value) query hint builds a reusable plan based on a specific value. OPTIMIZE FOR (@parameter UNKNOWN) uses average distribution statistics for a particular parameter.
How are execution plans cached in SQL Server?
If the same query is executed multiple times, then the cached plan can be reused over and over, without the need to generate a new plan. This saves time and resources, especially for common queries that are executed frequently. Execution plans are cached based on the exact text of the query.
How to reduce memory pressure in plan cache?
The solution to memory pressure in the plan cache is parameterization. For our query above, the DATETIME literal can be replaced with a parameter: When executed for the first time, an execution plan will be generated for this stored procedure that uses the parameter @order_date.