Earlier the execution plan displayed the count of the rows as 150 out of 4 which is not corrected as 150 of 150. Now let us run the stored procedure again with the newer value of the 90 and you will see that the execution plan changes from the original execution plan. Let us understand this further by clearing the execution plan for our stored procedure by recompiling it. However, if the newer parameter is better suited for the different execution plan there can be huge potential performance degradation. In the real world, there is no performance issue of the execution plan of the new parameter is the same as the original execution plan. This process of building the execution plan for the stored procedure with the initial run parameter is called Parameter Sniffing. You will see here there is mention of the parameter This will also demonstrate that the parameter is compiled with the value 1060 but currently it is running this with a value of 90. Now let us look at the property of the execution plan by right-clicking on the left-most SELECT operator in the SSMS. You will notice in the execution plan that the operator is reading 150 rows out of potential 4 rows. Next, run the stored procedure with a different value. You will notice in the execution plan we see operators reading 4 rows out of potential 4 rows. WHERE CustomerID = let us enable the actual execution plan for your query so we can, later on, inspect the execution plans.įirst, run the stored procedure with the first value. CREATE OR ALTER PROC GetCustomerOrders INT) To begin with, let us create the following stored procedure. While this works out great in most of the cases, there is often a negative impact of the parameter sniffing issue for the stored procedure. This entire exercise is done to save those repeated moments recompiling the stored procedures. The compilation process of the stored procedure can take some precious time for the execution of the procedure is very large. SQL Server does this exercise to build the execution plan once and use it multiple times without recreating the execution plan again and again. SQL Server creates an optimal plan for a stored procedure by using the parameters that are passed the first time to the stored procedure is executed is called Parameter Sniffing. This post summarizes the entire series of parameter sniffing, performance and recompiling query. Performance and Recompiling Query – Summary.The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance. SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE).This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing. SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING.You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN.It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. SQL SERVER – Parameter Sniffing and Local Variable in SP. In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
0 Comments
Leave a Reply. |