Hello,

I have a sequential workflow that is using tracking/persistence services. It seems like I am getting timeouts all the time from tracking and/or persistence services and I don't understand why. It is causing my application to be extremely slow at times and causing problems due to tracking/persistence information not being stored. This causes a mess in the app because the main data will get stored (not tracking or persistence) and then none of the tracking or persistence gets stored and I have orphaned workflows. Does anyone have any idea what is going on here? The DB resides on the same machine that the Workflow is hosted on and there should be no reason it ever times out, but it consistently does. Any thoughts?

Thanks!

Hi,

     There are a couple of things you could try.

1. Make sure that you have enough persistence points in your workflow. You can find more information on when a workflow persists here ... http://www.masteringbiztalk.com/blogs/jon/PermaLink,guid,5f4d8c41-73bf-4d7f-93b4-8934130a783b.aspx. By default, the tracking service uses transactional mode (sqlTrackingService.isTransactional=true). If you have not changed this default, the tracking data is not sent to the database until a persist happens. So if you dont have enough persist points in your workflow, you could end up with a lot of tracking data when a persist ultimately happens.

2. If you dont need transactional mode tracking, try turning it off. That will make sure that you dont have the occational huge database writes - but at a loss of performance since each tracked data is written to the database immediately and not batched together.

3. Try reducing the amount of tracking data that is created by modifying the profile. Make sure that you track only what you need.

Hope these tips help you solve the issue.

Thanks,

Ranjesh

Hi Ranjesh,

All of that makes sense however it sometimes happens on the very first activity in the Workflow, which would leave me to believe that there is not a bunch of tracking data built up, but I could be wrong. It is timing out on this line of code:

Dim inst as WorkflowInstance = theWorkflowRuntime.CreateWorkflow(t, parameters)

It seems like it is timing out after 30 seconds...where is this setting for tracking/persistence services? Upping the value may be ok, but it really shouldn't take more than 1 second so I don't think that is the solution..

I just don't see how a timeout could occur when it really shouldn't be inserting that much data. If I can insert millions of rows into another table with no timeout issue why is it happening on the tracking/persistence services for inserting tens of records?

I'm working on limiting my tracking profile now, so maybe that will help. Also, whenever I call an external event in my workflow I hook up to the "Workflow_Idled" event and manually unload the workflow, which should be a persistence point. There are however a few activities until the next handle external event activity.

Most importantly, it sometimes happens on the Atlas Workflow Monitor, which is not writing tracking data but retrieving it. Sometimes the system is very quick, but then all of a sudden it just gets killed and starts timing out every time I try to go to the monitor, even when there is only 1 instance of a workflow in the tracking/persistence stores. I am experiencing this on my dev machine (where nothing else is hitting the sql server) and on the production machine.

How can I turn transactional mode off?

Thanks for your comments!

Hi,

    You can make the tracking service non transacional by setting the isTransactional property of the sqlTrackignService object (that you add to the workflow runtime). More here https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=221518&SiteID=1.

Also, are you using the same database for both persistence and tracking? If so could you try using the

SharedConnectionWorkflowCommitWorkBatchService ? Some more details can be found in this posting http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1544322&SiteID=1.

Thanks,

Ranjesh

Arrrgh! This is so confusing. Ok, I was using seperate databases for persistence & tracking. I wanted to try to use the same DB for both so I got the script, created the database, and added the following to my web.config file:

<WorkflowConfiguration>

<CommonParameters>

<add name="ConnectionString"

value="Server=SERVER;Database=WindowsWorkflowTrackingDatabase;UID=WorkflowAdmin;Pwd=dfdfdfdfdfdf;Min Pool Size=0;Max Pool Size=100;Connect Timeout=15;Application Name=AppName;"/>

</CommonParameters>

<Services>

<add

type="System.Workflow.Runtime.Tracking.SqlTrackingService,

PublicKeyToken=31bf3856ad364e35"

<add

type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService,

PublicKeyToken=31bf3856ad364e35"

UnloadOnIdle="true"

<add

type="System.Workflow.Runtime.Hosting.SharedConnectionWorkflowCommitWorkBatchService,

PublicKeyToken=31bf3856ad364e35" />

</Services>

</WorkflowConfiguration>

And now nothing is persisting and when I try an Unload manually I get an error stating the following:

"The workflow hosting environment does not have a persistence service as required by an operation on the workflow instance"

So what am I doing wrong now? Doesn't adding these entries to the web.config file automatically add these services to the workflow runtime when I create an instance or do I still need to add them to the runtime using AddService, except with no connection string (because it is now no longer in the connectionstrings section)??

Thanks for all of your help so far. I think if I can get it working with the same database that would probably take care of my problem..

Ok, I think I have it working with the same database now. I wasn't able to get it to work by adding the configuration section to the web.config file so I tried this:

Dim SharedConnectionService as SharedConnectionWorkflowCommitWorkBatchService = New SharedConnectionWorkflowCommitWorkBatchService(strConn)

' 'Add system SQL state service

Dim stateService As SqlWorkflowPersistenceService = New SqlWorkflowPersistenceService(strConn)

Dim trackingService as SqlTrackingService = New SqlTrackingService(strConn)

trackingService.UseDefaultProfile = True

' Start

My only question is if it is really using the SharedConnectionWorkflowCommitWorkBatchService. I ask because I am still having to pass in connection strings to the Persistence & Tracking services...

Thanks Tom,

It looks like it is working and I am not getting the timeout errors anymore, but it's still early.

Thanks again!

Arrrrrrgh!!! The timeouts are back with a vengeance.... Both persistence and tracking use the same database (which is located on the same machine as the website hosting the workflow) and I am using the SharedConnectionService. I'm goint to try turning transactions off next, i'll post the results.

If anyone else has anything to offer on this please let me know, I don't see why the workflow monitor is timing out when it is simply retrieving records...

Thanks!

This has gotten to the point where user frustration is so high that it is becoming evident that WF is not feasible for an actual application that needs to retrieve data from the tracking database. It is timing out on the sql tracking queries...which I did not write.

Here is an example of where it is timing out:

sqlTrackingQuery.GetWorkflows(sqlTrackingQueryOptions)

The strangest thing is that it doesn't ALWAYS happen...sometimes it is very responsive but then it just starts happening and users will get timeouts for 15 minutes to a couple of hours...

I can't imagine it would be the number of users using the application (below 50). All databases reside on the same machine as the website (just part of my testing to try and find the bottleneck) and these are the only databases on the server except for reporting services.

Any ideas anyone???

Seriously people, please stop marking your own answers as the solution. If I find a solution that works, I will give the author the credit. The fact is that nothing here has helped, it all boils down to crappy performance for the API. I have never written an application that has so many freaking timeouts and problems which leads me to believe that WF and the tracking API have got a very long way to go. The API leaves out important functionality like being able to pull all workflow instances that are sitting at a certain step or use "AND" queries with the sqlTrackingQuery. Boo WF.

I'm very sorry to hear that you're having such problems with this.  We went to great lengths stress testing both of these components at a very high cpu load for various scenarios ranging from 36-72 hours, with both local and remote Sql Server machines, looking explicitly for Sql timeouts, blocking and deadlocks.  If possible please work with PSS so that we can take a closer look at the problems that you're encountering.

In regards to the timeouts that you are seeing here are a couple of things to look at (likely you've already covered these but just in case I'll list all that I can think of):

1. Are you getting connection timeouts or timeouts failing to execute a command?
2. If these are command timeouts is there blocking in the database?  When the timeouts occur do you see a blocking chain of spids in Sql?
3. How much stress are you putting on the system?  In general is the network and hardware up to the level of stress that is being applied?
4. How does the database machine look stress-wise?  What are is the Average Disk I/O, Memory and CPU when the timeouts occur?
5. You might consider running SqlProfiler to get an idea of the type, number and size of commands that you're asking the SqlTrackingService to run.

In regards to the SqlTrackingQuery api.  Yes, it is a limited API - no question about that.  The intention was for it to solve a fixed set of high level scenarios.  For functionality beyond what this component supplies the recommended approach is to directly query the views.  The general feeling was that rather than creating a custom query language it was better to provide a high level API and support direct queries against the database objects.

Something else to consider is that the SqlTrackingService and SqlWorkflowPersistenceService are just two possible implementations of what are extensibility points in the WorkflowRuntime.  The intent of these components was implementations that could meet a wide range of scenarios adequately and provide acceptable performance over a long period of uptime.  It is possible that your needs might be best served by writing a custom tracking service that it tuned for your exact scenarios.  I'm just throwing this out as a possibility however; we would like to understand your scenarios, workflows and resulting problems better as a first step.  Again, PSS is the best way to quickly work this to a resolution if that is available to you.

Thanks,
Joel West
MSFTE - SDE

This posting is provided "AS IS" with no warranties, and confers no rights

Thanks Joel,

I have looked at the performance monitor and the CPU is spiking at 92-99% taken up by SQL Server. The PC has a dual core 2.8 GHz processor. We have another machine that is running SQL Server that hosts about 100-200 databases for other applications and that machine only has a 2.4 GHz processor and it never spikes to 100% from SQL Server. Seems like it is the frequency of the tracking queries. I can't believe that testing was done on WF (and that it passed) when I am having such a hard time with a miniscule little application. I may have to try to write the tracking services myself but that is a huge undertaking and I'm really not sure I want to even try it based on the performance from WF that I have seen so far. What's the point of using a WF system when you basically have to write everything yourself? VERY frustrating!

Could it be because there aren't indexes built to match the queries that the tracking service use? For instance, The WorkflowInstance table only has 2 indexes:

1. WorkflowInstanceID + ContextGuid
2. WorkflowInstanceID

Howevere the Tracking Queries let you filter by other data such as the date initialized. If there is no index on the initialized date the query would take longer...

Why are the indexes not built to match the sqltrackingqueries? The tracking tables grow exponentially and they do it FAST, to not have indexes on queried columns could explain the horrible performance.

Any opinions on this?