SQL Server Performance

Slow Running Quires

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Raja.V, May 6, 2008.

  1. Raja.V New Member

    This is the scenario.....
    In the server we had we had sql server 2000 before but we upgarded it to sql server 2005....the stored procedures that were running before with the time span of 3 to 4 hours are now running for 8 to 9 hours......Below are some of the queries and their execution plan....please help me out with the solution for this ...
    Query 1
    UPDATE dbo.__m_dates
    SET acc_id = MP.acc_id
    FROM dbo.__m_dates M , dbo.__m_acc_id_map MP WITH (NOLOCK)
    WHERE M.acc_id = MP.m_acc_id
    The above query ran for 7 mins and the corresponding plan below

    Plan
    Table Update(OBJECT:([mrg_live006].[dbo].[__m_dates]), OBJECT:([mrg_live006].[dbo].[__m_dates].[Temp_Index]), SET:([mrg_live006].[dbo].[__m_dates].[acc_id] = RaiseIfNull([mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id])) WITH UNORDERED PREFETCH)
    |--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
    |--Table Spool
    |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
    |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [mrg_live006].[dbo].[__m_dates].[acc_id] as [M].[acc_id] = [mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id] THEN (1) ELSE (0) END))
    |--Top(ROWCOUNT est 0)
    |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([M].[acc_id]=ANY([mrg_live006].[dbo].[__m_dates].[acc_id] as [M].[acc_id]), [MP].[acc_id]=ANY([mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id])))
    |--Nested Loops(Inner Join, WHERE:([mrg_live006].[dbo].[__m_acc_id_map].[m_acc_id] as [MP].[m_acc_id]=[mrg_live006].[dbo].[__m_dates].[acc_id] as [M].[acc_id]))
    |--Index Scan(OBJECT:([mrg_live006].[dbo].[__m_dates].[Temp_Index] AS [M]), ORDERED FORWARD)
    |--Table Scan(OBJECT:([mrg_live006].[dbo].[__m_acc_id_map] AS [MP]))
    Query 2
    Line no 526
    UPDATE dbo.__m_amounts
    SET acc_id = MP.acc_id
    FROM dbo.__m_amounts M , dbo.__m_acc_id_map MP WITH (NOLOCK)
    WHERE M.acc_id = MP.m_acc_id

    The above query ran for 29 mins and the corresponding plan below
    Plan

    Table Update(OBJECT:([mrg_live006].[dbo].[__m_amounts]), SET:([mrg_live006].[dbo].[__m_amounts].[acc_id] = RaiseIfNull([mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id])))
    |--Top(ROWCOUNT est 0)
    |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([MP].[acc_id]=ANY([mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id])))
    |--Nested Loops(Inner Join, WHERE:([mrg_live006].[dbo].[__m_acc_id_map].[m_acc_id] as [MP].[m_acc_id]=[mrg_live006].[dbo].[__m_amounts].[acc_id] as [M].[acc_id]))
    |--Table Scan(OBJECT:([mrg_live006].[dbo].[__m_amounts] AS [M]))
    |--Table Scan(OBJECT:([mrg_live006].[dbo].[__m_acc_id_map] AS [MP]))
    Query 3

    Line no 587
    UPDATE dbo.__m_client_code_details
    SET acc_id = MP.acc_id
    FROM dbo.__m_client_code_details M, dbo.__m_acc_id_map MP WITH (NOLOCK)
    WHERE M.acc_id = MP.m_acc_idThe above query ran for 20 mins and the corresponding plan below
    Plan

    Table Update(OBJECT:([mrg_live006].[dbo].[__m_client_code_details]), SET:([mrg_live006].[dbo].[__m_client_code_details].[acc_id] = RaiseIfNull([mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id])))
    |--Top(ROWCOUNT est 0)
    |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([MP].[acc_id]=ANY([mrg_live006].[dbo].[__m_acc_id_map].[acc_id] as [MP].[acc_id])))
    |--Nested Loops(Inner Join, WHERE:([mrg_live006].[dbo].[__m_acc_id_map].[m_acc_id] as [MP].[m_acc_id]=[mrg_live006].[dbo].[__m_client_code_details].[acc_id] as [M].[acc_id]))
    |--Table Scan(OBJECT:([mrg_live006].[dbo].[__m_client_code_details] AS [M]))
    |--Table Scan(OBJECT:([mrg_live006].[dbo].[__m_acc_id_map] AS [MP]))
    Thanks alot for your help
    Regards,
    Raja.
  2. MichaelB Member

    Are you updating statistics often? defragging? reindexing? Just looking for some obvious things since I see a lot of table scans it seems like it doesnt want to use the indexes for some reason. (assuming there are indexes on those fields).
  3. moh_hassan20 New Member

    in query 1 i found:
    |--Index Scan(OBJECT:([mrg_live006].[dbo].[__m_dates].[Temp_Index] AS [M]), ORDERED FORWARD)
    |--Table Scan(OBJECT:([mrg_live006].[dbo].[__m_acc_id_map] AS [MP]))
    that means that the optimizer use for the referenced table __m_acc_id_map table scan ,
    That means that there is no index for the field __m_acc_id_map.m_acc_id
    so create index for that field

    i find that table __m_acc_id_map is used in the other queries
    as a rule of thump: be sure that fields included in join , where conditions and order by must have indexes

    - Try create the index and execute query plan again.
    - You can benefit from the enhancement of the index mechanism in sql 2005
    - Try to install last SP2 , there is a fix of long running queries at: http://support.microsoft.com/kb/919905/
    can i know the no of rows in the tables, type of index used (clustered or not clustered): even 4 hours is too long.
    let me know the results.
  4. Adriaan New Member

    Please check whether the column that you are updating is part of an index. If it is a clustered index, then note that the update will affect both the clustered index and all the non-clustered indexes on this table - this can take a lot of time.
    If there is a foreign key constraint with update, where this column is the 'parent', then this will again take time. Also check the indexes on the foreign table.
    If there is any trigger on the table for the update event, then test with the trigger(s) disabled, and check the difference.

Share This Page