SQL Hint Introduction Hash/merge/concat Union

The query hint has always been a controversial thing because he has influenced SQL Server to choose its own execution plan. Many people ask whether they should use the query prompt or not to use it carefully or not. However, individuals think that the use of tips in the context of not modifying the statement, is a common means. Also if you are a DBA of a company and you know the database you maintain and have a good understanding of the business, then query hints are also a tool for you.

　　　However, the hint you apply is based on the existing environment in the present scenario, which is relatively a good way to ensure that the hints you give are permanently valid, and that as the amount of data changes over time, the hints you add may become nightmares. So do not be fully confident not to use the hint easily.

　　　　Here are some of the union's operators Hash/merge/concat

Create two tables (union use) and insert test data

CREATE TABLE [dbo].[T4](    [a] [int] NULL,    [b] [datetime] NULL,    [C] [uniqueidentifier]  not NULL,    [D] [nchar](Ten)NULL, CONSTRAINT [Pk_t4] PRIMARY KEY CLUSTERED (    [C] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY])  on [PRIMARY]GOCREATE TABLE [dbo].[T5](    [a] [int] NULL,    [b] [datetime] NULL,    [C] [uniqueidentifier]  not NULL,    [D] [nchar](Ten)NULL, CONSTRAINT [Pk_t5] PRIMARY KEY CLUSTERED (    [C] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY])  on [PRIMARY]GOInsert  intoT4Select 1,getdate(),newid(),'Test'Go  -Insert  intoT5Select 1,getdate(),newid(),'Test'Go  the

Let's take a look at the execution plan of the normal union all, a normal tandem plan.

　　　　

　　　　

Let's add the following hint:

　　　　

　　　　

Found the hint of a little effect is not AH!!! What kind of a racket is this? Two queries what extra action does not cause the optimizer to choose to concatenate by default because of this?

Let's change the query to change the Union all to Union (this is equivalent to doing a repeat operation)

　　　　

The operator "concatenation" disappears into a hash match, so is it possible to use hints now? Let's try ....

Add hint option (merge union)

　　　　

Add hint option (concat union)

　　　　

What is the difference between these three plans? Please keep looking down ... ^_^

We continue to add a sort operation order and remove C (the only column) to make the go-to effect.

　　　　

　　　　

　　　　

The above three tips are selected separately for the different plans, so below we compare the previous group to the comprehensive description:

• First group (with no effect) this is because the optimizer is still smart. The concatenation of the default two result set does not require any sort operation, so the concatenation of the result set can be applied serially.
• The second set of select *, while also using the Union (to repeat the operation) but did not see any distinct operation, this is the field of select contains a unique key (NEWID), the personal think is also the performance of the optimizer intelligence, However, in the hint concat, there is a de-re operation of the stream aggregation.
• The third group, which is selected by default as the merge (more general choice, but also depending on the situation) is equivalent to sorting using the distinct sort sort operation and going back to merging later, This option is best used when the amount of table data is not too large (because a large table sort is a resource-intensive thing) or the sorted field is already sorted (Index scan)
• The plan in the hash hint is a bit unexpected because normally, distinct sort operations are not present, but because of the large data duplication in the test data, the optimizer chooses the distinct sort operation after the second table scan. (the optimizer's choice depends on the statistics, distinct-〉1509), and the way the distinct sort sort operation is normally replaced is Hase aggregation.
• The concat operation is equivalent to stitching the result set directly and then doing the redo and sort operations. The two tables here have less data to repeat, so there is no distinct Sort like the one above, such as:
• There is also the case that using the Union all and using the displayed order then the optimizer forces the use of the sort-〉merge operation, which is a subsidy map.

　　　　　　　　

Summary: For the choice of Union method, the individual thinks that the optimizer chooses the problem is not big, generally rarely has the situation need to use the hint to intervene, does not resemble the previous article the compulsion parallel frequently uses. But we need to fully understand the optimizer's choice, why choose such a way of execution, this is the case in a few cases mixed and add the optimizer smart choice to display reasons. Another way to replace the distinct sort operation is Hase aggregation. The Distinct sort operation requires memory that is proportional to the amount of data that is removed before the data collection is duplicated, while the memory required for the hash aggregation is proportional to the result integration after the removal of the repetition! So if there are many duplicate values in the data rows, then the memory consumed by the hash aggregation is less.

SQL Hint Introduction Hash/merge/concat Union