If there is long-running queries because of they use the wrong query plan, and you know that there is a better query plan, you can use this query hint.Д«ut my advice is to leave this job to sql server and keep your statistics up to date. 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.

Recompile view sql server