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

 

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

Thursday, August 16, 2012

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



 

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.

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