Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated by SQL to determine what indexes could help performance. Then use another script based on stats to determine which indexes are over kill and can be removed.

We don't have a DBA my main goal is to try to manage based on stats and not black magic. We just want to do the best we can with our resources (time)

  • 3
    Maintenance script commonly used: ola.hallengren.com/… – user507 Jan 21 '13 at 23:02
  • One word of advice: don't do it! SQL Server's "missing index" DMV is not reliable and smart enough to produce output that should be automated. It's great output to start looking at - but you should NOT automate index creation like that! Someone with a brain and know-how needs to check these readings.... – marc_s Jan 22 '13 at 5:44

Deciding which indexes you should use and or remove is a very involved process and is not something that can be simply automated. There are plenty of ways to identify under used or missing indexes, but adding or removing indexes can have a wide variety of impacts and these impacts need to be reviewed.

In order to properly understand how indexes can affect query performance, I strongly recommend you read Grant Fritchey's SQL Server 2012 Query Performance Tuning book. This is a valuable reference book for many DBAs and, while it is written for SQL 2012, much of the information is applicable to other versions of SQL Server.

Listed below are some tools you can use to identify indexes you can either add or remove. Do not apply these recommendations without reviewing them first.

Microsoft provides the Database Engine Tuning Adviser to review your database and provide recommendations on actions that can improve your performance. There are many factors the Tuning Adviser can not evaluate, so make sure you consider the suggestions individually before applying them.

You can also review missing index information from two places within SQL Server. The first is within the query plans. If you view a query plan, the green text at the top will typically recommend an index that can help your query. This data is typically found in the second place, sys.dm_db_missing_index_details. SQL Server tracks index usage stats and compiles recommendations that can be reviewed for possible addition to improve your performance. The Tuning Adviser referenced above also makes use of this information.

Glenn Berry has a very helpful query as part of his DMV script that shows you indexes that aren't very helpful:

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]
  , i.name AS [Index Name]
  , i.index_id
  , user_updates AS [Total Writes]
  , user_seeks + user_scans + user_lookups AS [Total Reads]
  , user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
  ON s.[object_id] = i.[object_id]
  AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
  AND s.database_id = DB_ID()
  AND user_updates > (user_seeks + user_scans + user_lookups)
  AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

Please note!

Query tuning and index management is a very deep and involved topic. It's not something you can simply turn on with a switch and allow an automated process to handle because of all the factors involved. Many companies have DBAs dedicated solely to the task of managing this, using the tools listed above. If you have no DBAs at your place of employment, I recommend either taking the time to train yourself in this OR seek a professional who has experience with this to aid you.

  • 2
    There are also some paid tools to help with some of this work (Performance Advisor / Fragmentation Manager), that have some advantages over the free / community stuff IMHO. Disclaimer: I work for SQL Sentry. You're right though - a lot of this simply can't be automated. You can point out potential areas for improvement but writing something that knows all the answers is simply not feasible. DBAs still need to make educated decisions. – Aaron Bertrand Jan 22 '13 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.