SQL Server Performance

Execution Plan differs

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ramkumar.mu, Jun 9, 2010.

  1. ramkumar.mu New Member

    I have a user database in DEV and UAT environment. Copy of UAT database is restored on DEV. There is a query that runs for 1 second in DEV and 34 minutes for UAT.
    While analysing the Query Plan, while joining two tables UAT uses Nested Loop and Dev uses Hash Match. Also, the exec plan shows that the Nested Loop stmt is executed 65K times in UAT and just once in DEV.
    When i force Hash Match either in Query or through Planguide, the Query in UAT executes in 1 second. Also, the tables dont have any index and when i try creating one in UAT, then also the query runs in 1 second.
    Is this something to do with the Environment? i have attached the query stats from both env below (you can copy that to excel to easy view)
    Please help...
    QuerySELECT dbo.lkup_EventTypes.EventType, dbo.tbl_Monitor_Tivoli_ProfileInventory.Profile, COUNT(dbo.tbl_Web_DashBoard_Servers.HostName) AS ServerCountFROM dbo.tbl_Groups_GroupInfo_Tas INNER JOIN dbo.tbl_Contacts_FirstSecond_Tas ON dbo.tbl_Groups_GroupInfo_Tas.TasGroupId = dbo.tbl_Contacts_FirstSecond_Tas.TasGroupId INNER JOIN dbo.tbl_Web_DashBoard_Servers ON dbo.tbl_Contacts_FirstSecond_Tas.TasSysId = dbo.tbl_Web_DashBoard_Servers.TasSysId INNER JOIN dbo.tbl_Monitor_Tivoli_ProfileInventory ON dbo.tbl_Web_DashBoard_Servers.HostName = dbo.tbl_Monitor_Tivoli_ProfileInventory.Server INNER JOIN dbo.lkup_EventTypes ON dbo.tbl_Monitor_Tivoli_ProfileInventory.ProfileType = dbo.lkup_EventTypes.EventTypeIDWHERE (dbo.tbl_Groups_GroupInfo_Tas.TasGroupId = 3)GROUP BY dbo.lkup_EventTypes.EventType, dbo.tbl_Monitor_Tivoli_ProfileInventory.Profile
    Execution Plan in DEV
    RowsExecutesStmt Text
    1431SELECT dbo.lkup_EventTypes.EventType, dbo.tbl_Monitor_Tivoli_ProfileInventory.Profile, COUNT(dbo.tbl_Web_DashBoard_Servers.HostName) AS ServerCount FROM dbo.tbl_Groups_GroupInfo_Tas INNER JOIN dbo.tbl_Contacts_FirstSecond_Tas ON dbo.tbl_Groups_GroupInfo_Tas.TasGroupId = dbo.tbl_Contacts_FirstSecond_Tas.TasGroupId INNER JOIN dbo.tbl_Web_DashBoard_Servers ON dbo.tbl_Contacts_FirstSecond_Tas.TasSysId = dbo.tbl_Web_DashBoard_Servers.TasSysId INNER JOIN dbo.tbl_Monitor_Tivoli_ProfileInventory ON dbo.tbl_Web_DashBoard_Servers.HostName = dbo.tbl_Monitor_Tivoli_ProfileInventory.Server INNER JOIN dbo.lkup_EventTypes ON dbo.tbl_Monitor_Tivoli_ProfileInventory.ProfileType = dbo.lkup_EventTypes.EventTypeID WHERE (dbo.tbl_Groups_GroupInfo_Tas.TasGroupId = 3) GROUP BY dbo.lkup_EventTypes.EventType, dbo.tbl_Monitor_Tivoli_ProfileInventory.Profile
    00 |--Compute Scalar(DEFINE:([Expr1019]=CONVERT_IMPLICIT(int,[Expr1022],0)))
    1431 |--Stream Aggregate(GROUP BY:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Profile], [SMRDB].[dbo].[lkup_EventTypes].[EventType]) DEFINE:([Expr1022]=Count(*)))
    39031 |--Sort(ORDER BY:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Profile] ASC, [SMRDB].[dbo].[lkup_EventTypes].[EventType] ASC))
    39031 |--Hash Match(Inner Join, HASH:([SMRDB].[dbo].[lkup_EventTypes].[EventTypeID])=([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[ProfileType]), RESIDUAL:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[ProfileType]=[SMRDB].[dbo].[lkup_EventTypes].[EventTypeID]))
    371 |--Table Scan(OBJECT:([SMRDB].[dbo].[lkup_EventTypes]))
    42151 |--Hash Match(Inner Join, HASH:([SMRDB].[dbo].[tbl_Web_DashBoard_Servers].[HostName])=([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Server]), RESIDUAL:([SMRDB].[dbo].[tbl_Web_DashBoard_Servers].[HostName]=[SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Server]))
    22081 |--Hash Match(Inner Join, HASH:([SMRDB].[dbo].[tbl_Contacts_FirstSecond_Tas].[TasSysId])=([SMRDB].[dbo].[tbl_Web_DashBoard_Servers].[TasSysId]))
    22141 | |--Nested Loops(Inner Join)
    11 | | |--Table Scan(OBJECT:([SMRDB].[dbo].[tbl_Groups_GroupInfo_Tas]), WHERE:([SMRDB].[dbo].[tbl_Groups_GroupInfo_Tas].[TasGroupId]=(3)))
    22141 | | |--Table Scan(OBJECT:([SMRDB].[dbo].[tbl_Contacts_FirstSecond_Tas]), WHERE:([SMRDB].[dbo].[tbl_Contacts_FirstSecond_Tas].[TasGroupId]=(3)))
    665191 | |--Table Scan(OBJECT:([SMRDB].[dbo].[tbl_Web_DashBoard_Servers]))
    711301 |--Clustered Index Scan(OBJECT:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[PK_tbl_Monitor_Tivoli_ProfileInventory]))Execution Plan in UAT
    RowsExecutesStmt Text
    1431SELECT dbo.lkup_EventTypes.EventType, dbo.tbl_Monitor_Tivoli_ProfileInventory.Profile, COUNT(dbo.tbl_Web_DashBoard_Servers.HostName) AS ServerCount FROM dbo.tbl_Groups_GroupInfo_Tas INNER JOIN dbo.tbl_Contacts_FirstSecond_Tas ON dbo.tbl_Groups_GroupInfo_Tas.TasGroupId = dbo.tbl_Contacts_FirstSecond_Tas.TasGroupId INNER JOIN dbo.tbl_Web_DashBoard_Servers ON dbo.tbl_Contacts_FirstSecond_Tas.TasSysId = dbo.tbl_Web_DashBoard_Servers.TasSysId INNER JOIN dbo.tbl_Monitor_Tivoli_ProfileInventory ON dbo.tbl_Web_DashBoard_Servers.HostName = dbo.tbl_Monitor_Tivoli_ProfileInventory.Server INNER JOIN dbo.lkup_EventTypes ON dbo.tbl_Monitor_Tivoli_ProfileInventory.ProfileType = dbo.lkup_EventTypes.EventTypeID WHERE (dbo.tbl_Groups_GroupInfo_Tas.TasGroupId = 3) GROUP BY dbo.lkup_EventTypes.EventType, dbo.tbl_Monitor_Tivoli_ProfileInventory.Profile
    00 |--Compute Scalar(DEFINE:([Expr1019]=CONVERT_IMPLICIT(int,[Expr1022],0)))
    1431 |--Stream Aggregate(GROUP BY:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Profile], [SMRDB].[dbo].[lkup_EventTypes].[EventType]) DEFINE:([Expr1022]=Count(*)))
    39031 |--Nested Loops(Inner Join)
    39031 |--Nested Loops(Inner Join, WHERE:([SMRDB].[dbo].[tbl_Contacts_FirstSecond_Tas].[TasSysId]=[SMRDB].[dbo].[tbl_Web_DashBoard_Servers].[TasSysId]))
    636151 | |--Nested Loops(Inner Join, WHERE:([SMRDB].[dbo].[tbl_Web_DashBoard_Servers].[HostName]=[SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Server]))
    653431 | | |--Sort(ORDER BY:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[Profile] ASC, [SMRDB].[dbo].[lkup_EventTypes].[EventType] ASC))
    653431 | | | |--Hash Match(Inner Join, HASH:([SMRDB].[dbo].[lkup_EventTypes].[EventTypeID])=([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[ProfileType]), RESIDUAL:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[ProfileType]=[SMRDB].[dbo].[lkup_EventTypes].[EventTypeID]))
    371 | | | |--Table Scan(OBJECT:([SMRDB].[dbo].[lkup_EventTypes]))
    711301 | | | |--Clustered Index Scan(OBJECT:([SMRDB].[dbo].[tbl_Monitor_Tivoli_ProfileInventory].[PK_tbl_Monitor_Tivoli_ProfileInventory]))
    434661636065343 | | |--Table Scan(OBJECT:([SMRDB].[dbo].[tbl_Web_DashBoard_Servers]))
    14084361063615 | |--Table Scan(OBJECT:([SMRDB].[dbo].[tbl_Contacts_FirstSecond_Tas]), WHERE:([SMRDB].[dbo].[tbl_Contacts_FirstSecond_Tas].[TasGroupId]=(3)))
    39033903 |--Table Scan(OBJECT:([SMRDB].[dbo].[tbl_Groups_GroupInfo_Tas]), WHERE:([SMRDB].[dbo].[tbl_Groups_GroupInfo_Tas].[TasGroupId]=(3)))
  2. Adriaan New Member

    Are you sure UAT has no supporting indexes? If you're joining on a (set of) column(s) that is (part of) the PK or that has a unique constraint, then there is an index.
    If there are no covering indexes for your queries, then you can't have predictable results.
    Other than that, perhaps UAT is multiprocessor, and DEV just (restricted to) 1 or 2? In that case, try adding OPTION (MAXDOP 1) as the very last part of the query on UAT.
  3. ramkumar.mu New Member

    I am sure there are no covering indexes for the query
    And, adding OPTION (MAXDOP 1) did not work either as neither of the plans (in UAT and DEV) was using Parallel execution plan
  4. Adriaan New Member

    Well, that brings me back to my previous point:
    If there are no covering indexes for your queries, then you can't have predictable results.
    For results, read: execution and/or response time.
  5. satya Moderator

    I think you may take help of sys.dm_db_missing_index_group_stats DMV, not sure if it works in SQL 2005 (sorry don't have 2005 instance to check).

Share This Page