sql - SQL Server 重建索引 - 脚本
问题描述
我正在使用来自@Namphibian 的脚本,但我有一些问题。
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
但我使用的是“详细”而不是“SAMPLED”,但仍有一些索引没有重建。我发现一些索引具有相同的超过 30% 的碎片值,但仍未重建或重组。该脚本在过去 4 天的每晚都运行。我的问题是我不能为此任务使用维护计划。
请问有什么想法吗?
解决方案
您需要考虑索引的页数才能知道是否进行重建
我会建议将您的 INSERT INTO SELECT 更改为此
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%],
page_count
FROM
sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'DETAILED') 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
AND s.avg_fragmentation_in_percent > 0
AND page_count > 1000
推荐阅读
- nlp - 如何测量单词共现频率
- python - 计算字符串中第 n 个单词的索引
- sql-server - 查询以返回所有数据库的数据库、模式、表、列
- c# - 使用 CheckBoxList 进行测验
- ruby-on-rails - Rails gemacts_as_list,将位置范围限定给用户
- django - csrftoken cookie 未通过安全测试 - 缺少标志
- python - 根据电子邮件的匹配值更新 Pandas 数据框,但替换用户 ID
- python-3.x - 在python中插入带格式的大括号
- email - 电子邮件中要包含哪种字体格式?
- docker - 我必须通过每个次要版本更新 gitlab 吗?