What the RECOMPILE hint does is force SQL Server to generate a new execution plan every time these queries run. Or to our stored procedure itself: DROP PROCEDURE IF EXISTS dbo.FilterCoffee GO CREATE PROCEDURE dbo.FilterCoffee varchar(30) AS BEGIN SELECT Name, Price, Description FROM WHERE Name LIKE + '%' OPTION (RECOMPILE) END GO We can simply add these query hints to either our EXEC statement: EXEC dbo.FilterCoffee = 'Ethiopia' WITH RECOMPILE EXEC dbo.FilterCoffee = 'Costa Rica' WITH RECOMPILE 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?” 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. However, 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 DBCC FREEPROCCACHE, we executed our stored procedure again but with ‘Ethiopia’ as our parameter. 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.
0 kommentar(er)
