Simple collation of hints (hint) in SQL Server 2008

the system query process for SQL Server is responsible for generating a query execution plan when the select query executes. SQL Server will "intelligently" choose an efficient plan to replace the inefficient one. Most of the time, SQL Server will do a great job. However, as some people are concerned, SQL Server is not omnipotent, and sometimes we investigate performance by querying execution plans, table statistics, supported indexes, and other factors, discovering that the execution plan selected by the query optimizer did not achieve the desired effect, or that the query optimizer made the wrong choice. At this point we may need to use hints (hint) to overwrite the process that the SQL Server query optimizer determines.

Hints (hint) is the specified mandatory option or policy that is executed by the SQL Server query processor for SELECT, INSERT, UPDATE, or DELETE statements. The hint overrides any execution plan that the query optimizer might choose for the query.

　　Before using hints, note:

(1) SQL Server makes the right choice in the vast majority of cases, even if the hints used is effective in the short term, but as the database content changes, the query plan used may be more efficient, but because hints is more overbearing, SQL Server does not "presume" to use the optimizer.

(2) Effective hints may also change after SQL Server releases the patch.

SQL Server three different types of Hints: Join hints (join Hints), query hints (queries Hints), table hints (tables Hints), including hint Server2008 introduced in SQL Forceseek, You can use it to replace an index scan with an index lookup.

1. Using the JOIN hint (join Hints)

Official explanation: The join hint is used to specify that the query optimizer enforces a join strategy between two tables.

Usage:<join_hint>:: = {LOOP | HASH | MERGE | REMOTE}

The junction hint forces the query optimizer to join the table using your command, which is implemented through an internal JOIN operation. Available Junction hints:

Hint Name	Describe
LOOP	Loop works well when one table is small and another table is large and has an index on the linked column
HASH	Hash joins are ideal for large unordered tables
MERGE	The merge junction is ideal for medium-size tables sorted by junction columns
REMOTE	Remote connections should be left table local and behave less than remote tables

Code Demo:

 UseTestDb2GOIF  not object_id('Hintsdemo','U') is NULLDROP TABLEHintsdemoGOIF  not object_id('HintsDemo2','U') is NULLDROP TABLEHintsDemo2GO----Create test data tablesCREATE TABLEDbo. Hintsdemo (HIDint, HtitleNvarchar( -))GOCREATE TABLEDbo. HintsDemo2 (HID2intHidint)GO----Insert 20 dataINSERT  intoHintsdemo (hid,htitle)VALUES(cast(Rand()*Ten  as INT),Replicate('X',cast(Rand()* -  as INT)) )GO  ---repeat the statement 20 timesINSERT  intoHintsDemo2SELECT TOP Ten cast(Rand()*Ten  as INT), HID fromHintsdemoGO 2--repeat the statement 2 times--at this point two tables each have 20 recordsSELECT *  fromHintsdemoSELECT *  fromHintsDemo2SETShowplan_xml onGOSELECTH.hid,h.htitle,d.hid2 fromHintsdemo HINNER JOINHintsDemo2 D onH.hid=D.hidGOSETShowplan_xmlOFFGO

nested queries are used by default:

　　

Use hash Join below

SELECT H.hid,h.htitle,d.hid2  
 from Inner JOIN = D.hid

　　

2. Use query hint (Hints)

Official explanation: During the duration of the query statement, the query hint takes precedence over the default behavior of the query optimizer. You can use query hints to specify the locking method for the affected table, one or more indexes, a query processing operation such as a table scan or an index lookup, or other options. Query hints apply to the entire query.

Its parameters are more complex:

<query_hint >:: = {{HASH | ORDER} GROUP | {CONCAT | HASH | MERGE} UNION | {LOOP | MERGE | HASH} JOIN | EXPAND views | FAST Number_rows | Force ORDER | Ignore_nonclustered_columnstore_index | KEEP PLAN | Keepfixed PLAN | MAXDOP Number_of_processors | Maxrecursion number | OPTIMIZE for (@variable_name {UNKNOWN | = literal_constant} [, ... n]) | OPTIMIZE for UNKNOWN | PARAMETERIZATION {Simple | Forced} | RECOMPILE | Robust PLAN | Use PLAN N ' Xml_plan ' | TABLE HINT (Exposed_object_name [, <table_hint> [[,]...N]])}<table_hint>:: =[NOEXPAND] {INDEX ( Index_value [,... n]) | INDEX = (Index_value) |   FORCESEEK [(Index_value (Index_column_name [,...])] | Forcescan | HOLDLOCK | NOLOCK | NOWAIT | Paglock | readcommitted | Readcommittedlock | READPAST | readuncommitted | RepeatableRead | Rowlock | SERIALIZABLE | Spatial_window_max_cells = integer | TABLOCK | Tablockx |   UPDLOCK| XLOCK}

In general, we can use the RECOMPILE query hint in a stored procedure, which enables SQL statement-level recompilation rather than the recompilation of the entire stored procedure (batch statement). We use an example to illustrate.

/** * * * Use Query hints * * **/DECLARE @HintsTitleDemo nvarchar( -)= '0e2fab59-9a22-4e14-b7be-33ab500e3b9e'SELECTHid,htitle fromHintsdemoWHEREHtitle= @HintsTitleDemoORDER  byHID/*HID HTitle6 0e2fab59-9a22-4e14-b7be-33ab500e3b9e*/--use DMV queries to see if the in-memory statistics plan can be reusedSELECTCacheobjtype, ObjType, usecounts fromSys.dm_exec_cached_plans CrossAPPLY sys.dm_exec_sql_text (plan_handle)WHERE text  like 'DECLARE @HintsTitleDemo%'

　　

--clear the process cache first!!! Please do not use the following sentence in the production environmentDBCCFreeproccache;/*DBCC execution completed. If DBCC printed error messages, contact your system administrator.*/DECLARE @HintsTitleDemo nvarchar( -)= '0e2fab59-9a22-4e14-b7be-33ab500e3b9e'SELECTHid,htitle fromHintsdemoWHEREHtitle= @HintsTitleDemoORDER  byHIDOPTION(RECOMPILE)--Force recompilation

　　

　　

Again, in most cases, SQL Server makes a better choice, and in extreme cases we need to intervene in its query plan to overwrite the SQL Server selection.

3, table hint (table Hints)

Official explanation: By specifying a locking method, one or more indexes, query processing operations such as table scan or index lookup, or other options, table hints can override the default behavior of the query optimizer during Data manipulation language (DML) statement execution. A table hint is specified in the FROM clause of a DML statement, affecting only the table or view referenced in the clause.

Table hints are similar to query hints for overriding the default behavior of SELECT, INSERT, update, and delete. You can set multiple table hints for a table and separate them with commas as long as they are not part of the same grouping.

With  (<table_hint> [[,]...N]) <table_hint>:: =  [NOEXPAND] {     INDEX  (index_value [,... N ] ) | INDEX =  (index_value)    | FORCESEEK [(Index_value (Index_column_name  [,...])]   | Forcescan   | FORCESEEK   | HOLDLOCK   | NOLOCK   | NOWAIT   | Paglock   | ReadCommitted   | Readcommittedlock   | READPAST   | ReadUncommitted   | RepeatableRead   | Rowlock   | SERIALIZABLE   | spatial_window_max_cells = integer   | TABLOCK   | Tablockx   | UPDLOCK   | XLOCK} <table_hint_limited>:: ={     keepidentity   | Keepdefaults   | HOLDLOCK   | Ignore_constraints   | Ignore_triggers   | NOLOCK   | NOWAIT   | Paglock   | ReadCommitted   | Readcommittedlock   | READPAST   | RepeatableRead   | Rowlock   | SERIALIZABLE   | TABLOCK   | Tablockx   | UPDLOCK   

It is noteworthy that the NOlOCK option, the following statement:

　　

-- Do not lock execution query SELECT Hid,htitle  from Hintsdemo  with (NOLOCK) WHERE = 4 /* HID HTitle4 e6da3db2-3d41-47b4-b4e3-dda90918434c4 1c4c9211-eb1c-42b5-a08a-558dc73462b44 667c9985-3b0a-4767-aed9-82fee623433d* *

The NOLOCK table hint lets the query leave the shared lock on the row or data that is not affected--allowing you to read without being blocked or blocking other queries (but experiencing "dirty read" issues).

Finally, SQL Server 2008 introduces the FORCESEEK table hint, which can be used to replace an index scan with an index lookup . There are a number of reasons why SQL Server produces a bad query plan. For example, the table data changes frequently and the information is no longer accurate, or a query with a poor WHERE clause does not provide useful or sufficient information for the query optimizer process.

The I/O overhead is certainly unacceptable if you want to find the specified data separately, and the entire table is scanned for the very large table before retrieving a row. Assume that the test table above is very large.

　　

SETShowplan_xml onGO--This example uses the WITH (ForceSeek) not necessarily optimal, but provides a way to modify the system to access the dataSELECT DISTINCTHtitle fromHintsdemo with(FORCESEEK)WHEREHidbetween 8  and Ten  andHtitle='141466e4-e8cc-4219-a9af-7c0d2b86a668'GOSETShowplan_xmlOFF

　　

You can also further specify which index to use

With (FORCESEEK,INDEX(idx_forceseekdemo))='141466e4-e8cc-4219-a9af-7c0d2b86a668' GO      

For this example, if you need better performance, consider using the specified rowset indexing feature introduced by SQL Server, for example, if you are only interested in listings with an average price of $5000-6000 in a rate table, you can index this interval specifically.

　　Summary: This article provides an overview of SQL Server three different types of join hints (join Hints), query hints (queries Hints), table hints (tables Hints), including SQL The hint Forceseek, introduced in Server2008, can be used to replace an index scan with an index lookup.

Note: tips need to use caution!!!

Simple collation of hints (hint) in SQL Server 2008