Nintex Workflow For SharePoint Maintenance (Getting Healthy)

Topic

Instructions
 

Number of items in the site Workflow History lists

A large number of records within the Workflow History list of a site can impact workflow performance, and it is recommended to keep this record count as low as possible. While this list may be named "NintexWorkflowHistory", it is still a SharePoint History List, and as such will follow SharePoint limitations on list sizes. To check the number of items in the Workflow History list on a site:

• Navigate to Site Settings > Nintex Workflow > Manage workflow history lists
  • This page will lists all of the Workflow History lists on the site and the number of records within each

If there are a large amount of items in this list, use the NWAdmin.exe operation ‘PurgeHistoryListData’ to purge items from the list. This will help prevent timeout issues at runtime when workflow instances attempt to interact with the Workflow History list. 

• For more information on using the 'PurgeHistoryListData' command, see NWAdmin Operations - Nintex Workflow 2013 (attachment below) or NWAdmin Operations - Nintex Workflow 2010.

Number of workflow tasks in the Task List this workflow utilizes

There should be ideally be under 5,000 items on the task list that is being used by workflows. By viewing your task list within Site Contents you can determine how many task items are present on the list. If you are unsure which task list is being used by your workflow, you can open Workflow Settings from within the Nintex Workflow Designer to identify which task list is being used. This list follows SharePoint limitations on list sizes (typically anything more than a few thousand entries can cause problems).

If there are a large amount of items in this list, terminate old workflows to clear out old or non-relevant tasks. 

Number of rows are in the ‘dbo.WorkflowProgress’ tables 

The dbo.WorkflowProgress table holds records for each action that workflows execute to help build the 'graphical workflow history.' It is recommended that there should be fewer than 15,000,000 items in the dbo.WorfklowProgress tables, as a large number of records in this table can cause a number of performance problems in workflows. The 'PurgeWorkflowData' NWAdmin operation can be used to purge data from this table to improve workflow performance. 

Note: Database performance and record limits will be dependent on farm infrastructure and overall database health.

This purge operation should be executed during a maintenance window or off-peak time as it may place locks on the workflow progress table. For more information, see Purging Workflow Progress Table.
 

Action count within SQL

Workflows that commit exponential amounts of data to the database can cause a massive performance issues, including hanging the SharePoint Workflow Timer Service, or preventing workflows from starting across the SharePoint environment. 

This query can be run against a Nintex Content database to pull back information on workflows that have a high level of interaction with the SQL environment:
 

select I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State, COUNT(P.WorkflowProgressID) as ActionCount from WorkflowInstance I inner join WorkflowProgress P on I.InstanceID = P.InstanceID group by I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State order by COUNT (P.WorkflowProgressID) desc

Here is a break down on what this query returns:

• WorkflowName- Will provide the name of the workflow.
• WorkflowInstanceID- A new ID is issued every time the workflow runs.
• SiteID: Will provide the ID number for the site where this is being run.
• WorkflowID: Will provide the ID of the Workflow itself.
• ActionCount: This is the number that will determine how many actions the workflow is adding to the Progress table. For each action processed in a Nintex Workflow two records are added to the progress table. One is the start of the action and the other is the completion of the action. 
• Workflow Status table:
  • WHERE i.State = '2' --'Running' 
  • WHERE i.State = '4' --'Completed' 
  • WHERE i.State = '8' --'Cancelled' 
  • WHERE i.State = '64' --'Error'    

If you are able to identify a workflow that has an exponentially growing action count, it is recommended to review the workflow design and determine why the record count is growing in such a manner. It may also be necessary to purge the workflow data from the dbo.WorkflowProgress table (instructions on this process can be found above).