Description

The Workbox application performance is determined by the volume of content and item access configuration in the solution. The more items that Workbox shows, the more computation operations Sitecore needs to perform,  leading to application loading delays.

Item workflow information is stored in the [VersionedFields] table. The default SQL query to locate items in a certain workflow state is the following:

SELECT TOP (maxVersionToLoad) [ItemId], [Language], [Version]
          FROM [VersionedFields] WITH (NOLOCK)
          WHERE [FieldId]='3e431de1-525e-47a3-b6b0-1ccbec3a8c98'
          AND [Value]= '{WorkflowStateID}'
          ORDER BY [Updated] desc

Although the SQL Engine is responsible for picking the query execution plan, under certain conditions the query might lead to an expensive [VersionedFields] table scan.

Solution

1. Since workflow is a well-known field (Sitecore.FieldIDs.State), a filtered SQL index can be created to cover only workflow field data in the Master database:

   CREATE NONCLUSTERED INDEX IX_Versioned_Workflow_Filtered
       ON VersionedFields (itemid,FieldId)
       INCLUDE (Value,language,version, updated)
       WHERE FieldId='{3E431DE1-525E-47A3-B6B0-1CCBEC3A8C98}'

   This allows SQL Engine query planner to use an index instead of the [VersionedFields] table. An additional SQL index makes Workbox faster, but slightly slows the item insert/update/remove speed.
2. Limit the number of rows Sitecore can load by adding the Workbox.SingleWorkflowStateVersionLoad.Threshold setting into the configuration:

   <setting name="Workbox.SingleWorkflowStateVersionLoad.Threshold" value="100"/>

   Sitecore loads no more than 2000 versions for Workbox by default. This default value is much larger than a person can review in a reasonable amount of time and was added to preserve legacy system behavior.
3. Increase the size of the AccessResultCache in your Sitecore configuration:

   <setting name="Caching.AccessResultCacheSize" value="100MB" />

   Only users with sufficient access permissions are allowed to move items between workflow states, so Sitecore evaluates access rules for each item. Sitecore uses a caching layer in order to avoid calculating access rights over and over again. As data volume grows, the cache size might not be large enough, leading to warning messages such as:

   WARN  AccessResultCache cache is cleared by Sitecore.Caching.Generics.Cache`1+DefaultScavengeStrategy[[Sitecore.Caching.AccessResultCacheKey, Sitecore.Kernel, Version=10.0.0.0, Culture=neutral, PublicKeyToken=null]] strategy. Cache running size was 9 MB.

4. The default Workbox behavior can be customized by overriding the  'SqlDataProvider.GetItemsInWorkflowState' API in a custom data provider.