**Update at bottom**
OK, so we had a SQL 2005 database that we migrated from another company. The application that uses it as a backend was having some terrible performance issues. We had limited information on the previous configuration, so we bumped the Application up to a newer server since it seemed most of the load issues were with that part. Afterwards, there were still performance issues and timeouts. SQL was queuing up commands and taking too long to process them. So we got the senior DBA involved to help us see what kind of performance increases we could get on the SQL server. Unfortunately, I did not note all of the changes, but this was the biggest improvement.
We found out that the previous company also had performance issues. When looking at the tables, we noticed many did not have indexes. This little query was a lifesaver. It shows you which tables are without an index, how many reads/writes and if things are queuing.
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
‘CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']‘
+ ‘ ON ‘ + mid.statement
+ ‘ (’ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (mid.inequality_columns, ”)
+ ‘)’
+ ISNULL (’ INCLUDE (’ + mid.included_columns + ‘)’, ”) AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
The second column is a query string generated by the first query that will create indexes based on what is needed. A note though, that query only creates standard indexes. I recommend to change it to create clustered indexes. They are considerably faster. Here are the commands I used to create our indexes.
CREATE CLUSTERED INDEX cix_MASTER1_HISTORY on MASTER1_HISTORY(mrID)
CREATE CLUSTERED INDEX cix_MASTER1_DESCRIPTIONS on MASTER1_DESCRIPTIONS(mrID)
CREATE CLUSTERED INDEX cix_MASTER1_TIMETRACKING on MASTER1_TIMETRACKING(mrID)
CREATE CLUSTERED INDEX cix_MASTER2_HISTORY on MASTER2_HISTORY(mrID)
Hopefully this helps some of you out there
**Updated 3/1/2010
This query to find indexes is dynamic. it doesn’t actually find all tables missing indexes, it finds tables that are CURRENTLY being searched without indexes and ranks them based on the performance problems they are causing. So run this query when your SQL starts getting backed up.
