![recompile stored procedure recompile stored procedure](https://www.mssqltips.com/tipimages2/2998_6.png)
FilterCoffee ParmCountry varchar ( 30 ) AS BEGIN SELECT Name, Price, Description FROM Sandbox. We can simply add these query hints to either our EXEC statement:ĭROP PROCEDURE IF EXISTS dbo. Let's take a look at some of the techniques.ġ. This question should really be rephrased as "how do I prevent SQL Server from using a sub-optimal plan from the query plan cache?"
![recompile stored procedure recompile stored procedure](https://data-flair.training/blogs/wp-content/uploads/sites/2/2018/09/Stored-Procedure-in-SQL-01.jpg)
Ok, so how do I prevent parameter sniffing? It then cached that Index Seek plan, which is why the second time around the 'Costa Rica' parameter received the execution plan with Index Seek. SQL Server determined that a plan with an index seek is optimal to retrieve only 6 of the 10,052 rows in the table. , we executed our stored procedure again but with 'Ethiopia' as our parameter. , since the Costa Rica plan was the first one to run, and therefore is the one that got added to the query plan cache, all other executions ended up using the same table scan execution plan.Īfter clearing our cached execution plan using Passing in any other country name into the stored procedure would return only a handful of records, making it more efficient for SQL Server to use our nonclustered index. This table scan query plan is only optimal for Costa Rica . This means that when we executed our stored procedure for the first time, SQL Server generated an execution plan that used a table scan because it thought this would be the most efficient way to retrieve 10,003 of the 10,052 rows. "Costa Rica" has more than 10,000 rows in this table, while all other country names are in the single digits. CoffeeInventory GROUP BY LEFT ( Name, CHARINDEX ( ' ', Name )) SELECT LEFT ( Name, CHARINDEX ( ' ', Name )) AS Country, COUNT ( * ) AS Countr圜ount FROM dbo. Since there is no specific Country column, we'll write it so it filters on the Name column: Now, let's write a stored procedure that will return a filtered list of coffees in our table, based on the country. The data is not distributed evenly (we'll see this in a minute) We have a nonclustered index on our Name column. The key things to know about this table are that: Let's start with our table dbo.CoffeeInventory which you can Parameter sniffing only becomes a problem when the cached plan isn't anywhere close to being the optimal plan for given input parameters.
![recompile stored procedure recompile stored procedure](https://user-images.githubusercontent.com/12203701/42794833-17fc5894-894f-11e8-950a-34f5d1cc9491.png)
Most of the time it's good: it means SQL Server is caching and reusing query plans to make your queries run faster. Parameter sniffing isn't always a bad thing. Today let's look at another reason: parameter sniffing. Implicit conversions could be one reason why your meticulously designed indexes aren't getting used