March 24, 2015 at 11:26 am
Hi All
I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
March 24, 2015 at 2:20 pm
It's really going to come down to testing to see if making modifications to the key values hurts performance, and if so, how much. Just having an index on the bit columns alone is unlikely to get picked for most queries by the query optimizer just because the statistics are going to be so uninteresting. But, you never know since the selectivity will be... well, not good, but better than nothing if you have multiple bit columns. Nah, it's unlikely to be very useful the more I think about it. However, testing that too won't hurt.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2015 at 2:15 pm
You'd want the flag column(s) to key the index, not column1. Start with whichever flag is more selective. So, either: (flag1, flag2) or (flag2, flag1).
But, as Grant noted, it's hard to imagine the selectivity on flag columns being high enough to get SQL to use a nonclus index.
In theory you could create two separate nonclus indexes, on (flag1, column1) and (flag2, column2) and SQL could seek each separately and join on the results, but it's extremely rare for that to actually happen in reality.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply