Click here to Skip to main content
15,916,949 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table with 2 million records and 6 columns in the table. I am executing below statement on it:

AND (t1.STATUS = 1)

The value for "Status" column in all the 2 million rows is 1.
The all distinct values for "SERVER_NAME" are 'mn' and 'op'. I have a non clustered index on Server_Name and Status column with ID column as included column.
'ID' column is a varchar field.

The query execution plan shows index seek for the NonClustered. The total time it is taking is apprx 4 seconds.

However, I am looking for any suggestions where the total execution time can be reduced to much lesser than 4 sec.

What I have tried:

Created index to make sure it is seek. Gained performance but still this 4 seconds of execution time is pretty high as this query gets executed close to 1000 times during batch processing, which in turn increasing the job execution time.
Updated 10-Sep-19 22:23pm

Maybe you can try a filtered index: An Essential Guide to SQL Server Filtered Indexes[^]

And here is another interesting article: Performance Surprises and Assumptions : GROUP BY vs. DISTINCT[^]
Share this answer
dinesh42 6-Sep-19 7:55am    
I tried implementing the filtered index, the optimizer is using the filtered index and the number of logical reads have come down to 6000. Earlier with normal non clustered index the total logical reads were 12000.

I am unsure if this reduce in logical reads will have impact in overall execution of the query when it runs for 1000 times during batch execution?
dinesh42 6-Sep-19 7:59am    
upon rebuilding the existing index also the logical reads have come down to 6000 which is similar to filtered index !
RickZeeland 6-Sep-19 8:58am    
It is a good thing to do reindexing on a regular basis, SQL Server needs a bit of help here. PostgreSQL needs less maintenance, one of the reasons we chose for PostgreSQL years ago :)
Adding indexes (filtered or not) are helpful and the best way to approach this.

What I am going to add into this is to use SSMS and view the Query Execution Plan to identify what index is going to help you best.

Read through this article to help you with SSMS and using the Execution Plans
Management Studio Hides Missing Indexes From You. - Brent Ozar Unlimited®[^]
Share this answer
You can try below approaches as well

1. Indexing will help, but you have already tried that.
2. define scope if possible, use "TOP" to fetch top X no. of rows.
3. try alternative, "group by" query.

Happy Coding!
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900