High SQL Disk Usage, High Servicedesk Processor Utilitization and Slow Servicedesk Performance

Products

• ServiceDesk

Issue/Introduction

A customer recently experienced high processor utilitization and memory usage on their Servicedesk server.  Their SQL server gave all of the indications of being disk-bound as well, with high perfmon counters for Average Disk Queue Length (both Read and Write), Buffer Cache Hit Ratio, etc.  

• SQL Timeout errors in all Key Projects
• Messages with queuenames containing "externaldata%",  consuming over 130GB of data in the database
• Messages without Workflow tasks associated

 

Environment

Servicedesk 8.0, SQL server 2008.

Cause

There was a variety of things found.  Key Items were:

• Customer Deleted a Group that was hardcoded into a veriety of Workflow processes
• One key workflow process had a hard loop in the error handling section.  Because the group reference was missing in first bullet item, the error-handling process kept looping, generating messages without tasks.

Resolution

This required some extensive troubleshooting.  Taking all of the projects and putting them in small groups or in individual IIS application pools helped isolate the problem workflow.  Being able to stop some applications helped as well. Once this was complete, we found the problems and remediated them as listed below:

1. Reassociated the places where the deleted group was called to a active group.
2. Cleaned out the messages table of all messages with an "...Externaldata%" queuename using the following SQL - DELETE from Messages where queuename like '%Externaldata%'
3. Added exception triggers to components in the error-handling process to not loop over each other.