Documents index (documents reports)

This page explains the IX_Documents_SubscriptionId_CreationDateUtc index, built when updating to version 3.0.0 (SIG-1919), and what to do if the automatic build fails. It applies to all instances updating from a version earlier than 3.0.0.

note

Updating at a time of low usage is strongly recommended. While the index does not exist, the application works normally. The cost is that the documents reports can be slow or time out on organizations with many documents.

What this index improves

The organization, user and folder documents reports load every document of a period, filtering by subscription (SubscriptionId) and sorting by creation date (CreationDateUtc). Without an index on those two columns, the database must sort a large volume of documents before returning the rows, which caused timeouts when generating the monthly report of large organizations.

IX_Documents_SubscriptionId_CreationDateUtc
  keys: SubscriptionId (ascending), CreationDateUtc (ascending)

If a report times out, the application log records a warning suggesting to raise ReportDatabaseCommandTimeoutSeconds or to check that the index exists.

How the automatic process works

1. Migration (AddDocumentsReportIndex). Run on the first start. It does not create the index, so the Documents table is not locked during the update.
2. Enqueue on startup. DocumentsReportIndexBuilderStarter checks whether the index exists. If it does not (or if it cannot check), it enqueues the CreateDocumentsReportIndexJob in Hangfire (Low queue, up to 3 attempts, no concurrent execution). This repeats on every startup while the index does not exist.
3. CreateDocumentsReportIndexJob. If the index already exists, it does nothing. Otherwise, it detects the SQL Server edition and builds the index with SORT_IN_TEMPDB = ON and a timeout of DocumentsReportIndexCommandTimeoutSeconds (default 7200):
   • ONLINE (does not lock the table) on Azure SQL Database, SQL Managed Instance and SQL Server Enterprise or Developer.
   • OFFLINE on other editions, which locks the Documents table until it finishes. The job logs a warning.
   • If the ONLINE build fails with an error that is not a timeout, the job tries an OFFLINE build. If the ONLINE build hits the timeout, it is not retried OFFLINE, so the table is not locked for even longer.

How to follow it

• In the Hangfire dashboard (/hangfire), follow the CreateDocumentsReportIndexJob.
• In the application logs, look for Creating IX_Documents_SubscriptionId_CreationDateUtc, created successfully or Failed to create.
• In the database:

SELECT name, is_disabled FROM sys.indexes
WHERE name = 'IX_Documents_SubscriptionId_CreationDateUtc' AND object_id = OBJECT_ID('Documents');

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) and the query above does not return the index.

1. Raise the timeout and restart

Raise DocumentsReportIndexCommandTimeoutSeconds (see General Settings) and restart the application. The job is enqueued again on startup.

2. Create the index manually

If it still times out, create the index on the database. 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_Documents_SubscriptionId_CreationDateUtc' AND object_id = OBJECT_ID('Documents'))
CREATE INDEX [IX_Documents_SubscriptionId_CreationDateUtc] ON [Documents] ([SubscriptionId] ASC, [CreationDateUtc] ASC)
WITH (ONLINE = ON, 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 are throttled (LOG_RATE_GOVERNOR wait; the CXCONSUMER wait is benign). 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.

3. Confirm

Run the query in the How to follow it section: the index must be present with is_disabled = 0. With the index created, the job finishes without rebuilding it on the next startup.