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:
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.
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.
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.
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:
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).