Fill Factor settings

There should be not many people who set Fill factors and focus on fragments in fill factor settings. The solutions are similar to rebuilding or restructuring every day. No one has really considered the fragmentation problem and what causes it, what is the impact? Even if you know that you can set the fill factor, many people cannot start with it. Some people don't want to set all the indexes with the specified fill factor.

There should be not many people who set Fill factors and focus on fragments in fill factor settings. The solutions are similar to rebuilding or restructuring every day. No one has really considered the fragmentation problem and what causes it, what is the impact? Even if you know that you can set the fill factor, many people cannot start with it. Some people don't want to set all the indexes with the specified fill factor.

Fill Factor settings

There should be few people who set the fill factor and focus on the fragments. In the United States, the processing methods are similar to rebuilding or restructuring every day.

No one has really considered the fragmentation issue. For example, if the US server has any cause or impact, even if you know that you can set the fill factor, many people will not be able to proceed.

Some people do not want to set the specified fill factor for all indexes. They just want to use a large amount of fill factor in the memory.

Today, I saw an SQL statement on paul's blog, which is very helpful for setting the fill factor. So I will share it with you here.

EXEC sp_MSforeachdbN 'if exists (SELECT 1 FROM (select distinct DB_NAME ([database_id]) AS [name] FROM sys. dm_ OS _buffer_descriptors) AS 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 100 mborder by [FreeSpacePC] DESC; end ';

Result:

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

If you do not have this configuration, you can use the current default value as the starting point. If feespace is serious, you can raise it to another high point. In this way, you can reach 1.1 points.

Then someone asked where the default fill factor is? Okay, run sp_configure.

Someone asked how to check the fill factor, so you can check sys. indexes. Of course, there is also a global fill factor (in sp_configure, the Hong Kong virtual host, but it is not recommended to set)

Posted on