SQL Server Tuning Series Play the chapter (how to run with query hint (Hint) bootstrap statement)

Objective

In the previous few we analyzed a series of SQL Server-related performance tuning, which I divided into two modules.

The first module focuses on the basic content of the master, a total of 6 articles to complete, covering a series of basic algorithms, the detailed analysis of how to view the execution plan, master Execution Plan optimization points, and a list of the everyday we usually write T-SQL statements will be applied operators. I believe that the T-SQL statements you normally write can find the corresponding decomposition operators in these articles.

The second module focuses on SQL Server execution of T-SQL statements when some insider parsing, a total of 5 articles, including: How to run the query optimizer, the runtime of several optimization indicator value detection, statistical information, the use of the index and a series of content. This piece of content lets us understand how SQL Server optimizes and runs the T-SQL statements we write.

From this chapter into the content of the third module, the first chapter, the module mainly let us guide SQL Server to adjust the direction of optimization to achieve the goal. The content of this module is based on the previous series of content, hoping to fully grasp the previous basic content, can enter the content of this module.

Technical preparation

The database version is SQL Server2012, which is analyzed using Microsoft's previous case library (Northwind), and part of the content is also applied to Microsoft's other case library AdventureWorks.

Trust the Friends of SQL Server, both libraries are not too unfamiliar.

Conceptual understanding

When it comes to hint, the concept is simple, just like the meaning of the word: hint, that is, let's give SQL Server hints (hint) to let the database run in accordance with our thinking, I estimate a lot of children who do not know SQL Server is not very well known, because the general application is not much.

In fact, the query optimizer of SQL Server itself has done well, so most of the cases do not need our human intervention, we can run very well, and optimize the operation of the maximum. But, as the saying goes: Tigers also have nap time, so, in some scenarios, we need to give the database to guide a direction, let it run more smoothly.

However, remember: the Hint you apply is given in the present context, in a relatively good way, not to ensure that the hints you give (Hint) are permanently valid, and that as time passes, the amount of data that you send (Hint) can become a stumbling block to database optimization. So there is not enough certainty not to use hint easily, and the best use of goal-oriented hint.

Hint is mainly divided into three kinds of applications: query hint, table hint, connection hint. Query hint affects the entire query, mainly used in query statement optimization, this article mainly analyzes query hint.

Table hint affects a single table referenced by the query, and the connection hint affects a separate connection.

There are two types of hint applications: goal-oriented hint and physical operator hint.

goal-oriented hint the goal of passing logic to the optimizer, without specifying how the optimizer should achieve this goal, what physical operators should be used, or how to arrange the operators. So this operator makes us recommend, the reason is very simple: I told ya follow this idea of execution can, as to how to achieve, own ways! This approach will have a much smaller impact on the database in the long run.

The other is the physical operator, which is more straightforward: just tell the steps of the YA, and you can do just that. This method is not recommended, the reason is very simple: your train of thought will be good for the time being, but not for a while.

First, query hints (Hint)

First, the query hint (Hint) is the most widely used in tuning, because most of the time we are tuning the performance of the query.

The optimization option in the query is a physical connection operator that directs SQL Server's connection type, aggregation type, union type, and so on. The detailed analysis of this block, you can refer to my tuning series in the previous articles, analyzed the quite detailed.

A, FAST N hint tips

I have already used this method in the previous article, in the introduction index that article, you can click here to view.

First, this hint is a goal-oriented hint. Tip The goal is simple: tell the database to give me the speed of the first n rows of data can be, and other data you love how to.

The best application environment for this hint is: Paging query in Application system, of course other environment can be used. A bit like the Select TOP N ....

Second, in our application environment, especially in the case of a lot of data, if this time our scene is: I want to speed to see the previous part of the data, other data you can display later, but in the implementation of T-SQL, SQL Server will consider the cost of many aspects, Then balance the pros and cons and choose what it thinks is a relatively good execution plan, and obviously this way of getting the data is wasteful, and the speed is much slower.

So, we take advantage of the fast n hint hint, so that SQL Server blocks the optimizer from using hash joins, hash aggregations, sorting, and even these large-consumption actions, while turning this N-data into a fast optimization and output. This is a very smart way to do this in the case of big data volumes.

Here's an example:

SELECT orderid,customerid,orderdate from Ordersorder by OrderDate

Simple query, and sort by OrderDate, without looking at the execution plan, we have speculated that this OrderDate is the most expendable of the execution plan, and that the sort is always high-loss, which is why all types of indexes are ordered in advance.

And then we'll take a look at the execution of this fast N hint hint.

SELECT orderid,customerid,orderdate from Ordersorder by OrderDate   1)

In order to get this line of data quickly, after using hint, the index Scan + bookmark lookup is changed, because this is one of the best ways to get a piece of data.

Because of the data volume of the relationship, so I did not show the above demonstration of the advantages of fast prompt, in fact, in the actual production, in the face of huge data volume, the general use of fast n hints to obtain some of the data, will no longer continue to run, because we are concerned about this part of the data.

Of course, this hint also has drawbacks: after a quick fetch of the first n rows of results, it may delay the overall response time of the entire query. That is, although fast n hint may cause the optimizer to quickly produce the first N output plans. But it will cause the optimizer to spend more time, consume more CPUs, and even more IO before ending the last line.

B, OPTIMIZE for hint tips

This hint is a very useful reminder that we often use in our daily routines.

This hint goal is simple: tell the optimizer that the target is assigned or executed with a hint value. This hint hint is supported starting with the SQL Server2005 version and can produce a plan based on the specified parameter values, especially for asymmetric datasets, because the data in this dataset is unevenly distributed, and different parameter values can lead to different cardinality evaluations and different query plans. We can choose an optimal execution plan from different parameters, as an execution plan for the following different parameters, avoiding the costly actions of SQL Server re-evaluation and recompilation.

Here's an example:

SELECT orderid,orderdatefrom orderswhere shippostalcode=n'51100'

This statement is simple enough to get the order ID and the order date by querying the ZIP code (shippostalcode).

To see this query statement, the best thing is to get the data directly through the index seek action. In fact, the best way is to include two columns of values through include.

Let's take a look at the actual execution plan:

SQL Server through the index lookup + bookmark Lookup way to get, this way is also OK, in fact, we can continue to optimize.

However, this is not the focus of the problem, the problem is that the section T-SQL generally we will use parameters to query or wrapped into a stored procedure through a parameter call. Right?? Don't you ever just query a fixed value .... Look at the statement

DECLARE  @ShipPostalCode NVARCHARSET @ShipPostalCode=n'51100'  SELECT orderid,orderdatefrom orderswhere shippostalcode[email protected]

Yes, this way can be reused, but packaged into a stored procedure or a function, and so on, it is estimated that the core code is such a child.

Take a look at the resulting execution plan:

That would have been nice. Nonclustered index lookup (seek), by adding a parameter to the clustered index scan (scanning), the performance of the clustered index scan is basically the same as the table scan, there is no quality improvement!

If the table has a particularly large amount of data, the nonclustered index that we designed for the statement is invalidated . You can only get the data by scanning sequentially. Is it interesting??? Boring!!!

How to solve it? This is the time we mentioned here hint appearances, tell the database: Ya just follow the "51100" query to execute the parameters I passed over.

DECLARE  @ShipPostalCode NVARCHARSET @ShipPostalCode=n'51100'  SELECT orderid,orderdatefrom orderswhere shippostalcode=@ShipPostalCodeOPTION (OPTIMIZE for ( @ShipPostalCode=n ' 51100 '))

See, here it goes back to the fast nonclustered index lookup (seek) state, and is not restricted to what arguments are passed in.

This hint simply tells the SQL Server query to follow this target value and does not actually affect the result value.

Of course, the above problem, if packaged into a stored procedure, can be re-compiled to solve, but compared with the use of hint, the re-compilation brings much more expensive. In particular, high concurrency is a high level of CPU consumption caused by recompilation.

C, physical connection tips (Hint)

About physical connections We have analyzed in detail in the previous article that there are three physical connection methods in SQL Server: Nested loops, merges, Hash joins.

You can refer to the links in my basic article: SQL Server Tuning Series Basics (Summary of common operators-three ways to analyze physical connections)

In this paper, the pros and cons of three kinds of connections are compared in detail, and the usage environment of three kinds of connections is introduced in detail. However, sometimes SQL Server does not optimize the connection for us, or does not meet our requirements, this time, we need to use our physical connection tips to guide.

A total of three query-level connection hint, which correspond to three physical connection operators, in turn: LOOP join, MERGE join, and HASH join

At the time of application, you can specify one or more, and if you specify one, all connections in the query plan use the specified connection type, and if you specify two, SQL Server chooses the best of the two connection types, that is, the third one is killed.

The application scenario is very much, according to three kinds of connection characteristics, we can choose to prompt, for example, we want a query does not consume memory, then you can specify the option (LOOP Join,merger join), so that the memory consumption of the hash connection, This, of course, reduces memory consumption but increases execution time. If a merge join (merger join) does not consume memory, but the merge connection needs to be ordered in advance (sort), sorting consumes a lot of memory.

Of course, sometimes the nested loop connection execution time is not ideal, you can specify a hash connection (hash join) to connect.

Let's look at an example:

SELECT o.orderidfrom Customers C JOIN Orders o on C.customerid=o.customeridwhere c.city=n' London '

The above query plan uses nested loops for the connection, and the two tables are sequentially nested execution.

If, after testing, there is a better way to use a merge connection, we can use the following hint to prompt the operation

SELECT o.orderidfrom Customers C JOIN Orders o on C.customerid=o.customeridwhere c.city=n' London ' OPTION (MERGE JOIN)

After the adjustment, the statement takes advantage of the nonclustered index we designed, and the original index scan becomes the index seek operation.

You can instruct SQL Server to choose between hash and merge connections in the following ways, but be sure to discard nested loops connections.

SELECT o.orderidfrom Customers C JOIN Orders o on C.customerid=o.customeridwhere c.city=n' London ' OPTION (HASH join,merge JOIN)

Look to see if SQL Server is evaluated or still selected for merge connection

In fact, this is very normal, the first data is not large, followed by a nonclustered index on the city column, so to make full use of, and in the two tables CustomerID are all covered by the index, which ensures that the two tables on this column are pre-ordered (sort), which fully satisfies the conditions of the merge connection. Of course, the default is to choose the reason for nested loops, I estimate the reason is one: Two table data volume is not large.

Of course, the hint way out above can specify the physical connection mode of the connection, and there is another more brutal way to enforce it. As follows:

INNER MERGE JOIN Orders O on C.customerid=o.customeridwhere c.city=n'London'

Of course, this approach is also manually achieved by specifying the method of merging connections.

However, there are serious drawbacks to this approach:

1, through the adoption of this approach seems to solve the problem temporarily, but after a period of time, this connection method may seriously hinder the database optimization, and to resolve this problem, you have to change the code.

2, can only be rude to specify a physical connection, not conform to the SQL Server itself optimization strategy.

The above approach is a very deprecated one, and most novices will choose this way.

Of course, the use of hint is not a surefire plan, but in the current basic can solve the problem, when running to a period of time, if the current hint interfere with the normal operation of SQL Server database, we can also use the appropriate way to deactivate hint. Make the database perfectly smooth and normal operation. Follow-up articles are described in turn.

About the use of this piece of hint, the content is quite a lot of, some of which also contains lock hints, etc., follow-up articles we introduced in turn, interested children shoes in advance attention.

In fact, hint is an important tool when we tune up. However, the correct use of this tool relies on solid fundamentals and accumulated experience. The so-called: thick accumulated thin hair ! Do not easily see the use of the scene to blindly use the blind. If used improperly, also can disturb the SQL Server database itself normal ecological environment, outweigh the gains, the more chaotic.

So: benefactor, think twice ...

Reference documents

• Microsoft Books Online logical operators and physical operator references
• Refer to the book SQL. server.2005. Technical Insider "series

Conclusion

This article first to this bar, about the SQL Server Tuning tool hint the use of a lot of content, followed by the introduction, the interest of children's shoes can be noticed in advance.

If you have any questions, you can leave a message or private messages, and look forward to an in-depth study of your child's shoes with SQL Server. Learn together and progress together.

At the end of the article gives the previous several connections, the following content basically covers our daily write query operations decomposition and tuning content items, are original, it seems necessary to sort out a directory ....

SQL Server Tuning Series Basics

SQL Server Tuning Series Basics (Summary of common operators)

SQL Server Tuning Series Basics (Union operator summary)

SQL Server Tuning Series basics (Parallel operations Summary)

SQL Server Tuning Series basics (Parallel operations Summary chapter II)

SQL Server Tuning Series Basics (Index operations Summary)

SQL Server Tuning Series Basics (subquery operations Summary)

-----------------The following step-by-step article-------------------

SQL Server Tuning Series advanced (how the query optimizer runs)

SQL Server Tuning Series Advanced (query statements run several indicator value monitoring)

SQL Server Tuning series advanced (in-depth profiling statistics)

SQL Server Tuning series advanced (How to index tuning)

SQL Server Tuning series advanced (how to maintain database indexes)

SQL Server Tuning Series Play the chapter (how to run with query hint (Hint) bootstrap statement)