How important is SQL Server to fully optimize-------indexes?

Thought for a long time index importance should be how to write? Talk about the principle structure? I estimate that most people are reluctant to look at it and are reluctant to spend so much time studying it carefully. Optical Write application? I don't know how it works. An example? Too many things to write about .... How do you write it?

　　Write it casually, and think of where to write it!

Many of the previous articles, regardless of CPU, memory, disk, statements and so on have mentioned the importance of the index, I think the students who have just started to learn the database are aware of the importance of index to statement performance. But they may not know that the importance of the statement is the importance of the system!

　　

　　Throw a question: Do you believe that a statement will let your big system hang up?

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

No more nonsense, just open the whole-----------------------------------------------------------------------------------------.

• Opening Quiz

　　How do you add the best index to a small SQL?

There are now only clustered indexes on two tables

The Bigproduct table already has a clustered index ProductID

The Bigtransactionhistory table already has a clustered index TransactionID

　　

Select P.productnumber,p.reorderpoint,th. Quantityfrom bigproduct as Pjoin bigtransactionhistory as th on th.productid=p.productid and th. TransactionDate > P.sellstartdatewhere p.name in (' LL Crankarm1000 ', ' ML Crankarm1000 ') and th. TransactionDate > ' 2010-01-01 '

Can you see it at one glance?

　　

The answer will be gradually revealed in the article ~ ~ ~

• Simple and rude Add index

　　Read my previous article of the crossing will find that I like to use the word "simple rude", one is because the vocabulary of small writing is also poor, really can not use the words of the tall! Another, you don't like simple rough ~ ~ Dry is the most important, right?

　　

First, let's look at the execution plan without optimization.

　　

　　

　　

　　Clustered index Scan This is actually a table scan, not the table scan just because there are clustered indexes on the tables

You can see that this query both tables use a table scan!

　　

　　Where Condition Add index

First of all, most people know that fields in the Where condition need to be indexed! Let's add a look at the effect creation

Create a Name column index on the bigproduct table and create a transactiondate column index on the bigtransactionhistory table.

Execute the statement again to see the effect!

　　

　　

　　

After you add the where index, you can see several phenomena

1. Bigproduct from the original clustered index scan into index seek
2. Extra key Lookup (clustered)
3. The cable added on the bigproduct is causing the function, and the logical read BIGPRODUCT is changed from 601 to 10.
4. Bigtransactionhistory no change, or clustered index scan

　　

　　Explain the phenomenon: the first point of the bigproduct side added to the Where Condition index, played a role, the execution is not full table scan, logical reading has a significant decline, the appearance of KEY Lookup is because the selection (select) of the column, not in the index, You need to look through the clustered index again, and it means a lot of overhead!

So why doesn't the Bigtransactionhistory table that also adds a Where condition index work? That's because the SQL Optimizer thinks that it would be better to find it without using the TransactionDate column index when choosing a plan!

Are you sure? Let's verify that the optimizer chooses the index lookup by specifying the selection index!

　　

　　

　　　After forcing the index, you can see that the logical read from 14W to 1961W, the statement time also becomes very long, this is why the optimizer does not choose your index! The optimizer is still smart.

　　High-energy warning: Optimizer is not always so smart ... Due to cache planning or optimizer extractor and other reasons, it will also appear optimizer with this index, resulting in your statement is very slow, read soaring directly affect your memory, disk, CPU resources! In addition, if such a statement is a very frequently running statement in the system, your system will be hung up! Yes, just hang up! This is the beginning of the problem is because of a statement!

Eliminate key Lookup add Select field

This is the legendary overlay index!

When you see the key Lookup in the execution plan and the high consumption ratio, such as the plan to force the index above, then we want to include the columns of those select in the index! If the consumption is low, the logic reads less, as the key Lookup in the Bigproduct table above can be ignored (if you pursue perfection, you can also optimize it).

Graphical creation of included columns: @ Qiu Xian Special Instructions for you

　　　

　　　

Statement creation is:

　　　

CREATE nonclustered INDEX transactiondate contains Productid_quantityon [dbo]. [Bigtransactionhistory] ([transactiondate])------include includes column include ([productid],[quantity]) GO

Let's add a look at the effect below:

　　　

　　　

What you can see when you add a Select index field:

1. The optimizer chose index seek by itself
2. Bigtransactionhistory the highest key lookup disappears.
3. Logical reads from the original 14W without index into 1W
4. Bigtransactionhistory table also hints for missing indexes?

　　　

By optimizing the index to add the Select field, we see that the statement has been lifted again bigtransactionhistory from table Scan to index lookup, logical read from 14W to 1w! This is a qualitative leap ah!

CREATE nonclustered INDEX transactiondate contains Productid_quantityon [dbo]. [Bigtransactionhistory] ([transactiondate])------include includes column include ([productid],[quantity]) GO

Then why do you suggest missing an index? Create a try it!

　　Index re-optimization joins Table association columns

Follow the prompts to create the index: different ProductID columns from the previous index are indexed columns by the containing column!

Use [adventureworks2012]gocreate nonclustered INDEX productid_transactiondate contains Quantityon [dbo]. [Bigtransactionhistory] ([Productid],[transactiondate]) INCLUDE ([Quantity])

Let's look at the effect:

　　

　　

After you have optimized the index again, you can see several phenomena

1. Bigtransactionhistory table or Index seek
2. Bigtransactionhistory still without the key Lookup
3. The two-table associated hash join becomes the nested loops
4. The parallel plan becomes a serial
5. Logic reading is changed from 1W to 18

　　Another qualitative leap! Read from the original 14W into 1W and become 18, this greatly reduces the memory and IO consumption, in addition to the parallel plan has become serial, and undoubtedly reduce the consumption of a lot of CPU! Statement time, I think there is no need to say more?

　　

High-energy warning: Here is the hash join, parallel to the serial, do not understand the friend can be in Baidu self-study, here is only for the current statement of the situation, can not generalize!

Refine your index

　　As you all know, indexing causes update, insert, and delete operations to slow down! It's a very important topic to try to streamline your index.

We created several indexes in the optimization process above, taking bigtransactionhistory as an example:

　　

The script is not posted here, in fact, we finally created the index productid_transactiondate contains quantity already contains the first two indexes, and can say that any similar statements are used productid_ TransactionDate contains quantity on the go!

Then we can clear the first two indexes!

　　　　

　　　

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

This statement optimization is the end, left is bigproduct still have a key lookup can be optimized, you can imitate the above to continue optimization, here is not elaborate. Statement just after simple index optimization from a 2-hand QQ into a Ferrari, is not it magical?

This is the importance of the index!

　

Did you do the right thing at the beginning of the quiz? If that's not true, then you have to simulate a scene to reproduce the topic of this article!

-----------------------------------------------------------------------------------------------------

Summary: Often the overall slowness of a system is due to indexing problems, optimized index is the simplest maintenance of your system!

Do not underestimate the power of a statement, a statement sufficient to make your system completely unable to work!

　　　　　

A question followed by a statement a aimless optimization? How do I find the system's problem statement? What's a priority?

See the previous article: Expert Diagnostic Optimization Series------------------statement tuning kick

I'll use the Expert for SQL Server tool to tell you how to index a key statement, like crossing, please mark! 　

Expert Diagnostic Optimization Series-------------indexing for key statements

----------------------------------------------------------------------------------------------------

Original link: http://www.cnblogs.com/double-K/archive/2016/06/02/5538249.html

For the convenience of reading a series of articles on the Guide link:

SQL Server fully optimized-------Expert for SQL Server Diagnostic series

Recommended blog----------------------in-depth indexing principles--------------------------

Visual inspection of these articles each writing time is more than 10 hours, it is worth reading!

Pursuer.chen's Blog

SQL Server Deep parse index store (top) SQL Server Deep Parse index store (middle) SQL Server Deep parse index store (bottom)

The blog of Hua Zi

　　How do you add the best index to a small SQL?

There are now only clustered indexes on two tables

The Bigproduct table already has a clustered index ProductID

The Bigtransactionhistory table already has a clustered index TransactionID

　　

Select P.productnumber,p.reorderpoint,th. Quantityfrom bigproduct as Pjoin bigtransactionhistory as th on th.productid=p.productid and th. TransactionDate > P.sellstartdatewhere p.name in (' LL Crankarm1000 ', ' ML Crankarm1000 ') and th. TransactionDate > ' 2010-01-01 '

Can you see it at one glance?

　　

The answer will be gradually revealed in the article ~ ~ ~

• Simple and rude Add index

　　Read my previous article of the crossing will find that I like to use the word "simple rude", one is because the vocabulary of small writing is also poor, really can not use the words of the tall! Another, you don't like simple rough ~ ~ Dry is the most important, right?

　　

First, let's look at the execution plan without optimization.

　　

　　

　　

　　Clustered index Scan This is actually a table scan, not the table scan just because there are clustered indexes on the tables

You can see that this query both tables use a table scan!

　　

　　Where Condition Add index

First of all, most people know that fields in the Where condition need to be indexed! Let's add a look at the effect creation

Create a Name column index on the bigproduct table and create a transactiondate column index on the bigtransactionhistory table.

Execute the statement again to see the effect!

　　

　　

　　

After you add the where index, you can see several phenomena

1. Bigproduct from the original clustered index scan into index seek
2. Extra key Lookup (clustered)
3. The cable added on the bigproduct is causing the function, and the logical read BIGPRODUCT is changed from 601 to 10.
4. Bigtransactionhistory no change, or clustered index scan

　　

　　Explain the phenomenon: the first point of the bigproduct side added to the Where Condition index, played a role, the execution is not full table scan, logical reading has a significant decline, the appearance of KEY Lookup is because the selection (select) of the column, not in the index, You need to look through the clustered index again, and it means a lot of overhead!

So why doesn't the Bigtransactionhistory table that also adds a Where condition index work? That's because the SQL Optimizer thinks that it would be better to find it without using the TransactionDate column index when choosing a plan!

Are you sure? Let's verify that the optimizer chooses the index lookup by specifying the selection index!

　　

　　

　　　After forcing the index, you can see that the logical read from 14W to 1961W, the statement time also becomes very long, this is why the optimizer does not choose your index! The optimizer is still smart.

　　High-energy warning: Optimizer is not always so smart ... Due to cache planning or optimizer extractor and other reasons, it will also appear optimizer with this index, resulting in your statement is very slow, read soaring directly affect your memory, disk, CPU resources! In addition, if such a statement is a very frequently running statement in the system, your system will be hung up! Yes, just hang up! This is the beginning of the problem is because of a statement!

Eliminate key Lookup add Select field

This is the legendary overlay index!

When you see the key Lookup in the execution plan and the high consumption ratio, such as the plan to force the index above, then we want to include the columns of those select in the index! If the consumption is low, the logic reads less, as the key Lookup in the Bigproduct table above can be ignored (if you pursue perfection, you can also optimize it).

Graphical creation of included columns: @ Qiu Xian Special Instructions for you

　　　

　　　

Statement creation is:

　　　

CREATE nonclustered INDEX transactiondate contains Productid_quantityon [dbo]. [Bigtransactionhistory] ([transactiondate])------include includes column include ([productid],[quantity]) GO

Let's add a look at the effect below:

　　　

　　　

What you can see when you add a Select index field:

1. The optimizer chose index seek by itself
2. Bigtransactionhistory the highest key lookup disappears.
3. Logical reads from the original 14W without index into 1W
4. Bigtransactionhistory table also hints for missing indexes?

　　　

By optimizing the index to add the Select field, we see that the statement has been lifted again bigtransactionhistory from table Scan to index lookup, logical read from 14W to 1w! This is a qualitative leap ah!

CREATE nonclustered INDEX transactiondate contains Productid_quantityon [dbo]. [Bigtransactionhistory] ([transactiondate])------include includes column include ([productid],[quantity]) GO

Then why do you suggest missing an index? Create a try it!

　　Index re-optimization joins Table association columns

Follow the prompts to create the index: different ProductID columns from the previous index are indexed columns by the containing column!

Use [adventureworks2012]gocreate nonclustered INDEX productid_transactiondate contains Quantityon [dbo]. [Bigtransactionhistory] ([Productid],[transactiondate]) INCLUDE ([Quantity])

Let's look at the effect:

　　

　　

After you have optimized the index again, you can see several phenomena

1. Bigtransactionhistory table or Index seek
2. Bigtransactionhistory still without the key Lookup
3. The two-table associated hash join becomes the nested loops
4. The parallel plan becomes a serial
5. Logic reading is changed from 1W to 18

　　Another qualitative leap! Read from the original 14W into 1W and become 18, this greatly reduces the memory and IO consumption, in addition to the parallel plan has become serial, and undoubtedly reduce the consumption of a lot of CPU! Statement time, I think there is no need to say more?

　　

High-energy warning: Here is the hash join, parallel to the serial, do not understand the friend can be in Baidu self-study, here is only for the current statement of the situation, can not generalize!

Refine your index

　　As you all know, indexing causes update, insert, and delete operations to slow down! It's a very important topic to try to streamline your index.

We created several indexes in the optimization process above, taking bigtransactionhistory as an example:

　　

The script is not posted here, in fact, we finally created the index productid_transactiondate contains quantity already contains the first two indexes, and can say that any similar statements are used productid_ TransactionDate contains quantity on the go!

Then we can clear the first two indexes!

　　　　

　　　

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

This statement optimization is the end, left is bigproduct still have a key lookup can be optimized, you can imitate the above to continue optimization, here is not elaborate. Statement just after simple index optimization from a 2-hand QQ into a Ferrari, is not it magical?

This is the importance of the index!

　

Did you do the right thing at the beginning of the quiz? If that's not true, then you have to simulate a scene to reproduce the topic of this article!

-----------------------------------------------------------------------------------------------------

Summary: Often the overall slowness of a system is due to indexing problems, optimized index is the simplest maintenance of your system!

Do not underestimate the power of a statement, a statement sufficient to make your system completely unable to work!

　　　　　

A question followed by a statement a aimless optimization? How do I find the system's problem statement? What's a priority?

See the previous article: Expert Diagnostic Optimization Series------------------statement tuning kick

I'll use the Expert for SQL Server tool to tell you how to index a key statement, like crossing, please mark! 　

Expert Diagnostic Optimization Series-------------indexing for key statements

----------------------------------------------------------------------------------------------------

Original link: http://www.cnblogs.com/double-K/archive/2016/06/02/5538249.html

For the convenience of reading a series of articles on the Guide link:

SQL Server fully optimized-------Expert for SQL Server Diagnostic series

Recommended blog----------------------in-depth indexing principles--------------------------

Visual inspection of these articles each writing time is more than 10 hours, it is worth reading!

Pursuer.chen's Blog

SQL Server Deep parse index store (top) SQL Server Deep Parse index store (middle) SQL Server Deep parse index store (bottom)

The blog of Hua Zi

SQL Server clustered index and nonclustered index re-study (top) SQL Server clustered index and nonclustered index re-study (bottom)

How important is SQL Server to fully optimize-------indexes?