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].[EmpFamili
arNm],
'')+')')) 33 2 1 Compute Scalar Compute Scalar
DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[E
mpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstN
m],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 43156.262 0.0
4.3156263E-3 168 92.666832 [BPE].[BusnPartEmpId], [Expr1003] NUL
L
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].[Busn
PartEmpId])
NULL 43156.262 0.0 5.5712514 97 92.662514 [BPE].[EmpFamiliarNm],
[BPE].[EmpFirstNm], [BPE].[EmpLastNm], [BPE].[BusnPa
rtEmpId] NULL
PLAN_ROW 0 1.0
|--Nested Loops(Inner Join, OUTER REFERENCES[DB].[DivBranchId]) WI
TH
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.4979
597E-4 1233
0.97270924 [DB].[DivBranchId], [DB].[DivNo] NULL PLAN_ROW 0
1.0
| | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDi
vBranch1]
AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 33 9 7
Index
Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivB
ranch1] AS
[DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk100
2]
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].[XPKDivB
ranchBusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranch
Id]) ORDERED
FORWARD) 33 13 4 Index Seek Index Seek
OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranch
BusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranch
Id]) 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 &
#91;BPE])
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[Em
pLastNm],
[BPE].[BusnPartEmpId] 1114682.0 84.239799 1.2262287 3282 85.466026
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[Em
pLastNm],
[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].[EmpFamili
arNm],
'')+')')) 17 2 1 Compute Scalar Compute Scalar
DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[E
mpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstN
m],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 4826.1626 0.0
4.8261625E-4 172 10.43982 [BPE].[EmpStatusCd], [BPE].[BusnPa
rtEmpId],
[Expr1003] NULL PLAN_ROW 0 1.0
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETC
H) 17 3 2
Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1000]),
OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETC
H
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[Em
pLastNm],
[BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] 4826.1626 2.28744
51
5.308779E-3 270 10.439338 [BPE].[EmpFamiliarNm], [BPE].[EmpF
irstNm],
[BPE].[EmpLastNm], [BPE].[EmpStatusCd], [BPE].[BusnP
artEmpId] 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 ORD
ER
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].[IDXDi
vBranch1]
AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 17 14 1
2 Index
Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDivB
ranch1] AS
[DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk100
2] 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].[XPKDivB
ranchBusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranch
Id]) ORDERED
FORWARD) 17 18 9 Index Seek Index Seek
OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranch
BusnPartEmp]
AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBranch
Id]) ORDERED
FORWARD [DBBPE].[BusnPartEmpId] 312.5162 3.2034251E-3 4.2320538E-4 2
3
0.01105837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 15.442919
|--Index Seek(OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusn
PartEmp] AS
[BPE]), SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmp
Id]) ORDERED
FORWARD) 17 19 6 Index Seek Index Seek
OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS &#
91;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.googlegroups.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].[EmpFami
liarNm],
> '')+')')) 33 2 1 Compute Scalar Compute Scalar
> DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[
;EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
> [Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirs
tNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 43156.262 0.0
> 4.3156263E-3 168 92.666832 [BPE].[BusnPartEmpId], [Expr1003] N
ULL
> 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].[Bu
snPartEmpId])
> NULL 43156.262 0.0 5.5712514 97 92.662514 [BPE].[EmpFamiliarNm],
> [BPE].[EmpFirstNm], [BPE].[EmpLastNm], [BPE].[Busn
PartEmpId] 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.49
79597E-4 1233
> 0.97270924 [DB].[DivBranchId], [DB].[DivNo] NULL PLAN_ROW
0 1.0
> | | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDX
DivBranch1]
> AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 33 9
7 Index
> Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDi
vBranch1] AS
> [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1
002]
> 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].[XPKDi
vBranchBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBran
chId]) ORDERED
> FORWARD) 33 13 4 Index Seek Index Seek
> OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBran
chBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBran
chId]) 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 [BP
E])) 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].[EmpFami
liarNm],
> '')+')')) 17 2 1 Compute Scalar Compute Scalar
> DEFINE[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[
;EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
> [Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirs
tNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 4826.1626 0.0
> 4.8261625E-4 172 10.43982 [BPE].[EmpStatusCd], [BPE].[Busn
PartEmpId],
> [Expr1003] NULL PLAN_ROW 0 1.0
> |--Bookmark Lookup(BOOKMARK[Bmk1000]),
> OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFE
TCH) 17 3 2
> Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1000]),
> OBJECT[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFE
TCH
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[
EmpLastNm],
> [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] 4826.1626 2.287
4451
> 5.308779E-3 270 10.439338 [BPE].[EmpFamiliarNm], [BPE].[Em
pFirstNm],
> [BPE].[EmpLastNm], [BPE].[EmpStatusCd], [BPE].[Bus
nPartEmpId] 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].[BusnPartEmp
Id])
> 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 O
RDER
> 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.732499
9E-4 29
> 0.96133989 [DB].[DivBranchId], [DB].[DivNo] NO
> STATS[tblDivBranch].[DivNo]) PLAN_ROW -1 1.0
> | | |--Index Seek(OBJECT[EDGE].[dbo].[tblDivBranch].[IDX
DivBranch1]
> AS [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD) 17 14
12 Index
> Seek Index Seek OBJECT[EDGE].[dbo].[tblDivBranch].[IDXDi
vBranch1] AS
> [DB]), SEEK[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1
002] 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].[XPKDi
vBranchBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBran
chId]) ORDERED
> FORWARD) 17 18 9 Index Seek Index Seek
> OBJECT[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBran
chBusnPartEmp]
> AS [DBBPE]), SEEK[DBBPE].[DivBranchId]=[DB].[DivBran
chId]) 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].[XPKBu
snPartEmp] AS
> [BPE]), SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartE
mpId]) ORDERED
> FORWARD) 17 19 6 Index Seek Index Seek
> OBJECT[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS
[BPE]),
> SEEK[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDER
ED 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.
>sql