How to tune SQL Server queries _mssql

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

Queries that have problems
Let's look at the following query:

 DECLARE @i INT = 999
 SELECT SalesOrderID, Salesorderdetailid, Carriertrackingnumber, 
   OrderQty, 
   LineTotal
 From Sales.SalesOrderDetail
 WHERE ProductID < @i order by
 Carriertrackingnumber
 Go

As you can see, here we use a local variable and a not equal predicate 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 800 milliseconds.
• The query optimizer introduced a filter operator in the query plan, which is used to check line-by-row comparisons for rows that are compliant (ProductID < @i)
• An ordering (sort) operator is introduced in the execution plan because of the order by Carriertrackingnumber.
• The sort operators spread to tempdb because of incorrect cardinality calculations (cardinality estimation). With a combination of local variables and not equal predicates, SQL Server estimates 30% of the rows from the base hard code of the table. The estimated number of rows in our case 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 I ask you-can you improve the query? What's your suggestion? Take a break and think for a few minutes. Without modifying the query itself, how do you improve this query?

We will debug the query!
Of course, we need to do index-related adjustments to improve. There are no supported nonclustered indexes, only the query optimizer can use the schedule to run our queries. But what is a good nonclustered index for this specified query? In general, I consider possible nonclustered speed prints by looking at the search predicate. In our example, the search predicate is as follows:

WHERE ProductID < @i

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

CREATE nonclustered INDEX idx_test on Sales.SalesOrderDetail (ProductID) Go

 

After the nonclustered index is created, we need to verify the changes, so we execute the query code again. How did it end? The query optimizer does not use the nonclustered index we just created! We created a supported nonclustered index on the search predicate, and the query optimizer does not reference it? Usually people have no other means of this. We can actually prompt the query optimizer to use a nonclustered index to better understand why the query optimizer does not automatically select indexes:

 DECLARE @i INT = 999
 
 SELECT SalesOrderID, Salesorderdetailid, Carriertrackingnumber, 
  OrderQty, 
  LineTotal
From 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 cost 370,109 logical reads! The running time is basically the same as just now. What the hell is going on here? When you look closely at the execution plan, you will find that the query optimizer introduces a bookmark lookup, because the nonclustered index that you just created is not an overlay nonclustered index for the query. The query crosses the so-called tipping point, because we use the current search predicate to get almost all rows. Tipping Therefore, it is meaningless to combine nonclustered indexes and bookmark lookups.

Instead of wondering why the query optimizer does not choose the nonclustered index just created, we have already expressed our ideas to the query optimizer itself, asking the query optimizer through query hints, and why the nonclustered indexes are not automatically selected. As I have just started, I don't want to think too much.

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

• Carriertrackingnumber
• OrderQty
• UnitPrice
• Unitdiscountprice

We rebuilt the nonclustered index:

CREATE nonclustered INDEX idx_test on Sales.SalesOrderDetail (ProductID)
INCLUDE (Carriertrackingnumber, OrderQty, UnitPrice, Unitpricediscount)
 with
[
 drop_existing = on
 ]
go

We've made another 1 changes, so we can rerun the query to verify it. But this time we do not add query hints, because the query optimizer will now automatically select nonclustered indexes. How did it end? When you look at the execution plan, the index is now selected.

SQL Server now has a lookup operation on a nonclustered index, but we also have the sort operator in the execution plan. Because the cardinality evaluates to 30% hard-coded, sorting (sort) still spreads to tempdb. I am God! Our logic reading has dropped to 757, but the run time is nearly 800 milliseconds. What are you supposed to 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 the column, so the sort operator should disappear. As a positive side effect, it will not spread to tempdb. In the execution plan, there are now no operator concerns about the wrong cardinality calculation. So let's try that hypothesis and recreate 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 carriertrackingnumber the data for the columns of the ProductID and the column. When you rerun the query, as you look at the execution plan, you see that the sort operator has disappeared, and SQL Server scans the entire leaf layer of the nonclustered index (using the remaining predicate (residual predicate) as the search predicate).

This implementation plan is not bad! We only need 763 logical reads, and now the run time has dropped to 600 milliseconds. Compared with the previous one, there have been 25% improvements! However: The query optimizer recommends that we have a better nonclustered index by missing index recommendations (Missing index Recommendations)! For the moment, we 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 rerun the initial query now, you will find the surprising thing: the query optimizer uses the nonclustered index that "we" just created, and the missing index recommendation has disappeared!

You have just created an index that SQL Server never uses-except for the insert,update and DELETE statements, SQL Server is going to maintain your nonclustered indexes. For your database, you have just created an index of "pure" wasted space. When on the other hand, you have met the query optimizer by eliminating the suggestion of missing indexes. But this is not the purpose: to create an index that will be used again.

Conclusion: Never trust the query optimizer!

Summary

Today's article is a bit controversial, but I want you to show it to you, but when you create an index, how the query optimizer helps you, and how the query optimizer makes a fool of you. So make a small adjustment and run your query immediately, verifying that the change is very important.

The above is the entire content of this article, I hope to help you learn.