September 9, 2009 at 12:24 pm
I have ran Brent Ozar FindMissingIndexes, my first question is should I create 2 index off the results(please see attachment), seem like these 2 should cover all 10 results?
CREATE NONCLUSTERED INDEX ix_IndexName ON visits ( [Account_ID],[Date_Created] ) INCLUDE ([Seconds_Online],[Page_Count]);
CREATE NONCLUSTERED INDEX ix_IndexName ON visits ( [Account_ID], [profile_id] ) INCLUDE ([Seconds_Online], [Date_Created]);
my other question is the selectivity is low on the following 3 col, so will the indexes be used as index scans
select count(distinct account_id) from visits -- 18,668/2,460,725 = .07
select count(distinct date_created) from visits -- 2,190,932/2,460,725 = .89
select count(distinct profile_id) from visits --697/2,460,725 = .002
select count(*) from visits -- = 2,460,725
THANKS
September 9, 2009 at 1:47 pm
It's hard to say for sure, not knowing your queries, your structure or anything about your system.
Probably, those two indexes will work. They both have the same leading edge (the first column), but are somewhat different after that. Based on what you've shown, they don't seem to be duplicates and I don't see a good scenario for combining them. You need to take any missing index information with a grain of salt and test it carefully.
The selectivity is probably OK because you're creating compound indexes. Otherwise, I'd worry about the date_created column.
"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
September 9, 2009 at 2:10 pm
Thanks Grant!
I will make sure to use the missing index as a starting point...sounds like I need to track down the Statements that are causing the missing indexes to show up then test each change in DEV before going any further.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply