Moustafa Khalil Ahmed (from the Windows Workflow Foundation Team) recently blogged about how the SqlTrackingService included in WF does data partitioning across tables to keep data partitioned to smaller subsets for completed workflows.
Moustafa was kind enough to answer some extra questions about how it worked on the WF forums, and this was his answer, which I believe completements the stuff on his weblog:
"The
partition is done on the completion of the workflow instance. That is
your records will be in your regular tables until the instance
completes and this is when the records are going to move to the added
tables. This is designed for applications that have no downtime do not
want to incur downtime. We do have an offline scheme as well, so if you
have a downtime and want to do partitioning during that time or you
want to avoid moving records around when the instances complete or for
any other reason you can schedule a task that runs
PartitionCompletedWorkflowInstances stored proc which will move all
currently completed instances from the live tables to the partitions.
Host applications don’t need special privileges to create those tables,
the user should be part of the tracking_ roles that are defined in the
SqlTrackingService database.
SqlTrackingQuery
will look at the partitioned tables as well. You can look at the
PartitionCompletedWorkflowInstances stored proc as a starting point if
you want to have your own Archiving and Purging story other than the
one we provide out of box. We are working on documenting the
SqlTrackingService schema in WF’s help."
