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 -- &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 公众号,将有更多精彩内容分享: