How to quickly check the health of your Nintex Workflow SQL Indexes

Nintex Newbie
12 1 3,968

Products: Nintex Workflow 2013, Nintex Workflow 2010

We often encounter a situation where the performance of a SharePoint farms workflow infrastructure could be drastically improved if fragmentation on Nintex Workflow databases were minimal. This script will provide a way to review the fragmentation of all table indexes inside of the Nintex Workflow Configuration\Standalone Content database.

Run the below (attached as well as a *.txt file for your convenience) PowerShell script (PowerShell ISE works well) from a SharePoint Server:

PowerShell Script
  1. Add-PSSnapin Microsoft.SharePoint.PowerShell
  2. [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
  3. [void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow")
  4. [void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow.Administration")
  5. $cmd = New-Object -TypeName System.Data.SqlClient.SqlCommand
  6. $cmd.CommandType = [System.Data.CommandType]::Text
  7. $cmd.CommandText = "SELECT OBJECT_NAME(i.object_id) AS TableName , AS TableIndexName ,phystat.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 10"
  8. $reader = [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase().ExecuteReader($cmd)
  9. $indexes = @()
  10. while($reader.Read())
  11. {
  12.     $row = New-Object System.Object
  13.     $row | Add-Member -MemberType NoteProperty -Name "TableName" -Value $reader["TableName"]
  14.     $row | Add-Member -MemberType NoteProperty -Name "TableIndexName" -Value $reader["TableIndexName"]
  15.     $row | Add-Member -MemberType NoteProperty -Name "avg_fragmentation_in_percent" -Value $reader["avg_fragmentation_in_percent"]
  16. $indexes += $row
  17. }
  18. $indexes

To use the script do the following:

  • Ensure you are running the PowerShell console as a SharePoint Administrative account.
1 Comment
Not applicable

Thanks for the article Aaron. Can you please suggest if rebuilding indexes required on Nintex on O365? If so, what are the steps?