Как посмотреть использование индексов в MS SQL
http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

Вставка

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Использование

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Размеры таблиц

DECLARE @SQL VARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@SQL)

CREATE TABLE #foo
(
name VARCHAR(255),
rows INT ,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255)
)

INSERT into #foo
EXEC sp_MSForEachtable 'sp_spaceused''?'''

SELECT * FROM #foo ORDER BY rows DESC
DROP TABLE #foo

Частичное обновление статистики

exec sp_MSforeachtable N'
----- искусственно исключаем две таблицы по 150 гигабайт
----- потому что за ночные 6 часов статистика не успевает
----- РС. СтоимостьТоваров и РС.ВерсииОбъектом
IF OBJECT_ID(''?'') NOT IN (OBJECT_ID(''_InfoRg4037''),
                                                    OBJECT_ID(''_InfoRg4447''))
update statistics ? with fullscan'

Недостающие индексы
http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
http://infostart.ru/public/128175/

SELECT
      migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
      'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
      + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
      + ' 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 mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
    WHERE 
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (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