索引查询创建

1、选中需要优化的数据库，然后执行下面的语句

SELECT avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,
last_user_seek ,
last_user_scan ,
[statement] AS [Object] ,
'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + ''
+ CONVERT(VARCHAR(32), D.index_handle) + ''
+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
+ included_columns
+ ')', '') AS Create_Index_Syntax
FROM sys.dm_db_missing_index_groups AS G
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
ORDER BY PossibleImprovement DESC

将这个列里面的内容拷贝出来，然后执行
Create_Index_Syntax列内容

SqlServer 索引定期重新生成

SELECT
'ALTER INDEX ['+ind.name + '] ON [dbo].[' +OBJECT_NAME(ind.OBJECT_ID) + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90);',
OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 70
and isnull(ind.name,'') <> ''
and indexstats.index_type_desc = 'NONCLUSTERED INDEX'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

上面的sql，是查找数据库中，索引碎片率高于70% 的数据库索引，并生成重构的语句，可把查询结果第一列复制出来，在数据库中执行索引重构，对性能有所提升有帮助