SqlTrackingService performance when used with short transactions

  • Article

If you develop programs using Windows Workflow (WF) it is very common to use the Tracking service which automatically tracks activities – specifically the SqlTrackingService which logs all of this into a nice little database. However there is one issue that we recently discovered in a WF project – SqlTrackingService will batch all tracking information and write it to the database in one shot at the end of the workflow. This is a good performance optimization, but it does take a little bit of time to write all these records to the database. If you have a relatively long Workflow (> 1 second) you probably won't notice this extra time for tracking. However, if you have short transactions, this can take up a lot of the workflow execution time – upto 70% of the execution time!

So how do we address this problem? The SqlTracking service cannot be made 'asynchronous' – a good idea for future versions but this is not there right now. As is common practice, most programs wait for the WorkflowCompleted event to signify the end of the workflow execution, but this event only gets fired once the Tracking information is written to the database. One idea that was tried successfully is not waiting for the WorkflowCompletes event, but quiting the workflow earlier once the last activity in the workflow has completed and you have all the results you need. This short-circuiting of the workflow is accomplished by firing a special custom event at the end of the workflow. In your main thread, you would simply wait for this custom event instead of the WorkflowCompleted event. At this point you return the data to the caller and the workflow continues asynchronously to write all the tracking information to the database.

Needless to say there are a few tradeoffs here:

  • If there is a problem with the tracking database, you will lose information.
  • The thread usage will increase because there will be many threads that are just writing tracking information - and this could lead to thread starvation.