SQL Server – Troubleshooting Slow Performance on Update

explainsql serverupdate

I would like to ask your help. I have very slow update statements on this tables even if the table is only a basic table. I would like to know the reason for the slow update even if what I'm throwing is only a simple update.

update "NO_FORM_INV_DTL" set "VERSION" = @P0 , "STATUS" = @P1 , "UPDATED_DT" = @P2 where "ID" = @P3

There's a clustered index on ID.

Please advise what data you need. I'm running on SQL Server 2014. The update statement is really slow. I don't get it. Is it because of the implicit convert ? I pasted the explain plan below.

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.4100.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="(@P0 int,@P1 int,@P2 nvarchar(4000),@P3 datetime,@P4 nvarchar(4000),@P5 nvarchar(4000))update &quot;NO_FORM_INV_DTL&quot; set &quot;VERSION&quot; =  @P0 , &quot;STATUS&quot; =  @P1 , &quot;BOX_CD&quot; =  @P2 , &quot;UPDATED_DT&quot; =  @P3 , &quot;NO_FORM_TRANSFER_ID&quot; =  @P4  where &quot;ID&quot; =  @P5" StatementId="1" StatementCompId="1" StatementType="UPDATE" RetrievedFromCache="true" StatementSubTreeCost="15.4273" StatementEstRows="1" StatementOptmLevel="TRIVIAL" QueryHash="0xEF1E1FC6A7C07838" QueryPlanHash="0x49324E4A1CCCBF9" CardinalityEstimationModelVersion="70"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /><QueryPlan CachedPlanSize="32" CompileTime="2" CompileCPU="2" CompileMemory="352"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(32),[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[ID],0)" /><PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(32),[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[ID],0)=[@P5]" /></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="314572" EstimatedPagesCached="314572" EstimatedAvailableDegreeOfParallelism="8" /><RelOp NodeId="0" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="15.4273" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList /><Update DMLRequestSort="0"><Object Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Index="[PK_NO_FORM_INV_DTL_ID]" IndexKind="Clustered" Storage="RowStore" /><SetPredicate><ScalarOperator ScalarString="[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[VERSION] = RaiseIfNullUpdate([@P0]),[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[STATUS] = RaiseIfNullUpdate([@P1]),[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[BOX_CD] = [Expr1002],[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[UPDATED_DT] = [@P3],[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[NO_FORM_TRANSFER_ID] = [Expr1003]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="VERSION" /><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Column="@P0" /></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="STATUS" /><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Column="@P1" /></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="BOX_CD" /><ScalarOperator><Identifier><ColumnReference Column="Expr1002" /></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="UPDATED_DT" /><ScalarOperator><Identifier><ColumnReference Column="@P3" /></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="NO_FORM_TRANSFER_ID" /><ScalarOperator><Identifier><ColumnReference Column="Expr1003" /></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="68" EstimatedTotalSubtreeCost="15.4173" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="ID" /><ColumnReference Column="Expr1002" /><ColumnReference Column="Expr1003" /></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002" /><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(10),[@P2],0)"><Identifier><ColumnReference Column="ConstExpr1011"><ScalarOperator><Convert DataType="varchar" Length="10" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@P2" /></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1003" /><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(32),[@P4],0)"><Identifier><ColumnReference Column="ConstExpr1012"><ScalarOperator><Convert DataType="varchar" Length="32" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@P4" /></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimateIO="13.6239" EstimateCPU="1.54126" AvgRowSize="43" EstimatedTotalSubtreeCost="15.1651" TableCardinality="1.401e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="ID" /></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="ID" /></DefinedValue></DefinedValues><Object Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Index="[IX_NO_FORM_INV_DTL]" IndexKind="NonClustered" Storage="RowStore" /><Predicate><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(32),[test-prd-tester-main].[dbo].[NO_FORM_INV_DTL].[ID],0)=[@P5]"><Compare CompareOp="EQ"><ScalarOperator><Convert DataType="nvarchar" Length="64" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[test-prd-tester-main]" Schema="[dbo]" Table="[NO_FORM_INV_DTL]" Column="ID" /></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@P5" /></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Just to add

(No column name)    execution_count total_logical_reads last_logical_reads  total_logical_writes    last_logical_writes total_worker_time   last_worker_time    Total_elapsed_time_Secs Last_elapsed_time_Secs  last_execution_time query_plan      



update "NO_FORM_INV_DTL" set "VERSION" =  @P0 , "STATUS" =  @P1 , "BOX_CD" =  @P2 , "UPDATED_DT" =  @P3 , "NO_FORM_TRANSFER_ID" =  @P4  where "ID" =  @P5   12106   225213777   18625   3493    0   12603012772 1061575 100328  11  2016-10-10 15:48:36.940
update "NO_FORM_INV_DTL" set "VERSION" =  @P0 , "STATUS" =  @P1 , "UPDATED_DT" =  @P2  where "ID" =  @P3    10938   203491896   18630   3423    1   11436325822 1058915 91826   11  2016-10-10 15:48:34.207

The size of the table is around 2 million records.
This should be a simple update. Don't know why it is slow.

Here is how the table looks like

enter image description here

Best Answer

You've got some code, somewhere, converting your ID to an NVARCHAR(32) field. I don't know how or why this is happening, but that's causing it to ignore your index, resulting in a full scan instead of a seek.

plan diagram

Hovering over the warning on the UPDATE, we see this:

UPDATE Warning

There are two ways to fix this.

  • The column is a VARCHAR and your code is generating NVARCHAR. This means you probably have an ORM generating the code, and there might be a switch to fix it.
  • Otherwise, changing the column to NVARCHAR will work for this use case, but NVARCHAR (and NCHAR) are Unicode data types, which use twice as much disk space as VARCHAR and CHAR to store data.

Choose wisely.

Related Question