I personally prefer to analyze Logical Reads as key performance parameter for Query Tuning. Below is the screenshot of statistics for above query from statisticsparser.com:- Also, Below is the execution plan:- Digging further on top right most Clustered Index Scan Operator, and checking the value for Number of Rows Read & Actual Number of Rows, we can see that SQL Server is suggesting of Residual IO operation. In order to rectify the problem, we may need to remove this Residual IO operation with Index Seek operator by considering residual predicate.
So lets first modify it to make it easier to read and analyze.
Query Modified - What is my accepted answer percentage rate
[dbo].[Posts] has only Primary Clustered Index on
[Id] field. 4-5 indexes comprising 2-3 key columns should not be issue in most cases. So let’s create a non-clustered index on Predicate Fields of CTE
Create Non-Clustered Index to remove Residual IO by Index Seek
-- Create Non-Clustered Index to remove Residual IO by Index Seek
Below are the Execution Plan and stats screenshot after creating above Non-Clustered Index.
We can see that our key performance parameter Logical Reads has reduced to 7,734,109 from 23,201,186 thus executing within 2 minutes.
Now, to further optimize our query, we need to replace bottom half of Nested Loop in Execution Plan with another non-clustered Index to avoid Clustered Index scan for IN predicate logic of CTE
Non-Clustered Index to Remove Clustered Scan for AcceptedAnswerId
After executing, the query again, we have below Execution Plan and Stats:-
Finally our query has executed 68 milliseconds performing only 153 Logical Reads to achieve the desired result.
Conclusion: A little modification of query along with analyzing the Execution Plan can help us to decide proper indexing and querying changes. Happy Coding
Friends, please feel free to correct me by comments. Share your approach if it differs from mine. If you like the article, do Like & Share.
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.