{"id":12134,"date":"2019-05-10T23:24:04","date_gmt":"2019-05-10T23:24:04","guid":{"rendered":"https:\/\/dbtut.com\/?p=12134"},"modified":"2019-05-10T23:24:06","modified_gmt":"2019-05-10T23:24:06","slug":"query-hints-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/","title":{"rendered":"Query Hints in SQL Server"},"content":{"rendered":"

What is Query Hint?<\/h2>\n

You can change the execution plan of the query by adding the query hints to the end of the query. 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). Or, you can use the recompile query hint to ensure that the query generates a query plan each time it is executed.<\/p>\n

Before you apply Query Hint, you need to make sure that the operation you want is correct. Because using query hint, you are changing SQL Server’s normal behavior. The scripts mentioned in the article were taken from microsoft’s site and tested on the AdventureWorks2012 database.<\/p>\n

Below, I share some examples of query hints that I think you might find useful.<\/p>\n

Recompile Query Hint<\/h2>\n

When a stored procedure or parameterized query executed, SQL Server creates a query plan as soon as they are running. The second time they are executed, they are not compiled again, they use the query plan they produced earlier. In this way, they do not need to be recompiled each time.<\/p>\n

This is SQL Server’s default behaviour. You can change this in many ways. For Example; “Optimize for ad hoc workloads and Parameterization in SQL Server<\/a>” or Query Hints.<\/p>\n

The normal behavior of SQL Server often improves performance. However, if the data distribution in the table is uneven, sometimes it causes parameter sniffing. In the article “What is Parameter Sniffing<\/a>” you will find details about the parameter sniffing. 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<\/a>” you can find the example of using Recompile Query Hint.<\/p>\n

OPTIMIZE FOR Query Hint<\/h2>\n

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. Like RECOMPILE query hint, this can be useful for parameter sniffing. In my article “What is Parameter Sniffing<\/a>” you can find an example with this type of query hint.<\/p>\n

USE PLAN Query Hint<\/h2>\n

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.<\/p>\n

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.<\/p>\n

But my advice is to leave this job to sql server and keep your statistics up to date. You may want to read the article “Statistic Concept and Performance Effect on SQL Server<\/a>” for detailed information on statistics.<\/p>\n

However, if you want to use the use plan query hint, you can find details about how to use it in the link below.<\/p>\n

https:\/\/technet.microsoft.com\/en-us\/library\/ms186954(v=sql.105).aspx<\/a><\/p>\n

PARAMETERIZATION Query Hint<\/h2>\n

The behavior of some queries changes when we set parameterization in the database. With the Template plan guide, we can change the parameterization method for specific queries, difference from the parameterization method set at the database level.<\/p>\n

Read the following article for Template Plan Guide.<\/p>\n

“Increase Query Performance in SQL Server Using sp_create_plan_guide System Stored Procedure<\/a>”<\/p>\n

Read the following article for Parameterization.<\/p>\n

“Optimize for ad hoc workloads and Parameterization<\/a>”<\/p>\n

MERGE JOIN Query Hint<\/h2>\n

The following example uses a query hint that forces join to MERGE JOIN. For more information about join operations in SQL Server, please refer to “Join Types in SQL Server<\/a>” and “JOIN Types in SQL Server Execution Plan<\/a>“.<\/p>\n

SELECT *   \nFROM Sales.Customer AS c  \nINNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID  \nWHERE TerritoryID = 5  \nOPTION (MERGE JOIN);  \nGO<\/pre>\n
MAXRECURSION Query Hint<\/h2>\n
MAXRECURSION Query Hint prevents a poorly designed CTE (Common Table Expression) from entering the infinite loop. For more information about CTE (Common Table Expression), you may want to read the article “Common Table Expression (CTE)<\/a>“.<\/p>\n
The following example creates a CTE that enters an infinite loop, and sets the maximum number of loops of the query that uses the CTE.<\/p>\n
--CTE that enters an infinite loop\nWITH cte (CustomerID, PersonID, StoreID) AS  \n(  \n    SELECT CustomerID, PersonID, StoreID  \n    FROM Sales.Customer  \n    WHERE PersonID IS NOT NULL  \n  UNION ALL  \n    SELECT cte.CustomerID, cte.PersonID, cte.StoreID  \n    FROM cte   \n    JOIN  Sales.Customer AS e   \n        ON cte.PersonID = e.CustomerID  \n)  \n--Query using CTE and using the MAXRECURSION query hint\nSELECT CustomerID, PersonID, StoreID  \nFROM cte  \nOPTION (MAXRECURSION 2);  \n\nGO<\/pre>\n
When I execute the query in the database, it entered the loop 2 times and terminated with an error like the following.<\/p>\n
Msg 530, Level 16, State 1, Line 1<\/em><\/span><\/p>\n
The statement terminated. The maximum recursion 2 has been exhausted before statement completion.<\/em><\/span><\/p>\n
\"\"<\/p>\n
Removing the OPTION (MAXRECURSION 2) indian in the query changed the error as follows. I performed this test on SQL Server 2014. After the query entered the loop 100 times, SQL Server automatically stopped the script.<\/p>\n
Msg 530, Level 16, State 1, Line 1<\/em><\/span><\/p>\n
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.<\/em><\/span><\/p>\n
\"\"<\/p>\n
MAXDOP Query Hint<\/h2>\n
By specifing OPTION (MAXDOP 2) hint at the end of the query, we have specified that the maxdop setting of this query will be 2 regardless of the instance setting. Thus, this query will work two parallel.<\/p>\n
It is a query hint that will be useful for report queries. But be careful while using. Because, if many report queries works in parallel, you may encounter CPU bottleneck. You may want to read the below article about MAXDOP and CPU usage.<\/p>\n
“Numa Nodes, MAX\/MIN Server Memory, Log Pages In Memory and MAXDOP<\/a>”<\/p>\n
You can use MAXDOP Query hint as follows.<\/p>\n
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total \nFROM Sales.SalesOrderDetail \nWHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty \nORDER BY ProductID, OrderQty OPTION (MAXDOP 2);<\/pre>\n
FORCE INDEX Query Hint<\/h2>\n
In SQL Server, we use this hint to tell a query to work with the specified index. Actually SQL Server almost always makes the best choice. So if you keep your statistics up-to-date and rebuild your indexes on a regular basis, using this hint will usually slow you down.<\/p>\n
I suggest you read the following articles about statistics and indexes.<\/p>\n
“Index Concept and Performance Effect on SQL Server”<\/a>,<\/p>\n
“Statistic Concept and Performance Effect on SQL Server”<\/a><\/p>\n
Let’s take a look at the execution plan of the following select statement by clicking the checkbox in the following screen. As you can see, under normal circumstances Clustred Index Scan process is performed as follows.<\/p>\n
USE AdventureWorks2014Yeni\nGO\nSELECT *\nFROM Person.Person\nGO\n<\/pre>\n
\"\"<\/p>\n
Let’s add a hint to the query. As you can see, it used the Index we specified and then it had to perform Key Lookup to bring the needed records.<\/p>\n
\"\"<\/p>\n
I wanted to show it because it could be needed in very rare cases, but don’t use it unless it’s really necessary. Because as I mentioned before, it will mostly decrease your performance.<\/p>\n
Nolock Query Hint<\/h2>\n
This query hint force query to work like read uncommitted. Usually dbas advice to software developers due to locking. But be careful using this query hint. Because if you use this hint in select statements, it reads dirty data.<\/p>\n
I suggest you read the following articles.<\/p>\n
“Isolation Levels 1<\/a>“,<\/p>\n
“Isolation Levels 2<\/a>“,<\/p>\n
“Isolation Levels 3<\/a>“,<\/p>\n
Example:<\/strong><\/p>\n
SELECT *  FROM [AdventureWorks2012].[Person].[Address] WITH(NOLOCK)<\/pre>\n
Other Query Hints<\/h2>\n\n\n\n\n\n\n\n
ROWLOCK<\/td>\nIt force the query to place the row-level lock instead of the page or table-level locking<\/td>\n<\/tr>\n
TABLOCK<\/td>\nIt force the query to place the table-level lock instead of the page or row-level locking<\/td>\n<\/tr>\n
TABLOCKX<\/td>\nIt force the query to place the  exclusive lock on the entire table.<\/td>\n<\/tr>\n
UPDLOCK<\/td>\nIt force the query to place the  update lock. This guarantees that the data will be same until the update will be finished.<\/td>\n<\/tr>\n
XLOCK<\/td>\nIt force the query to place the  exclusive lock<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Sample Usage<\/h4>\n
SELECT *  FROM [AdventureWorks2012].[Person].[Address] WITH(XLOCK)<\/pre>\n
You may want to read the below article about lock types in SQL Server.<\/p>\n
“SQL Server Lock Types<\/a>”<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
What is Query Hint? You can change the execution plan of the query by adding the query hints to the end of the query. 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). Or, you can use the …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":12152,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[4073,4100,4076,4099,4075,4101,4102,4103,4108,4107,4111,4106,4105,4109,4112,4080,4104,4098,4081,4082,4067,4066,4083,4110,4068,4095,4094,4072,4093,4092,4070,4087,4069,4113,4089,4079,4074,4085,4086,4078,4077,4088,4090,4097,4096,4091,4071,4084],"class_list":["post-12134","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-does-nolock-improve-performance","tag-force-query-to-use-index","tag-how-do-you-use-nolock","tag-how-to-use-index-in-select-query-in-sql-server","tag-is-read-uncommitted-the-same-as-nolock","tag-maxdop-query-hint","tag-maxdop-query-hint-in-sql-server","tag-maxrecursion-query-hint","tag-merge-join-query-hint","tag-optimize-for-parameter-value","tag-optimize-for-query-hint","tag-option-clause-in-sql-server","tag-parallel-hint-in-sql-server","tag-parameterization-query-hint","tag-recompile-query-hint","tag-rowlock","tag-sql-server-index-hint","tag-sql-server-query-hints","tag-tablock","tag-tablockx","tag-the-statement-terminated-the-maximum-recursion-100-has-been-exhausted-before-statement-completion","tag-the-statement-terminated-the-maximum-recursion-2-has-been-exhausted-before-statement-completion","tag-updlock","tag-use-plan-query-hint","tag-what-are-hints-in-sql","tag-what-is-a-table-hint","tag-what-is-force-index-sql","tag-what-is-hint-in-sql-server-with-example","tag-what-is-index-hint-in-sql-server","tag-what-is-nolock-and-rowlock-in-sql-server","tag-what-is-nolock-hint-in-sql-server","tag-what-is-nolock-in-sql-server","tag-what-is-option-recompile-in-sql-server","tag-what-is-query-hint","tag-what-is-rowlock-in-sql-server","tag-what-is-sql-parallelism","tag-what-is-sql-server-maxdop","tag-what-is-tablock-in-sql-server","tag-what-is-tablockx-in-sql-server","tag-what-is-the-use-of-nolock-in-sql-server","tag-what-is-the-use-of-nolock-in-sql-server-2008","tag-what-is-updlck-in-sql-server","tag-what-is-xlock-in-sql-server","tag-what-is-xlock","tag-when-should-we-use-nolock-in-sql","tag-which-hints-is-used-to-control-the-locking-mechanism-of-tables","tag-why-nolock-is-used-sql-server","tag-xlock"],"aioseo_notices":[],"yoast_head":"\nQuery Hints in SQL Server - Database Tutorials<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Hints in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"What is Query Hint? You can change the execution plan of the query by adding the query hints to the end of the query. 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). Or, you can use the …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-10T23:24:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-05-10T23:24:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png\" \/>\n\t<meta property=\"og:image:width\" content=\"776\" \/>\n\t<meta property=\"og:image:height\" content=\"431\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Query Hints in SQL Server\",\"datePublished\":\"2019-05-10T23:24:04+00:00\",\"dateModified\":\"2019-05-10T23:24:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\"},\"wordCount\":1202,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png\",\"keywords\":[\"Does Nolock improve performance?\",\"force query to use index\",\"How do you use Nolock?\",\"how to use index in select query in sql server\",\"Is read uncommitted the same as Nolock?\",\"MAXDOP Query Hint\",\"MAXDOP Query Hint in sql server\",\"MAXRECURSION Query Hint\",\"MERGE JOIN Query Hint\",\"optimize for (@parameter = value)\",\"OPTIMIZE FOR Query Hint\",\"option clause in sql server\",\"parallel hint in sql server\",\"PARAMETERIZATION Query Hint\",\"Recompile Query Hint\",\"ROWLOCK\",\"sql server index hint\",\"sql server query hints\",\"TABLOCK\",\"TABLOCKX\",\"The statement terminated. The maximum recursion 100 has been exhausted before statement completion.\",\"The statement terminated. The maximum recursion 2 has been exhausted before statement completion.\",\"UPDLOCK\",\"USE PLAN Query Hint\",\"What are hints in SQL?\",\"What is a table hint?\",\"What is force index SQL?\",\"What is hint in SQL Server with example?\",\"What is index hint in SQL Server?\",\"What is Nolock and Rowlock in SQL Server?\",\"What is Nolock hint in SQL Server?\",\"What is NOLOCK in SQL Server?\",\"What is option recompile in SQL Server?\",\"What is Query Hint?\",\"What is ROWLOCK in SQL Server?\",\"What is SQL parallelism?\",\"What is SQL Server Maxdop?\",\"What is Tablock in SQL Server?\",\"What is Tablockx in SQL Server?\",\"What is the use of Nolock in SQL Server\",\"What is the use of Nolock in SQL Server 2008?\",\"What is UPDLCK in SQL Server?\",\"What is XLOCK in SQL Server?\",\"What is Xlock?\",\"When should we use Nolock in SQL?\",\"Which hints is used to control the locking mechanism of tables?\",\"Why Nolock is used SQL Server?\",\"XLOCK\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\",\"name\":\"Query Hints in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png\",\"datePublished\":\"2019-05-10T23:24:04+00:00\",\"dateModified\":\"2019-05-10T23:24:06+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png\",\"width\":776,\"height\":431},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Hints in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query Hints in SQL Server - Database Tutorials","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Query Hints in SQL Server - Database Tutorials","og_description":"What is Query Hint? You can change the execution plan of the query by adding the query hints to the end of the query. 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). Or, you can use the …","og_url":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-05-10T23:24:04+00:00","article_modified_time":"2019-05-10T23:24:06+00:00","og_image":[{"width":776,"height":431,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Query Hints in SQL Server","datePublished":"2019-05-10T23:24:04+00:00","dateModified":"2019-05-10T23:24:06+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/"},"wordCount":1202,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png","keywords":["Does Nolock improve performance?","force query to use index","How do you use Nolock?","how to use index in select query in sql server","Is read uncommitted the same as Nolock?","MAXDOP Query Hint","MAXDOP Query Hint in sql server","MAXRECURSION Query Hint","MERGE JOIN Query Hint","optimize for (@parameter = value)","OPTIMIZE FOR Query Hint","option clause in sql server","parallel hint in sql server","PARAMETERIZATION Query Hint","Recompile Query Hint","ROWLOCK","sql server index hint","sql server query hints","TABLOCK","TABLOCKX","The statement terminated. The maximum recursion 100 has been exhausted before statement completion.","The statement terminated. The maximum recursion 2 has been exhausted before statement completion.","UPDLOCK","USE PLAN Query Hint","What are hints in SQL?","What is a table hint?","What is force index SQL?","What is hint in SQL Server with example?","What is index hint in SQL Server?","What is Nolock and Rowlock in SQL Server?","What is Nolock hint in SQL Server?","What is NOLOCK in SQL Server?","What is option recompile in SQL Server?","What is Query Hint?","What is ROWLOCK in SQL Server?","What is SQL parallelism?","What is SQL Server Maxdop?","What is Tablock in SQL Server?","What is Tablockx in SQL Server?","What is the use of Nolock in SQL Server","What is the use of Nolock in SQL Server 2008?","What is UPDLCK in SQL Server?","What is XLOCK in SQL Server?","What is Xlock?","When should we use Nolock in SQL?","Which hints is used to control the locking mechanism of tables?","Why Nolock is used SQL Server?","XLOCK"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/","name":"Query Hints in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png","datePublished":"2019-05-10T23:24:04+00:00","dateModified":"2019-05-10T23:24:06+00:00","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/05\/Ads\u0131z-22.png","width":776,"height":431},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/05\/10\/query-hints-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Query Hints in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/12134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=12134"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/12134\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/12152"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=12134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=12134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=12134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}