I performed this test on SQL Server 2014. Removing the OPTION (MAXRECURSION 2) indian in the query changed the error as follows. The maximum recursion 2 has been exhausted before statement completion. When I execute the query in the database, it entered the loop 2 times and terminated with an error like the following. For more information about join operations in SQL Server, please refer to “ Join Types in SQL Server” and “ JOIN Types in SQL Server Execution Plan“. The following example uses a query hint that forces join to MERGE JOIN. “ Optimize for ad hoc workloads and Parameterization” MERGE JOIN Query Hint Read the following article for Parameterization. “ Increase Query Performance in SQL Server Using sp_create_plan_guide System Stored Procedure” Read the following article for Template Plan Guide. With the Template plan guide, we can change the parameterization method for specific queries, difference from the parameterization method set at the database level. The behavior of some queries changes when we set parameterization in the database. (v=sql.105).aspx PARAMETERIZATION Query Hint
However, if you want to use the use plan query hint, you can find details about how to use it in the link below.
You may want to read the article “ Statistic Concept and Performance Effect on SQL Server” for detailed information on statistics.
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.