填充因子设置问题



填充因子设置问题 设置填充因子和关注碎片的人应该不多，大家的处理方法都是类似每天重建或者重组 没有人真正考虑过碎片问题，什么原因造成，会有什么影响，就算知道可以通过设置填充因子来搞，很多人都无从下手 有的人不想全部索引都设置上指定的填充因子

填充因子设置问题

设置填充因子和关注碎片的人应该不多，美国空间，大家的处理方法都是类似每天重建或者重组

没有人真正考虑过碎片问题，美国服务器，什么原因造成，会有什么影响，就算知道可以通过设置填充因子来搞，很多人都无从下手

有的人不想全部索引都设置上指定的填充因子 只是想在内存中占用较大的浪费较多的设置上填充因子。

今天从paul的blog上看到一个sql，感觉对设置填充因子帮助很大所以发这里和大家分享

EXEC sp_MSforeachdb N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name] FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ) BEGIN USE [?] SELECT AS [Database], OBJECT_NAME (p.[object_id]) AS [Object], p.[index_id], i.[name] AS [Index], i.[type_desc] AS [Type], --au.[type_desc] AS [AUType], --DPCount AS [DirtyPageCount], --CPCount AS [CleanPageCount], --DPCount * 8 / 1024 AS [DirtyPageMB], --CPCount * 8 / 1024 AS [CleanPageMB], (DPCount + CPCount) * 8 / 1024 AS [TotalMB], --DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace], --CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace], ([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB], CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC] FROM (SELECT allocation_unit_id, SUM (CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS [DPCount], SUM (CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS [CPCount], SUM (CASE WHEN ([is_modified] = 1) THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace], SUM (CASE WHEN ([is_modified] = 1) THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace] FROM sys.dm_os_buffer_descriptors WHERE [database_id] = DB_ID () GROUP BY [allocation_unit_id]) AS buffers INNER JOIN sys.allocation_units AS au ON au.[allocation_unit_id] = buffers.[allocation_unit_id] INNER JOIN sys.partitions AS p ON au.[container_id] = p.[partition_id] INNER JOIN sys.indexes AS i ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id] WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than 100MB ORDER BY [FreeSpacePC] DESC; END';

那么结果：

Database Object index_id Index Type TotalMB FreeSpaceMB FreeSpacePC -------- ------ -------- ------------ ------------ ------- ----------- ----------- ProdDB TableG 1 TableG_IX_1 CLUSTERED 531 130 24.5 ProdDB TableI 1 TableI_IX_1 CLUSTERED 217 48 22.2 ProdDB TableG 2 TableG_IX_2 NONCLUSTERED 127 27 21.8 ProdDB TableC 1 TableC_IX_1 CLUSTERED 224 47 21.4 ProdDB TableD 3 TableD_IX_3 NONCLUSTERED 1932 393 20.4 ProdDB TableH 1 TableH_IX_1 CLUSTERED 162 33 20.4 ProdDB TableF 5 TableF_IX_5 NONCLUSTERED 3128 616 19.7 ProdDB TableG 9 TableG_IX_9 NONCLUSTERED 149 28 19.1 ProdDB TableO 10 TableO_IX_10 NONCLUSTERED 1003 190 19 ProdDB TableF 6 TableF_IX_6 NONCLUSTERED 3677 692 18.8

有了这个不就好设置了，以当前默认值为出发点，feespace严重的你就给再提高点，这样一点一点来。

那么又有人问默认的填充因子在哪里看？好吧，运行sp_configure里面有

有人问怎么查看填充因子，那么可以查看 sys.indexes，当然也有个全局的填充因子（在sp_configure中，香港虚拟主机，但是不建议设置）

posted on