January 28, 2009 at 11:45 am
Hi Forum,
We've been live with a new system from a 3rd party vendor for 3 days now. I found during testing and debugging that their indexes aren't very well designed.
I'm using this code to get a list of missing indexes.
Selectb.name, c.name, a.equality_columns, a.inequality_columns,
a.included_columns
FROM sys.dm_db_missing_index_details a, sys.databases b, sys.tables c
where b.database_id = a.database_id and c.object_id = a.object_id
That returns 340 records on the main database. That seems like a lot to me, I get over 70 missing indexes reported on one table alone. How much can I trust that, can I blindly follow it and create all the indexes that are reported missing ?
Is it possible to make a database slower instead of faster by having too many indexes on a table ?
I also see that some reported missing indexes seem to be repeating field names like one row showing [Field1],[Field2] in the equality_columns and then the next row showing [Field1],[Field2],[Field3],[Field4]. Wouldn't it make sense to only have the index with the 4 columns ?
Another thing I see is 2 lines with the same equality_columns but different included_columns. Should I create 2 indexes or just 1 with the included_columns from both rows ?
January 28, 2009 at 11:57 am
I thought an example of what I'm saying could be useful so here's an example, I changed the field names to make it easier to read.
Database Table equality inequality Included
columns Columns Columns
-----------------------------------------------------------------
DatabaseName TableName [A] NULL
DatabaseName TableName [A] NULL [D]
DatabaseName TableName [A] [C]
DatabaseName TableName [A] NULL NULL
DatabaseName TableName [A] [E]
How should I interpret this, do I create 5 indexes as they're shown here or should I create only one on columns A and B, including columns C,D and E ?
January 28, 2009 at 12:02 pm
eric (1/28/2009)
I thought an example of what I'm saying could be useful so here's an example, I changed the field names to make it easier to read.
Database Table equality inequalityIncluded
columns Columns Columns
-----------------------------------------------------------------
DatabaseName TableName [A] NULL
DatabaseName TableName [A] NULL [D]
DatabaseName TableName [A] [C]
DatabaseName TableName [A] NULL NULL
DatabaseName TableName [A] [E]
How should I interpret this, do I create 5 indexes as they're shown here or should I create only one on columns A and B, including columns C,D and E ?
One index:
create index IDX_Whatever on TableName (
[A] asc,
asc
) include (
[D],[C],[E]
);
The syntax above may not be totally correct, but I hope it gives you the idea I'm try to get at.
Edit:
Why?
Obviously, there is a need for an index on [A]. Some of those queries will also benefit if is also in the index, and this won't affect those where it isn't needed. The included columns will be of benefit for some as well. Stepping back a moment, however, be careful with the included columns. I have seen "missing" index enteries on my system that want 40 clumns added as included columns. NO WAY, that would make the index hugh and impact inserts, updates, and deletes in a significant way IMHO.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply