SQL Server索引管理——索引碎片管理

最新推荐文章于 2024-03-14 14:53:40 发布

三空道人

最新推荐文章于 2024-03-14 14:53:40 发布

阅读量589

点赞数

分类专栏： SQL Server SQL Server 索引优化 SQL Server 性能优化文章标签：索引碎片管理 sys.dm_db_index_physical_stats 索引重建索引重组 LOB

本文链接：https://blog.csdn.net/zhoujunah/article/details/89840108

版权

SQL Server 同时被 3 个专栏收录

63 篇文章 11 订阅

订阅专栏

SQL Server 性能优化

20 篇文章 7 订阅

订阅专栏

SQL Server 索引优化

19 篇文章 11 订阅

订阅专栏

SQL Server索引管理——索引碎片管理

您需要了解SQL Server基础知识才能将数据库性能保持在最高水平。这些知识也会帮助你准备好面对任何潜在的问题。在处理文件时，您可能会发现没有足够的空闲空间来存储文件中所需的数据。默认情况下，这种情形SQL Server 将锁住文件，然后进行扩展（被称为自增长）。所有自增长的事件都存储在SQL Server 日志中：

SELECT
       DatabaseName
       ,[FileName]
       , CONVERT(VARCHAR(20), EndTime - StartTime, 114)
       ,StartTime
       ,EndTime
       ,FileType=
              CASE  EventClass WHEN 92 THEN 'Data'
              WHEN 93 THEN 'Log'
              END
FROM(
SELECT pt=REVERSE(SUBSTRING(REVERSE([path]),CHARINDEX('\',REVERSE([path])),260))+N'log.trc'
FROM sys.traces
WHERE is_default=1) p
CROSS APPLY sys.fn_trace_gettable(pt,DEFAULT)
WHERE EventClass IN(92,93)
ORDER BY StartTime DESC;

频繁调用自增长文件，可能会明显降低生产率，也可能导致硬盘中的文件碎片。下面的设置（和推荐的不一样），可能会影响这种引用：

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
       DROP TABLE #temp;
GO
CREATE TABLE #temp (
       db SYSNAME DEFAULT DB_NAME(),
       flname SYSNAME,
       size_after_growth DECIMAL(18,2),
       SIZE DECIMAL(18,2),
       space_used DECIMAL(18,2),
       growth INT,
       is_percent_growth BIT,
       PRIMARY KEY CLUSTERED (db, flname)
);
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = STUFF((
       SELECT '
              USE [' + name + ']
              INSERT INTO #temp (flname, size_after_growth, space_used, size, growth, is_percent_growth)
              SELECT
              name
              , CASE WHEN is_percent_growth = 1 THEN size * (1 + (growth / 100.)) ELSE size + growth END * 8. / 1024
              , space_used = FILEPROPERTY(name, ''SpaceUsed'') * 8. / 1024
              , size = size * 8. / 1024
              , CASE WHEN is_percent_growth = 1 THEN growth ELSE growth * 8. / 1024 END
              , is_percent_growth
              FROM sys.database_files'
       FROM sys.databases
       WHERE [state] = 0
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
EXECUTE sys.sp_executesql @SQL;
SELECT
       db
       , flname
       , size_after_growth
       , SIZE
       , space_used
       , CAST(growth AS VARCHAR(10)) + CASE WHEN is_percent_growth = 1 THEN ' %' ELSE ' MB' END
FROM #temp
WHERE (is_percent_growth = 0 AND growth < 50)       --按固定增量增长，每次增长少于50MB
OR (is_percent_growth = 1 AND growth< 5)        --按百分比增长，每次增长比例低于5%
OR (SIZE - space_used < 20);              --剩余可用文件大小低于20MB

如果这个查询的返回一些文件在最后一列的值是1MB，那么想一下，如果我们需要插入100MB的数据的情形吧。每次SQL Server都会阻塞文件并将其增加1 MB，然后将数据粘贴到其中。我建议为日志和数据文件留有充分的空间。

有两个类型的文件碎片：

逻辑碎片（也称为外部碎片或区段碎片）：页面的逻辑顺序和其对应的物理顺序不一致。结果导致增加了SQL Server从硬盘中的物理（random）读，使得预读机制低效。这直接影响查询的执行时间，这是因为从硬盘的随机读取比序列读取性能要低的多。

内部碎片：索引中的数据页包含可用空间。这导致了在查询期间增加了逻辑读，因为索引使用了更多的数据页存储数据。

为管理索引碎片问题，SQL Server 提供了两个语句：ALTER INDEX REBUILD/ REORGANIZE。

REBUILD操作为索引创建了一个新的架构。REORGANIZE 操作更轻量级。它从索引的页级别开始运行，确定页的物理顺序，应用先前设定的填充因子，压缩页。

不要忽略较高的索引碎片。你可以通过如下脚本获取碎片信息：

--索引碎片大于指定值的表及索引信息
SELECT
SCHEMA_NAME(o.[schema_id]) AS [schema_name]
, o.name AS parent_name
, i.name
, s.avg_fragmentation_in_percent
, s.avg_page_space_used_in_percent
, i.type_desc
, s.page_count
, size = s.page_count * 8. / 1024
, p.partition_number
, p.[rows]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id
       AND s.partition_number = p.partition_number
JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
JOIN sys.objects o ON o.[object_id] = i.[object_id]
WHERE i.is_disabled = 0
       AND i.is_hypothetical = 0
       AND s.index_level = 0
       AND s.page_count> 0
       AND i.[type] > 0
       AND s.avg_fragmentation_in_percent > 15  --索引碎片大于指定值
       AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
       AND o.[type] IN ('U', 'V')
ORDER BY s. avg_fragmentation_in_percent DESC

如下脚本增加了索引中是否有lob数据的判断

DECLARE @db_id INT
SET @db_id = DB_ID()
SELECT
       SCHEMA_NAME(o.[schema_id]) AS [schema_name]
       , o.name AS parent_name
       , i.name
       , s.avg_fragmentation_in_percent
       , i.type_desc
       , o.[type] AS object_type
       , s.page_count
       , p.partition_number
       , p.[rows]
       , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy
       , ISNULL(lob.is_lob, 0) AS is_lob
       , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL) s
JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number
JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
LEFT JOIN (
       SELECT
              c.[object_id]
              , index_id = ISNULL(i.index_id, 1)
              , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END)
              , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END)
       FROM sys.columns c
       LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id> 0
       WHERE c.system_type_id IN (34, 35, 99)   --34:image,35:text,99:ntext
              OR c.max_length = -1
       GROUP BY c.[object_id], i.index_id
) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = i.[object_id]
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.[type] IN (1, 2)
       AND i.is_disabled = 0
       AND i.is_hypothetical = 0
       AND s.index_level = 0
       AND s.alloc_unit_type_desc = 'IN_ROW_DATA'
       AND o.[type] IN ('U', 'V')

根据碎片的级别，可以自动产生重建或重组索引脚本：

--生成索引重建或重组脚本
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
       SELECT '
              ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
              CASE WHEN s.avg_fragmentation_in_percent> 30
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
              END + ';'
       FROM (
              SELECT
                     s.[object_id]
                     , s.index_id
                     , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
              FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
              WHERE s.page_count > 128 -- &amp;gt; 1 MB
              AND s.index_id > 0 -- 排除 HEAP
              AND s.avg_fragmentation_in_percent > 5
              GROUP BY s.[object_id], s.index_id
       ) s
       JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
       JOIN sys.objects o ON o.[object_id] = s.[object_id]
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
--EXEC sys.sp_executesql @SQL

注意，在生产环境中，索引的重建或重组需要考虑表的大小，表使用的频繁程度，是否可以在线重建等，该脚本产生的脚本不可直接执行，对于超千万行的表最好在停机维护的时候操作。

仅仅IN_ROW_DATA才有碎片

IN_ROW_DATA：存储数据的页是固定宽度的列。变长数据（不超过8060字节），不适合IN_ROW_DATA页，存储在ROW_OVERFLOW_DATA页，该页链接到IN_ROW_DATA。超过8060字节的数据，如VARCHAR（max），NVARCHAR（max)、XML、TEXT、IMAGE存储在LOB_DATA页。

页中的空闲空间越多，IN_ROW_DATA中内部碎片越多。如果碎片级别很高，SQL Server需要读大量的页。这会增加逻辑读，降低性能。

现在再回到脚本。脚本的部分也可能不清晰：

SELECT
       c.[object_id]
       , index_id = ISNULL(i.index_id, 1)
       , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END)
       , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END)
FROM sys.columns c
LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id>0
WHERE c.system_type_id IN (34, 35, 99)
       OR c.max_length = -1
GROUP BY c.[object_id], i.index_id;

这个脚本返回哪个表使用LOB列。为什么这个这么重要？SQL Server 2005 不支持包含LOB列的索引在线重建。如果你指定ONLINE 属性，当索引重建的时候不可用。下面是一个展示为什么LOB检查很重要：

IF OBJECT_ID('dbo.test1','U') IS NOT NULL
       DROP TABLE dbo.test1;
GO
CREATE TABLE dbo.test1(ID INT PRIMARY KEY,[text] NVARCHAR(MAX));
GO
ALTER INDEX ALL ON dbo.test1 rebuild WITH(ONLINE=ON);

消息 2725，级别 16，状态 2，第 6 行

不能对索引 'PK__test1__3214EC271273C1CD' 执行联机操作，因为该索引包含数据类型为 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列 'text'。对于非聚集索引，该列可能是索引的包含列。对于聚集索引，它可能是该表的任何列。如果使用 DROP_EXISTING，则该列可能是新索引或旧索引的一部分。该操作必须离线执行。

SQL Server 2008及以前的版本，表中包含数据类型为 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列，都不能在线重建索引。SQL Server 2012 及以后版本就没有对所有类型进行限制（除了IMAGE、TEXT、NTEXT）

IF OBJECT_ID('dbo.test1','U') IS NOT NULL
       DROP TABLE dbo.test1;
GO
CREATE TABLE dbo.test1(ID INT PRIMARY KEY,[text] NVARCHAR(MAX));
GO
ALTER INDEX ALL ON dbo.test1 rebuild WITH(ONLINE=ON);
IF OBJECT_ID('dbo.test2', 'U') IS NOT NULL DROP TABLE dbo.test2;
GO
CREATE TABLE dbo.test2 (ID INT PRIMARY KEY, [Text] TEXT);
GO
ALTER INDEX ALL ON dbo.test2 REBUILD WITH(ONLINE=ON);
GO

消息 2725，级别 16，状态 2，第 396 行

不能对索引“PK__test2__3214EC270208A67D”执行联机操作，因为该索引包含数据类型为 text、ntext、image 或 FILESTREAM 的列“Text”。对于非聚集索引，该列可能是索引的包含列。对于聚集索引，该列可能是该表的任何列。如果使用 DROP_EXISTING，则该列可能是新索引或旧索引的一部分。该操作必须脱机执行。

如果喜欢，可以搜索关注 MSSQLServer 公众号，将有更多精彩内容分享：

三空道人

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL Server索引管理——索引碎片管理

SQL Server索引管理——索引碎片管理您需要了解SQL Server基础知识才能将数据库性能保持在最高水平。这些知识也会帮助你准备好面对任何潜在的问题。在处理文件时，您可能会发现没有足够的空闲空间来存储文件中所需的数据。默认情况下，这种情形SQLServer将锁住文件，然后进行扩展（被称为自增长）。所有自增长的事件都存储在SQLServer日志中：SELECT ...
复制链接

扫一扫