是否有可能添加一个维护作业来检查索引碎片化情况。如果大于50%,则自动重建这些索引?
索引大小可能从100MB到10GB不等。 SQL 2005。
谢谢。
是否有可能添加一个维护作业来检查索引碎片化情况。如果大于50%,则自动重建这些索引?
索引大小可能从100MB到10GB不等。 SQL 2005。
谢谢。
我使用这个脚本。请注意,我建议您了解一下我在这里使用的dmv,它们是SQL2005+中的一个隐藏宝石。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes
(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT
)
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
@RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
WHEN [Fragmentation%] > 30
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REBUILD;'
WHEN [Fragmentation%] > 10
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes
请注意,此脚本可能需要一段时间并阻止访问您的表格。除非您拥有企业版SQL,否则在重建索引时SQL可以锁定表格。这将阻塞使用该索引的该表格的所有查询,直到完成索引整理为止。因此,建议仅在维护窗口期间运行索引重建,而不是在操作时间内运行。如果您正在运行企业版,则可以使用ONLINE=ON选项在线进行碎片整理。这会使用更多的空间,但在整理操作期间不会阻止/锁定您的表格。
如果需要更多信息,请告知。
更新:
如果您在较小的数据库上运行此查询,您可能可以在调用sys.dm_db_index_physical_stats时使用“DETAILED”参数。这可能是对索引的更详细的检查。评论中的讨论也会指出,在更大的表格上,进行SAMPLED扫描可能值得一试,因为这将有助于减少执行索引扫描所需的时间。
如果您想避免创建任何临时表并解析字符串以创建SQL字符串列表,这里有一种有效的方法来完成它:
USE databasename
GO
DECLARE @Queryresult NVARCHAR(4000)
SET @Queryresult=''
SELECT
@Queryresult=@Queryresult + 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON '
+ QUOTENAME('dbo') + '.'
+ QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) + ' REBUILD;'
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ss
INNER JOIN sys.indexes i ON i.OBJECT_ID = ss.OBJECT_ID AND i.index_id = ss.index_id
INNER JOIN sys.objects o ON ss.object_id = o.object_id
WHERE ss.avg_fragmentation_in_percent > 50
AND ss.record_count > 0
AND o.is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation
AND ss.index_id > 0 --Excludes heap indexes
EXEC sp_executesql @Queryresult
您可以使用sys.dm_db_index_physical_stats获取有关索引碎片化的信息(请参阅avg_fragmentation_in_percent列)。然后,每当达到阈值时,您可以使用重建子句进行索引更改。
可以的。
您可以使用以下查询获取分段索引:
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
根据结果构建一个命令来重新创建它们。
我会将所有内容封装在存储过程中,并从SQL Server作业中调用它。
顺便说一下,50%的碎片化非常严重。我会选择更少的碎片化。