Missing Index Details

While looking at the Dynamic Management Views for missing indexes on the  Microsoft site I came across the comment on the SQL 2012 version.  It contains, supposedly, the script used by Microsoft Customer Support to help identify missing indexes and their impact.  BTW, it does work on SQL Server 2008r2.


http://msdn.microsoft.com/en-us/library/ms345434.aspx



The comment is below, both for my future reference an in case it is deleted on the msdn site...

Example script to see missing indexes as used by Microsoft Customer Support

PRINT 'Missing Indexes: '

PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

PRINT 'be seen if the index was created.  This is a unitless number, and has meaning only relative '

PRINT 'the same number for other indexes.  The measure is a combination of the avg_total_user_cost, '

PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

PRINT ''

PRINT '-- Missing Indexes --'

SELECT

    CONVERT (varchar, getdate(), 126) AS runtime,

    mig.index_group_handle,

    mid.index_handle,

  CONVERT (decimal (28,1),

   migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)

    + '_' + CONVERT (varchar, mid.index_handle)

    + ' 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           AS mig

    INNER JOIN sys.dm_db_missing_index_group_stats      AS migs

        ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details          AS mid

        ON mig.index_handle = mid.index_handle

WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost

                                * migs.avg_user_impact

                                * (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

           

PRINT ''

GO

Get a record or rowcount for all tables in a database

Received a request for a list of our tables and how many records each table had in it. 

In a situation like this you would want to avoid using the following logic as it actually goes through and counts each individual row.

SELECT count(*) FROM TableA

 

Instead using the system tables you can get an accurate and extremely fast list:

select t.name as TableName

     , i.rows as Records

    from     sysobjects as t

        JOIN sysindexes as i

            on i.id = t.id

    where t.xtype = 'U' and i.indid in (0,1)

order by TableName

This query works for SQL Server 2000 and greater.

What is the current database name

It seems I've lost the ability to retain even the basic SQL information that I've learned over the years.  For the life of me I couldn't remember how to view what the current database name was. 

Fortunately Google has become my memory and my crutch. 

SELECT DB_NAME() AS DatabaseName