Spotlight-Using the index and query execution plan (v)

Objective

We talked about the effect of clustered indexes on nonclustered indexes and the performance optimizations that the database has been emphasizing, so we'll take a look at the index to see what the query execution plan is, what the short content is, and how to understand it.

View query execution plan through index

First, let's take a look at the first example.

1. Use Index by default

Use Tsql2012goselect OrderID  
From Sales.orders

The cost of the 2nd query that we see above is 3 times times the 1th query cost, and of course it involves the 1th query returning only one column and the 2nd query returning all columns, which also consumes a fraction of the performance. For SQL Server queries, it internally uses the index to take the shortest path for optimal performance. We can note that even though OrderID is the primary key, the returned data is not a clustered index that is automatically generated by the primary key being used, but rather a nonclustered index. I believe there are a lot of people subjectively feel that return primary key and query no query condition should be a clustered index of the primary key, but sometimes this is not the case, the last one we have discussed this issue, no longer described. Using the * return data in the 2nd query is a clustered index that takes advantage of the primary key.

2. Forcing the primary key to use the clustered index

To force the use of an index we use the WITH (index name) to create, as follows:

Use tsql2012goselect OrderID   * FROM sales.orders with (INDEX (pk_orders))

We can see from the above that the default returns the primary key column when using a nonclustered index, where we force it to go to the clustered index, and for the 2nd query is not to mention, at this time the cost of the two is equivalent.

3. Forced use of nonclustered indexes

We continue to look down and force a nonclustered index lookup on the query as follows:

Use tsql2012goselect OrderID   * FROM sales.orders with (INDEX (Idx_nc_custid))

From the above, the cost difference between the two is large, for the use of nonclustered index query 1 returns a single column, and query 2 returns all columns faster so much, by using the index by default, forcing the use of clustered indexes, forcing the use of nonclustered indexes we know that for retrieving all column result sets using the primary key for the clustered index is the best choice.

Summarize

With the above demo we know that even if you create a clustered index and do not use a clustered index to retrieve results, sometimes using a nonclustered index would provide better performance than using a clustered index, and of course not generalize, both of which have a usage scenario. When it comes to database optimization at each interview, the first thought is the index, then there is no context, how to use the index, how to use different indexes in different scenarios? Indexing in any database has always been a very big topic and is a complex content, complex content is made up of simple accumulation, we must like snail-like to study slowly, cocoon stripping silk, eventually there will be a good effect. Short content, in-depth understanding.

Spotlight-Using the index and query execution plan (v)