How do I tune SQL Server queries

Original: How do I tune SQL Server queries

I'm a lazy man, I just want to do as little as possible. I don't want to be too much when I'm working. Yes, you're right, it looks bad, and as a DBA it's not qualified. But in today's article, I want to show you how you can communicate your work and thinking process to the query optimizer when you want to create an indexed design for a particular query. Sounds interesting? Well, then go into my index tuning world!

queries that are problematic

Let's look at the following query:

1 DECLARE @i INT = 9992 SELECT3 SalesOrderID,4 Salesorderdetailid,5 Carriertrackingnumber,6 OrderQty,7 LineTotal8  fromSales.SalesOrderDetail9 WHEREProductID< @iTen  ORDER  byCarriertrackingnumber One  GO

As you can see, here is a local variable with a non-equal predicate to fetch 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 introduces a filter operator in the query plan that is used for line-by-row comparisons to check for rows that match (ProductID < @i)
• Because order by Carriertrackingnumber, a sort operator is introduced in the execution plan.
• The sort operator spread to tempdb because of an incorrect cardinality calculation (cardinality estimation). With a combination of a local variable and a non-equal predicate, SQL Server estimates 30% of the rows from the table's cardinality hard code. 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. How do you improve this query without modifying the query itself?

Let's debug the query!

of course, we have to make index-related adjustments to improve. There are no supported nonclustered indexes, that can only be the query optimizer can use a schedule to run our query. But what is a good nonclustered index for this specified query? In general, I consider the possible non-clustered speed printing by looking at the search predicate. In our example, the search predicate is as follows:

WHERE ProductID < @i

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

1 CREATE nonclustered INDEX  on Sales.SalesOrderDetail (ProductID) 2 GO

After the nonclustered index is created, we need to verify the change, so we execute the query code just now. How did the results pinch? The query optimizer did not use the nonclustered index we just created! We created a supported nonclustered index on the search predicate, and the query optimizer did not reference it? Usually people have no such a rut. In fact, we can prompt the query optimizer to use a nonclustered index to better understand why the query optimizer does not automatically select an index:

1 DECLARE @i INT = 9992 3 SELECT4 SalesOrderID,5 Salesorderdetailid,6 Carriertrackingnumber,7 OrderQty,8 LineTotal9  fromSales.SalesOrderDetail with(INDEX(idx_test))Ten WHEREProductID< @i One  ORDER  byCarriertrackingnumber A  GO

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

The query took 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 introduced a bookmark lookup, because the nonclustered index that you just created is not an overwrite nonclustered index for the query. The query crosses the so-called critical point (Tipping points) because we use the current search predicate to get almost all the rows. So it doesn't make sense to combine nonclustered indexes and bookmark lookups.

Not to think about why the query optimizer does not select the nonclustered index that was just created, we have already expressed our ideas to the query optimizer itself, queried the query optimizer by query hints, why the nonclustered index is not automatically selected. As I was beginning to say: I don't want to think too much.

using a nonclustered index to solve this problem, in the leaf layer of a nonclustered index, we must include additional columns from the request 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 that nonclustered index:  

 1  create  nonclustered  index  idx_test on   Sales.SalesOrderDetail (ProductID)  2  include (Carriertrackingnumber, OrderQty, UnitPrice, Unitpricediscount)  with  4   (  drop_existing =  on  6  )  7  go  

We've made another 1 changes so we can rerun the query to verify the next one. But this time we do not add query hints, because the query optimizer now automatically chooses a nonclustered index. How did the results pinch? When you look at the execution plan, the index is now selected.

SQL Server now finds operations on nonclustered indexes, but we also have sort operators in the execution plan. Because cardinality computes 30% hard-coded, the sort is still going to spread to tempdb. I drop God! Our logic readings have dropped to 757, but the running time is almost 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 for the SQL Server sort 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 does not spread to tempdb. In the execution plan, there is no operator now concerned with the wrong cardinality calculation. So let's try that hypothesis and rebuild the nonclustered index again:

 1  create  nonclustered  index  idx_test on   Sales.SalesOrderDetail (Carriertrackingnumber, ProductID)  2  include (OrderQty, UnitPrice, Unitpricediscount)  3  with  4   ( 5  drop_existing =  on  6  )  go 

As you can see from the index definition, we now have physical pre-ordering of the data in the Carriertrackingnumber and ProductID columns. When you re-execute the query again, when you view the execution plan, you see that the sort operator has disappeared, and SQL Server scanned the entire leaf layer of the nonclustered index (using the remaining 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 dropped to 600 milliseconds. Compared with the previous one, there has been a 25% improvement! However: The query optimizer suggests that we have a better nonclustered index by missing index suggestions (Missing index Recommendations)! For the moment, we create a proposed nonclustered index:

1 CREATE nonclustered INDEX [sql Server doesn ' t care on names, why am I should care about names?]2  on [Sales].[SalesOrderDetail]([ProductID])3INCLUDE ([SalesOrderID],[Salesorderdetailid],[Carriertrackingnumber],[OrderQty],[LineTotal])4 GO

When you re-execute the initial query now, you will find the surprising thing: the query optimizer uses the nonclustered index we just created, missing index suggestions have disappeared!

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

Conclusion: Never trust the query optimizer!

Summary

Today's article is a little 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 can fool you. So make a small adjustment, run your query immediately, and verify that the change is very important. And when you use the missing index suggestions to query the optimizer, consider this suggestion as a good one. Well, I've already said--I'll just drop it. hehe ~ ~ ~

Thanks for your attention!

How do I tune SQL Server queries