I'm working on enviorment SQL azure.
I've got a stored procedure that aim that runs from several AKS simultaneously
and insert a message.
So it's a race for who writes the message first and the rest fails
and the the insert fails due to Unique index and send the Id of the failed message to the service.
here is the stored procedure:
CREATE OR ALTER PROCEDURE Saga.AddMessage
(
@SagaIdentity NVARCHAR(256),
@MessageId NVARCHAR(64),
@SagaMessage NVARCHAR(MAX),
@Created DATETIME2(7),
@Updated DATETIME2(7),
@SubscriptionStatus SMALLINT
)
AS
BEGIN
BEGIN TRY
INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])
SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId);
SELECT [Id] AS Results
FROM [Saga].[SubscribersMessages]
WHERE MessageId = @MessageId
END TRY
BEGIN CATCH
SELECT [Id] AS Results
FROM [Saga].[SubscribersMessages]
WHERE MessageId = @MessageId
END CATCH;
END
and here is the unique index :
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Saga.SubscribersMessages','U') AND name = 'UX_SubscribersMessages_MessageId')
BEGIN
CREATE UNIQUE INDEX UX_SubscribersMessages_MessageId
ON [Saga].[SubscribersMessages] (MessageId);
END
I have a couple of problems with this service:
Peformance issue - I don't like the fact the the process rely CATCH on failed processed to return the Id.
I want to Partition the table using the Created field in order to truncate partition in the future and i get an error -
Column 'Created' is partitioning column of the index 'UX_SubscribersMessages_MessageId'. Partition columns for a unique index must be a subset of the index key.
Here is the execution plan:
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.588" Build="16.0.5506.10218" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0432927" StatementText="INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])
 SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus 
 WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId)" StatementType="INSERT" QueryHash="0x9495ED884EEA5C34" QueryPlanHash="0xB362830815E8CA5B" RetrievedFromCache="true" StatementSqlHandle="0x0900576108E5BB43DB4B85D93C51904339770000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="814625945" StatementParameterizationType="0" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="432">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="566169" EstimatedPagesCached="70771" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2918912" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[55a15ae9-f6f0-4e08-8548-21516f68285e]" Schema="[Saga]" Table="[SubscribersMessages]" Statistics="[UX_SubscribersMessages_MessageId]" ModificationCount="68591" SamplingPercent="0.928381" LastUpdate="2024-05-07T07:13:48.34" />
</OptimizerStatsUsage>
<WaitStats>
<Wait WaitType="PREEMPTIVE_HTTP_REQUEST" WaitTimeMs="15" WaitCount="3" />
<Wait WaitType="PAGEIOLATCH_EX" WaitTimeMs="9" WaitCount="1" />
<Wait WaitType="LOGMGR_FLUSH" WaitTimeMs="4" WaitCount="1" />
</WaitStats>
<QueryTimeStats CpuTime="0" ElapsedTime="30" />
<RelOp AvgRowSize="9" EstimateCPU="4E-06" EstimateIO="0.04" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="0.0432927">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="30" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[Saga_SubscribersMessages]" IndexKind="Clustered" Storage="RowStore" />
<Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
<Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[IX_SubscribersMessages_SagaIdentity]" IndexKind="NonClustered" Storage="RowStore" />
<Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[IX_SubscribersMessages_SubscriptionStatus]" IndexKind="NonClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SagaIdentity] = RaiseIfNullInsert([@SagaIdentity]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[MessageId] = RaiseIfNullInsert([@MessageId]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SagaMessage] = RaiseIfNullInsert([@SagaMessage]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Created] = RaiseIfNullInsert([@Created]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Updated] = [Expr1006],[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SubscriptionStatus] = RaiseIfNullInsert([@SubscriptionStatus]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Id] = [Expr1005]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SagaIdentity" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@SagaIdentity" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@MessageId" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SagaMessage" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@SagaMessage" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Created" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Created" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Updated" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SubscriptionStatus" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@SubscriptionStatus" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00328874">
<OutputList>
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328864">
<OutputList>
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(7),getdate(),0)">
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Convert DataType="datetime2" Scale="7" Style="0" Implicit="true">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328854">
<OutputList>
<ColumnReference Column="Expr1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
</RunTimeInformation>
<ComputeScalar ComputeSequence="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="getidentity((750625717),(22),NULL)">
<Intrinsic FunctionName="getidentity">
<ScalarOperator>
<Const ConstValue="(750625717)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(22)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Anti Semi Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00328844">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="5" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<ConstantScan />
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16349100">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="1" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues />
<Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@MessageId]">
<Identifier>
<ColumnReference Column="@MessageId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@SubscriptionStatus" ParameterDataType="smallint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@Created" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2024-05-07 08:08:32.7700000'" ParameterRuntimeValue="'2024-05-07 08:08:32.7700000'" />
<ColumnReference Column="@SagaMessage" ParameterDataType="nvarchar(max)" ParameterCompiledValue="N'{"RequestId":"ef48ce6c-201e-4c88-a823-cb43cb8cb467","CorrelationId":"f519d43d-7a8b-4833-b393-ab96fe85b0ff","Created":"2023-11-21T14:20:09.9658724Z"}'" ParameterRuntimeValue="N'{"RequestId":"ef48ce6c-201e-4c88-a823-cb43cb8cb467","CorrelationId":"f519d43d-7a8b-4833-b393-ab96fe85b0ff","Created":"2023-11-21T14:20:09.9658724Z"}'" />
<ColumnReference Column="@SagaIdentity" ParameterDataType="nvarchar(256)" ParameterCompiledValue="N'40677e00-5528-4310-bcd1-d1ed19b11758'" ParameterRuntimeValue="N'40677e00-5528-4310-bcd1-d1ed19b11758'" />
<ColumnReference Column="@MessageId" ParameterDataType="nvarchar(64)" ParameterCompiledValue="N'586772d1101b432390126954ced52ed6'" ParameterRuntimeValue="N'586772d1101b432390126954ced52ed6'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="2" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0032831" StatementText="SELECT [Id] AS Results
 FROM [Saga].[SubscribersMessages] 
 WHERE MessageId = @MessageId" StatementType="SELECT" QueryHash="0x65EB6536A27948CF" QueryPlanHash="0x79E4B69DD48411A3" RetrievedFromCache="true" StatementSqlHandle="0x09001BDE820A3F9CBFDE19FD57F0EBDA0E040000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="814625945" StatementParameterizationType="0" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="9" CompileCPU="0" CompileMemory="288">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="566169" EstimatedPagesCached="70771" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2918912" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[55a15ae9-f6f0-4e08-8548-21516f68285e]" Schema="[Saga]" Table="[SubscribersMessages]" Statistics="[UX_SubscribersMessages_MessageId]" ModificationCount="68591" SamplingPercent="0.928381" LastUpdate="2024-05-07T07:13:48.34" />
</OptimizerStatsUsage>
<QueryTimeStats CpuTime="0" ElapsedTime="0" />
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16349100">
<OutputList>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
</DefinedValue>
</DefinedValues>
<Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@MessageId]">
<Identifier>
<ColumnReference Column="@MessageId" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<ParameterList>
<ColumnReference Column="@MessageId" ParameterDataType="nvarchar(64)" ParameterCompiledValue="N'586772d1101b432390126954ced52ed6'" ParameterRuntimeValue="N'586772d1101b432390126954ced52ed6'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
