SQL Server 索引优化查询

1.1、查找缺失索引

SELECT
A.USER_SEEKS 查找次数,
A.USER_SCANS 扫描次数,
ROUND( A.AVG_TOTAL_USER_COST, 2 ) 减少的用户查询的平均成本,
A.AVG_USER_IMPACT 可能获得的平均百分比收益,
ROUND( ( A.USER_SEEKS+ A.USER_SCANS ) * A.AVG_TOTAL_USER_COST* A.AVG_USER_IMPACT/ 100, 2 ) 可能的改进优势,
A.LAST_USER_SEEK 最近查找时间,
A.LAST_USER_SCAN 最近扫描时间,
C.[STATEMENT] 表名,
'CREATE INDEX [IDX_' + CONVERT ( VARCHAR, A.GROUP_HANDLE ) + '_' + CONVERT ( VARCHAR, C.INDEX_HANDLE ) + '_' + REPLACE( REPLACE( REPLACE( C.[STATEMENT], ']', '' ), '[', '' ), '.', '' ) + ']' + ' ON ' + C.[STATEMENT] + ' (' + ISNULL( C.EQUALITY_COLUMNS, '' ) +
CASE

WHEN NOT C.EQUALITY_COLUMNS IS NULL
AND NOT C.INEQUALITY_COLUMNS IS NULL THEN
',' ELSE ''
END + ISNULL( C.INEQUALITY_COLUMNS, '' ) + ')' + ISNULL( ' INCLUDE (' + C.INCLUDED_COLUMNS+ ')', '' ) '创建语句'
FROM
sys.dm_db_missing_index_group_stats A
INNER JOIN sys.dm_db_missing_index_groups B ON A.GROUP_HANDLE= B.INDEX_GROUP_HANDLE
INNER JOIN sys.dm_db_missing_index_details C ON B.INDEX_HANDLE= C.INDEX_HANDLE
WHERE
C.DATABASE_ID= DB_ID( ) --默认当前数据库，若指定数据库则使用DB_ID(['DB_NAME'])

ORDER BY
ROUND( A.USER_SEEKS* A.AVG_TOTAL_USER_COST* A.AVG_USER_IMPACT/ 100, 2 ) DESC

1.2、查找未使用索引

SELECT
C.NAME 表名,
B.INDEX_ID 索引 ID,
B.NAME 索引名,
A.USER_SEEKS 搜索次数,
A.USER_SCANS 扫描次数,
A.USER_LOOKUPS 查找次数,
A.USER_UPDATES 更新次数,
E.TABLEROWS 表行数,
'DROP INDEX ' + QUOTENAME( B.NAME ) + ' ON ' + QUOTENAME( D.NAME ) + '.' + QUOTENAME( OBJECT_NAME( A.OBJECT_ID ) ) '删除语句'
FROM
sys.dm_db_index_usage_stats A
INNER JOIN sys.indexes B ON A.INDEX_ID= B.INDEX_ID
AND A.OBJECT_ID = B.OBJECT_ID
INNER JOIN sys.objects C ON A.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.schemas D ON C.schema_id = D.schema_id
INNER JOIN ( SELECT INDEX_ID, OBJECT_ID, SUM ( ROWS ) TABLEROWS FROM sys.partitions GROUP BY INDEX_ID, OBJECT_ID ) E ON A.INDEX_ID= E.INDEX_ID
AND A.OBJECT_ID = E.OBJECT_ID
WHERE
OBJECTPROPERTY( A.OBJECT_ID, 'IsUserTable' ) = 1
AND A.DATABASE_ID= DB_ID( )
AND B.TYPE_DESC= 'NONCLUSTERED'
AND B.IS_PRIMARY_KEY= 0
AND B.IS_UNIQUE_CONSTRAINT= 0 --AND C.NAME='INVMB' --根据实际修改表名

ORDER BY
( A.USER_SEEKS+ A.USER_SCANS+ A.USER_LOOKUPS ) ASC

当更新次数很大而搜索次数及扫描次数很小或为0时，说明该索引一直在更新但基本不被使用，因而也未对查询提供多少帮助，所以可以考虑删除。

1.3、查看索引使用情况

SELECT
OBJECT_NAME( A.[OBJECT_ID] ) 表名,
B.INDEX_ID 索引 ID,
B.[NAME] 索引名称,
B.[TYPE_DESC] 索引类型,
A.USER_SEEKS+ A.USER_SCANS+ A.USER_LOOKUPS 读,
A.USER_UPDATES 写,
B.FILL_FACTOR 填充因子
FROM
sys.dm_db_index_usage_stats A
INNER JOIN sys.indexes B ON A.[OBJECT_ID] = B.[OBJECT_ID]
AND A.INDEX_ID= B.INDEX_ID
WHERE
OBJECTPROPERTY( A.[OBJECT_ID], 'ISUSERTABLE' ) = 1
AND A.DATABASE_ID= DB_ID( ) --默认当前数据库，若指定数据库则使用DB_ID(['DB_NAME'])

ORDER BY
OBJECT_NAME( A.[OBJECT_ID] ),
A.USER_UPDATES DESC,
A.USER_SEEKS+ A.USER_SCANS+ A.USER_LOOKUPS DESC