填充因子设置问题
设置填充因子和关注碎片的人应该不多，大家的处理方法都是类似每天重建或者重组
没有人真正考虑过碎片问题，什么原因造成，会有什么影响，就算知道可以通过设置填充因子来搞，很多人都无从下手
有的人不想全部索引都设置上指定的填充因子 只是想在内存中占用较大的浪费较多的设置上填充因子，
填充因子设置问题
。今天从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严重的你就给再提高点，这样一点一点来，
电脑资料
《填充因子设置问题》(https://www.unjs.com)。那么又有人问默认的填充因子在哪里看？好吧，运行sp_configure里面有
有人问怎么查看填充因子，那么可以查看 sys.indexes，当然也有个全局的填充因子（在sp_configure中，但是不建议设置）