aaron.labiosa@nintex.com

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

Blog Post created by aaron.labiosa@nintex.com Champion on Dec 5, 2014

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 ,i.name 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.

Attachments

Outcomes