填充因子设置问题 设置填充因子和关注碎片的人应该不多，大家的处理方法都是类似每天重建或者重组 没有人真正考虑过碎片问题，什么原因造成，会有什么影响，就算知道可以通过设置填充因子来搞，很多人都无从下手 有的人不想全部索引都设置上指定的填充因子
填充因子设置问题
设置填充因子和关注碎片的人应该不多，美国空间，大家的处理方法都是类似每天重建或者重组
没有人真正考虑过碎片问题，美国服务器，什么原因造成，会有什么影响，就算知道可以通过设置填充因子来搞，很多人都无从下手
有的人不想全部索引都设置上指定的填充因子 只是想在内存中占用较大的浪费较多的设置上填充因子。
今天从paul的blog上看到一个sql，感觉对设置填充因子帮助很大所以发这里和大家分享
EXEC sp_MSforeachdbN‘IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]FROM sys.dm_os_buffer_descriptors) AS来4源gaodaimacom搞#代%码*网 names WHERE [name] = )BEGINUSE [?]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(SELECTallocation_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_descriptorsWHERE [database_id] = DB_ID ()GROUP BY [allocation_unit_id]) AS buffersINNER JOIN sys.allocation_units AS auON au.[allocation_unit_id] = buffers.[allocation_unit_id]INNER JOIN sys.partitions AS pON au.[container_id] = p.[partition_id]INNER JOIN sys.indexes AS iON 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 100MBORDER BY [FreeSpacePC] DESC;END‘;
那么结果：
Database Object index_id Index Type TotalMB FreeSpaceMB FreeSpacePC——– —— ——– ———— ———— ——- ———– ———–ProdDB TableG 1 TableG_IX_1 CLUSTERED 531 130 24.5ProdDB TableI 1 TableI_IX_1 CLUSTERED 217 48 22.2ProdDB TableG 2 TableG_IX_2 NONCLUSTERED 127 27 21.8ProdDB TableC 1 TableC_IX_1 CLUSTERED 224 47 21.4ProdDB TableD 3 TableD_IX_3 NONCLUSTERED 1932 393 20.4ProdDB TableH 1 TableH_IX_1 CLUSTERED 162 33 20.4ProdDB TableF 5 TableF_IX_5 NONCLUSTERED 3128 616 19.7ProdDB TableG 9 TableG_IX_9 NONCLUSTERED 149 28 19.1ProdDB TableO 10 TableO_IX_10 NONCLUSTERED 1003 190 19ProdDB TableF 6 TableF_IX_6 NONCLUSTERED 3677 692 18.8
有了这个不就好设置了，以当前默认值为出发点，feespace严重的你就给再提高点，这样一点一点来。
那么又有人问默认的填充因子在哪里看？好吧，运行sp_configure里面有
有人问怎么查看填充因子，那么可以查看 sys.indexes，当然也有个全局的填充因子（在sp_configure中，香港虚拟主机，但是不建议设置）
posted on
转载请注明原文链接：填充因子设置问题
