Share via

SQL Server Performance Tuning : Backward Scanning of an Index

  • Article

One of the key areas to investigate during performance optimization is the indexing strategy. Not building the right index based on the read pattern may be disastrous specially when databases are big. In this article, I will cover a scenario around how SQL Server scans an index while reading BACKWARD, like when ORDER BY Col1 DESC is used in a query.

 

We shall use the AdventureWorks2012 Database for demonstrating this. I will create a new table TransactionHistory2 for this.

 

Query1:

I will now add more rows to the table and follow it up by creating a Clustered and a non-Clustered Index on it

 

Query2:

 

 

Query3:

 

 

As part of the demonstration, I will  query for TransactionID,TransactionDate,TransactionType from  table TransactionHistory2 by filtering on the attribute TransactionID.

The machine I am using for the demonstration is a 64 bit OS with an Windows 8.1 Enterprise with 16 GB RAM. Also find below the CPU Configuration.

 

 

My first goal is to ensure that the queries that I picked up for my demonstration run with parallelism. The property Cost threshold for Parallelism on my SQL Instance is set to 5. I executed the below code changing the value of the predicate TransactionID such that the Estimated Subtree Cost for the query goes over 5. Note that the mentioned properties are of the SELECT operator.

 

Query4:

 

 

 

 

Now, with a value greater than 166000 (I have selected  a much higher number) , I go fetch the data ordering first by the TransactionDate in Ascending Order followed by Descending Order. 

Query5:  

 

 

And as expected, SQL Server scans the data in parallel. Lets do the same again but changing the direction of the ordering.

 

Query6:

 

 

 

 

 

SQL Server now choses a Clustered Scan but does not process the descending order clause with a parallel scan even while the Estimated Subtree Cost is over 17%. That’s because, currently a BACKWARD scan is not possible with parallelism. It is good to be aware of such implications while designing indexes. If the requirement is to fetch data from tables in a descending order, it may be more performant to create an index in descending order. Similarly, if the data needed is ordered by multiple attributes, both in opposite direction of ordering , it will be prudent to store the data within the index in that fashion (column1, column2 desc ) else there will be an explicit sorting of data, which is quite avoidable in such cases.

 

Thanks for reading!!

Comments

  • Anonymous
    April 27, 2015
    I think that you may be presenting this information incorrectly due to the fact that you are searching for TransactionID <= 625,000 in the ascending query, and <= 825,000 in the descending query. The larger number of records in the descending query is why it is choosing to a non-parallel scan of the clustered index instead of doing a parallel scan on the nonclustered index. I am able to get both the ascending and descending queries to search the nonclustered index in parallel when selecting the same number of records (<=625,000). Also, if you increase the number of records selected in the ascending query to <= 825,000, it also performs a non-parallel scan of the clustered index.

  • Anonymous
    May 03, 2015
    Hi JeremyH, Can you please share how you were able to achieve the parallelism during a Backward Scan of an index. I have tried this on SQL 2008 and 2012 , and have not seen this behavior. I shall drop a note after I manage to do a quick cross check on SQL 2014. Thanks as always for your feedback.

  • Anonymous
    May 06, 2015
    Hello nagasuraj, I am not able to achieve parallelism during a Backward Scan, and I am not claiming that it is possible. My point, is that your queries are choosing different plans because you are changing the amount of records you are selecting. Using your test setup, run the following query and you will still get a parallel seek on the non-clustered index just like you did with the ASC order query: select transactionid, transactiondate, transactiontype from production.TransactionHistory2 where transactionid <= 625000 order by TransactionDate DESC Also, if you were to run the following query, you would get a non-parallel scan of the clustered index just like you did with the DESC hint: select transactionid, transactiondate, transactiontype from production.TransactionHistory2 where transactionid <= 825000 order by TransactionDate In fact, I am not able to get a parallel scan of the clustered index no matter how I query this table. I am just saying that your examples are not reflecting the point you are trying to make.

  • Anonymous
    May 09, 2015
    Hi JeremyH - First of all thanks for bringing this out. I see your point. Changing the predicate value between the two queries must have been a little confusing. Let me clarify that my intent was to bring out how SQL Server processes a  BACKWARD scan of an Index. The queries choosing different plans can be because of multiple reasons. Anyway, we can achieve the same with the below queries which have similar predicates , but then I had to force using the Clustered Index on the query. But hope, you see the point. DBCC FREEPROCCACHE WITH NO_INFOMSGS; SELECT transactionid, transactiondate, transactiontype FROM production.TransactionHistory2 WITH (INDEX(CI_TransactionHistory2)) WHERE transactionid <= 425000 ORDER BY transactiondate DESC OPTION (RECOMPILE) SELECT transactionid, transactiondate, transactiontype FROM production.TransactionHistory2  WITH (INDEX(CI_TransactionHistory2)) WHERE transactionid <= 425000 ORDER BY transactiondate OPTION (RECOMPILE) Regds, nagasuraj

  • Anonymous
    May 13, 2015
    Hello nagasuraj, I apologize if I am not being clear. It seems to me, that in query 5 of your post, you intend to show a parallel scan of an index. It does not do this. It is showing a parallel seek of the nonclustered index. An example of what a parallel scan of an index looks like can be viewed in Figure 1 of the following post by Itzik Ben-Gan - (I apparently cannot put a link here without getting flagged as spam, so just search for sqlmag descending indexes). I just think that your post needs clarified - either your content does not match your intended examples, or your examples do not match your intended content.