SQL Server Tuning series play turn three (use index hint (Hint) to boot statement maximum optimization run)

Objective

This article continues to play the content of the module, about the location of the index in SQL Server No need to say, this article will analyze how to use the Hint Bootstrap statement to fully use the index to run, as well as a solid grasp of the previous series of content before entering the content analysis of this module.

Gossip, enter the content of this article.

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.

One, parallel Hint hint (MAXDOP N Hint)

In the current multi-core hyper-threading today, parallel operation is no longer a rarity, so in SQL Server also has its own parallel operators, to fully utilize the existing hardware resources, maximize operational efficiency.

There are two articles in this series that specialize in parallel operations on SQL Server, and you can click to view: SQL Server Parallel operations Summary, SQL Server Parallel Operations Summary Chapter II

Therefore, in Hint also gives the hint about parallel operation: MAXDOP N Hint, this Hint is often used, especially when indexing operations, in order to shorten the operation time, we often use the maximum of parallel operations.

In addition, this hint takes precedence over configuration options at the database level. It is also said that although the MAXDOP 1 is set in the database (which is enforced sequentially), the database settings are ignored if this hint is used.

Of course, parallel operations, while most of the situation can improve operational efficiency, but also not absolute, we know that multi-threaded operation is required to maintain the data exchange between threads and execution order, and so on, all the time multithreaded execution is not necessarily a single-threaded high efficiency.

Let's look at an example:

Select [Key],[data]from testmaxdoptablewhere data<-OPTION (MAXDOP 1) SELECT [Key],[data]from Testmaxdoptablewhere data<-OPTION (MAXDOP 4)    

The above is a serial operation, the following is a parallel operation of 4 threads.

Of course, here are a few thread operations can set their own, the maximum recommended for the current system configuration of the logical core number, of course, the large set can be just useless.

Second , index Hint hints (index Hint)

The so-called index hint hint is to force the query optimizer to perform a scan of a query statement or use a specified index.

In this way, we often use in the tuning of a way, many times we create the index is invalid, of course, most of the time the reason for the failure is to create an index improper when caused, but there are some cases, we need to guide the operation of the next T-SQL, this is the index hint use of the scene.

Of course, the premise that the index hint can be used here is that the current table exists index, if it is the case of heap table, there is no index hint, only through the table scan to get the data.

Take a look at the usage of this hint: with (INDEX (N))

Here n is the index in the table in the index order number, to see the index number in a table:

SELECT * from sys.indexeswhere object_name (object_id) ='Orders'   

As you can see, there are 10 indexes in the table, followed by sorting, from 1 to 10, the first is a clustered index (primary key), and then a nonclustered index.

So, the value of n above us is this number, and specifying a few is which index is required.

Take a look at the script:

With (Index (1)) where Shippostalcode=n'99362' with(index (9)) where shippostalcode= N'99362'          

See, in the example above we have selected two indexes: A clustered index of number 1 pk_orders, a nonclustered index with number 9 is shippostalcode. Of course, there are several other indexes that are interesting to play with.

Let's go through the analysis of the index usage of this statement:

First, from the query criteria, we are based on Shippostalcode query, so it is best to be indexed in this column is overwritten, so that in the case of large data volume, the query optimizer can use Index lookup (index Seek), So, here we chose the 9th nonclustered index , which happens to overwrite the value of the column, from the above query plan can also be seen, using the INDEX hint hint after the query cost increased from 69% to 3% ... However, since this nonclustered index does not contain the CustomerID column, you have to introduce a bookmark lookup (key lookup) to get the value of the column, and the bookmark lookup consumption is also relatively large: 60%, so the best way is to include the index into the CustomerID column.

Of course, this approach may be uncomfortable because we need to look up the sort number of each index in the current table when we use it.

So, our most recommended and most commonly used way is this:

With (INDEX (' IndexName '))

Is that we directly specify the index name can be, to see an example:

SELECT Orderid,customeridfrom Orders with (INDEX (customersorders)) WHERE shippostalcode=n'99362' 

It seems much simpler because the name of the index we can see directly, to see how we assign this query statement to the execution plan on this nonclustered index customersorders.

Take a look at this query plan: Ya! ..... The query overhead soared directly to 100% ... The reason is simple: this nonclustered index and this query a dime relationship .... But we are forced to specify that the statement is executed using the index ....

First the nonclustered index contains the column: [Orderid],[customerid]

The value we want to get is filtered according to Shippostalcode, so to get the result you have to do a single scan of the nonclustered index (index Scan), which can also, after all, nonclustered indexes are ordered, but in order to filter, you must introduce a bookmark lookup (Key Lookup) To filter, we know that the bookmark lookup is random io, the consumption is huge, so this filter is like in the whole table random to find data, in fact, efficiency is not as good as a table scan, so this overhead soared to 95%!

The above example is also an easy mistake for many beginners.

I remember in the first chapter of the play module, we mentioned a problem where the execution plan evaluation was not allowed by using the OPTIMIZE FOR hint hint to solve an introduction parameter.

The article can be seen here: SQL Server Tuning series play turn (how to run with query hint (Hint) bootstrap statement)

Let's review the following:

-- General query statement select Orderid,orderdatefrom orderswhere shippostalcode=n'51100'-- parameterized query statement declare  @ShipPostalCode NVARCHARSET @ShipPostalCode =n'51100'SELECT OrderID, Orderdatefrom orderswhere [email protected]          

Exactly the same logical query statement, just the following we pass the value of the parameter.

We just added a parameter, SQL Server will have the same query statement, the previous index lookup has become an index scan!

The consumption increased from 46% to 54% at a sudden ..... This is also a common problem with the statements we write, because in many cases, we implement the reuse of this statement through the use of parameters.

However, why adding parameters makes the query performance worse, obviously not a good way, in the first chapter of the play, our solution is through the OPTIMIZE for hint prompt solution.

Here, let's look at a workaround, or you can force the statement to specify a lookup by the given index hint.

Here's how:

-- Parameterized query statement declare  @ShipPostalCode NVARCHARSET @ShipPostalCode =n'51100'  SELECT Orderid,orderdatefrom Orders with (INDEX (Shippostalcode)) WHERE [email protected]     

is not a very handsome way.

I hope you can understand the benefits of these methods as a bonus.

Conclusion

This article first to this bar, to this play has three articles, about the SQL Server Tuning tool hint use there is a lot of content, follow-up, followed by, interested children shoes can be concerned about 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.

SQL Server Tuning series play turn three (use index hint (Hint) to boot statement maximum optimization run)