jfkrueger119584

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!



Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

Ranjesh - MSFT

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





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

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!





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

Ranjesh - MSFT

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





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

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,

System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral,

PublicKeyToken=31bf3856ad364e35"

/>

<add

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

System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral,

PublicKeyToken=31bf3856ad364e35"

UnloadOnIdle="true"

/>

<add

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

System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral,

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..





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

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)

wr.AddService(SharedConnectionService)

' 'Add system SQL state service

Dim stateService As SqlWorkflowPersistenceService = New SqlWorkflowPersistenceService(strConn)

wr.AddService(stateService)

Dim trackingService as SqlTrackingService = New SqlTrackingService(strConn)

trackingService.UseDefaultProfile = True

wr.AddService(trackingService)

' Start

wr.StartRuntime()

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...





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

Tom Lake - MSFT

Yes, you should be using the shared connection workflow ... service. Make sure they are all using the same connection string or you will get a error. The syntax for the config that you posted previously looks correct, I am not sure why it is not working. For a working example go to http://blogs.msdn.com/tomlake/archive/2006/05/17/examples-of-using-persistence-and-tracking-in-asp-net.aspx.




Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

Thanks Tom,

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

Thanks again!





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

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!





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

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





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jfkrueger

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.





Re: Windows Workflow Foundation Poor performance for Tracking/Persistence services - Timeouts

jdw - MSFT

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