deadlock-why select hold an updlock?

Question

deadlock-why select hold an updlock?

fby001_wangdui

SSC Enthusiast

Points: 142
More actions
August 31, 2011 at 3:48 am

#390311

<resource-list>
<keylock id="lock8ec3440" associatedObjectId="72057594059882496" mode="U" indexname="PK_QUESTION" objectname="dbo.Question" dbid="6" hobtid="72057594059882496">
<owner-list>
<owner id="processabb018" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="processaba7a8" mode="X" requestType="convert"/>
</waiter-list>
</keylock>
<keylock id="lockcc95040" associatedObjectId="72057594059882496" mode="X" indexname="PK_QUESTION" objectname="dbo.Question" dbid="6" hobtid="72057594059882496">
<owner-list>
<owner id="processaba7a8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processabb018" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

What are the statements after that in the transaction?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Please post the entire deadlock graph in its XML format

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

fby001_wangdui SSC Enthusiast Points: 142 More actions · Answer 3

Thanks for all answers.

the entire deadlock graph in XML format(sql server version: 2005):

<deadlock-list>

<deadlock victim="processabb018">

<process-list>

<process id="processaba7a8" taskpriority="0" logused="59140" waitresource="KEY: 6:72057594059882496 (f10173fc945f)" waittime="8156" ownerId="3209289133"

transactionname="user_transaction" lasttranstarted="2011-08-27T21:00:00.120" XDES="0x65dd0f8" lockMode="X" schedulerid="1" kpid="5980" status="suspended"

spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-08-27T21:00:00.120" lastbatchcompleted="2011-08-27T21:00:00.120"

clientapp="SQLAgent - TSQL JobStep (Job 0xCFAA50DA84CAF4428B812BABDD45B9A8 : Step 1)" hostname="define" hostpid="2904" loginname="NT AUTHORITY\LOCAL SERVICE"

isolationlevel="read committed (2)" xactid="3209289133" currentdb="6" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">

<executionStack>

<frame procname="adhoc" line="63" stmtstart="3282" stmtend="3572" sqlhandle="0x02000000eb861727a24567812425b77f7ed76d7757caebd9">

UPDATE Question SET CommentCount=tC.cCount

from @tCount tC

WHERE Question.IsAutoAdd=100

and Question.QuestionID=tC.questionid </frame>

</executionStack>

<inputbuf>

begin tran;

declare @error int;

declare @t table(userid int,username nvarchar(50),commenttime datetime,questionid nvarchar(50));

insert @t(userid,username,commenttime,questionid)

select userid,username,commenttime,questionid from(

select userid,username,commenttime,questionid from comment c

where questionid in

(

select questionId from question

where CreateTime<getdate()

and IsAutoAdd = 100)

and c.CommentStatus = -1

and commenttime<getdate()

)

allResult

where NOT exists

(select 1 from (

select userid,username,commenttime,questionid from comment d

where questionid in(

select questionId from question

where CreateTime<getdate()

and IsAutoAdd = 100)

and d.CommentStatus = -1

and commenttime<getdate()

) x

where x.questionid=allResult.questionid and x.commenttime> allResult.commenttime)

;

update question set lastcommentuserID=t.userid,

lastcommentuserName=t.username,

LastCommentTime=t.commenttime

from @t t

where question.questionID=t.questionid;

set @error = @error+@@error;

update question set questionstatus=1

where CreateTime<getdate()

and IsAutoAdd=100 and questionstatus=-1

set @error=@error+@@error;

update comment set CommentStatus=0

where CommentTime<getdate()

and IsAutoAdd=100 and CommentStatus=-1

set @error=@error+@@error;

declare @tCount table(questionid nvarchar(50),cCount int);

insert @tCount(questionid,cCount)

SELECT questionid,count(CommentID) cCount from Comment

where 1=1

--and IsAutoAdd=100

AND CommentTime BETWEEN '2011-08-08' and getdate()

GROUP by QuestionID

UPDATE Question SET CommentCount=tC.cCount

from @tCount tC

WHERE Question.IsAutoAdd=100

and Question.QuestionID=tC.questionid

set @error = @error+@@error;

update Comment set IsBestComment=1

where CommentID in

(

select BestCommentID from QuestionBestComment

where DATEADD(day,7,OperateTime)<getdate()

)

AND IsBestComment<>1

set @error=@error+@@error;

update Question set OperateStatus=2

where isautoadd=100

and questionid in (

select questionid from QuestionBestComment

where DATEADD(day,7,OperateTime)<getdate()

)

set @error=@error+@@error;

delete from QuestionBestComment

where BestCommentID IN (

SELECT commentid FROM comment where isautoadd=100 and isbestcomment=1

)

set @error=@error+@@error;

if(@error<>0)

begin

rollback tran;

end

commit tran;

</inputbuf>

</process>

<process id="processabb018" taskpriority="0" logused="0" waitresource="KEY: 6:72057594059882496 (2502665bf679)" waittime="10156" ownerId="3209290114"

transactionname="SELECT" lasttranstarted="2011-08-27T21:00:02.417" XDES="0xe4d9bf0" lockMode="S" schedulerid="1" kpid="5968" status="suspended" spid="95"

sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2011-08-27T21:00:02.417" lastbatchcompleted="2011-08-27T21:00:02.417" clientapp=".Net

SqlClient Data Provider" hostname="B4601B57" hostpid="4024" loginname="tail" isolationlevel="read committed (2)" xactid="3209290114" currentdb="6"

lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

<executionStack>

<frame procname="adhoc" line="1" stmtstart="116" stmtend="432" sqlhandle="0x0200000097437107b469d1fbb95c6d0e09fa43564377b685">

SELECT TOP (@PageSize) *

FROM [Question]

WHERE [OperateStatus] = @OperateStatus11 AND [QuestionStatus] >= @QuestionStatus15

ORDER BY CreateTime DESC; </frame>

<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

(@OperateStatus11 int,@QuestionStatus15 int,@PageSize int)SELECT TOP (@PageSize) *

FROM [Question]

WHERE [OperateStatus] = @OperateStatus11 AND [QuestionStatus] >= @QuestionStatus15

ORDER BY CreateTime DESC;

SELECT COUNT(*) AS TotalCount

FROM [Question]

WHERE [OperateStatus] = @OperateStatus11 AND [QuestionStatus] >= @QuestionStatus15 </inputbuf>

</process>

</process-list>

<resource-list>

<keylock hobtid="72057594059882496" dbid="6" objectname="dbo.Question" indexname="PK_QUESTION" id="lock8ec3440" mode="U"

associatedObjectId="72057594059882496">

<owner-list>

<owner id="processabb018" mode="S"/>

</owner-list>

<waiter-list>

<waiter id="processaba7a8" mode="X" requestType="convert"/>

</waiter-list>

</keylock>

<keylock hobtid="72057594059882496" dbid="6" objectname="dbo.Question" indexname="PK_QUESTION" id="lockcc95040" mode="X"

associatedObjectId="72057594059882496">

<owner-list>

<owner id="processaba7a8" mode="X"/>

</owner-list>

<waiter-list>

<waiter id="processabb018" mode="S" requestType="wait"/>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

the update statements plan in XML format:

<Statements>

<StmtSimple StatementCompId="12" StatementEstRows="1" StatementId="10" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0679375" StatementText=" UPDATE Question SET CommentCount=tC.cCount from @tCount tC WHERE Question.IsAutoAdd=100 and Question.QuestionID=tC.questionid " StatementType="UPDATE" QueryHash="0xD7E83BFD427A384E" QueryPlanHash="0x209ECC223AD6F46E">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan CachedPlanSize="40" CompileTime="13" CompileCPU="13" CompileMemory="448">

<RelOp AvgRowSize="9" EstimateCPU="5E-06" EstimateIO="0.05" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0679375">

<OutputList />

<Update DMLRequestSort="false">

<Object Database="[definedb]" Schema="[dbo]" Table="[Question]" Index="[PK_QUESTION]" IndexKind="Clustered" />

<Object Database="[definedb]" Schema="[dbo]" Table="[Question]" Index="[idx_AnswerTime_QuestionStatus]" IndexKind="NonClustered" />

<Object Database="[definedb]" Schema="[dbo]" Table="[Question]" Index="[idx_QuestionStatus_Age]" IndexKind="NonClustered" />

<Object Database="[definedb]" Schema="[dbo]" Table="[Question]" Index="[idx_LastCommentTime_QuestionStatus_ExpertID]" IndexKind="NonClustered" />

<Object Database="[definedb]" Schema="[dbo]" Table="[Question]" Index="[_dta_index_Question_6_757577737__K12D_K1_K33_K13_K8_2_3_4_5_6_7_9_11_14_15_16_17_19_22_23_24_25_26_27_28_29_30_31_32]" IndexKind="NonClustered" />

<SetPredicate>

<ScalarOperator ScalarString="[definedb].[dbo].[Question].[CommentCount] = RaiseIfNullUpdate(@tCount.[cCount] as [tC].[cCount])">

<ScalarExpressionList>

<ScalarOperator>

<MultipleAssign>

<Assign>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

<ScalarOperator>

<Intrinsic FunctionName="RaiseIfNullUpdate">

<ScalarOperator>

<Identifier>

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Assign>

</MultipleAssign>

</ScalarOperator>

</ScalarExpressionList>

</ScalarOperator>

</SetPredicate>

<RelOp AvgRowSize="70" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0179325">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

<ColumnReference Column="Expr1012" />

<ColumnReference Column="Expr1013" />

<ColumnReference Column="Expr1014" />

<ColumnReference Column="Expr1015" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1013" />

<ScalarOperator ScalarString="[Expr1013]">

<Identifier>

<ColumnReference Column="Expr1013" />

</Identifier>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1014" />

<ScalarOperator ScalarString="[Expr1014]">

<Identifier>

<ColumnReference Column="Expr1014" />

</Identifier>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1012" />

<ScalarOperator ScalarString="[Expr1012]">

<Identifier>

<ColumnReference Column="Expr1012" />

</Identifier>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1015" />

<ScalarOperator ScalarString="[Expr1015]">

<Identifier>

<ColumnReference Column="Expr1015" />

</Identifier>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="70" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0179325">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

<ColumnReference Column="Expr1012" />

<ColumnReference Column="Expr1013" />

<ColumnReference Column="Expr1014" />

<ColumnReference Column="Expr1015" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1012" />

<ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1009" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1013" />

<ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1009" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1014" />

<ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1009" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Expr1015" />

<ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END">

<IF>

<Condition>

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1009" />

</Identifier>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="55" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0179324">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

<ColumnReference Column="Expr1009" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1009" />

<ScalarOperator ScalarString="CASE WHEN [definedb].[dbo].[Question].[CommentCount] = @tCount.[cCount] as [tC].[cCount] THEN (1) ELSE (0) END">

<IF>

<Condition>

<ScalarOperator>

<Compare CompareOp="BINARY IS">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

<ScalarOperator>

<Const ConstValue="(1)" />

</ScalarOperator>

</Then>

<Else>

<ScalarOperator>

<Const ConstValue="(0)" />

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="58" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0179323">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</OutputList>

<Top RowCount="true" IsPercent="false" WithTies="false">

<TopExpression>

<ScalarOperator ScalarString="(0)">

<Const ConstValue="(0)" />

</ScalarOperator>

</TopExpression>

<RelOp AvgRowSize="58" EstimateCPU="0.000100058" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0179322">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</OutputList>

<MemoryFractions Input="0.5" Output="1" />

<Sort Distinct="true">

<OrderBy>

<OrderByColumn Ascending="true">

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

</OrderByColumn>

</OrderBy>

<RelOp AvgRowSize="58" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657086">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</OutputList>

<NestedLoops Optimized="true">

<OuterReferences>

<ColumnReference Table="@tCount" Alias="[tC]" Column="questionid" />

</OuterReferences>

<RelOp AvgRowSize="65" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="9" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">

<OutputList>

<ColumnReference Table="@tCount" Alias="[tC]" Column="questionid" />

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</OutputList>

<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Table="@tCount" Alias="[tC]" Column="questionid" />

</DefinedValue>

<DefinedValue>

<ColumnReference Table="@tCount" Alias="[tC]" Column="cCount" />

</DefinedValue>

</DefinedValues>

<Object Table="[@tCount]" Alias="[tC]" />

</TableScan>

</RelOp>

<RelOp AvgRowSize="58" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="511635">

<OutputList>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

</OutputList>

<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="CommentCount" />

</DefinedValue>

</DefinedValues>

<Object Database="[definedb]" Schema="[dbo]" Table="[Question]" Index="[PK_QUESTION]" IndexKind="Clustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="@tCount.[questionid] as [tC].[questionid]">

<Identifier>

<ColumnReference Table="@tCount" Alias="[tC]" Column="questionid" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<Predicate>

<ScalarOperator ScalarString="[definedb].[dbo].[Question].[IsAutoAdd]=(100)">

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[definedb]" Schema="[dbo]" Table="[Question]" Column="IsAutoAdd" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="(100)" />

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Sort>

</RelOp>

</Top>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</ComputeScalar>

</RelOp>

</Update>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

the select statements plan in XML format:

<?xml version="1.0" encoding="utf-8"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4.59596" StatementText="SELECT TOP (20) * FROM [Question] WHERE [OperateStatus] = 0 AND [QuestionStatus] >= 0 ORDER BY CreateTime DESC;" StatementType="SELECT" QueryHash="0xEB7408CCB06AFD07" QueryPlanHash="0x4D9F6C32343E8687">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan CachedPlanSize="56" CompileTime="3" CompileCPU="3" CompileMemory="224">

<RelOp AvgRowSize="1280" EstimateCPU="0.0230141" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="4.59596">

<OutputList>

... some ColumnReference nodes

</OutputList>

<MemoryFractions Input="0.030303" Output="1" />

<TopSort Distinct="false" Rows="20">

<OrderBy>

<OrderByColumn Ascending="false">

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="CreateTime" />

</OrderByColumn>

</OrderBy>

<RelOp AvgRowSize="1280" EstimateCPU="0.00587167" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1404.71" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.56168">

<OutputList>

... some ColumnReference nodes

</OutputList>

<NestedLoops Optimized="true" WithUnorderedPrefetch="true">

<OuterReferences>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Column="Expr1003" />

</OuterReferences>

<RelOp AvgRowSize="62" EstimateCPU="0.00170218" EstimateIO="0.0109485" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1404.71" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0126507" TableCardinality="511635">

<OutputList>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="Age" />

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionStatus" />

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="OperateStatus" />

</OutputList>

<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="Age" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionStatus" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="OperateStatus" />

</DefinedValue>

</DefinedValues>

<Object Database="definedb" Schema="[dbo]" Table="[Question]" Index="[idx_OperateStatus_QuestionStatus]" IndexKind="NonClustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="OperateStatus" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="(0)">

<Const ConstValue="(0)" />

</ScalarOperator>

</RangeExpressions>

</Prefix>

<StartRange ScanType="GE">

<RangeColumns>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionStatus" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="(0)">

<Const ConstValue="(0)" />

</ScalarOperator>

</RangeExpressions>

</StartRange>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

<RelOp AvgRowSize="1644" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="1403.71" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="4.54316" TableCardinality="511635">

<OutputList>

... some ColumnReference nodes

</OutputList>

<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="ExpertID" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="UserName" />

</DefinedValue>

<DefinedValue>

...

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="IsBestQuestion" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="SetBestTime" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="IsCase" />

</DefinedValue>

</DefinedValues>

<Object Database="definedb" Schema="[dbo]" Table="[Question]" Index="[PK_QUESTION]" TableReferenceId="-1" IndexKind="Clustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="definedb.[dbo].[Question].[QuestionID]">

<Identifier>

<ColumnReference Database="definedb" Schema="[dbo]" Table="[Question]" Column="QuestionID" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</TopSort>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>