Use Plan Query Hint is the query hint that instructs the query optimizer to use an existing query plan for a query when creating the Query Plan. In my article “ What is Parameter Sniffing” you can find an example with this type of query hint. Like RECOMPILE query hint, this can be useful for parameter sniffing. Optimize For Query Hint is the query hint that instructs the query optimizer to use a specific value for a local variable when creating the Query Plan. In the article “ What is Parameter Sniffing” you can find the example of using Recompile Query Hint. To resolve this issue, you can use Recompile Query Hint to generate a query plan each time the query is executed. In the article “ What is Parameter Sniffing” you will find details about the parameter sniffing. However, if the data distribution in the table is uneven, sometimes it causes parameter sniffing. The normal behavior of SQL Server often improves performance. For Example “ Optimize for ad hoc workloads and Parameterization in SQL Server” or Query Hints.
In this way, they do not need to be recompiled each time. The second time they are executed, they are not compiled again, they use the query plan they produced earlier. When a stored procedure or parameterized query executed, SQL Server creates a query plan as soon as they are running. The scripts mentioned in the article were taken from microsoft’s site and tested on the AdventureWorks2012 database.īelow, I share some examples of query hints that I think you might find useful. Because using query hint, you are changing SQL Server’s normal behavior. Or, you can use the recompile query hint to ensure that the query generates a query plan each time it is executed.īefore you apply Query Hint, you need to make sure that the operation you want is correct. For example, if you add the maxdop query hint to the end of a query that uses a single CPU, the query may use multiple CPUs(parallelism). You can change the execution plan of the query by adding the query hints to the end of the query.
0 kommentar(er)
