I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and statistics for said individual cases with no concern for the impact on the DB overall. How do I remedy this situation?

I myself am not a DBA but am interested in doing this properly -

  • Should I drop all indexes and statistics and start over?

  • Is there a tool out there that can look at the entirety of SQL operations and recommend which indexes and stats to delete, update or create.

  • Any recommended standard procedures for this situation, I can't imagine I am unique in encountering this scenario

I'm on SQL Server 2012

Consider following the STOP pattern that's used in first aid training. Stop, think, observe and plan. That is, don't rush into action. Start by gathering some information, draft an action plan and only then start doing things. If there's an electricity accident, one doesn't touch the victim before cutting power. Likewise, one shouldn't start dropping indexes without knowing what use those would be.

Is the system working well enough? How can you tell? That is, what's the definition of "well enough?"

Do you have a set of business rules like "Report X must be ready within Y minutes" and "Daily ETL must be finished by 0600 as cube update starts at 0610." Maybe there's something like "Database backup must be finished at 0200, as disk-to-tape backup starts at 0300." Be aware there might be some ad-hoc rules like "CFO needs $stuff NOW" that are usually not documented at all.

If you don't have any business constraints, you need to discuss with your boss, other users and business about such constraints.

If you've got a set of rules, that's great news. Now, see if the system respects those limits at the moment.

After you know the rules for the system, maybe it would be a great idea to do some basic health check. Brent Ozar's sp_Blitz is widely used free tool. There's warnings if backups are not handled properly, lack of recent integrity checks, configuration issues and a lot more.

You can use the following query to find out which indexes are used and which are only maintained:

select object_name(s.object_id) as [object name], 
       i.name as index_name, 
       user_seeks, 
       user_scans, 
       user_lookups, 
       user_updates 
from   sys.dm_db_index_usage_stats s 
       join sys.indexes i 
           on i.object_id = s.object_id 
          and i.index_id = s.index_id 
where  objectproperty(s.object_id,'isusertable') = 1
       and s.database_id = db_id();

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.

Here sys.dm_db_index_usage_stats (Transact-SQL) you can find more explanation.

Remember that some indexes are there in your database to enforce uniqueness so they should not be touched at all, here is how you find them:

select indexname = i.name,
       schemaname = schema_name(schema_id),
       tablename = object_name(o.object_id),
       is_primary_key,
       is_unique
from   sys.indexes i
       join sys.objects o
         on i.object_id = o.object_id
where  (is_primary_key = 1
       or is_unique = 1)
       and objectproperty(o.object_id,'isusertable') = 1
order  by schemaname,
          tablename,
          indexname; 

Finally, your db can have duplicate indexes as there is no control over it, here you can find the code that finds them: Removing duplicate indexes by Kimberly Tripp

you can search for missing and unused indexes There are a couple of dynamic views in SQL that you can use!

Here you can find some scripts that can help you

Missing Indexes

Unused indexes

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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