Dynamically create missing indexes frm DMVs

Here's the code. Note: DO NOT simply runn this and apply all the indexes; look for redundancy first, there's likely to be a lot. A couple of notes:

1. Make sure to change the database name in 2 places
2. Decide how many of these you want. I (somewhat arbitrarily) set an 80% impact threshold on what I'm bringing back; you may want to adjust this.

use YourDatabaseName

 

select

      'create index IX_' +

            replace(replace(replace (equality_columns, '[', ''),']',''),', ','_') +

            ' on ' +

            sch.name + '.' + obj.name +

            ' (' +

            equality_columns +

            case when inequality_columns is null

                                    then ''

                                    else ',' +  inequality_columns end +

            ')' +

            case when included_columns is not null then

                  ' include (' +

                  isnull(included_columns,'') +

                  ') ' else '' end +

            ' -- ' + convert (varchar, avg_user_impact) + '% anticipated impact'

      from sys.dm_db_missing_index_details mid join

                  sys.dm_db_missing_index_groups mig

                        on mid.index_handle = mig.index_handle          join

                  sys.dm_db_missing_index_group_stats migs

                        on migs.group_handle = mig.index_group_handle join

                        sys.objects obj on obj.object_id = mid.object_id join

                        sys.schemas sch on obj.schema_id = sch.schema_id

                       

                        where db_name(database_id) = 'YourDatabaseName' and

            avg_user_impact > 80

      order by obj.name, equality_columns --avg_user_impact desc

SQL Server World User's Group

I'm speaking again at their virtual conference... to sign up, go to:

 https://www.vconferenceonline.com/event/regeventp.aspx?id=661

If you do sign up, please use the tracking code to let them know you found out here: VCJEFFREY



 

Indexes not used since the last reboot

This query gives you a list of indexes that have not been used since the last reboot. Notes: 1)     I’m excluding primary key indexes. Amazing how often they’re listed (i.e. primary access is not via primary key). 2)      I am running this for a specific database (see the “database_id” column in the where clause). You can identify a database’s database id by running the command: a.       Select db_id(‘insert database name here’) b.      Or, if you want to go the other way,                                                                i.      Select db_name(‘insert database id here’) 3)      You get three columns, table name, index name, and the drop command to get rid of the index. It’s sorted by table name / index name a.       It might be erring on the side of constructive paranoia to generate a create script for each of the indexes you’re about to drop before dropping them, in case circumstances require a quick recreate. index drops of unused indexes select       object_name(i.object_id),       i.name,       'drop index [' + sch.name + '].[' + 'obj.name' + '].[' + i.name + ']' from       sys.dm_db_index_usage_stats ius join       sys.indexes i on             ius.index_id = i.index_id and             ius.object_id = i.object_id join             sys.objects obj on             ius.object_id = obj.object_id join             sys.schemas sch on             obj.schema_id = sch.schema_id where       database_id = 8 and       user_seeks + user_scans + user_lookups = 0 and       i.name not like 'PK_%' order by       object_name(i.object_id),       i.name