#MSSQL #Research Recommended Fill Factor

fill_factor.sql

-- http://lassesen.com/msdn/Optimizing_Fill_Factors_for_SQLServer.pdf
SELECT
  '[' + s.name + '].[' + t.name + ']' AS TableName,
  '[' + i.name + ']' AS IndexName,
  SUM(c.Max_length) AS [KeySize],
  CASE
    WHEN SUM(c.Max_length) <= 900 AND
      SUM(c.Max_length) > 848 THEN '97'
    WHEN SUM(c.Max_length) <= 848 AND
      SUM(c.Max_length) > 403 THEN '96'
    WHEN SUM(c.Max_length) <= 403 AND
      SUM(c.Max_length) > 222 THEN '95'
    WHEN SUM(c.Max_length) <= 222 AND
      SUM(c.Max_length) > 135 THEN '94'
    WHEN SUM(c.Max_length) <= 135 AND
      SUM(c.Max_length) > 89 THEN '93'
    WHEN SUM(c.Max_length) <= 89 AND
      SUM(c.Max_length) > 61 THEN '92'
    WHEN SUM(c.Max_length) <= 61 AND
      SUM(c.Max_length) > 44 THEN '91'
    WHEN SUM(c.Max_length) <= 44 AND
      SUM(c.Max_length) > 33 THEN '90'
    WHEN SUM(c.Max_length) <= 33 AND
      SUM(c.Max_length) > 25 THEN '89'
    WHEN SUM(c.Max_length) <= 25 AND
      SUM(c.Max_length) > 20 THEN '88'
    WHEN SUM(c.Max_length) <= 20 AND
      SUM(c.Max_length) > 16 THEN '87'
    WHEN SUM(c.Max_length) <= 16 AND
      SUM(c.Max_length) > 13 THEN '86'
    WHEN SUM(c.Max_length) <= 13 AND
      SUM(c.Max_length) > 10 THEN '85'
    WHEN SUM(c.Max_length) <= 10 AND
      SUM(c.Max_length) > 9 THEN '84'
    WHEN SUM(c.Max_length) <= 9 AND
      SUM(c.Max_length) > 7 THEN '83'
    WHEN SUM(c.Max_length) <= 7 AND
      SUM(c.Max_length) > 6 THEN '82'
    WHEN SUM(c.Max_length) <= 6 AND
      SUM(c.Max_length) > 5 THEN '81'
    WHEN SUM(c.Max_length) <= 5 AND
      SUM(c.Max_length) > 4 THEN '79'
    WHEN SUM(c.Max_length) <= 4 AND
      SUM(c.Max_length) > 3 THEN '78'
    WHEN SUM(c.Max_length) <= 3 AND
      SUM(c.Max_length) > 2 THEN '75'
    WHEN SUM(c.Max_length) <= 2 THEN '71'
  END AS 'RecommendedFillFactor'
FROM sys.tables t
INNER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i
  ON i.object_id = t.object_id
INNER JOIN sys.index_columns ic
  ON ic.object_id = t.object_id
INNER JOIN sys.columns c
  ON c.object_id = t.object_id
  AND ic.column_id = c.column_id
WHERE i.type IN (1, 2)
-- must be a key column
AND ic.key_ordinal > 0
GROUP BY s.name,
         t.name,
         i.name
ORDER BY t.name ASC