July 2, 2013 at 4:16 pm
I have a query that does not run very frequently but when it does it table scans a large table so causes issues.
This query has only one value in the WHERE clause and returns 21 fields.
The value in the WHERE clause is not in any NC indexes.
Initial Execution:
Clustered Index Table Scan:
1878874 Reads 181902ms
I tested two scenarios.
1. Add a new NC index for the WHERE clause value. To many fields to cover so a bookmark look up will be inevitable.
The query uses the new index and peforms a SEEK.
7 Reads
4ms total time
151mb Index Size
2. Add an INCLUDE column to an existing NC Index containing the WHERE clause value (remove new NC index first)
The query now performs a SCAN but on the newly created INCLUDE data.
29647 Reads
3277ms total time
101mb additional size to NC index
So it looks like adding a new NC index is the better option.
As a rough guess would you say the small Index size increase and the overhead of maintaining all Index levels (rather than just the leaf for an INCLUDE field?) would mean adding a new NC index is the better of the two options?
I could live with either option but if their is little difference then it would make sense to add a new NC index.
As a side note: I ran the offending query through the DTA as work load file and as expected it came back with a new index as the recommendation but it also included all 20 fields in the INCLUDE list which I thought in this case was a little pointless.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply