SQL Server Performance

Why does this SQL slow down or hang?????

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by ryanwatton, Jul 26, 2006.

  1. ryanwatton New Member

    I'm trying to provide as much information as possible so here is goes: SQL 2005 SP1 + June Hotfixes, Win2003 SP1 with latest Hotfixes. Machine is AMD Opteron with 4GB mem.
    I have some SQL I'm trying to run in a highly normalized table structure. Most of the time it runs ok (2 to 5 mintues), then sometimes it takes hours, and sometimes it runs over 15+ hours before I end up killing the process (seems to be hung with no indication). The entity table might contain 3 million records and the attribute table usually will contain about 15 million.
    There isn't any locking/blocking going on. The files are not growing since enough space is allocated. Is this a case of SQL Server tripping over itself? Are index pages being locked while it's trying to update itself? If so, I can't see it in the Activity monitor. Is it not able to update a table while referencing itself several times?

    I thought I got around this problem by changing the update to a select into #temptable, then using the temp table to update the attribute table, but it still happened. I finally added another filegroup and put all the nonclustered indexes on the new filegroup then it stopped (I'm still using the temp tables also).

    Can some tell me why this is happening? Questions? Comments? I'm just looking for a better understanding of SQL 2005.

    Query description: I'm trying to find duplicates based on an entity type and two attributes that match. There could be multiple matches so I want to set the new_eid to the first one I find. If there is a better way, please let me know. I'm dealing with large amounts of data at a time 500MB and speed is most important!

    CREATE TABLE ENTITY(
    ID bigint NOT NULL,
    TYPE varchar(100),
    CONSTRAINT PK_STAGE_ENTITY PRIMARY KEY CLUSTERED(ID ASC))

    CREATE TABLE ATTRIBUTE(
    ID bigint NOT NULL,
    EID bigint NOT NULL,
    TYPE varchar(100),
    VALUE varchar(255),
    new_eid int NULL,
    CONSTRAINT PK_STAGE_STRINGATTRIBUTE PRIMARY KEY CLUSTERED
    (ID ASC))

    CREATE NONCLUSTERED INDEX ix_se_type ON ENTITY (Type);
    CREATE NONCLUSTERED INDEX ix_ss_eid ON ATTRIBUTE (EID ASC);
    CREATE NONCLUSTERED INDEX ix_type_value ON ATTRIBUTE (TYPE ASC,VALUE ASC);
    CREATE NONCLUSTERED INDEX ix_new_eid ON ATTRIBUTE (NEW_EID ASC);

    Update b set b.new_eid = a.eid
    from attribute a
    inner join entity se on a.eid = se.id
    inner join attribute b on (a.type = b.type and a.value = b.value and a.id <> b.id and a.eid <> b.eid)
    where se.type = 'human' and a.type = 'name' and exists
    (select 1 from attribute y
    inner join entity x on y.eid = x.id
    inner join attribute z on (y.type = z.type and y.value = z.value and y.id <> z.id and y.eid <> z.eid)
    where x.type = 'human' and y.type = 'dob' and y.eid = a.eid and z.eid = b.eid)
    and a.id in (select min(a1.id) from attribute a1
    inner join entity se1 on a1.eid = se1.id
    inner join attribute b1 on (a1.type = b1.type and a1.value = b1.value and a1.id <> b1.id and a1.eid <> b1.eid)
    where se1.type = 'human' and a1.type = 'name' and exists
    (select 1 from attribute y1
    inner join entity x1 on y1.eid = x1.id
    inner join attribute z1 on (y1.type = z1.type and y1.value = z1.value and y1.id <> z1.id and y1.eid <> z1.eid)
    where x1.type = 'human' and y1.type = 'dob' and y1.eid = a1.eid and z1.eid = b1.eid)
    group by a1.value)

    Regards,
    Ryan
  2. merrillaldrich New Member

    It seems like there must be a simpler update statement for this. Can you post a few rows of sample data? Because I am slightly confused about what exactly you are defining as a duplicate. Specifically, why are the "exists" clauses there? What do they do?
  3. ryanwatton New Member

    Sample data:

    Insert into entity (1,'human')
    Insert into entity (2,'human')
    Insert into entity (3,'human')
    Insert into entity (4,'human')
    Insert into entity (5,'animal')

    Insert into attribute (1,1,'name','John Smith', NULL)
    Insert into attribute (2,1,'dob', '01/01/1970', NULL)
    Insert into attribute (3,2,'name', 'Kevin Smith', NULL)
    Insert into attribute (4,2,'dob', '02/02/1970', NULL)
    Insert into attribute (5,3,'name', 'John Smith', NULL)
    Insert into attribute (6,3,'dob', '01/01/1970', NULL)
    Insert into attribute (7,4,'name','John Smith', NULL)
    Insert into attribute (8,4,'dob','01/01/1970', NULL)
    Insert into attriubte (9,4,'pob','USA', NULL)
    Insert into attribute (10,5,'name','Fido',NULL)

    Does this make more sense? I'm trying to compress all the duplicates into one.
    There are different entities and multiple attributes for each entity.

    Ryan
  4. joechang New Member

    is there a large difference in affected row count from run to run?
    on each run, compared the estimated row count to the actual row count, look for large discrepancies

    is there disk activity when the update takes a long time?
    to which files?
    use fn_filestats or whatever its called

    what the is size of the tempdb while this is running?

    described the estimated execution plan
    or better yet, since you are on 2005,
    save the execution plan in xml, ie, .sqlplan

    paste the content in here, those with 2005 can look at the plan
  5. ryanwatton New Member

    There is disk activity mainly in the file I hold the indexes.<br /><br />Query plan:<br /><br />&lt;?xml version="1.0" encoding="utf-16"?&gt;<br />&lt;ShowPlanXML xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />si="http://www.w3.org/2001/XMLSchema-instance" xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />sd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2153.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;<br /> &lt;BatchSequence&gt;<br /> &lt;Batch&gt;<br /> &lt;Statements&gt;<br /> &lt;StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="25.0896" StatementText=" Update b set b.new_eid = a.eid#xD;#xA;from attribute a#xD;#xA;inner join entity se on a.eid = se.id#xD;#xA;inner join attribute b on (a.type = b.type and a.value = b.value and a.id &lt;&gt; b.id and a.eid &lt;&gt; b.eid)#xD;#xA;where se.type = 'human' and a.type = 'name' and exists#xD;#xA;(select 1 from attribute y#xD;#xA;inner join entity x on y.eid = x.id#xD;#xA;inner join attribute z on (y.type = z.type and y.value = z.value and y.id &lt;&gt; z.id and y.eid &lt;&gt; z.eid)#xD;#xA;where x.type = 'human' and y.type = 'dob' and y.eid = a.eid and z.eid = b.eid)#xD;#xA;and a.id in (select min(a1.id) from attribute a1#xD;#xA;inner join entity se1 on a1.eid = se1.id#xD;#xA;inner join attribute b1 on (a1.type = b1.type and a1.value = b1.value and a1.id &lt;&gt; b1.id and a1.eid &lt;&gt; b1.eid)#xD;#xA;where se1.type = 'human' and a1.type = 'name' and exists#xD;#xA;(select 1 from attribute y1#xD;#xA;inner join entity x1 on y1.eid = x1.id#xD;#xA;inner join attribute z1 on (y1.type = z1.type and y1.value = z1.value and y1.id &lt;&gt; z1.id and y1.eid &lt;&gt; z1.eid)#xD;#xA;where x1.type = 'human' and y1.type = 'dob' and y1.eid = a1.eid and z1.eid = b1.eid)#xD;#xA;group by a1.value)" StatementType="UPDATE"&gt;<br /> &lt;StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /&gt;<br /> &lt;QueryPlan CachedPlanSize="120"&gt;<br /> &lt;RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="25.0896"&gt;<br /> &lt;OutputList /&gt;<br /> &lt;Update&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" /&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_new_eid]" /&gt;<br /> &lt;SetPredicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[new_eid] = [Expr1027]"&gt;<br /> &lt;ScalarExpressionList /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/SetPredicate&gt;<br /> &lt;RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;ColumnReference Column="Expr1034" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1034" /&gt;<br /> &lt;ScalarOperator ScalarString="[Expr1034]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Expr1034" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;ColumnReference Column="Expr1034" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1034" /&gt;<br /> &lt;ScalarOperator ScalarString="CASE WHEN [Expr1028] THEN (1) ELSE (0) END"&gt;<br /> &lt;IF&gt;<br /> &lt;Condition&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Expr1028" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Condition&gt;<br /> &lt;Then&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="(1)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Then&gt;<br /> &lt;Else&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="(0)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Else&gt;<br /> &lt;/IF&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;ColumnReference Column="Expr1028" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1028" /&gt;<br /> &lt;ScalarOperator ScalarString="CASE WHEN [Stage].[dbo].[ATTRIBUTE].[new_eid] as <b>.[new_eid] = [Expr1027] THEN (1) ELSE (0) END"&gt;<br /> &lt;IF&gt;<br /> &lt;Condition&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="BINARY IS"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Condition&gt;<br /> &lt;Then&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="(1)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Then&gt;<br /> &lt;Else&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="(0)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Else&gt;<br /> &lt;/IF&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="25.0696"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Top RowCount="true" IsPercent="false" WithTies="false"&gt;<br /> &lt;TopExpression&gt;<br /> &lt;ScalarOperator ScalarString="(0)"&gt;<br /> &lt;Const ConstValue="(0)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/TopExpression&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;ComputeScalar&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1027" /&gt;<br /> &lt;ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID],0)"&gt;<br /> &lt;Convert DataType="int" Style="0" Implicit="true"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Convert&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;RelOp AvgRowSize="27" EstimateCPU="0.000100027" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="25.0696"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0.5" Output="1" /&gt;<br /> &lt;Sort Distinct="true"&gt;<br /> &lt;OrderBy&gt;<br /> &lt;OrderByColumn Ascending="true"&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/OrderByColumn&gt;<br /> &lt;/OrderBy&gt;<br /> &lt;RelOp AvgRowSize="27" EstimateCPU="4.49054E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0582"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="43" EstimateCPU="5.1566E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Filter" NodeId="9" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="25.0417"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Filter StartupExpression="false"&gt;<br /> &lt;RelOp AvgRowSize="43" EstimateCPU="4.49054E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0417"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="31" EstimateCPU="4.49054E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0383"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.035"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0317"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Column="Expr1026" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.000100015" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="15" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="25.0285"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Expr1026" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0.5" Output="0.5" /&gt;<br /> &lt;Sort Distinct="true"&gt;<br /> &lt;OrderBy&gt;<br /> &lt;OrderByColumn Ascending="true"&gt;<br /> &lt;ColumnReference Column="Expr1026" /&gt;<br /> &lt;/OrderByColumn&gt;<br /> &lt;/OrderBy&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="16" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="25.0171"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Column="Expr1026" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;StreamAggregate&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Column="Expr1026" /&gt;<br /> &lt;ScalarOperator ScalarString="MIN([Stage].[dbo].[ATTRIBUTE].[ID] as [a1].[ID])"&gt;<br /> &lt;Aggregate AggType="MIN" Distinct="false"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Aggregate&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;GroupBy&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/GroupBy&gt;<br /> &lt;RelOp AvgRowSize="36" EstimateCPU="0.000100036" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="17" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="25.0171"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0.0047619" Output="0.5" /&gt;<br /> &lt;Sort Distinct="false"&gt;<br /> &lt;OrderBy&gt;<br /> &lt;OrderByColumn Ascending="true"&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/OrderByColumn&gt;<br /> &lt;/OrderBy&gt;<br /> &lt;RelOp AvgRowSize="36" EstimateCPU="16.5087" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Right Semi Join" NodeId="18" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="25.0057"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0" Output="0" /&gt;<br /> &lt;Hash&gt;<br /> &lt;DefinedValues /&gt;<br /> &lt;HashKeysBuild&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/HashKeysBuild&gt;<br /> &lt;HashKeysProbe&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /&gt;<br /> &lt;/HashKeysProbe&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />robeResidual&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y1].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as [a1].[EID] AND [Stage].[dbo].[ATTRIBUTE].[EID] as [z1].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as [b1].[EID]"&gt;<br /> &lt;Logical Operation="AND"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Logical&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/ProbeResidual&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="19" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0164332"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="20" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0131459"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Filter StartupExpression="false"&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="21" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131454"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="22" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985814"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="24" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[y1]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="TYPE" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'dob'"&gt;<br /> &lt;Const ConstValue="'dob'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[y1]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y1].[ID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="30" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[z1]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="TYPE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="VALUE" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'dob'"&gt;<br /> &lt;Const ConstValue="'dob'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [y1].[VALUE]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y1].[ID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[ID] as [z1].[ID]"&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="32" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[z1]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [z1].[ID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y1].[EID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[EID] as [z1].[EID]"&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/Filter&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="37" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList /&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues /&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[ix_se_type]" Alias="[x1]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x1]" Column="TYPE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x1]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'human'"&gt;<br /> &lt;Const ConstValue="'human'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y1].[EID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="60" EstimateCPU="6.25028" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2523200" LogicalOp="Inner Join" NodeId="38" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="8.48064"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="0.776952" Output="0.995238" /&gt;<br /> &lt;Hash&gt;<br /> &lt;DefinedValues /&gt;<br /> &lt;HashKeysBuild&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/HashKeysBuild&gt;<br /> &lt;HashKeysProbe&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /&gt;<br /> &lt;/HashKeysProbe&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />robeResidual&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [a1].[VALUE]=[Stage].[dbo].[ATTRIBUTE].[VALUE] as [b1].[VALUE] AND [Stage].[dbo].[ATTRIBUTE].[ID] as [a1].[ID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[ID] as [b1].[ID] AND [Stage].[dbo].[ATTRIBUTE].[EID] as [a1].[EID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[EID] as [b1].[EID]"&gt;<br /> &lt;Logical Operation="AND"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Logical&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/ProbeResidual&gt;<br /> &lt;RelOp AvgRowSize="44" EstimateCPU="0.362119" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="17669.8" LogicalOp="Inner Join" NodeId="39" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.31272"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;MemoryFractions Input="1" Output="0.223048" /&gt;<br /> &lt;Hash&gt;<br /> &lt;DefinedValues /&gt;<br /> &lt;HashKeysBuild&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /&gt;<br /> &lt;/HashKeysBuild&gt;<br /> &lt;HashKeysProbe&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;/HashKeysProbe&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />robeResidual&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a1].[EID]=[Stage].[dbo].[ENTITY].[ID] as [se1].[ID]"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/ProbeResidual&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0097853" EstimateIO="0.0231883" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8753" LogicalOp="Index Seek" NodeId="40" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0329736"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[ix_se_type]" Alias="[se1]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="TYPE" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'human'"&gt;<br /> &lt;Const ConstValue="'human'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="53" EstimateCPU="0.142678" EstimateIO="0.712755" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27481" LogicalOp="Clustered Index Scan" NodeId="41" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.855433"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[a1]" TableReferenceId="-1" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[TYPE] as [a1].[TYPE]='name'"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="TYPE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="'name'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Hash&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="53" EstimateCPU="0.142678" EstimateIO="0.712755" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27481" LogicalOp="Clustered Index Scan" NodeId="43" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.855433"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[b1]" TableReferenceId="-1" /&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[TYPE] as [b1].[TYPE]='name'"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="TYPE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="'name'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Hash&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Hash&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Sort&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/StreamAggregate&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Sort&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="53" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="49" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="TYPE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="TYPE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[a]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Expr1026]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Column="Expr1026" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[TYPE] as [a].[TYPE]='name'"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="TYPE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="'name'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="51" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList /&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues /&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[ix_se_type]" Alias="[se]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se]" Column="TYPE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'human'"&gt;<br /> &lt;Const ConstValue="'human'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.000158182" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Index Seek" NodeId="52" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328318"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="<b>" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="TYPE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="VALUE" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'name'"&gt;<br /> &lt;Const ConstValue="'name'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [a].[VALUE]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [a].[ID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[ID] as <b>.[ID]"&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="19" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="54" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00333661"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="new_eid" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="<b>" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as <b>.[ID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[EID] as <b>.[EID]"&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/Filter&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="61" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0165781"&gt;<br /> &lt;OutputList /&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;RelOp AvgRowSize="18" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="62" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00333647"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="TYPE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="TYPE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[PK_STAGE_ENTITY]" Alias="[x]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ENTITY].[TYPE] as [x].[TYPE]='human'"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="TYPE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Const ConstValue="'human'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="9" EstimateCPU="8.8E-07" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="63" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0132366"&gt;<br /> &lt;OutputList /&gt;<br /> &lt;Filter StartupExpression="false"&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="64" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0132357"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="65" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00989455"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="44" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="66" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0065947"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Filter StartupExpression="false"&gt;<br /> &lt;RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.0742907" EstimateRows="1" LogicalOp="Inner Join" NodeId="67" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00659418"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;NestedLoops Optimized="false"&gt;<br /> &lt;OuterReferences&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;/OuterReferences&gt;<br /> &lt;RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0.0742907" EstimateRows="1" LogicalOp="Index Seek" NodeId="68" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329485"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[y]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="TYPE" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'dob'"&gt;<br /> &lt;Const ConstValue="'dob'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0.0742907" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="70" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00329485"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[y]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y].[ID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]"&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/Filter&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="75" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329485"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[z]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="TYPE" /&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="VALUE" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="'dob'"&gt;<br /> &lt;Const ConstValue="'dob'" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [y].[VALUE]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y].[ID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[ID] as [z].[ID]"&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="77" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00333661"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[z]" TableReferenceId="-1" /&gt;<br /> &lt;SeekPredicates&gt;<br /> &lt;SeekPredicate&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />refix ScanType="EQ"&gt;<br /> &lt;RangeColumns&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/RangeColumns&gt;<br /> &lt;RangeExpressions&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [z].[ID]"&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/RangeExpressions&gt;<br /> &lt;/Prefix&gt;<br /> &lt;/SeekPredicate&gt;<br /> &lt;/SeekPredicates&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />redicate&gt;<br /> &lt;ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y].[EID]&lt;&gt;[Stage].[dbo].[ATTRIBUTE].[EID] as [z].[EID] AND [Stage].[dbo].[ATTRIBUTE].[EID] as [z].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as <b>.[EID]"&gt;<br /> &lt;Logical Operation="AND"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="NE"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Compare CompareOp="EQ"&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;ScalarOperator&gt;<br /> &lt;Identifier&gt;<br /> &lt;ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="<b>" Column="EID" /&gt;<br /> &lt;/Identifier&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Compare&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Logical&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/Predicate&gt;<br /> &lt;/Filter&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/NestedLoops&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Sort&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Top&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/ComputeScalar&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Update&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/QueryPlan&gt;<br /> &lt;/StmtSimple&gt;<br /> &lt;/Statements&gt;<br /> &lt;/Batch&gt;<br /> &lt;/BatchSequence&gt;<br />&lt;/ShowPlanXML&gt;
  6. ryanwatton New Member

    There was a problem with the SQL in the first post. I have updated it so it will create the tables correctly.

    Also, here is output from sp_lock while the slow-down is occurring. 13 is the db and 2 is tempdb. My spid is 59. Instead of doing an update, I changed the sql to select into a temp table then I use that to update the table. I thought I had the problem resolved, but it's back again:

    51400DB SGRANT
    541000DB SGRANT
    551000DB SGRANT
    56111151510180TAB ISGRANT
    561300DB SGRANT
    591300DB SGRANT
    591355750582PAG3:8 ISGRANT
    59136386253180TAB SGRANT
    59200HBT[BULK_OPERATION] IXGRANT
    5913535752290TAB ISGRANT
    5923792004510PAG1:275 XGRANT
    59200MD1(169a23c3:0:0) Sch-SGRANT
    592990RID1:275:1 XGRANT
    592990RID1:275:2 XGRANT
    591321050585350TAB ISGRANT
    591355750582KEY(ae00ca69d10c) SGRANT
    59200DB[BULKOP_BACKUP_LOG] NULLGRANT
    5923792004510TAB XGRANT
    591321050585352KEY(3c00e927312a) SGRANT
    59136226252610TAB SGRANT
    5913535752293KEY(c203e4e0095c) SGRANT
    591355750580TAB ISGRANT
    59200DB[BULKOP_BACKUP_DB] NULLGRANT
    601000DB SGRANT
    611000DB SGRANT
    631300DB SGRANT
    641000DB SGRANT
    65400DB SGRANT
    671300DB SGRANT
    681000DB SGRANT
    691300DB SGRANT
    701000DB SGRANT
    711300DB SGRANT
  7. merrillaldrich New Member

    Hi -<br /><br />This is an interesting problem. I came up with this statement (which is not exactly equivalent to yours, but is more general, so it might or might not be useful):<br /><pre id="code"><font face="courier" size="2" id="code"><br />select *<br />from entity e1<br />where exists ( select * <br />from entity e2<br />where e1.type = e2.type<br />and e2.id &lt; e1.id<br />and not exists (<br />( select type, value from attribute where eid = e1.id<br />except<br /> select type, value from attribute where eid = e2.id )<br />union<br />( select type, value from attribute where eid = e2.id <br />except<br /> select type, value from attribute where eid = e1.id )<br />)<br />)<br /></font id="code"></pre id="code"><br />This will find a "duplicate entity" defined as an entity for which another exists, having exactly the same attributes. This differs from yours in that I guess you look at just the name and DOB do define a duplicate, not the set of all attributes. But I thought you might find this useful, and it was fun to figure it out <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />If one were also to add this foreign key relation with cascade:<br /><pre id="code"><font face="courier" size="2" id="code"><br />ALTER TABLE [dbo].[ATTRIBUTE] WITH CHECK ADD CONSTRAINT [FK_ATTRIBUTE_ENTITY] FOREIGN KEY([EID])<br />REFERENCES [dbo].[ENTITY] ([ID])<br />ON DELETE CASCADE<br />GO<br /><br />ALTER TABLE [dbo].[ATTRIBUTE] CHECK CONSTRAINT [FK_ATTRIBUTE_ENTITY]<br />GO<br /></font id="code"></pre id="code"><br />Then one could delete from ENTITY with the criteria above, and have the duplicate Attribute rows also removed, automatically.<br /><br />Also, I think the query above could be modified to locate entities for which there is another entity that has a superset of the same attributes (by which I mean, for example, one entity has three attributes and another has those three plus two more, as in the John Smith of your example).<br /><br />Don't know if this method would perform better for you or not <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  8. ryanwatton New Member

    Thanks Merrill!
    I'm going to compare times with your SQL and what I wrote and let you know how it turns out. I'll just put the type = 'name', etc in your SQL to do the proper matching. After updating the new_eid, I turn around and update the new_eid of the other attribute records that match eids. Then I update the eids of some other tables, then I set eid = new_eid on the attribute. I don't delete any records or entities in these staging tables. It's a lot of data and it takes time so I do an select distinct to filter out the dupes on updates and inserts.

    My new problem is sql gets "stuck". I'm performing a SELECT INTO into a temp table (granted I'm using bad practice and not creating the temp table first) and it never completes. No locking/blocking. I posted the sp_lock above. It's been stuck since 8:42 this morning and I have no clue why? Like a challenge? It's still sitting here grinding.

    Ryan
  9. merrillaldrich New Member

    Here's a swing at locating the "superset" duplicates -- that is, if I have the logic right! be sure to double-check my thinking! --



    select *
    from entity e1
    where exists ( select *
    from entity e2
    where e1.type = e2.type
    and ( -- There is an entity with a lower id having the same attributes exactly
    ( e2.id < e1.id
    and not exists (
    ( select type, value from attribute where eid = e1.id
    except
    select type, value from attribute where eid = e2.id )
    union
    ( select type, value from attribute where eid = e2.id
    except
    select type, value from attribute where eid = e1.id )
    )
    )
    or ( -- There is another entity someplace with a superset including the same attributes
    e2.id <> e1.id
    and not exists
    ( select type, value from attribute where eid = e1.id
    except
    select type, value from attribute where eid = e2.id )
    and exists
    ( select type, value from attribute where eid = e2.id
    except
    select type, value from attribute where eid = e1.id )
    )
    )
    )


  10. merrillaldrich New Member

    Can you pull up Perf Mon, or sysprocesses, and see what it's doing? In my experience, SQL Server never "hangs" or "gets in its own way" -- it's practically always a situation where the query is just taking a long, long, LONG time, or else you would see an error message. If I had to bet, I'd say its working away doing something, and perhaps the process it has generated to satisfy the query is just very intensive. For example, we have seen some Select statements on a server here take several hours and perform billions of reads due to an indexing problem. During the time these were running (successfully, but VERY slowly) the server was just chugging along, 25% of it's 4CPU capacity working away, table-scanning a whole table it had in RAM over and over and over.
  11. ryanwatton New Member

    Right. It's not hanging, it's tripping over itself. It is doing something, just not sure what. The sql statement will run fine (I'm loading data into the database) over and over again, then all of a sudden, I get this where 10 hours later, it's not finished on something that should have only taken about 20 mintues. How do I prevent this from happening?
    I'll review the sql you sent also! Thanks!
  12. ryanwatton New Member

    It almost seems like it all of a sudden it uses the wrong execution plan or something of that nature. 9 times out of 10 it will run fine.
  13. joechang New Member

    i can not look at this in detail now,
    but try the following index change
    CREATE NONCLUSTERED INDEX ix_type_value ON ATTRIBUTE (TYPE ASC,VALUE ASC, EID)
    WITH DROP_EXISTING


    also does this run only at night?
    the other 2 indexes on ATTRIBUTE are not involved,
    if the row count is high, and nothing else is running
    drop those 2 then recreate later
  14. TheSQLGuru New Member

    How about this possibility: you are getting a query plan that uses nested loops due either to 'bad' statisics or perhaps a previous run is cached that really DID affect a small number of rows and SHOULD have used nested loops?

    I have seen queries go off into la-la land looping kajillions of times when they really should have been doing hash or merge activity. Maybe you can throw this statement into a sproc and force it to RECOMPILE on each execution. Make sure your stats are up to date. If you KNOW how it should do it's work most efficiently (because you KNOW your data and data distributions) you can use query optimizer hints to force join/order, etc. Others have mentioned indexing and that should be evaluated too. Also, I didn't see any NOLOCK table hints in your query either. Consider their appropriate use here.

    SQLGuru

    SQLGuru
  15. ryanwatton New Member

    Ok. Seems I had experimented with an index and change the type to be (EID, Type, Value) on the Attribute table when the left-most column should have been Type. It should have been like Joechang said above (Type, Value, EID). It may have been a different SQL that was hanging, but when Type was not first, it forgot type and value were in the index and decided to tablescan. Thank you to everyone that replied. Now another question: Should I drop the index on EID and just keep it in the composite index? Or should I keep it if some other queries reference it? Would it be bad to have EID in it's own index and in another? Opinions?

    Ryan

Share This Page