Often times it results in an average or bad execution plan so I don’t like using it. OPTIMIZE FOR UNKNOWN will use a query plan that’s generated from the average distribution stats for that column/index. One of these would get added to the same location as OPTION (RECOMPILE) did in the above stored procedure: OPTION (OPTIMIZE FOR UNKNOWN)) Use the OPTIMIZE FOR query hintĪnother option we have is to add either one of the following hints to our query. If your parameter sniffed query doesn’t get ran often, or if the query doesn’t run often enough to stay in the query plan cache anyway, then RECOMPILE is a good solution. If your parameter sniffed query is getting ran frequently, RECOMPILE is probably a bad idea because you will encounter a lot of overheard to generate the query plan regularly. The disadvantage here is that we lose all benefit from having SQL Server save CPU cycles by caching execution plans. Using RECOMPILE eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time we execute the query. 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)
