Pular para o conteúdo principal

DocumentUsers index (version 2.21.0)

This page explains the per-user document list optimization (SIG-780), introduced in version 2.21.0, and what happens when an instance updates from a version earlier than 2.21.0 to 2.21.0 or later, including 3.x. Instances already on 2.21.0 or later have already gone through this process and are not affected.

warning

On very large DocumentUsers tables, the process causes a period of slowness and higher database consumption after the update. Updating at a time of low usage is strongly recommended. The application keeps working during the process.

What this optimization improves​

A user's document list must filter and sort by the document's situation and update date. To avoid a JOIN with the Documents table on every listing, Signer copies those two values to the DocumentUsers table (columns DocumentStatusCode and DocumentUpdateDateUtc) and builds a covering index over them:

IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc
  keys:     UserId, DocumentStatusCode, DocumentUpdateDateUtc (descending)
  includes: DocumentId, IsPending, SignerId, ApproverId, SignRuleId, ApproverRuleId

Until the backfill and the index finish, the document list is slower, and documents that predate the update may show up out of order or not show up in the per-user list.

How the automatic process works​

The process has three stages, and only the first happens during the migration:

  1. Migration (DenormalizeDocumentUserWithTrigger). Run on the first start. It adds the two (empty) columns to DocumentUsers, drops the old IX_DocumentUsers_UserId index and creates the TR_Documents_SyncDocumentUsers trigger on Documents, which keeps the columns in sync from then on. It is fast: it does not fill the existing data and does not build the new index, so the update is not blocked.
  2. Enqueue on startup. DocumentUserBackfillStarter checks the DocumentUserDenormalizationCompleted setting and whether the index exists. If either condition is not met, it enqueues the DocumentUserDenormalizationJob in Hangfire (Low queue, up to 3 attempts). This repeats on every startup until the process is complete.
  3. DocumentUserDenormalizationJob. Runs, in this order:
    1. Backfill of the columns of the existing rows, in batches walking the Id key (batch size in DocumentUserDenormalizationBatchSize, default 10000; per-batch timeout in DocumentUserDenormalizationBackfillCommandTimeoutSeconds, default 300). Every 20 batches the job saves its progress point (DocumentUserDenormalizationBackfillCursor), so a new attempt continues where it stopped.
    2. Marks completion, writing DocumentUserDenormalizationCompleted = True to the Settings table.
    3. Builds the index IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc, with a timeout of DocumentUserDenormalizationIndexCommandTimeoutSeconds (default 7200). The build is ONLINE (does not lock the table) on Azure SQL Database, SQL Managed Instance and SQL Server Enterprise or Developer, and OFFLINE on other editions. The index is only built after the backfill, so the two operations do not compete for the table.
danger

If the ONLINE build fails with any SQL error, including a timeout, the job tries to build the index OFFLINE, which locks the DocumentUsers table for the whole build. On large tables, avoid this scenario by raising the timeout before the update or by creating the index manually (see below).

How to follow it​

  • In the Hangfire dashboard (/hangfire), follow the DocumentUserDenormalizationJob. When it finishes, it shows as succeeded.
  • In the application logs, look for Backfill DocumentUser denormalization, Creating IX_DocumentUsers_... and created successfully.
  • In the database:
-- Backfill completed? (must return 'True')
SELECT [Value] FROM Settings
WHERE [Key] = 'DocumentUserDenormalizationCompleted' AND AgentId IS NULL AND SubscriptionId IS NULL;

-- Rows still not filled (must return 0)
SELECT COUNT_BIG(*) FROM DocumentUsers WHERE DocumentStatusCode IS NULL OR DocumentUpdateDateUtc IS NULL;

-- Index created?
SELECT name, is_disabled, fill_factor FROM sys.indexes
WHERE name = 'IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc' AND object_id = OBJECT_ID('DocumentUsers');

What to do if the automatic process fails​

The job failed if it used up its 3 attempts in Hangfire, or if the logs show a timeout or a deadlock (SQL error 1205). Follow the steps in order, and stop as soon as the process completes.

1. Adjust the settings and restart​

If the failure was a timeout, raise the settings below (see General Settings) and restart the application. The job is enqueued again on startup and, if the backfill was in progress, continues from the last saved point.

  • DocumentUserDenormalizationBatchSize: lower it to reduce the lock time of each batch on instances with live traffic.
  • DocumentUserDenormalizationBackfillCommandTimeoutSeconds: timeout of each backfill batch.
  • DocumentUserDenormalizationIndexCommandTimeoutSeconds: timeout of the index build.

2. Fill the columns manually​

If the backfill does not complete, run the script below on the database. It walks the table by the clustered Id key in a single progressive pass, in batches, and prints the progress. It can be interrupted and re-run safely.

SET NOCOUNT ON;

DECLARE @last uniqueidentifier = '00000000-0000-0000-0000-000000000000';
DECLARE @step int = 5000, @rows int = 1;
DECLARE @totalDone bigint = 0, @batch int = 0;
DECLARE @t0 datetime2 = SYSUTCDATETIME();
DECLARE @msg nvarchar(200);

WHILE @rows > 0 BEGIN
  ;WITH batch AS (
    SELECT TOP (@step) Id
    FROM DocumentUsers
    WHERE Id > @last
    ORDER BY Id
  )
  UPDATE du
  SET du.DocumentStatusCode    = d.StatusCode,
      du.DocumentUpdateDateUtc = d.UpdateDateUtc
  FROM DocumentUsers du WITH (ROWLOCK)
  INNER JOIN batch     b ON b.Id = du.Id
  INNER JOIN Documents d ON d.Id = du.DocumentId
  WHERE du.DocumentStatusCode IS NULL OR du.DocumentUpdateDateUtc IS NULL;

  DECLARE @updated int = @@ROWCOUNT;
  SET @totalDone += @updated;
  SET @batch += 1;

  SELECT @last = MAX(Id), @rows = COUNT(*)
  FROM (SELECT TOP (@step) Id FROM DocumentUsers WHERE Id > @last ORDER BY Id) x;

  SET @msg = CONCAT('Batch ', @batch, ' | updated ', @updated, ' | total ', @totalDone,
                    ' | ', DATEDIFF(second, @t0, SYSUTCDATETIME()), 's | last=', CONVERT(char(36), @last));
  SET @msg = REPLACE(@msg, '%', '%%');
  RAISERROR(@msg, 0, 1) WITH NOWAIT;
END

RAISERROR('Backfill completed.', 0, 1) WITH NOWAIT;

Then confirm that the count of unfilled rows is 0 (query above) and record the completion, so the job does not repeat the backfill:

IF EXISTS (SELECT 1 FROM Settings WHERE [Key] = 'DocumentUserDenormalizationCompleted' AND AgentId IS NULL AND SubscriptionId IS NULL)
  UPDATE Settings SET [Value] = 'True'
  WHERE [Key] = 'DocumentUserDenormalizationCompleted' AND AgentId IS NULL AND SubscriptionId IS NULL;
ELSE
  INSERT INTO Settings (Id, [Key], [Value], AgentId, SubscriptionId) VALUES (NEWID(), 'DocumentUserDenormalizationCompleted', 'True', NULL, NULL);

3. Create the index manually​

Only after confirming the backfill finished. Do it in a low-traffic window and in a single session: two simultaneous ONLINE builds on the same table deadlock on the final lock swap, and IF NOT EXISTS does not protect against concurrent runs. On editions without ONLINE support (SQL Server Standard and Express), use ONLINE = OFF, which locks the table during the build.

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc' AND object_id = OBJECT_ID('DocumentUsers'))
CREATE INDEX [IX_DocumentUsers_UserId_DocumentStatusCode_DocumentUpdateDateUtc] ON [DocumentUsers] ([UserId] ASC, [DocumentStatusCode] ASC, [DocumentUpdateDateUtc] DESC)
INCLUDE ([DocumentId], [IsPending], [SignerId], [ApproverId], [SignRuleId], [ApproverRuleId])
WITH (ONLINE = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

Precautions:

  • Deadlock. The ONLINE build competes for locks with the application's writes and can cause a deadlock (error 1205); the victim may be an application transaction rather than the build. After the build, check the application logs for 1205 errors in that period.
  • Azure SQL Database. Index creation is always fully logged, so the time depends on the service tier's log rate limit. Low tiers, such as the 800 DTU S7, are throttled (LOG_RATE_GOVERNOR wait; the CXCONSUMER wait is benign). On an instance with about 129 million DocumentUsers rows on S7, the build took a long time and one attempt ended in a deadlock. If you can, scale the service tier before starting the build, since changing the tier midway aborts a build in progress.
  • Resumable build (optional). On SQL Server 2019 or later and Azure SQL, add RESUMABLE = ON to the WITH clause so an interruption does not discard the progress (check sys.index_resumable_operations and use ALTER INDEX ... RESUME or ABORT). RESUMABLE = ON cannot be combined with SORT_IN_TEMPDB = ON; remove that option.

4. Confirm and restart​

Run the queries in the How to follow it section: completion must be True, no pending rows and the index present. Restart the application. With the completion recorded and the index created, DocumentUserBackfillStarter no longer enqueues the job.

View as Markdown