August 3, 2011 at 12:30 pm
I am using SQL 2008 (64-bit) on Windows 2003 (64-bit), if that makes a difference.
Say I have two similar queries, each has a missing index.
Query1 says it needs Index1 that uses Col1, Col2 and includes Col4 and Col7.
Query2 says it needs Index2 that uses Col1, Col2 and includes Col3, Col4, Col6 and Col7.
If I create Index2, do I need to create Index1 as well? Or will Query1 use Index2 and be happy?
The table is wide (like 80+ columns) and deep (10+ million rows), so I don't want to create more indexes than I need.
Any suggestions?
Thanks!
Norman
August 3, 2011 at 12:32 pm
Both queries will be happy with index 2.
August 3, 2011 at 12:43 pm
Create the common factor index without the included columns initially. Both queries should use this index, unless the index already exists on the table.
The reason you want to include columns is just to reduce traffic between the index and the table. i.e. if the columns you want to return are included in the index, you dont need to do a lookup.
But an index that results in a lookup already will reduce i/o due to no tablescan, if it is well placed, and the combined index is unique enough that the optimiser uses the index, and your query returns few results, i.e. Where x = @x instead of Where x>=@x.
If this really does not serve your need then maybe you can consider including columns.
But index overhead then may become an issue.
Oh, Do this on your QA environment first, then look at the stats to see if there are seeks happening against that index.
August 3, 2011 at 12:50 pm
I was hoping that was the answer, thanks for the quick response!
Norman
August 3, 2011 at 12:52 pm
n.heyen (8/3/2011)
I was hoping that was the answer, thanks for the quick response!Norman
HTH, MadTester has brought in valid points. I live in an environement where index space is not an issue so I go with the "agressive" options. I have this luxury as we have 500 to 1 Read / write ratio along with awesome san.
August 3, 2011 at 2:08 pm
I guess it all goes back the appropriate answer to any SQL Server question is "It Depends". 😀
This is a data warehouse, so lots more reads than writes for me as well. Disk space isn't really the issue either other than generally not to waste it if possible. The awesome SAN is not installed here though...
Again, thanks to both of you for the suggestions.
Norman
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply