MSSQL之文件碎片
Tags： MSSQLSQL Server文件碎片
👉 本文共约1922个字，系统预计阅读时间或需8分钟。
本页目录 隐藏
在处理文件时，您可能会发现没有足够的空闲空间来存储文件中所需的数据。默认情况下，这种情形SQL Server 将锁住文件，然后进行扩展（被称为自增长）。所有自增长的事件都存储在SQL Server 日志中：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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; |
频繁调用自增长文件，可能会明显降低生产率，也可能导致硬盘中的文件碎片。下面的设置（和推荐的不一样），可能会影响这种引用：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | 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 操作更轻量级。它从索引的页级别开始运行，确定页的物理顺序，应用先前设定的填充因子，压缩页。
不要忽略较高的索引碎片。你可以通过如下脚本获取碎片信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | --索引碎片大于指定值的表及索引信息 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数据的判断
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | 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') |
根据碎片的级别，可以自动产生重建或重组索引脚本：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | --生成索引重建或重组脚本 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 -- > 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 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
