December 11, 2008 at 3:54 pm
So I found a great new tool, now I am just not sure what to do with it. On our production server I have ran a query against the view: sys.dm_db_missing_index_details
I found that I have 247 missing index on my server running this against the master database, I know that I could increase the performance of the server by correcting these but I am trying to judge the best way to go about it. After reading BOL it seems like you would go through one by one and create a new index for each one listed in the results (247).
So in the example on BOL they give:
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_PersonAddress_StateProvinceID')
DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_PersonAddress_StateProvinceID
ON Person.Address (StateProvinceID)
INCLUDE (City, PostalCode);
GO
My questions are:
1. Do alot of DBA's spend time creating and dropping index's, would this part of day to day maintenance?
2. Do you just create the name of the index from the statement and equality_columns?
3. Would it be a good idea to use T-SQL to cycle through each of these and create a non-clustered index? Is this even possible?
December 11, 2008 at 5:37 pm
Proper indexes are key to any production database and there is enough information to write a book on the subject. I'm sure there is already one out there already! But I'll try to condense some of my thoughts on the missing index dmv's and indexes in general. I'll start by directly answering your questions.
1. Do a lot of DBA's spend time creating and dropping index's, would this part of day to day maintenance?
Indexing should be part of creating any new structures. Before a new stored proc is put into production I always review the query plan to identify index/table scans that need to be taken care of with an index. Things always do change once a good deal of data is in a table so the missing index table helps to identify those indexes you may have missed when creating the tables and procs. So make sure indexes are a focus from the start but remember that you'll still have to do some maintenance in the future.
2. Do you just create the name of the index from the statement and equality_columns?
This is a tricky question. Many dba's create the index name based on the columns that comprise the index. This is great until you run into indexes that have either long column names or multiple columns. If that starts happening we just name the index IX_TABLENAME_1. So it's really whatever works for you.
3. Would it be a good idea to use T-SQL to cycle through each of these and create a non-clustered index? Is this even possible?
This is possible and there are scripts out there to do this. I don't think it is a good idea to blindly create indexes just because they are in the missing index tables. Keep in mind that indexes have overhead and unless you have Enterprise Edition and build the indexes online you may cause blocking when you create them. I'd review the indexes based on which will make the most impact. Make sure there is not a similar index already on the table that would just need an included column to make a seek. And be sure that the improvement in reads due to the index outweigh the overhead of maintaining the index.
Here's a little blog that goes over some good points on the dmv's.
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
Make sure you also have a plan for maintaining your indexes. They will become fragmented over time so set up a schedule to rebuild or reorganize your indexes.
December 11, 2008 at 8:19 pm
The biggest thing is to weigh up the benefits of including the index versus the overhead of maintaining those indexes during inserts, updates and deletes.
Don't implement blindly, get a baseline and try each one to check performance. Do this in a test system wherever possible.
December 12, 2008 at 12:50 am
SQL_Easy_btn? (12/11/2008)
I found that I have 247 missing index on my server running this against the master database,
Don't make any changes in the master database.
3. Would it be a good idea to use T-SQL to cycle through each of these and create a non-clustered index? Is this even possible?
Possible, yes. Good idea, probably not. The logic that goes into adding entries to the missing index details is less comprehensive than the logic that the Database Tuning Advisor uses. Missing indexes is quite likely to have very similar indexes in it, or indexes that are very similar to existing ones.
Use it as a starting point to suggest what indexes may be needed, don't take it as absolute truth though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply