Analysis of SQL Server's focus on using indexes and query execution plans and SQL Execution plans

Analysis of SQL Server's focus on using indexes and query execution plans and SQL Execution plans

Preface

In the previous article "Analysis of the Impact of SQL Server focused indexes on non-clustered indexes", we talked about the impact of clustered indexes on non-clustered indexes and the performance optimization that has been emphasized by the database, in this section, we will talk about how to use indexes to check the query execution plan. The short content and in-depth understanding will be provided.

Query execution plans through Indexes

Let's take a look at the first example.

1. indexes are used by default.

USE TSQL2012GOSELECT orderid FROM Sales.OrdersSELECT * FROM Sales.Orders

We can see that the overhead of 2nd queries is three times the overhead of 1st queries. Of course, 1st queries only return one column and 2nd queries return all columns, this also consumes a small amount of performance. For SQL Server queries, it uses indexes to obtain the optimal performance in the shortest path. Even if orderid is used as the primary key, the returned data is not the clustered Index automatically generated by the primary key, but not the clustered index. I believe many people think that the primary key returned and no query condition in the query should be the clustered index of the primary key, but sometimes this is not the case. We have discussed this issue in the previous article and will not describe it any more. In 2nd queries, * returned data is clustered indexes of the primary key.

2. Force the primary key to use clustered Index

We use With (index name) to create a mandatory index, as shown below:

USE TSQL2012GOSELECT orderid FROM Sales.Orders WITH(INDEX(PK_Orders))SELECT * FROM Sales.Orders WITH(INDEX(PK_Orders))

We can see from the above that the non-clustered index is used when the primary key column is returned by default. Here we force it to take the clustered index, which is not required for 2nd queries. At this time, the overhead of the two is equivalent.

3. Forced Use of non-clustered Indexes

Let's continue to look at it and forcibly use non-clustered index search for the query, as shown below:

USE TSQL2012GOSELECT orderid FROM Sales.Orders WITH(INDEX(idx_nc_custid))SELECT * FROM Sales.Orders WITH(INDEX(idx_nc_custid))

As can be seen from the above, there is a large difference in overhead between the two. For a non-clustered index query 1, a single column is returned, while query 2 returns all columns so quickly, by default, indexes are used, clustered indexes are forcibly used, and non-clustered indexes are forcibly used. We know that primary key clustered indexes are the best choice for retrieving all column result sets.

Summary

Through the above demonstration, we know that even if a clustered index is created, the clustered index search results will not be used. Sometimes, using non-clustered indexes provides better performance than using clustered indexes. Of course, they cannot be generalized, both are applicable scenarios. When talking about database optimization during each interview, the first thing that comes to mind is the index. Then there is no more information. How to Use the index and how to use different indexes in different scenarios? Indexing in any database is always a big topic and a complex content. Complicated content is made up of simple accumulation. We must study it slowly like a snail bait, in the end, it will have a good effect. Brief content and in-depth understanding.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!