一、索引的利弊
优点： 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
View Code
过程 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
View Code
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
View Code
过程 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
View Code
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
View Code
过程 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;
View Code
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
View Code
过程 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;
View Code
五、简化作业调用
用一个过程，灵活调用以上的过程。
再用作业来调用这个过程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
View Code
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
