填充因子设置问题

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

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

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

今天从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中，但是不建议设置）