RE: Query on identical objects different systems, A performs speedy with index seek, B performs poorly with clustered index scan

SSCertifiable

Points: 6340

September 26, 2013 at 9:59 am

johntam (9/26/2013)
Hi. I freed the entire procedure cache -- twice. I also compared the schema of test and production using SQL Compare, including indexes, which are identical for all tables. I am restoring a copy of the prod database to another test server. The problem did not restore; the test server still runs perfectly.
Stumped doesn't begin to describe my thoughts. :w00t:

Good Plan (names changed to protect the innocent:

StmtText

--------

Compute Scalar(DEFINE: ([Expr1023]=CASE WHEN [Expr1032]=(1) THEN [TESTSERVER].[dbo].[acuheader].[apar_id] as [cas].[apar_id] ELSE CASE WHEN CONVERT_IMPLICIT(int,[TESTSERVER].[dbo].[aq1extacuheader].[org_level] as [xh].[org_level],0)=(2) THEN [TESTSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xh].[parent_cas_id] ELSE CASE WHEN CONVERT_IMPLICIT(int,[TESTSERVER].[dbo].[aq1extacuheader].[org_level] as [xh].[org_level],0)=(3) THEN [TESTSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xh2].[parent_cas_id] ELSE NULL END END END, [Expr1027]='A', [Expr1028]=(0)))

|--Parallelism(Gather Streams)

|--Nested Loops(Left Outer Join, OUTER REFERENCES: ([xh].[client], [xh].[parent_cas_id], [Expr1032]))

|--Nested Loops(Inner Join, OUTER REFERENCES: ([r].[att_value], [xh].[client]))

| |--Hash Match(Inner Join, HASH: ([r].[client], [r].[att_value])=([xh].[client], [al].[article]), RESIDUAL: ([TESTSERVER].[dbo].[aglrelvalue].[client] as [r].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client] AND [TESTSERVER].[dbo].[aglrelvalue].[att_value] as [r].[att_value]=[TESTSERVER].[dbo].[auiinvdetail].[article] as [al].[article]))

| | |--Parallelism(Distribute Streams, Broadcast Partitioning)

| | | |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[aglrelvalue].[IX_AGLRELVALUE_RELATTRID_ATTRIBUTEID_ATTVALUE_RELVALUE] AS [r]), SEEK: ([r].[rel_attr_id]='Q50' AND [r].[attribute_id]='D0') ORDERED FORWARD)

| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([al].[agrtid], [Expr1042]) OPTIMIZED WITH UNORDERED PREFETCH)

| | |--Nested Loops(Inner Join, OUTER REFERENCES: ([xal].[sequence_no], [xal].[subscription_id], [xh].[client], [Expr1041]) OPTIMIZED WITH UNORDERED PREFETCH)

| | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([ai].[sequence_no], [ai].[subscription_id], [xh].[client], [Expr1040]) WITH UNORDERED PREFETCH)

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([ai].[participant_id], [xh].[client], [Expr1039]) OPTIMIZED WITH UNORDERED PREFETCH)

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([ai].[agree_item_id], [ai].[client], [Expr1038]) OPTIMIZED WITH UNORDERED PREFETCH)

| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([xh].[apar_id], [xh].[client], [Expr1037]) OPTIMIZED WITH UNORDERED PREFETCH)

| | | | | | | |--Compute Scalar(DEFINE: ([Expr1032]=CONVERT_IMPLICIT(int,[TESTSERVER].[dbo].[aq1extacuheader].[org_level] as [xh].[org_level],0)))

| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([cas].[apar_id], [cas].[client], [Expr1036]) WITH UNORDERED PREFETCH)

| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([cas].[apar_id], [cas].[client], [Expr1035]) WITH UNORDERED PREFETCH)

| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([xc].[apar_id], [xc].[client], [Expr1034]) WITH UNORDERED PREFETCH)

| | | | | | | | | | |--Parallelism(Repartition Streams, RoundRobin Partitioning)

| | | | | | | | | | | |--Index Scan(OBJECT: ([TESTSERVER].[dbo].[aq1extacuheader].[IX_EXTACUHEADER_ORGLEVEL_INCL_APAR_CLIENT_PARENTCAS] AS [xc]), WHERE: ([TESTSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xc].[parent_cas_id]=[@2]))

| | | | | | | | | | |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[acuheader].[IX_ACUHEADER_APARID_CLIENT_STATUS_APARGRID] AS [cas]), SEEK: ([cas].[apar_id]=[TESTSERVER].[dbo].[aq1extacuheader].[apar_id] as [xc].[apar_id] AND [cas].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xc].[client] AND [cas].[status]='N') ORDERED FORWARD)

| | | | | | | | | |--Clustered Index Seek(OBJECT: ([TESTSERVER].[dbo].[acuheader].[PK_acuheader] AS [c]), SEEK: ([c].[apar_id]=[TESTSERVER].[dbo].[acuheader].[apar_id] as [cas].[apar_id] AND [c].[client]=[TESTSERVER].[dbo].[acuheader].[client] as [cas].[client]) ORDERED FORWARD)

| | | | | | | | |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[aq1extacuheader].[IX_aq1extacuheader_aparid_client_parentcasid_orglevel] AS [xh]), SEEK: ([xh].[apar_id]=[TESTSERVER].[dbo].[acuheader].[apar_id] as [cas].[apar_id] AND [xh].[client]=[TESTSERVER].[dbo].[acuheader].[client] as [cas].[client]) ORDERED FORWARD)

| | | | | | | |--Filter(WHERE: (STARTUP EXPR([@0]=[@1])))

| | | | | | | |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[aq1agreeitem].[IX_AQ1AGREEITEM_APARID_CLIENT] AS [ai]), SEEK: ([ai].[apar_id]=[TESTSERVER].[dbo].[aq1extacuheader].[apar_id] as [xh].[apar_id] AND [ai].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)

| | | | | | |--Clustered Index Seek(OBJECT: ([TESTSERVER].[dbo].[aq1agreeitem].[aiaq1agreeitem0] AS [ai]), SEEK: ([ai].[client]=[TESTSERVER].[dbo].[aq1agreeitem].[client] as [ai].[client] AND [ai].[agree_item_id]=[TESTSERVER].[dbo].[aq1agreeitem].[agree_item_id] as [ai].[agree_item_id]) LOOKUP ORDERED FORWARD)

| | | | | |--Clustered Index Seek(OBJECT: ([TESTSERVER].[dbo].[aq1participant].[PK_aq1participant] AS [p]), SEEK: ([p].[participant_id]=[TESTSERVER].[dbo].[aq1agreeitem].[participant_id] as [ai].[participant_id] AND [p].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)

| | | | |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[aq1extuidetail].[IX_AQ1EXTUIDETAIL_SUBSCRIPTID_SEQNO_CLIENT_NEXTWEARBEGIN_PICKUPFLAG_INCL] AS [xal]), SEEK: ([xal].[subscription_id]=[TESTSERVER].[dbo].[aq1agreeitem].[subscription_id] as [ai].[subscription_id] AND [xal].[sequence_no]=[TESTSERVER].[dbo].[aq1agreeitem].[sequence_no] as [ai].[sequence_no] AND [xal].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)

| | | |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[auiinvdetail].[IX_AUIINVDETAIL_CLIENT_SUBID_SEQ_AGRTID_CREATEDT_DATETO_APARID] AS [al]), SEEK: ([al].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client] AND [al].[subscription_id]=[TESTSERVER].[dbo].[aq1extuidetail].[subscription_id] as [xal].[subscription_id] AND [al].[sequence_no]=[TESTSERVER].[dbo].[aq1extuidetail].[sequence_no] as [xal].[sequence_no]) ORDERED FORWARD)

| | |--Clustered Index Seek(OBJECT: ([TESTSERVER].[dbo].[auiinvdetail].[PK_auiinvdetail ] AS [al]), SEEK: ([al].[agrtid]=[TESTSERVER].[dbo].[auiinvdetail].[agrtid] as [al].[agrtid]) LOOKUP ORDERED FORWARD)

| |--Index Seek(OBJECT: ([TESTSERVER].[dbo].[aglrelvalue].[aiaglrelvalue3] AS [r2]), SEEK: ([r2].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client] AND [r2].[att_value]=[TESTSERVER].[dbo].[aglrelvalue].[att_value] as [r].[att_value] AND [r2].[rel_attr_id]='JL85'), WHERE: ([TESTSERVER].[dbo].[aglrelvalue].[attribute_id] as [r2].[attribute_id]='D0') ORDERED FORWARD)

|--Filter(WHERE: (STARTUP EXPR([Expr1032]=(3))))

|--Index Seek(OBJECT: ([TESTSERVER].[dbo].[aq1extacuheader].[IX_aq1extacuheader_aparid_client_parentcasid_orglevel] AS [xh2]), SEEK: ([xh2].[apar_id]=[TESTSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xh].[parent_cas_id] AND [xh2].[client]=[TESTSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)

BAD Plan:

StmtText

--------

Compute Scalar(DEFINE: ([Expr1023]=CASE WHEN [Expr1032]=(1) THEN [PRODSERVER].[dbo].[acuheader].[apar_id] as [cas].[apar_id] ELSE CASE WHEN CONVERT_IMPLICIT(int,[PRODSERVER].[dbo].[aq1extacuheader].[org_level] as [xh].[org_level],0)=(2) THEN [PRODSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xh].[parent_cas_id] ELSE CASE WHEN CONVERT_IMPLICIT(int,[PRODSERVER].[dbo].[aq1extacuheader].[org_level] as [xh].[org_level],0)=(3) THEN [PRODSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xh2].[parent_cas_id] ELSE NULL END END END, [Expr1027]='A', [Expr1028]=(0)))

|--Parallelism(Gather Streams)

|--Nested Loops(Left Outer Join, OUTER REFERENCES: ([xh].[client], [xh].[parent_cas_id], [Expr1032], [Expr1048]) WITH UNORDERED PREFETCH)

|--Nested Loops(Inner Join, OUTER REFERENCES: ([ai].[participant_id], [xh].[client], [Expr1047]) OPTIMIZED WITH UNORDERED PREFETCH)

| |--Hash Match(Inner Join, HASH: ([xh].[client], [xh].[apar_id], [xal].[subscription_id], [xal].[sequence_no])=([ai].[client], [ai].[apar_id], [ai].[subscription_id], [ai].[sequence_no]), RESIDUAL: ([PRODSERVER].[dbo].[aq1agreeitem].[client] as [ai].[client]=[PRODSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client] AND [PRODSERVER].[dbo].[aq1agreeitem].[apar_id] as [ai].[apar_id]=[PRODSERVER].[dbo].[aq1extacuheader].[apar_id] as [xh].[apar_id] AND [PRODSERVER].[dbo].[aq1extuidetail].[subscription_id] as [xal].[subscription_id]=[PRODSERVER].[dbo].[aq1agreeitem].[subscription_id] as [ai].[subscription_id] AND [PRODSERVER].[dbo].[aq1extuidetail].[sequence_no] as [xal].[sequence_no]=[PRODSERVER].[dbo].[aq1agreeitem].[sequence_no] as [ai].[sequence_no]))

| | |--Bitmap(HASH: ([xh].[client], [xh].[apar_id], [xal].[subscription_id], [xal].[sequence_no]), DEFINE: ([Bitmap1046]))

| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([xh].[client], [xh].[apar_id], [xal].[subscription_id], [xal].[sequence_no]))

| | | |--Hash Match(Inner Join, HASH: ([xh].[client], [al].[subscription_id], [al].[sequence_no])=([xal].[client], [xal].[subscription_id], [xal].[sequence_no]), RESIDUAL: ([PRODSERVER].[dbo].[aq1extuidetail].[client] as [xal].[client]=[PRODSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client] AND [PRODSERVER].[dbo].[auiinvdetail].[subscription_id] as [al].[subscription_id]=[PRODSERVER].[dbo].[aq1extuidetail].[subscription_id] as [xal].[subscription_id] AND [PRODSERVER].[dbo].[auiinvdetail].[sequence_no] as [al].[sequence_no]=[PRODSERVER].[dbo].[aq1extuidetail].[sequence_no] as [xal].[sequence_no]))

| | | |--Bitmap(HASH: ([xh].[client], [al].[subscription_id], [al].[sequence_no]), DEFINE: ([Bitmap1045]))

| | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([xh].[client], [al].[subscription_id], [al].[sequence_no]))

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([r2].[att_value], [xh].[client], [Expr1044]) WITH UNORDERED PREFETCH)

| | | | |--Compute Scalar(DEFINE: ([Expr1032]=CONVERT_IMPLICIT(int,[PRODSERVER].[dbo].[aq1extacuheader].[org_level] as [xh].[org_level],0)))

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([cas].[apar_id], [cas].[client], [Expr1043]) WITH UNORDERED PREFETCH)

| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([xc].[apar_id], [r2].[client], [Expr1042]) WITH UNORDERED PREFETCH)

| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([xc].[apar_id], [r2].[client], [Expr1041]) WITH UNORDERED PREFETCH)

| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([xc].[apar_id], [xc].[client], [Expr1040]) WITH UNORDERED PREFETCH)

| | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([r2].[client]))

| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES: ([r].[att_value], [r].[client]))

| | | | | | | | | | |--Parallelism(Distribute Streams, RoundRobin Partitioning)

| | | | | | | | | | | |--Index Seek(OBJECT: ([PRODSERVER].[dbo].[aglrelvalue].[IX_AGLRELVALUE_RELATTRID_ATTRIBUTEID_ATTVALUE_RELVALUE] AS [r]), SEEK: ([r].[rel_attr_id]='Q50' AND [r].[attribute_id]='D0') ORDERED FORWARD)

| | | | | | | | | | |--Index Seek(OBJECT: ([PRODSERVER].[dbo].[aglrelvalue].[aiaglrelvalue2] AS [r2]), SEEK: ([r2].[attribute_id]='D0' AND [r2].[att_value]=[PRODSERVER].[dbo].[aglrelvalue].[att_value] as [r].[att_value] AND [r2].[rel_attr_id]='JL85'), WHERE: ([PRODSERVER].[dbo].[aglrelvalue].[client] as [r].[client]=[PRODSERVER].[dbo].[aglrelvalue].[client] as [r2].[client]) ORDERED FORWARD)

| | | | | | | | | |--Index Seek(OBJECT: ([PRODSERVER].[dbo].[aq1extacuheader].[IX_AQ1EXTACUHEADER_CLIENT_PARENTCASID] AS [xc]), SEEK: ([xc].[client]=[PRODSERVER].[dbo].[aglrelvalue].[client] as [r2].[client] AND [xc].[parent_cas_id]=[@2]) ORDERED FORWARD)

| | | | | | | | |--Clustered Index Seek(OBJECT: ([PRODSERVER].[dbo].[aq1extacuheader].[PK_aq1extacuheader] AS [xc]), SEEK: ([xc].[apar_id]=[PRODSERVER].[dbo].[aq1extacuheader].[apar_id] as [xc].[apar_id] AND [xc].[client]=[PRODSERVER].[dbo].[aq1extacuheader].[client] as [xc].[client]) LOOKUP ORDERED FORWARD)

| | | | | | | |--Clustered Index Seek(OBJECT: ([PRODSERVER].[dbo].[acuheader].[PK_acuheader] AS [c]), SEEK: ([c].[apar_id]=[PRODSERVER].[dbo].[aq1extacuheader].[apar_id] as [xc].[apar_id] AND [c].[client]=[PRODSERVER].[dbo].[aglrelvalue].[client] as [r2].[client]) ORDERED FORWARD)

| | | | | | |--Index Seek(OBJECT: ([PRODSERVER].[dbo].[acuheader].[IX_ACUHEADER_APARID_CLIENT_STATUS_APARGRID] AS [cas]), SEEK: ([cas].[apar_id]=[PRODSERVER].[dbo].[aq1extacuheader].[apar_id] as [xc].[apar_id] AND [cas].[client]=[PRODSERVER].[dbo].[aglrelvalue].[client] as [r2].[client] AND [cas].[status]='N') ORDERED FORWARD)

| | | | | |--Index Seek(OBJECT: ([PRODSERVER].[dbo].[aq1extacuheader].[IX_aq1extacuheader_aparid_client_parentcasid_orglevel] AS [xh]), SEEK: ([xh].[apar_id]=[PRODSERVER].[dbo].[acuheader].[apar_id] as [cas].[apar_id] AND [xh].[client]=[PRODSERVER].[dbo].[acuheader].[client] as [cas].[client]) ORDERED FORWARD)

| | | | |--Index Seek(OBJECT: ([PRODSERVER].[dbo].[auiinvdetail].[IX_AUIINVDETAIL_ARTICLE_APARID_STATUS_SUBSCRIPTID_SEQNO_CLIENT_INCL] AS [al]), SEEK: ([al].[article]=[PRODSERVER].[dbo].[aglrelvalue].[att_value] as [r2].[att_value]), WHERE: ([PRODSERVER].[dbo].[auiinvdetail].[client] as [al].[client]=[PRODSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)

| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([xal].[client], [xal].[subscription_id], [xal].[sequence_no]))

| | | |--Index Scan(OBJECT: ([PRODSERVER].[dbo].[aq1extuidetail].[IX_AQ1EXTUIDETAIL_PICKUPFLAG_SUBSCRIPTIONID_SEQNO_NEXTWEARBEGIN_INCL] AS [xal]), WHERE: (PROBE([Bitmap1045],[PRODSERVER].[dbo].[aq1extuidetail].[client] as [xal].[client],[PRODSERVER].[dbo].[aq1extuidetail].[subscription_id] as [xal].[subscription_id],[PRODSERVER].[dbo].[aq1extuidetail].[sequence_no] as [xal].[sequence_no])))

| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS: ([ai].[client], [ai].[apar_id], [ai].[subscription_id], [ai].[sequence_no]))

| | |--Filter(WHERE: (STARTUP EXPR([@0]=[@1])))

| | |--Clustered Index Scan(OBJECT: ([PRODSERVER].[dbo].[aq1agreeitem].[aiaq1agreeitem0] AS [ai]), WHERE: (PROBE([Bitmap1046],[PRODSERVER].[dbo].[aq1agreeitem].[client] as [ai].[client],[PRODSERVER].[dbo].[aq1agreeitem].[apar_id] as [ai].[apar_id],[PRODSERVER].[dbo].[aq1agreeitem].[subscription_id] as [ai].[subscription_id],[PRODSERVER].[dbo].[aq1agreeitem].[sequence_no] as [ai].[sequence_no])))

| |--Clustered Index Seek(OBJECT: ([PRODSERVER].[dbo].[aq1participant].[PK_aq1participant] AS [p]), SEEK: ([p].[participant_id]=[PRODSERVER].[dbo].[aq1agreeitem].[participant_id] as [ai].[participant_id] AND [p].[client]=[PRODSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)

|--Filter(WHERE: (STARTUP EXPR([Expr1032]=(3))))

|--Index Seek(OBJECT: ([PRODSERVER].[dbo].[aq1extacuheader].[IX_aq1extacuheader_aparid_client_parentcasid_orglevel] AS [xh2]), SEEK: ([xh2].[apar_id]=[PRODSERVER].[dbo].[aq1extacuheader].[parent_cas_id] as [xh].[parent_cas_id] AND [xh2].[client]=[PRODSERVER].[dbo].[aq1extacuheader].[client] as [xh].[client]) ORDERED FORWARD)