Eric M Russell - Wednesday, September 20, 2017 9:16 AM

So my understanding is that you have this table containing millions of rows and a procedure which updates the following NULL-able columns, sometimes as often as 1,000 times per minute (or 17 times per second). 

  [DLRStatus] [nvarchar](100) NULL,
  [ErrorCode] [int] NULL,
  [ErrorDescription] [nvarchar](2000) NULL,

I'm guessing the back story is that this table logs or queues SMS messages, row updates occur retoractively for specific messages that fail, and when the messaging service is down this table gets pounded pretty hard. I suspect the issue going on here is page splitting. In addition to causing increased write activity, page splitting can also cause index fragmentation. For this table you need to specify a Fill Factor of maybe 90 or 80 percent. What this does is reserve some un-allocated space in each page as it's initially inserted, so when a row is subsequently updated, changing it's data length, there is room for the row to change in size within the same page, without SQL Server having to split rows onto a new page and shuffle clustered pages around. However, specifying a new Fill Factor on the clustered index would require rebuilding all indexes on the table and therefore a window of downtime.

So we know for sure what's going on with fill factor and page allocation, the following query will reveal more detail about the allocation and configuration of the table in question.


SELECT
    -- @@servername AS ServerName
    -- , DB_NAME() AS DatabaseName
    s.name AS SchemaName
    , o.name AS ObjectName
    , MIN(o.create_date) AS CreateDate
    , ISNULL(i.name,'-') AS IndexName
    , i.type_desc AS IndexType
    , pp.data_compression_desc AS CompressionType
    , MAX(CAST(i.is_primary_key AS TINYINT)) AS IsPrimary
    , MAX(CAST(i.is_unique AS TINYINT)) AS IsUnique
    , MAX(CAST(i.is_padded AS TINYINT)) AS IsPadded
    , AVG(i.fill_factor) AS FillFactorPct
    , MAX(STATS_DATE(i.object_id,i.index_id)) AS StatsLastUpdated
    , CASE WHEN SUM(pp2.partition_number) = 2 THEN 'Y' ELSE 'N' END AS IsPartitioned
    , ps.partition_number AS PartitionNumber
    , MAX( ps.record_count ) AS RowsCount
    , (SUM(ps.page_count) * 8192 ) / 1024000 AS SizeTotalMB
    , CAST( AVG( ps.avg_page_space_used_in_percent ) AS SMALLINT ) AS AvgPageFullPct
    , CAST( AVG( ps.avg_fragmentation_in_percent ) AS SMALLINT ) AS AvgFragmentationPct
FROM sys.dm_db_index_physical_stats(
        DB_ID()    -- database_id
        , NULL    -- object_id
        , NULL    -- index_id
        , NULL    -- partition_number
        , 'SAMPLED' -- LIMITED | SAMPLED | DETAILED
        ) ps
    INNER JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
    INNER JOIN sys.partitions AS pp ON pp.object_id = ps.object_id AND pp.index_id = ps.index_id AND pp.partition_number = ps.partition_number
    LEFT OUTER JOIN sys.partitions AS pp2 ON pp2.object_id = ps.object_id AND pp2.index_id = ps.index_id AND pp2.partition_number = 2
    INNER JOIN sys.objects o ON o.object_id = ps.object_id
        INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.name = 'tbl_sms_job_detail'
GROUP BY
    s.name
    , o.name
    , i.name
    , i.type_desc
    , i.index_id
    , ps.partition_number
    , pp.data_compression_desc;