SLV

I've got a performance issue while using workflow tracking. The application encounters a Sql timeout when calling SqlTrackingQuery.GetWorkflows. This actually results in a call to the GetWorkflows stored procedure that won't return it's result, or returns it very slowly. GetWorkflow is invoked with @WorkflowStatusId = 0. Checking the execution plan I observed that the stored procedure creates a temporary table #TrackingDataItems and freezes when inserting data into this table using OPENXML. Any ideas on what the problem is


Re: Windows Workflow Foundation Workflow Tracking performance issue

SLV

Studying the problem more I found that the script runs slowly when performing the following SELECT statement:

Code Snippet

SELECT 'CurrentEventTimeStamp' = GetUTCDate()
,[wi].[WorkflowInstanceId]
,[wi].[WorkflowInstanceInternalId]
,[wi].[InitializedDateTime]
,[wi].[CallerInstanceId]
,'WorkflowStatus' =
CASE
WHEN [wie].[TrackingWorkflowEventId] IS NULL THEN cast(4 as int) /* No events tracked - all we know is that it was created */
WHEN [wie].[TrackingWorkflowEventId] = 0 THEN cast(4 as int) /* Created */
WHEN [wie].[TrackingWorkflowEventId] = 1 THEN cast(1 as int) /* Completed */
WHEN [wie].[TrackingWorkflowEventId] = 3 THEN cast(2 as int) /* Suspended */
WHEN [wie].[TrackingWorkflowEventId] = 9 THEN cast(3 as int) /* Terminated */
ELSE cast(0 as int) /* Running */
END
,CASE
WHEN [t].[IsInstanceType] = 0 THEN [t].[TypeFullName]
ELSE NULL
END
,CASE
WHEN [t].[IsInstanceType] = 0 THEN [t].[AssemblyFullName]
ELSE NULL
END
FROM [vw_WorkflowInstance] [wi]
INNER JOIN [dbo].[vw_Type] [t]
ON [wi].[WorkflowTypeId] = [t].[TypeId]
LEFT OUTER JOIN [dbo].[vw_WorkflowInstanceEvent] [wie]
ON [wi].[WorkflowInstanceInternalId] = [wie].[WorkflowInstanceInternalId]
WHERE ( [wie].[WorkflowInstanceEventId] =
(
SELECT max([WorkflowInstanceEventId])
FROM [dbo].[vw_WorkflowInstanceEvent] [wie2]
WHERE [wie2].[WorkflowInstanceInternalId] = [wie].[WorkflowInstanceInternalId]
AND [wie2].[TrackingWorkflowEventId] NOT IN ( 5, 6, 7 ) -- Persisted, Unloaded, Loaded
)
OR [wie].[EventOrder] IS NULL ) -- Profile might not track instance events
AND ( [wie].[TrackingWorkflowEventId] in ( 2, 4, 8, 10, 11, 12 ) )


and it takes a lot to select all the data from the WorkflowInstanceEvent table and perform all the joins. I've got only two workflows tracked in that database, but they persist very often. For about 25000 records that I have in the WorkflowInstanceEvent table it takes about 13 seconds for the script to return the result. Later as the workflow instances are older the query will take 30 sec. and more to perform and I'll get the timeouts.




Re: Windows Workflow Foundation Workflow Tracking performance issue

Hejduk Rostislav

Solutions:

a) You should change your tracking profile - remove persistance tracks.

Code Snippet

TrackingProfile profile = new TrackingProfile();

ActivityTrackPoint activityTrack = new ActivityTrackPoint();

ActivityTrackingLocation activityLocation = new ActivityTrackingLocation(typeof(Activity));

activityLocation.MatchDerivedTypes = true;

IEnumerable<ActivityExecutionStatus> statuses = Enum.GetValues(typeof(ActivityExecutionStatus)) as IEnumerable<ActivityExecutionStatus>;

foreach (ActivityExecutionStatus status in statuses)

{

activityLocation.ExecutionStatusEvents.Add(status);

}

activityTrack.MatchingLocations.Add(activityLocation);

profile.ActivityTrackPoints.Add(activityTrack);

profile.Version = Version > VychoziVerze Version : VychoziVerze;

WorkflowTrackPoint workflowTrack = new WorkflowTrackPoint();

WorkflowTrackingLocation workflowLocation = new WorkflowTrackingLocation();

IEnumerable<TrackingWorkflowEvent> eventStatuses = Enum.GetValues(typeof(TrackingWorkflowEvent)) as IEnumerable<TrackingWorkflowEvent>;

foreach (TrackingWorkflowEvent status in eventStatuses)

{

// consider constraints about events which should not be tracked (persisted, unloaded )

workflowLocation.Events.Add(status);

}

workflowTrack.MatchingLocation = workflowLocation;

profile.WorkflowTrackPoints.Add(workflowTrack);

UserTrackPoint utp = new UserTrackPoint();

UserTrackingLocation utl = new UserTrackingLocation(typeof(object), typeof(Activity));

utl.MatchDerivedActivityTypes = true;

utl.MatchDerivedArgumentTypes = true;

utp.MatchingLocations.Add(utl);

profile.UserTrackPoints.Add(utp);

More info is in the wfsamples - technology and Usertrackpoints

b) Don't persist when there is no change





Re: Windows Workflow Foundation Workflow Tracking performance issue

SLV

Thanks for your reply. Actually, I've considered the option of adjusting the tracking profile so that I don't have that much data in the workflow events table. First, I'm not sure I won't need the persist and unloaded track points, they help in tracking what happened to the instance. Second, I have only two instances being tracked in the database, there's an indexed table with 25000 records, and a couple of joins performed over that data, does it have to be so slow What if I have dozens or hundreds of instances being tracked in the database I might change the tracking profile to minimize even more the quantity of data that gets into the database, but how do I track those instances then





Re: Windows Workflow Foundation Workflow Tracking performance issue

Hejduk Rostislav

I am not DB expert but 25000 of records seems a lot.. There is some stored procedure, that might help you to partition table rows into more tables, BUT you should know, that if you use SqlTracking object model, all events are loaded from sql tables..

There are no usable infos about workflows, just that something happend, I think you should analyze what do you really need to track, what is customer critical and so on..

Another not much nice hints can be found here:

http://msdn.microsoft.com/msdnmag/issues/07/03/Foundations/default.aspx

Btw you can track only some of the data easily by using TrackData method call inside workflow Executing operations.

If you need to log persistance, you can inherit sqlpersistanceservice and overwrite methods for loading and unloading.

Another hard way is to write your own tracking system, because this is still hot stuff.. ;-) You will find more fun with it after a while of usage. ;-)