Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to optimize the SQL Server query. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

In today's article, I want to show you how to communicate your work and thinking process to the query optimizer when you want to create an index design for a particular query. Let's discuss it together.

For questionable queries, let's take a look at the following queries:

DECLARE @ I INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal FROM Sales.SalesOrderDetail WHERE ProductID < @ i ORDER BY CarrierTrackingNumber GO

As you can see, a local variable and a non-equal predicate are used to get some records from the Sales.SalesOrderDetail table. When you execute that query and look at its execution plan, you will find that it has some serious problems:

SQL Server needs to scan the entire nonclustered index of the Sales.SalesOrderDetail table because there are no supported nonclustered indexes. For this scan, the query requires 1382 logical reads and runs for nearly 800ms. The query optimizer introduces a filter (Filter) operator into the query plan, which compares row by row to check for matching rows (ProductID < @ I) because ORDER BY CarrierTrackingNumber, a Sort operator is introduced in the execution plan. The sort operator spreads to TempDb because of incorrect cardinality calculation (Cardinality Estimation). Using a combination of local variables and predicates that are not equal to, SQL Server hard-codes 30% of the rows from the cardinality of the table. In our case, the estimated number of rows is 36395 (121317 * 30%). The query actually returns 120621 rows, which means that the Sort operator must spread to TempDb because the requested memory grant is too small.

Now let me ask you-- can you improve this query? What's your suggestion? Take a break and think for a few minutes. How can you improve the query without modifying the query itself?

Let's debug the query! Of course, we have to make index-related adjustments to improve. There is no supported nonclustered index, which is the only one that the query optimizer can use plans to run our queries. But what is a good nonclustered index for this specified query? In general, I consider possible non-aggregate fast printing by looking at the search predicate. In our example, the search predicate is as follows:

WHERE ProductID < @ I

We request rows to be filtered in the ProductID column. So we want to create a supported nonclustered index on that column. We build an index:

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail (ProductID) GO

After the nonclustered index is created, we need to validate the changes, so we execute the query code again. What was the result? The query optimizer does not use the nonclustered index we just created! We created a supported nonclustered index on the search predicate, which is not referenced by the query optimizer? Usually there is nothing people can do about it. In fact, we can prompt the query optimizer to use nonclustered indexes to better understand why the query optimizer does not automatically select indexes:

DECLARE @ I INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotalFROM Sales.SalesOrderDetail WITH (INDEX (idx_Test)) WHERE ProductID < @ i ORDER BY CarrierTrackingNumber GO

When you look at the execution plan now, you will see the following wildness-- a parallel plan:

The query took 370109 logical reads! The running time is basically the same as just now. What's going on here? When you look closely at the execution plan, you will see that the query optimizer introduces bookmark lookup, because the nonclustered index you just created is not an overlay nonclustered index for the query. The query crosses the so-called tipping point (Tipping Point) because we use the current search predicate to get almost all rows. So it doesn't make sense to combine nonclustered indexes with bookmark lookups.

Instead of thinking about why the query optimizer did not choose the nonclustered index just created, we have expressed our ideas to the query optimizer itself and asked the query optimizer through the query hint why the nonclustered index was not selected automatically. As I said at the beginning: I don't want to think too much.

Using nonclustered indexes to solve this problem, at the leaf layer of nonclustered indexes, we must include additional columns requested from the SELECT list. You can look at the bookmark search again to see which columns are currently missing in the leaf layer:

CarrierTrackingNumber OrderQty UnitPrice UnitDiscountPrice

Let's rebuild that nonclustered index:

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail (ProductID) INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount) WITH (DROP_EXISTING = ON) GO

We have made another change, so we can rerun the query to verify it. But this time we don't add a query hint, because now the query optimizer automatically selects a nonclustered index. What was the result? When you look at the execution plan, the index is now selected.

SQL Server now looks up on the nonclustered index, but we also have the Sort operator in the execution plan. Because the cardinality calculates 30% of the hard code, Sort still has to spread to TempDb. Oh, my God! Our logical read has been reduced to 757, but the running time is still nearly 800 milliseconds. What should you do now?

Now we can try to include the CarrierTrackingNumber column directly in the navigation structure of the nonclustered index. This is the column in which SQL Server sorts the operator. When we add this column directly to the nonclustered index (as the primary key), we physically sort that column, so the Sort operator should disappear. As a positive side effect, it will not spread to TempDb. In the execution plan, there is no operator concerned about the wrong cardinality calculation. So let's try that assumption and rebuild the nonclustered index again:

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail (CarrierTrackingNumber, ProductID) INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount) WITH (DROP_EXISTING = ON) GO

As you can see from the index definition, we have now physically pre-sorted the data for the CarrierTrackingNumber and ProductID columns. When you re-execute the query again, when you look at the execution plan, you will see that the Sort operator has disappeared, and SQL Server scans the entire leaf layer of the nonclustered index (using the residual predicate (residual predicate) as the search predicate).

This execution plan is not bad! We only need 763 logical reads, and now the run time has been reduced to 600 milliseconds. Compared with the previous one, there has been a 25% improvement! But: the query optimizer suggests that we have a better nonclustered index, through the lack of index recommendations (Missing Index Recommendations)! For the time being, let's create the recommended nonclustered index:

CREATE NONCLUSTERED INDEX [SQL Server doesn't care about names, why I should care about names?] ON [Sales]. [SalesOrderDetail] ([ProductID]) INCLUDE ([SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [LineTotal]) GO

When you re-execute the original query now, you will find something surprising: the query optimizer uses the nonclustered index we just created, and the missing index recommendation has disappeared!

You have just created an index that SQL Server never uses-except for INSERT,UPDATE and DELETE statements, SQL Server maintains your nonclustered index. For your database, you have just created a "pure" space-wasting index. On the other hand, you have satisfied the query optimizer by eliminating missing index recommendations. But that's not the goal: the goal is to create an index that will be used again.

Conclusion: never trust the query optimizer!

The above is the editor for you to share how to tune the SQL Server query, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.