TOPICHow to clean up the front-end and back-end of SharePoint for improved performance.
Front-end related clean upNumber of items in the ‘NintexWorkflowHistory’ lists
There should be under 5,000 items. Navigate to http://SiteName/Lists/NintexWorkflowHistory and check how many items are present. 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, use the NWAdmin operation ‘PurgeHistoryListData’ to purge some of these items to prevent timeout issues at runtime. For more information, see NWAdmin Operations - Nintex Workflow 2013 or NWAdmin Operations - Nintex Workflow 2010.
Number of workflow tasks in the Task List this workflow utilizes
There should be under 5,000 items. Navigate to the task list the workflow or task action is configured to use and check how many items are present. 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. Workflows that error out or are terminated eliminate their associated tasks as well.
SQL side related cleanupSize of the ‘dbo.WorkflowLog’ tables
The tracking data for each action and task are stored in the WorkflowLog table when verbose logging is enabled. For more information, see How to purge large Nintex Workflow History list and dbo WorkflowProgress table.
Number of rows are in the ‘dbo.WorkflowProgress’ tables
There should be fewer than 15,000 items in the dbo.WorfklowProgress tables. A large amount of items (8,000,000-15,000,000~) in this table can cause a number of problems in workflow/tasks as Nintex records data about each action as it processes in this table for reporting/history purposes. Use NWAdmin operation ‘PurgeWorkflowData’ to purge data and prevent SQL from timing out at runtime.
The PurgeWorkflowData will help clean-up the dbo.WorkflowProgress table in the Nintex database which should be performed when the table reaches several million records (actual record counts depend on the environment/hardware). Perform these actions during a maintenance window or off-peak time as they will place locks on the workflow progress table and can affect running and new workflows. For more information, see NWAdmin Operations - Nintex Workflow 2013 or NWAdmin Operations - Nintex Workflow 2010.
Action count within SQL
Attempting to restart the workflow over and over can cause huge backlog issues within SQL as the Workflow instance is trying to run over and over. The workflow instance continues to hit the workflow progress table, this causes actions to compile within SQL and puts a lot of stress on the workflow timer service. This can cause the workflow not to run at all due to the compiled actions. Here are a few things to pay attention to, especially action count:
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 the backlog. Anything over 200 is a sign of an issue. Anything over the 500 mark needs to be addressed immediately as this can cause workflows not to run. The list output by the command sometimes can be large and hard to read within SQL, take a copy of the list and put it into a excel spread sheet. This makes it easier to read and identify which workflows are over that limit.
Once these workflows have been identified, use the "Identifying workflows potentially causing bottlenecks" section of the Purge Workflow Data - Reduce the size of the workflow progress database table guide for the necessary SQL script, as well as the NWadmin command needed to clear the backlog. When deciding which workflow data to purge, target deleted sites, workflows with errors, and workflow instances that have created surplus records.
Workflow Status table:
--WHERE i.State = '2' --'Running'
--WHERE i.State = '4' --'Completed'
--WHERE i.State = '8' --'Cancelled'
--WHERE i.State = '64' --'Error'