SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query on identical objects different systems, A performs speedy with index seek, B performs poorly...


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

Author
Message
SQLBlimp
SQLBlimp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 510
Identical Query. A select with one column condition. Selecting from a view. The view is bad, joining eight tables and another view. Crazy That wasn't my call.

In production, we are getting horrendous performance. 41 minutes for query. In test, it is subsecond.

Using SQL Compare, database schemas verified identical for all relevant tables and views. Indexes identical. Data in test is a copy of a recent production backup and is substantially identical. Only difference is that the test DB is in simple recovery mode.

Query plan shows production query creating bitmaps and doing the clustered index scan on a probe of one of those bitmaps; the test query plan shows a simple index seek on a nonclustered, and no bitmap creation anywhere in the query plan.

Tried all of this:

1. Recreating index used in test query plan in prod
2. Freeing procedure cache with DBCC FREEPROCCACHE
3. Setting ARITHABORT expressly ON and OFF (no difference)

I am completely stumped as executive mgmt. does a slow burn.

Thoughts?

Thanks
John
ssismaddy
ssismaddy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1660 Visits: 2401
May be you can try updating stats on all the 8 base tables. Also check fragmentation on all the 8 tables. Verify the MAXDOP settings on both the machines.
SQLBlimp
SQLBlimp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 510
ssismaddy (9/25/2013)
May be you can try updating stats on all the 8 base tables. Also check fragmentation on all the 8 tables. Verify the MAXDOP settings on both the machines.


Checked the fragmentation on all 8 tables. Nothing bad (worst table is 35%). MAXDOP is set to 4 on both machines.

Update stats may take a while and I'm hesitant unless we are off-hours.

Thanks.
John.
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50197 Visits: 10844
You need to find out what the differences are between test and production. Perhaps you have a bad plan cached on production? Perhaps you have a missing index on one of the 8 references tables? Is the referenced view the same in test and production?

If you haven't seen it, Erland Sommarskog hosts an excellent utility called SQL trace. It pulls a lot of useful information that may help you troubleshoot the problem. The only thing is that you're going to have to troubleshoot it in production because the problem doesn't exist in test. Anyway, have a look: http://www.sommarskog.se/sqlutil/sqltrace.html

HTH


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228577 Visits: 46342
Probably stale stats. Update stats on the tables involved with full scan. But need execution plans from both to make anything more than a wild guess.

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

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


SQLBlimp
SQLBlimp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 510
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
SQLBlimp
SQLBlimp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 510
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)


ssismaddy
ssismaddy
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1660 Visits: 2401
So while the query is executing on Prod, do you see any blocks? is the query on running state all the time or suspended? do you know what is the wait type if its suspended?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228577 Visits: 46342
Plans in XML format please. The text format is awfully hard to read and is missing about 99% of the information.

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

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


SQLBlimp
SQLBlimp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 510
GilaMonster (9/26/2013)
Plans in XML format please. The text format is awfully hard to read and is missing about 99% of the information.


In test, nothing. It flies.

In production, I see CXPACKET only, no blocks, just boatloads of parallelism.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search