---
title: "DocumentUsers index (version 2.21.0)"
description: "How Signer fills the denormalized columns and builds the DocumentUsers table index when updating from a version earlier than 2.21.0 to 2.21.0 or later (including 3.x), and what to do if the automatic process fails."
canonical: "https://docs.lacunasoftware.com/en-us/articles/signer/on-premises/documentusers-index"
locale: "en"
product: "Signer"
last_updated: "2026-10-16"
---

# 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:

```text
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:

```sql
-- 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](https://docs.lacunasoftware.com/en-us/articles/signer/on-premises/settings#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.

```sql
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:

```sql
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.

```sql
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](https://docs.lacunasoftware.com/en-us/articles/signer/on-premises/documentusers-index#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.