Thursday, August 30, 2012

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



      'create index IX_' +

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

            ' on ' +

   + '.' + +

            ' (' +

            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, equality_columns --avg_user_impact desc

Thursday, August 16, 2012

SQL Server World User's Group

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

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


Tuesday, August 14, 2012

Indexes not used since the last reboot

This query gives you a list of indexes that have not been used since the last reboot.


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

      'drop index [' + + '].[' + '' + '].[' + + ']'
      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
      database_id = 8 and
      user_seeks + user_scans + user_lookups = 0 and not like 'PK_%'
order by