填充因子设置问题

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

填充因子设置问题

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

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

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

今天从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