Query plan different in different instances

Hi there,
I am not sure if this post is relevant to this forum, but I am
desperate to find an answer for my problem.
Here goes my problem, I have a query
SELECT
BPE.[EmpLastNm]+'
'+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
[ContactName]
,[EmpStatusCd]
,BPE.BusnPartEmpId
FROM
[dbo].[tblBusnPartEmp] AS BPE
inner JOIN [dbo].[tblDivBranchBusnPartEmp] AS DBBPE ON
BPE.[BusnPartEmpId]=DBBPE.[BusnPartEmpId]
inner JOIN [dbo].[tblDivBranch] AS DB ON
DB.[DivbranchId] = DBBPE.[DivbranchId]
WHERE
DB.[BusnPartId] = 2068--@.BusnPartId
AND DB.[DivNo] = '000'
and this query has different query plan in dev and test env.
I checked on statistics of this table, fragmentation of the table and
all look similar with no difference.
I am totally lost on what to be done next.
Query Plan in Dev. Env:
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-----------
-- -- -- --
-------
-- --
-- -- --
-- -- --
SELECT
BPE.[EmpLastNm]+'
'+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
[ContactName]
-- ,[EmpStatusCd]
--,[dbo]. 33 1 0 NULL NULL 1 NULL 43156.262 NULL NULL NULL 92.666832
NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1003]=[BPE].[EmpLastNm]+'
'+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
'')+')')) 33 2 1 Compute Scalar Compute Scalar
DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 43156.262 0.0
4.3156263E-3 168 92.666832 [BPE].[BusnPartEmpId], [Expr1003] NULL
PLAN_ROW 0 1.0
|--Hash Match(Inner Join,
HASH[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])) 33 3 2 Hash
Match Inner Join HASH[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])
NULL 43156.262 0.0 5.5712514 97 92.662514 [BPE].[EmpFamiliarNm],
[BPE].[EmpFirstNm], [BPE].[EmpLastNm], [BPE].[BusnPartEmpId] NULL
PLAN_ROW 0 1.0
|--Nested Loops(Inner Join, OUTER REFERENCES[DB].[DivBranchId]) WITH
PREFETCH) 33 4 3 Nested Loops Inner Join OUTER
REFERENCES[DB].[DivBranchId]) WITH PREFETCH NULL 43156.262 0.0
0.18039317 1247 1.6252334 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
| |--Filter(WHERE[DB].[DivNo]='000')) 33 6 4 Filter Filter
WHERE[DB].[DivNo]='000') NULL 141.84932 0.0 1.5263824E-4 1233
0.97286189 [DB].[DivBranchId] NULL PLAN_ROW 0 1.0
| | |--Bookmark Lookup(BOOKMARK[Bmk1002]),
OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 33 7 6
Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1002]),
OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
[DB].[DivBranchId], [DB].[DivNo] 317.99634 0.96872675 3.4979597E-4 1233
0.97270924 [DB].[DivBranchId], [DB].[DivNo] NULL PLAN_ROW 0 1.0
| | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 33 9 7 Index
Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
[DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002]
317.99634 3.2034251E-3 4.2924995E-4 19 3.6326749E-3 [Bmk1002] NULL
PLAN_ROW 0 1.0
| |--Index
Seek(OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD) 33 13 4 Index Seek Index Seek
OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD [DBBPE].[BusnPartEmpId] 304.24017 3.2034251E-3 4.1407693E-4 23
0.47197837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 141.84932
|--Table Scan(OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])) 33 14 3
Table Scan Table Scan OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
[BPE].[BusnPartEmpId] 1114682.0 84.239799 1.2262287 3282 85.466026
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
[BPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
Query plan in test env.:
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-----------
-- -- -- --
-------
-- --
-- -- --
-- --
-- --
SELECT
BPE.[EmpLastNm]+'
'+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
[ContactName]
,[EmpStatusCd]
,BPE.BusnPar 17 1 0 NULL NULL 1 NULL 4826.1626 NULL NULL NULL 10.43982
NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1003]=[BPE].[EmpLastNm]+'
'+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
'')+')')) 17 2 1 Compute Scalar Compute Scalar
DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 4826.1626 0.0
4.8261625E-4 172 10.43982 [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId],
[Expr1003] NULL PLAN_ROW 0 1.0
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH) 17 3 2
Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1000]),
OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
[BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] 4826.1626 2.2874451
5.308779E-3 270 10.439338 [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm],
[BPE].[EmpLastNm], [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] NULL
PLAN_ROW 0 1.0
|--Parallelism(Gather Streams) 17 5 3 Parallelism Gather Streams NULL
NULL 4826.1626 0.0 3.8873836E-2 23 8.1465836 [Bmk1000] NULL PLAN_ROW -1
1.0
|--Nested Loops(Inner Join, OUTER REFERENCES[DBBPE].[BusnPartEmpId])
WITH PREFETCH) 17 6 5 Nested Loops Inner Join OUTER
REFERENCES[DBBPE].[BusnPartEmpId]) WITH PREFETCH NULL 4826.1626 0.0
0.01008668 23 8.1077099 [Bmk1000] NULL PLAN_ROW -1 1.0
|--Sort(ORDER BY[DBBPE].[BusnPartEmpId] ASC)) 17 8 6 Sort Sort ORDER
BY[DBBPE].[BusnPartEmpId] ASC) NULL 4826.1626 5.6306305E-3
4.6113774E-2 11 1.034305 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
| |--Nested Loops(Inner Join, OUTER REFERENCES[DB].[DivBranchId])
WITH PREFETCH) 17 9 8 Nested Loops Inner Join OUTER
REFERENCES[DB].[DivBranchId]) WITH PREFETCH NULL 4826.1626 0.0
0.01008668 43 0.98256058 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
| |--Filter(WHERE[DB].[DivNo]='000')) 17 11 9 Filter Filter
WHERE[DB].[DivNo]='000') NULL 15.442919 0.0 7.5600001E-5 29
0.96141553 [DB].[DivBranchId] NULL PLAN_ROW -1 1.0
| | |--Bookmark Lookup(BOOKMARK[Bmk1002]),
OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 17 12 11
Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1002]),
OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
[DB].[DivBranchId], [DB].[DivNo] 315.0 0.95935196 1.7324999E-4 29
0.96133989 [DB].[DivBranchId], [DB].[DivNo] NO
STATS[tblDivBranch].[DivNo]) PLAN_ROW -1 1.0
| | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 17 14 12 Index
Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
[DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002] 315.0
1.6017125E-3 2.129725E-4 19 1.8146849E-3 [Bmk1002] NULL PLAN_ROW -1 1.0
| |--Index
Seek(OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD) 17 18 9 Index Seek Index Seek
OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD [DBBPE].[BusnPartEmpId] 312.5162 3.2034251E-3 4.2320538E-4 23
0.01105837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 15.442919
|--Index Seek(OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS
[BPE]), SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED
FORWARD) 17 19 6 Index Seek Index Seek
OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [BPE]),
SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED FORWARD
[Bmk1000] 1.0 3.2034251E-3 7.9603E-5 19 7.0633183 [Bmk1000] NULL
PLAN_ROW -1 4826.1626
If you notice the query plan are different by a major physical
operation. Test env. query plan uses index seek where as dev
environment query plan uses a table scan. Why? I am totally confused.
I checked on indexes also, and in both env. it is the same.
For your quick reference here are the index details too
index_name index_description index_keys
------
---------
-----------
IDXBusnPartEmp1 nonclustered located on PRIMARY EmpNTLogin
IX_tblBusnPartEmp nonclustered located on PRIMARY EdgeUserTypeCd
XPKBusnPartEmp nonclustered, unique, primary key located on PRIMARY
BusnPartEmpId
Can somebody help me on this issue? The sql instances sit on different
system having same hardware, server configuration. The service packs
are also same.
Thanks in advance.
Hi
Run DBCC DROPCLEANBUFFERS and run it again
"VIKING" <msrviking@.gmail.com> wrote in message
news:1163641697.241152.88950@.i42g2000cwa.googlegro ups.com...
> Hi there,
> I am not sure if this post is relevant to this forum, but I am
> desperate to find an answer for my problem.
> Here goes my problem, I have a query
> SELECT
> BPE.[EmpLastNm]+'
> '+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
> [ContactName]
> ,[EmpStatusCd]
> ,BPE.BusnPartEmpId
> FROM
> [dbo].[tblBusnPartEmp] AS BPE
> inner JOIN [dbo].[tblDivBranchBusnPartEmp] AS DBBPE ON
> BPE.[BusnPartEmpId]=DBBPE.[BusnPartEmpId]
> inner JOIN [dbo].[tblDivBranch] AS DB ON
> DB.[DivbranchId] = DBBPE.[DivbranchId]
> WHERE
> DB.[BusnPartId] = 2068--@.BusnPartId
> AND DB.[DivNo] = '000'
> and this query has different query plan in dev and test env.
> I checked on statistics of this table, fragmentation of the table and
> all look similar with no difference.
> I am totally lost on what to be done next.
> Query Plan in Dev. Env:
> StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
> -----------
> -- -- -- --
> --
> -------
> ----
> -- --
> -- -- --
> ----
> -- -- --
> --
> SELECT
> BPE.[EmpLastNm]+'
> '+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
> [ContactName]
> -- ,[EmpStatusCd]
> --,[dbo]. 33 1 0 NULL NULL 1 NULL 43156.262 NULL NULL NULL 92.666832
> NULL NULL SELECT 0 NULL
> |--Compute Scalar(DEFINE[Expr1003]=[BPE].[EmpLastNm]+'
> '+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
> '')+')')) 33 2 1 Compute Scalar Compute Scalar
> DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
> [Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 43156.262 0.0
> 4.3156263E-3 168 92.666832 [BPE].[BusnPartEmpId], [Expr1003] NULL
> PLAN_ROW 0 1.0
> |--Hash Match(Inner Join,
> HASH[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])) 33 3 2 Hash
> Match Inner Join HASH[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])
> NULL 43156.262 0.0 5.5712514 97 92.662514 [BPE].[EmpFamiliarNm],
> [BPE].[EmpFirstNm], [BPE].[EmpLastNm], [BPE].[BusnPartEmpId] NULL
> PLAN_ROW 0 1.0
> |--Nested Loops(Inner Join, OUTER REFERENCES[DB].[DivBranchId]) WITH
> PREFETCH) 33 4 3 Nested Loops Inner Join OUTER
> REFERENCES[DB].[DivBranchId]) WITH PREFETCH NULL 43156.262 0.0
> 0.18039317 1247 1.6252334 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
> | |--Filter(WHERE[DB].[DivNo]='000')) 33 6 4 Filter Filter
> WHERE[DB].[DivNo]='000') NULL 141.84932 0.0 1.5263824E-4 1233
> 0.97286189 [DB].[DivBranchId] NULL PLAN_ROW 0 1.0
> | | |--Bookmark Lookup(BOOKMARK[Bmk1002]),
> OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 33 7 6
> Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1002]),
> OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
> [DB].[DivBranchId], [DB].[DivNo] 317.99634 0.96872675 3.4979597E-4 1233
> 0.97270924 [DB].[DivBranchId], [DB].[DivNo] NULL PLAN_ROW 0 1.0
> | | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
> AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 33 9 7 Index
> Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
> [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002]
> 317.99634 3.2034251E-3 4.2924995E-4 19 3.6326749E-3 [Bmk1002] NULL
> PLAN_ROW 0 1.0
> | |--Index
> Seek(OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD) 33 13 4 Index Seek Index Seek
> OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD [DBBPE].[BusnPartEmpId] 304.24017 3.2034251E-3 4.1407693E-4 23
> 0.47197837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 141.84932
> |--Table Scan(OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])) 33 14 3
> Table Scan Table Scan OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
> [BPE].[BusnPartEmpId] 1114682.0 84.239799 1.2262287 3282 85.466026
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
> [BPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
>
> Query plan in test env.:
> StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
> -----------
> -- -- -- --
> --
> -------
> ----
> -- --
> -- -- --
> ----
> -- --
> -- --
> SELECT
> BPE.[EmpLastNm]+'
> '+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
> [ContactName]
> ,[EmpStatusCd]
> ,BPE.BusnPar 17 1 0 NULL NULL 1 NULL 4826.1626 NULL NULL NULL 10.43982
> NULL NULL SELECT 0 NULL
> |--Compute Scalar(DEFINE[Expr1003]=[BPE].[EmpLastNm]+'
> '+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
> '')+')')) 17 2 1 Compute Scalar Compute Scalar
> DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
> [Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 4826.1626 0.0
> 4.8261625E-4 172 10.43982 [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId],
> [Expr1003] NULL PLAN_ROW 0 1.0
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
> OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH) 17 3 2
> Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1000]),
> OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
> [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] 4826.1626 2.2874451
> 5.308779E-3 270 10.439338 [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm],
> [BPE].[EmpLastNm], [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] NULL
> PLAN_ROW 0 1.0
> |--Parallelism(Gather Streams) 17 5 3 Parallelism Gather Streams NULL
> NULL 4826.1626 0.0 3.8873836E-2 23 8.1465836 [Bmk1000] NULL PLAN_ROW -1
> 1.0
> |--Nested Loops(Inner Join, OUTER REFERENCES[DBBPE].[BusnPartEmpId])
> WITH PREFETCH) 17 6 5 Nested Loops Inner Join OUTER
> REFERENCES[DBBPE].[BusnPartEmpId]) WITH PREFETCH NULL 4826.1626 0.0
> 0.01008668 23 8.1077099 [Bmk1000] NULL PLAN_ROW -1 1.0
> |--Sort(ORDER BY[DBBPE].[BusnPartEmpId] ASC)) 17 8 6 Sort Sort ORDER
> BY[DBBPE].[BusnPartEmpId] ASC) NULL 4826.1626 5.6306305E-3
> 4.6113774E-2 11 1.034305 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
> | |--Nested Loops(Inner Join, OUTER REFERENCES[DB].[DivBranchId])
> WITH PREFETCH) 17 9 8 Nested Loops Inner Join OUTER
> REFERENCES[DB].[DivBranchId]) WITH PREFETCH NULL 4826.1626 0.0
> 0.01008668 43 0.98256058 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
> | |--Filter(WHERE[DB].[DivNo]='000')) 17 11 9 Filter Filter
> WHERE[DB].[DivNo]='000') NULL 15.442919 0.0 7.5600001E-5 29
> 0.96141553 [DB].[DivBranchId] NULL PLAN_ROW -1 1.0
> | | |--Bookmark Lookup(BOOKMARK[Bmk1002]),
> OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 17 12 11
> Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1002]),
> OBJECT[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
> [DB].[DivBranchId], [DB].[DivNo] 315.0 0.95935196 1.7324999E-4 29
> 0.96133989 [DB].[DivBranchId], [DB].[DivNo] NO
> STATS[tblDivBranch].[DivNo]) PLAN_ROW -1 1.0
> | | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
> AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 17 14 12 Index
> Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
> [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002] 315.0
> 1.6017125E-3 2.129725E-4 19 1.8146849E-3 [Bmk1002] NULL PLAN_ROW -1 1.0
> | |--Index
> Seek(OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD) 17 18 9 Index Seek Index Seek
> OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD [DBBPE].[BusnPartEmpId] 312.5162 3.2034251E-3 4.2320538E-4 23
> 0.01105837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 15.442919
> |--Index Seek(OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS
> [BPE]), SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED
> FORWARD) 17 19 6 Index Seek Index Seek
> OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [BPE]),
> SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED FORWARD
> [Bmk1000] 1.0 3.2034251E-3 7.9603E-5 19 7.0633183 [Bmk1000] NULL
> PLAN_ROW -1 4826.1626
> If you notice the query plan are different by a major physical
> operation. Test env. query plan uses index seek where as dev
> environment query plan uses a table scan. Why? I am totally confused.
> I checked on indexes also, and in both env. it is the same.
> For your quick reference here are the index details too
> index_name index_description index_keys
> ------
> ---------
> -----------
> IDXBusnPartEmp1 nonclustered located on PRIMARY EmpNTLogin
> IX_tblBusnPartEmp nonclustered located on PRIMARY EdgeUserTypeCd
> XPKBusnPartEmp nonclustered, unique, primary key located on PRIMARY
> BusnPartEmpId
>
> Can somebody help me on this issue? The sql instances sit on different
> system having same hardware, server configuration. The service packs
> are also same.
> Thanks in advance.
>