Nintex – Useful SQL Queries

This script will show you how many Workflow progress records each workflow in your environment has generated.

SELECT i.WorkflowName, COUNT(p.InstanceID) [Number of WF Progress Records],  i.siteid, i.webid, i.listid
 FROM dbo.workflowinstance i
 inner join dbo.workflowprogress p
 on i.InstanceID = p.InstanceID
 GROUP BY i.WorkflowName, i.siteid, i.webid, i.listid
 ORDER BY COUNT(p.InstanceId) DESC

This script will show you the top workflow instances that have generated the most records.

SELECT I.WorkflowName, COUNT(P.WorkflowProgressID) AS WorkflowProgressRecords,
 CASE i.state WHEN 2 THEN 'Running' WHEN 4 THEN 'Completed' WHEN 8 THEN 'Cancelled' WHEN 64 THEN 'Error' END AS Workflow_State,
 I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID
 FROM dbo.WorkflowInstance AS I INNER JOIN dbo.WorkflowProgress AS P WITH (NOLOCK) 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 WorkflowProgressRecords DESC

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.