Post reply

Query plan on different instances

  • November 15, 2006 at 7:46 am

    #117618

    Hi there,

    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?

    Thanks in advance.

    Thanks!

    Viking

  • November 15, 2006 at 9:16 am

    #672019

    Are the SQL Instances exactly the same ? Same hardware, same memory allocation, same server options etc ?

     

  • November 15, 2006 at 6:36 pm

    #672179

    Yes they are. The hardware configuration of the servers and server options are exactly the same. No difference.

    Thanks!

    Viking

  • November 16, 2006 at 7:54 am

    #672293

    Can update the statistics on both server and check the plan again?

    If the scan causes big perfromance issue, maybe just use index hint WITH(OPTION(indexName) for SQL server to use that index

     

  • November 16, 2006 at 8:41 pm

    #672506

    Yes, I did update statistics on the tables of dev. environment and checked table structure for double assurance. I am not sure why is the cause of difference in query plan.

    But, on doing more analysis I have realized that query plan on test environment is incorrect because no. of execution time is 4000+ times unlike that of dev. environment. I did a statistics profile on the query in test env. and found this issue. Now, after this analysis and realization I went ahead and updated the statistics in test env tables too. But still I see the difference.

    Why?, is my thought always .

    Thanks!

    Viking

  • November 17, 2006 at 4:35 am

    #672546

    So are the databases identical ? The SQL editions the same, the patch levels? same o/s etc. etc.

    How about database and sevrer collations also identical?

    Is this a proc or a query .. I suggest you clear the data and proc cache before running the query too as cached plans may well throw all your tests out.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply