Workflow taking a long time to start and causing a jump in sqlservr.exe

Question

I have a workflow that checks an item in another list, and based on the data in that list item, will fill data in some of its own columns. The trouble I am having is that every time the workflow runs, it takes a significant amount of time to run.

I am getting the infamous "Due to heavy load" message, which I am happy to ignore at the moment, but the part that has me perplexed is that when the workflow runs and the due to heavy load error is present, the sqlservr.exe process on the server jumps to around 50%.

From my understanding, workflows initially run in the w3wp.exe process, until such time as there is a pause, in which case they are run by the OWSTimer.exe process. And due to the heavy workflow error message, that would indicate a pause and then be carried out by the timer services, correct?

Why is my sqlservr.exe process taking over workflow processing?

Edit: I am using SharePoint 2010. It is a SharePoint Designer workflow, and it does query another list with ~1800 items. On that list though, there is a Yes/No column, and the Workflow only looks for items that have this field set to No (~320 items).

Also, if the workflow has the heavy load message, saying it is waiting to start, then shouldn't the sqlservr process still be inactive as the workflow hasn't started querying the list?

Answer 1

You're correct about the flow of things. W3WP can queue a Workflow with OWTimer for later execution, that's what happens when the heavy load message appears. SQLServer never executes your workflow, however it might be queried extensively by external systems (e.g. your workflow).

Are you sure your Workflow is the problem? Is it really just a SharePoint Designer Workflow? Is it maybe querying a list consisting of millions of items and that's bringing the SQL Server to its knees?

You could activate tracing on the SQL Server to see who generates the heavy load.