【SQLSERVER】索引的维护优化
一、索引的利弊
优点： 1.大大加快数据的检索速度；
2.创建唯一性索引，保证数据库表中每一行数据的唯一性；
3.加速表和表之间的连接；
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点： 1.索引需要占物理空间；
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度；
3.索引过多，生成执行计划的时候，也可能导致优化器需要更多的时间去选择一个合适的索引，消耗更多的CPU时间。
二、 索引的优化方法 ：
1 索引缺失-------增加索引----新增
2 索引过多-------合并索引----删除合并
3 索引碎片-------索引维护----重建、重组索引
TIP: 索引碎片会降低索引页的数据密度，在查询时造成额外的I/O。
4 更新统计信息
三、索引的优化思路
通过作业调用存储过程，定期、自动地完成索引的优化维护，并记录日志；
索引的整合，不合理索引的删除合并主要还是依赖人工判断、取舍，暂时没想到比较好的自动化处理的办法。
四、相关的存储过程源码
1、索引缺失，根据系统视图，自动创建可能带来最大性能提升的10条索引，并记录日志：
建表 DBA_MissingIndexCreateRecord
IF OBJECT_ID('DBA_MissingIndexCreateRecord') IS NOT NULL DROP TABLE DBA_MissingIndexCreateRecord CREATE TABLE [dbo].[DBA_MissingIndexCreateRecord]( [Datekey] [INT] NULL, [PossibleImprovement] [FLOAT] NULL, [Last_User_Seek] [DATETIME] NULL, [Last_User_Scan] [DATETIME] NULL, [DBName] [NVARCHAR](128) NULL, [TableName] [NVARCHAR](128) NULL, [IndexName] [NVARCHAR](4000) NULL, [CreateSql] [NVARCHAR](4000) NULL, [Status] [INT] NOT NULL ) GO ALTER TABLE [dbo].[DBA_MissingIndexCreateRecord] ADD DEFAULT ((0)) FOR [Status] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'Datekey' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'可能的性能提高' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'PossibleImprovement' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'由可能使用了组中建议索引的用户查询所导致的上次查找日期和时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'Last_User_Seek' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'由可能使用了组中建议索引的用户查询所导致的上次扫描日期和时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'Last_User_Scan' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'DBName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'TableName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'IndexName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'执行脚本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'CreateSql' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态 0未执行 1已执行' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord', @level2type=N'COLUMN',@level2name=N'Status' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'缺失索引创建记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_MissingIndexCreateRecord' GO
过程 DBAMissingIndexCreate
CREATE PROCEDURE [dbo].[DBAMissingIndexCreate] @Result INT = 0 OUTPUT ----返回结果 AS /* 创建者：TurboWay 申请人：TurboWay 所属模板：数据库维护 所属项目：数据库索引维护 过程说明：top10缺失索引创建并记录到 DBA_DBA_MissingIndexCreateRecord 创建时间：2017-06-27 使用索引： 返回字段信息： @Result 返回结果 0成功,-2失败 */ BEGIN SET NOCOUNT ON; DECLARE @undo INT = 1; DECLARE @CreateSql NVARCHAR(MAX); DECLARE @UUID BIGINT; SET @UUID = CONVERT(BIGINT,(((((((YEAR(GETDATE())-1990)*12+MONTH(GETDATE()))*31+DAY(GETDATE()))*24+ CONVERT(VARCHAR(2),DATEPART(HOUR,GETDATE())))*60+CONVERT(VARCHAR(2),DATEPART(MINUTE,GETDATE())))*60+ CONVERT(VARCHAR(2),DATEPART(SECOND,GETDATE())))))*1000000+ CAST(CEILING(RAND() * 999999) AS BIGINT) BEGIN TRY /*记录TOP10缺失索引*/ INSERT INTO DBA_MissingIndexCreateRecord(Datekey,PossibleImprovement,Last_User_Seek,Last_User_Scan,DBName,TableName,IndexName,CreateSql) SELECT a.Datekey, a.PossibleImprovement, a.last_user_seek, a.last_user_scan, a.DbName, a.TableName, CASE WHEN LEN(a.Index_Name) >= 128 THEN SUBSTRING(a.Index_Name,0,CHARINDEX( '_i',a.Index_Name))+'_IncloudeTooMany'+CAST((@UUID+ROW_NUMBER()OVER(ORDER BY PossibleImprovement DESC)) AS NVARCHAR(30)) ELSE a.Index_Name END AS NewIndexName, --索引名有长度限制 CASE WHEN LEN(a.Index_Name) >= 128 THEN REPLACE(CreateSql,a.Index_Name,SUBSTRING(a.Index_Name,0,CHARINDEX( '_i',a.Index_Name))+'_IncloudeTooMany'+CAST((@UUID+ROW_NUMBER()OVER(ORDER BY PossibleImprovement DESC)) AS NVARCHAR(30))) ELSE CreateSql END AS NewCreateSql FROM ( SELECT TOP 10 CONVERT(INT,CONVERT(NVARCHAR(8),GETDATE(),112)) AS Datekey, CAST(avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks )AS NUMERIC(18,2)) AS PossibleImprovement , last_user_seek , last_user_scan , DB_NAME() AS DbName , OBJECT_NAME(D.object_id) AS TableName , REPLACE(REPLACE(ISNULL('x' + REPLACE(equality_columns, ', ', '_x'), '') + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN '_' ELSE '' END + ISNULL('x' + REPLACE(inequality_columns, ', ', '_x'), '') + CASE WHEN included_columns IS NOT NULL AND included_columns IS NOT NULL THEN '_' ELSE '' END + ISNULL('i' + REPLACE(included_columns, ', ', '_i'), ''), '[', ''), ']', '') AS Index_Name , 'CREATE INDEX [' + REPLACE(REPLACE(ISNULL('x' + REPLACE(equality_columns, ', ', '_x'), '') + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN '_' ELSE '' END + ISNULL('x' + REPLACE(inequality_columns, ', ', '_x'), '') + CASE WHEN included_columns IS NOT NULL AND included_columns IS NOT NULL THEN '_' ELSE '' END + ISNULL('i' + REPLACE(included_columns, ', ', '_i'), ''), '[', ''), ']', '') + ']' + ' 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 CreateSql 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 WHERE D.database_id = DB_ID() ORDER BY PossibleImprovement DESC ) a /*创建索引，执行脚本*/ WHILE @undo <> 0 BEGIN SET @CreateSql = ( SELECT TOP 1 CreateSql FROM dbo.DBA_MissingIndexCreateRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ); EXEC sp_executesql @CreateSql; UPDATE dbo.DBA_MissingIndexCreateRecord SET Status = 1 WHERE Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) AND Status = 0 AND CreateSql = @CreateSql; SET @undo = ( SELECT COUNT(1) FROM dbo.DBA_MissingIndexCreateRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ) END END TRY BEGIN CATCH SET @Result = -2; PRINT '执行失败的语句是：''' + @CreateSql + ''' 错误信息: ' + ERROR_MESSAGE(); END CATCH SELECT @Result AS Result; SET NOCOUNT OFF END
2、索引过多，将所有的索引及使用情况记录到表，再人为分析判断，删除合并：
建表 DBA_IndexMergeRecord
IF OBJECT_ID('DBA_IndexMergeRecord') IS NOT NULL DROP TABLE DBA_IndexMergeRecord CREATE TABLE [dbo].[DBA_IndexMergeRecord]( [DbName] [NVARCHAR](255) NULL, [TableName] [NVARCHAR](255) NULL, [IndexName] [NVARCHAR](255) NULL, [IndexType] [NVARCHAR](60) NULL, [IsUnique] INT NULL, [Key_Cols] [NVARCHAR](MAX) NULL, [Included_Cols] [NVARCHAR](MAX) NULL, [StartDate] [DATETIME] NULL, [IsDrop] INT DEFAULT 0 , [IsAuto] INT DEFAULT 0 , [DropDate] AS (CASE WHEN IsDrop = 1 THEN GETDATE() ELSE '1900-01-01 00:00:00.000' END ), [DropSql] [NVARCHAR](MAX) NULL, [USER_SEEKS] BIGINT NULL , [USER_SCANS] BIGINT NULL , [USER_LOOKUPS] BIGINT NULL , [USER_UPDATES] BIGINT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'DbName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'TableName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'IndexName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'IndexType' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否唯一索引' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'IsUnique' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'Key_Cols' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引包含列' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'Included_Cols' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否已删除 0正常 1已整合删除 2其它途径删除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'IsDrop' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否自动创建 0正常 1自动创建' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'IsAuto' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'DropDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除脚本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'DropSql' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引查找的次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'USER_SEEKS' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引扫描的次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'USER_SCANS' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'书签查找的次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'USER_LOOKUPS' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'增删改操作的次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord', @level2type=N'COLUMN',@level2name=N'USER_UPDATES' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引整合记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexMergeRecord' GO
过程 DBAIndexMerge
ALTER PROCEDURE [dbo].[DBAIndexMerge] @Result INT = 0 OUTPUT ----返回结果 AS /* 创建者：TurboWay 申请人：TurboWay 所属模板：数据库维护 所属项目：数据库索引维护 过程说明：将所有新增的非聚集索引记录到 DBA_IndexMergeRecord 创建时间：2017-06-27 使用索引： 返回字段信息： @Result 返回结果 0成功,-2失败 */ BEGIN SET NOCOUNT ON; BEGIN TRY CREATE TABLE #temp (tbname NVARCHAR(255) NULL , idname NVARCHAR(255) NULL , USER_SEEKS BIGINT , USER_SCANS BIGINT, USER_LOOKUPS BIGINT, USER_UPDATES BIGINT ) --索引使用情况 INSERT INTO #temp(tbname,idname,USER_SEEKS,USER_SCANS,USER_LOOKUPS,USER_UPDATES) 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 S.[database_id] = DB_ID() --更新索引的删除状态 UPDATE DBA_IndexMergeRecord SET IsDrop = 2 --其它途径删除 WHERE IsDrop = 0 AND NOT EXISTS( SELECT 1 FROM sys.indexes a WHERE a.name= IndexName AND a.object_id = OBJECT_ID(TableName)) --更新索引的重建状态 UPDATE DBA_IndexMergeRecord SET IsDrop = 0, IsRebuild = 1 --重建 WHERE IsDrop = 1 AND EXISTS( SELECT 1 FROM sys.indexes a WHERE a.name= IndexName AND a.object_id = OBJECT_ID(TableName)) --更新索引使用情况 UPDATE DBA_IndexMergeRecord SET USER_SEEKS = b.USER_SEEKS, USER_SCANS = b.USER_SCANS, USER_LOOKUPS = b.USER_LOOKUPS, USER_UPDATES = b.USER_UPDATES FROM DBA_IndexMergeRecord a, #temp b WHERE a.TableName = b.tbname AND a.IndexName = b.idname --记录新增索引 INSERT INTO DBA_IndexMergeRecord([DbName],[TableName] ,[IndexName] ,[IndexType] ,[IsUnique],[Key_Cols] ,[Included_Cols] ,[StartDate] ,[DropSql], [USER_SEEKS] ,[USER_SCANS] ,[USER_LOOKUPS] ,[USER_UPDATES]) SELECT DB_NAME(), o.name AS TableName, i.name AS IndexName, i.type_desc AS IndexType, i.is_unique AS IsUnique, SUBSTRING(ikey.cols, 3, LEN(ikey.cols)) AS Key_Cols , SUBSTRING(inc.cols, 3, LEN(inc.cols)) AS Included_Cols , STATS_DATE(o.object_id, i.index_id) AS StartDate, 'drop index ['+i.name+'] on ['+ o.name +']' + ' update DBA_IndexMergeRecord set IsDrop = 1 where TableName = '''+o.name+ ''' And IndexName = '''+ i.name +'''' AS DropSql, USER_SEEKS , USER_SCANS , USER_LOOKUPS , USER_UPDATES FROM sys.objects o JOIN sys.indexes i ON i.object_id = o.object_id CROSS APPLY ( SELECT ', ' + c.name + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('') ) AS ikey ( cols ) OUTER APPLY ( SELECT ', ' + c.name FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH('') ) AS inc ( cols ) LEFT JOIN (SELECT * FROM SYS.DM_DB_INDEX_USAGE_STATS WHERE [database_id] = DB_ID()) t ON t.index_id = i.index_id AND t.object_id = i.object_id WHERE o.type = 'u' AND i.type IN (1,2) AND NOT EXISTS(SELECT 1 FROM DBA_IndexMergeRecord rd WHERE o.name = rd.TableName AND i.name = rd.IndexName) ORDER BY o.name , i.index_id --更新索引来源 UPDATE DBA_IndexMergeRecord SET IsAuto = 1 --自动创建 FROM DBA_IndexMergeRecord a ,DBA_MissingIndexCreateRecord b WHERE IsAuto = 0 AND a.TableName = b.TableName AND a.IndexName= b.IndexName END TRY BEGIN CATCH SET @Result = -2; END CATCH SELECT @Result AS Result; SET NOCOUNT OFF END GO
3、索引碎片，通过索引重组或重建，减少或消除索引，并记录日志：
建表 DBA_IndexDefragRecord
CREATE TABLE [dbo].[DBA_IndexDefragRecord]( [Datekey] [INT] NULL, [DBName] [NVARCHAR](255) NULL, [SchemaName] [NVARCHAR](255) NULL, [TableName] [NVARCHAR](255) NULL, [IndexName] [NVARCHAR](255) NULL, [AvgFragmentB] [DECIMAL](5, 2) NULL, [AvgFragmentE] [DECIMAL](5, 2) NULL, [DefragSql] [NVARCHAR](MAX) NULL, [Status] [INT] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[DBA_IndexDefragRecord] ADD DEFAULT ((0)) FOR [Status] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'Datekey' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'DBName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'架构' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'SchemaName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'TableName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'IndexName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'维护前的碎片程度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'AvgFragmentB' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'维护后的碎片程度' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'AvgFragmentE' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'执行脚本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'DefragSql' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态 0未执行 1已执行' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord', @level2type=N'COLUMN',@level2name=N'Status' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'索引维护记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_IndexDefragRecord' GO
过程 DBA_IndexDefrag
CREATE PROCEDURE [dbo].[DBAIndexDefrag] @Result INT = 0 OUTPUT ----返回结果 AS /* 创建者：TurboWay 申请人：TurboWay 所属模板：数据库维护 所属项目：数据库索引维护 过程说明：整理索引碎片并记录到 DBA_IndexDefragRecord 创建时间：2017-06-27 使用索引： 返回字段信息： @Result 返回结果 0成功,-2失败 */ BEGIN SET NOCOUNT ON; DECLARE @undo INT = 1; DECLARE @DefragSql NVARCHAR(MAX); IF OBJECT_ID('tempdb..#Frag') IS NOT NULL DROP TABLE #Frag; CREATE TABLE #Frag ( Datekey INT , DBName NVARCHAR(255) , SchemaName NVARCHAR(255) , TableName NVARCHAR(255) , IndexName NVARCHAR(255) , AvgFragmentE DECIMAL(5, 2) ); BEGIN TRY /*记录索引碎片情况和维护脚本，索引碎片10-30，重组索引；索引碎片30以上，重建索引*/ IF NOT EXISTS ( SELECT 1 FROM DBA_IndexDefragRecord WHERE Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ) BEGIN INSERT INTO DBA_IndexDefragRecord ( Datekey , DBName , SchemaName , TableName , IndexName , AvgFragmentB , DefragSql ) SELECT CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) Datekey , DB_NAME() AS DBName , sysSc.name AS SchemaName , sysTa.name AS TableName , sysIn.name AS IndexName , func.avg_fragmentation_in_percent AvgFragmentB , CASE WHEN func.avg_fragmentation_in_percent BETWEEN 10.0 AND 30.0 THEN N'ALTER INDEX [' + sysIn.name + '] ON [' + DB_NAME() + '].[' + sysSc.name + '].[' + sysTa.name + '] REORGANIZE' WHEN func.avg_fragmentation_in_percent > 30.0 THEN N'ALTER INDEX [' + sysIn.name + '] ON [' + DB_NAME() + '].[' + sysSc.name + '].[' + sysTa.name + '] REBUILD' END AS DefraSql FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS func INNER JOIN sys.indexes AS sysIn ON func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id LEFT JOIN sys.all_objects AS sysTa ON func.object_id = sysTa.object_id LEFT JOIN sys.schemas AS sysSc ON sysTa.schema_id = sysSc.schema_id WHERE sysIn.index_id > 0 AND func.page_count > 8 --只维护数据页有8页以上的索引 AND func.avg_fragmentation_in_percent > 10 --只维护碎片达到10%以上的索引 AND sysTa.type IN ( 'u', 'v' ) --维护表索引、视图索引 ORDER BY func.avg_fragmentation_in_percent; END; /*整理索引碎片，执行脚本*/ WHILE @undo <> 0 BEGIN SET @DefragSql = ( SELECT TOP 1 DefragSql FROM dbo.DBA_IndexDefragRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ); EXEC sp_executesql @DefragSql; UPDATE dbo.DBA_IndexDefragRecord SET Status = 1 WHERE Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) AND Status = 0 AND DefragSql = @DefragSql; SET @undo = ( SELECT COUNT(1) FROM dbo.DBA_IndexDefragRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ); END; /*更新维护后的索引碎片程度*/ INSERT INTO #Frag ( Datekey , DBName , SchemaName , TableName , IndexName , AvgFragmentE ) SELECT CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) Datekey , DB_NAME() AS DBName , sysSc.name AS SchemaName , sysTa.name AS TableName , sysIn.name AS IndexName , func.avg_fragmentation_in_percent AvgFragmentE FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS func INNER JOIN sys.indexes AS sysIn ON func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id LEFT JOIN sys.all_objects AS sysTa ON func.object_id = sysTa.object_id LEFT JOIN sys.schemas AS sysSc ON sysTa.schema_id = sysSc.schema_id WHERE sysIn.index_id > 0 AND sysTa.type IN ( 'u', 'v' ); --维护表索引、视图索引 UPDATE dbo.DBA_IndexDefragRecord SET AvgFragmentE = b.AvgFragmentE FROM dbo.DBA_IndexDefragRecord a , #Frag b WHERE a.Datekey = b.Datekey AND a.DBName = b.DBName AND a.SchemaName = b.SchemaName AND a.TableName = b.TableName AND a.IndexName = b.IndexName; END TRY BEGIN CATCH SET @Result = -2; PRINT '执行失败的语句是：''' + @DefragSql + ''' 错误信息: ' + ERROR_MESSAGE(); END CATCH; SELECT @Result AS Result; DROP TABLE #Frag; SET NOCOUNT OFF; END;
4、更新统计信息，并记录日志(这个日志并没什么用，但统计信息是有意义的，有利于优化器选择合理的索引、生成最优的执行计划)
建表 DBA_StatisticsUpdateRecord
CREATE TABLE [dbo].[DBA_StatisticsUpdateRecord]( [Datekey] [INT] NULL, [DBName] [NVARCHAR](255) NULL, [SchemaName] [NVARCHAR](255) NULL, [TableName] [NVARCHAR](255) NULL, [StatisticsName] [NVARCHAR](255) NULL, [LastStatUpdateDate] [DATETIME] NULL, [RowModCTR] [INT] NULL, [TotalRowsInTable] [INT] NULL, [UpdateSql] [NVARCHAR](MAX) NULL, [Status] [INT] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[DBA_StatisticsUpdateRecord] ADD DEFAULT ((0)) FOR [Status] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'Datekey' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'DBName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'架构' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'SchemaName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'TableName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'统计信息名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'StatisticsName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后更新的时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'LastStatUpdateDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抽样行数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'RowModCTR' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实际行数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'TotalRowsInTable' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'执行脚本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'UpdateSql' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态 0未执行 1已执行' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord', @level2type=N'COLUMN',@level2name=N'Status' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'统计信息更新记录表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_StatisticsUpdateRecord' GO
过程 DBAStatisticsUpdate
CREATE PROCEDURE [dbo].[DBAStatisticsUpdate] @Result INT = 0 OUTPUT ----返回结果 AS /* 创建者：TurboWay 申请人：TurboWay 所属模板：数据库维护 所属项目：数据库统计信息维护 过程说明：更新过时的统计信息并记录到 DBA_StatisticsUpdateRecord 创建时间：2017-06-27 使用索引： 返回字段信息： @Result 返回结果 0成功,-2失败 */ BEGIN SET NOCOUNT ON; DECLARE @undo INT = 1; DECLARE @UpdateSql NVARCHAR(MAX); BEGIN TRY /*记录过时的统计信息*/ INSERT INTO DBA_StatisticsUpdateRecord ( [Datekey] , [DBName] , [SchemaName] , [TableName] , [StatisticsName] , [LastStatUpdateDate] , [RowModCTR] , [TotalRowsInTable] , [UpdateSql] ) SELECT DISTINCT CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) Datekey , DB_NAME() DBName , SCHEMA_NAME(SO.schema_id) SchemaName , OBJECT_NAME(SI.object_id) AS TableName , SI.name AS StatisticsName , STATS_DATE(SI.object_id, SI.index_id) AS LastStatUpdateDate , SSI.rowmodctr AS RowModCTR , SP.rows AS TotalRowsInTable , 'UPDATE STATISTICS [' + DB_NAME() + '].[' + SCHEMA_NAME(SO.schema_id) + '].[' + OBJECT_NAME(SI.object_id) + ']' + SPACE(2) +'[' +SI.name+']' AS UpdateSql FROM sys.indexes AS SI ( NOLOCK ) INNER JOIN sys.objects AS SO ( NOLOCK ) ON SI.object_id = SO.object_id INNER JOIN sys.sysindexes SSI ( NOLOCK ) ON SI.object_id = SSI.id AND SI.index_id = SSI.indid INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_id WHERE SSI.rowmodctr > 0 AND STATS_DATE(SI.object_id, SI.index_id) IS NOT NULL AND SO.type = 'U' ORDER BY RowModCTR DESC; /*更新过时的统计信息，执行脚本*/ WHILE @undo <> 0 BEGIN SET @UpdateSql = ( SELECT TOP 1 UpdateSql FROM dbo.DBA_StatisticsUpdateRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ); EXEC sp_executesql @UpdateSql; UPDATE dbo.DBA_StatisticsUpdateRecord SET Status = 1 WHERE Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) AND Status = 0 AND UpdateSql = @UpdateSql; SET @undo = ( SELECT COUNT(1) FROM dbo.DBA_StatisticsUpdateRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112)) ); END; END TRY BEGIN CATCH SET @Result = -2; PRINT '执行失败的语句是：' + @UpdateSql; END CATCH; SELECT @Result AS Result; SET NOCOUNT OFF; END;
五、简化作业调用
用一个过程，灵活调用以上的过程。
再用作业来调用这个过程DBAMaintenance，定期执行，达到自动维护的效果。
过程DBAMaintenance
ALTER PROCEDURE [dbo].[DBAMaintenance] @Returnvalue INT = 0 OUTPUT ----返回结果 AS /* 创建者：TurboWay 申请人：TurboWay 所属模板：数据库维护 所属项目：数据库维护 过程说明：执行数据库维护的过程,供作业直接调用 创建时间：2017-06-27 使用索引： 返回字段信息： Returnvalue 返回结果 0 没有执行，成功 大于0 表示执行次数 -2 执行失败，超出执行次数 */ BEGIN /*整理索引碎片*/ DECLARE @IsSucess INT =0 DECLARE @Count INT =0 --执行计数 EXEC DBAIndexDefrag @IsSucess OUTPUT IF @IsSucess = -2 --由于重建索引可能被高并发的查询,阻塞牺牲,导致失败,所以需要等待重复执行 BEGIN WHILE EXISTS(SELECT 1 FROM dbo.DBA_IndexDefragRecord WHERE Status = 0 AND Datekey = CONVERT(INT, CONVERT(NVARCHAR(8), GETDATE(), 112))) --存在未执行的脚本 AND @Count < 30 --执行过程的次数不得高于30，防止出现死循环 BEGIN WAITFOR DELAY '00:00:10' --延迟10秒执行,等待引起阻塞的查询完成 BEGIN EXEC DBAIndexDefrag --再次整理碎片 SET @Count = @Count + 1 END END END /*更新统计信息*/ EXEC DBAStatisticsUpdate /*创建缺失索引top10*/ EXEC DBAMissingIndexCreate /*记录索引整体情况*/ EXEC DBAIndexMerge /*返回执行情况*/ SELECT @Returnvalue = CASE WHEN @Count = 0 THEN 0 WHEN @Count > 0 AND @Count < 30 THEN @Count ELSE -2 END SELECT @Returnvalue AS Returnvalue END GO
