If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems.
SQL Server tries to optimize the execution of your stored procedures by creating compiled execution plans. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”. Parameter sniffing can lead to inefficient execution plans sometimes; especially when a stored procedure is called with parameter values that have different cardinality.
SQL Server tries to optimize the execution of your stored procedures by creating compiled execution plans. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”. Parameter sniffing can lead to inefficient execution plans sometimes; especially when a stored procedure is called with parameter values that have different cardinality.
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. By “first time”, I really mean whenever SQL Server is forced to compile or recompile a stored procedures because it is not in the procedure cache. Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.
Not all execution plans are created equal. Execution plans are optimized based what they need to do. The SQL Server engine looks at a query and determines the optimal strategy for execution. It looks at what the query is doing, uses the parameter values to look at the statistics, does some calculations and eventually decides on what steps are required to resolve the query. This is a simplified explanation of how an execution plan is created. The important point for us is that those parameters passed are used to determine how SQL Server will process the query. An optimal execution plan for one set of parameters might be an index scan operation, whereas another set of parameters might be better resolved using an index seek operation.
No comments:
Post a Comment