Monday, January 28, 2013

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.

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 '-- Missing Indexes --'

    CONVERT (varchar, getdate(), 126) AS runtime,
  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,
    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

Wednesday, January 23, 2013

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 as TableName
     , i.rows as Records
    from     sysobjects as t
        JOIN sysindexes as i
            on =
    where t.xtype = 'U' and i.indid in (0,1)
order by TableName

table row count select statement for SQL Server

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

select db_Name() as DatabaseName - SQL Server