January 11, 2012 at 8:27 am
Hi,
My developers are asking to implement a workflow framework, and the code they are submitting is "straight from microsoft" according to them. They want it to reside on the production server with all the other OLTP databases (yes, we have a single monolithic server - Don't ask...), and I really do not like the look of the scripts submitted. They make wide and varied usage of index hints, triggers, system stored procedure calls related to locking, and lots of tables with varbinary(max) columns, of which I am also not fond.
Everything takes place within a schema with the name "System.Activities.DurableInstancing". Is anyone familiar with this stuff. Any comments on how much of a performance hit it adds in a consolidated server environment? Should it be on a stand-alone? How intensive is it with respect to I/O?
The developers have insinuated that it could grow to millions of records very quickly, and my lack of faith increases every time I get a script from them. Here is an example of the Workflow code:
Thanks in advance
create procedure [System.Activities.DurableInstancing].[SaveInstance]
@instanceId uniqueidentifier,
@surrogateLockOwnerId bigint,
@handleInstanceVersion bigint,
@handleIsBoundToLock bit,
@primitiveDataProperties varbinary(max),
@complexDataProperties varbinary(max),
@writeOnlyPrimitiveDataProperties varbinary(max),
@writeOnlyComplexDataProperties varbinary(max),
@metadataProperties varbinary(max),
@metadataIsConsistent bit,
@encodingOption tinyint,
@timerDurationMilliseconds bigint,
@suspensionStateChange tinyint,
@suspensionReason nvarchar(max),
@suspensionExceptionName nvarchar(450),
@keysToAssociate xml,
@keysToComplete xml,
@keysToFree xml,
@concatenatedKeyProperties varbinary(max),
@unlockInstance bit,
@isReadyToRun bit,
@isCompleted bit,
@singleKeyId uniqueidentifier,
@lastMachineRunOn nvarchar(450),
@executionStatus nvarchar(450),
@blockingBookmarks nvarchar(max),
@workflowHostType uniqueidentifier,
@serviceDeploymentId bigint,
@operationTimeout int
as
begin
set nocount on
set transaction isolation level read committed
set xact_abort on;
declare @currentInstanceVersion bigint
declare @deleteInstanceOnCompletion bit
declare @enqueueCommand bit
declare @isSuspended bit
declare @lockAcquired bigint
declare @metadataUpdateOnly bit
declare @now datetime
declare @result int
declare @surrogateInstanceId bigint
declare @pendingTimer datetime
set @result = 0
set @metadataUpdateOnly = 0
exec @lockAcquired = sp_getapplock @Resource = 'InstanceStoreLock', @LockMode = 'Shared', @LockTimeout = @operationTimeout
if (@lockAcquired < 0)
begin
select @result as 'Result'
set @result = 13
end
set @now = getutcdate()
if (@primitiveDataProperties is null and @complexDataProperties is null and @writeOnlyPrimitiveDataProperties is null and @writeOnlyComplexDataProperties is null)
set @metadataUpdateOnly = 1
LockOrCreateInstance:
if (@result = 0)
begin
exec [System.Activities.DurableInstancing].[LockInstance] @instanceId, @surrogateLockOwnerId, @handleInstanceVersion, @handleIsBoundToLock, @surrogateInstanceId output, @currentInstanceVersion output, @result output
if (@result = 0 and @surrogateInstanceId = 0)
begin
exec [System.Activities.DurableInstancing].[CreateInstance] @instanceId, @surrogateLockOwnerId, @workflowHostType, @serviceDeploymentId, @surrogateInstanceId output, @result output
if (@result = 0 and @surrogateInstanceId = 0)
goto LockOrCreateInstance
set @currentInstanceVersion = 1
end
end
if (@result = 0)
begin
select @enqueueCommand = [EnqueueCommand],
@deleteInstanceOnCompletion = [DeletesInstanceOnCompletion]
from [LockOwnersTable]
where ([SurrogateLockOwnerId] = @surrogateLockOwnerId)
if (@isCompleted = 1 and @deleteInstanceOnCompletion = 1)
begin
exec [System.Activities.DurableInstancing].[DeleteInstance] @surrogateInstanceId
goto Finally
end
update [InstancesTable]
set @instanceId = [InstancesTable].[Id],
@workflowHostType = [WorkflowHostType] =
case when (@workflowHostType is null)
then [WorkflowHostType]
else @workflowHostType
end,
@serviceDeploymentId = [ServiceDeploymentId] =
case when (@serviceDeploymentId is null)
then [ServiceDeploymentId]
else @serviceDeploymentId
end,
@pendingTimer = [PendingTimer] =
case when (@metadataUpdateOnly = 1)
then [PendingTimer]
else [System.Activities.DurableInstancing].[GetExpirationTime](@timerDurationMilliseconds)
end,
@isReadyToRun = [IsReadyToRun] =
case when (@metadataUpdateOnly = 1)
then [IsReadyToRun]
else @isReadyToRun
end,
@isSuspended = [IsSuspended] =
case when (@suspensionStateChange = 0) then [IsSuspended]
when (@suspensionStateChange = 1) then 1
else 0
end,
[SurrogateLockOwnerId] = case when (@unlockInstance = 1 or @isCompleted = 1)
then null
else @surrogateLockOwnerId
end,
[PrimitiveDataProperties] = case when (@metadataUpdateOnly = 1)
then [PrimitiveDataProperties]
else @primitiveDataProperties
end,
[ComplexDataProperties] = case when (@metadataUpdateOnly = 1)
then [ComplexDataProperties]
else @complexDataProperties
end,
[WriteOnlyPrimitiveDataProperties] = case when (@metadataUpdateOnly = 1)
then [WriteOnlyPrimitiveDataProperties]
else @writeOnlyPrimitiveDataProperties
end,
[WriteOnlyComplexDataProperties] = case when (@metadataUpdateOnly = 1)
then [WriteOnlyComplexDataProperties]
else @writeOnlyComplexDataProperties
end,
[MetadataProperties] = case
when (@metadataIsConsistent = 1) then @metadataProperties
else [MetadataProperties]
end,
[SuspensionReason] = case
when (@suspensionStateChange = 0) then [SuspensionReason]
when (@suspensionStateChange = 1) then @suspensionReason
else null
end,
[SuspensionExceptionName] = case
when (@suspensionStateChange = 0) then [SuspensionExceptionName]
when (@suspensionStateChange = 1) then @suspensionExceptionName
else null
end,
[IsCompleted] = @isCompleted,
[IsInitialized] = case
when (@metadataUpdateOnly = 0) then 1
else [IsInitialized]
end,
[DataEncodingOption] = case
when (@metadataUpdateOnly = 0) then @encodingOption
else [DataEncodingOption]
end,
[MetadataEncodingOption] = case
when (@metadataIsConsistent = 1) then @encodingOption
else [MetadataEncodingOption]
end,
[BlockingBookmarks] = case
when (@metadataUpdateOnly = 0) then @blockingBookmarks
else [BlockingBookmarks]
end,
[LastUpdated] = @now,
[LastMachineRunOn] = case
when (@metadataUpdateOnly = 0) then @lastMachineRunOn
else [LastMachineRunOn]
end,
[ExecutionStatus] = case
when (@metadataUpdateOnly = 0) then @executionStatus
else [ExecutionStatus]
end
from [InstancesTable]
where ([InstancesTable].[SurrogateInstanceId] = @surrogateInstanceId)
if (@@rowcount = 0)
begin
set @result = 99
select @result as 'Result'
end
else
begin
if (@keysToAssociate is not null or @singleKeyId is not null)
exec @result = [System.Activities.DurableInstancing].[AssociateKeys] @surrogateInstanceId, @keysToAssociate, @concatenatedKeyProperties, @encodingOption, @singleKeyId
if (@result = 0 and @keysToComplete is not null)
exec @result = [System.Activities.DurableInstancing].[CompleteKeys] @surrogateInstanceId, @keysToComplete
if (@result = 0 and @keysToFree is not null)
exec @result = [System.Activities.DurableInstancing].[FreeKeys] @surrogateInstanceId, @keysToFree
if (@result = 0) and (@metadataUpdateOnly = 0)
begin
delete from [InstancePromotedPropertiesTable]
where [SurrogateInstanceId] = @surrogateInstanceId
end
if (@result = 0)
begin
if (@metadataIsConsistent = 1)
begin
delete from [InstanceMetadataChangesTable]
where [SurrogateInstanceId] = @surrogateInstanceId
end
else if (@metadataProperties is not null)
begin
insert into [InstanceMetadataChangesTable] ([SurrogateInstanceId], [EncodingOption], [Change])
values (@surrogateInstanceId, @encodingOption, @metadataProperties)
end
end
if (@result = 0 and @unlockInstance = 1 and @isCompleted = 0)
exec [System.Activities.DurableInstancing].[InsertRunnableInstanceEntry] @surrogateInstanceId, @workflowHostType, @serviceDeploymentId, @isSuspended, @isReadyToRun, @pendingTimer
end
end
Finally:
if (@result != 13)
exec sp_releaseapplock @Resource = 'InstanceStoreLock'
if (@result = 0)
select @result as 'Result', @currentInstanceVersion
return @result
end
go
January 12, 2012 at 2:03 pm
Windows Workflow Foundation!
I googled a section or two from your stored procedure and thats what it looks like. We should hope Microsoft's product is nice enough not to mess your server up too much!
January 14, 2012 at 4:43 pm
Thanks. It is not so much Microsoft that I am worried about. I am worried that in the hands of well-meaning but ill-equipped developers this could and probably will cause problems. I have googled it and see that it is fairly well documented.
Appreciate the effort.
Chudman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply