SQL Server Performance

Sql 2000 db query perform slow on sql 2008.

Discussion in 'ALL SQL SERVER QUESTIONS' started by Jörgen, May 14, 2012.

  1. Jörgen New Member

    Hi everybody

    I have a problem that i can not solv.
    The sql query attached have ben used against a sql 2000 without any problem and takes about 7 sec to perform.
    Now i have installed a new server with sql 2008 and suddenly the query takes about 5 min to perfom.

    The new server has more memory and have faster cpu's and is installed by best practice.
    The database is backed up from the old server and restored on the new.
    Compability mode is set to sql 2000.

    If i run a more simple query it's very fast.

    If i change tbl1.art or tbl5.pdate to a static value insted in this row it also runs very fast.
    AND Tbl4.prcid = (SELECT MAX(prcid) FROM Tbl4 WHERE (ftg='DAT' AND Tbl4.PRICING = 0 AND Tbl4.art = Tbl1.art AND Tbl4.Tdate = Tbl5.pdate))

    So what has ben changed between sql 2000 and sql 2008 ?
    Shouldn't compabilty mode sql 2000 make a sql 2000 database and querys run without problem ?
    What should i do ?

    SELECT
    Tbl1.cstid AS Kgrp, sum((Tbl1.cstpqty + (Tbl1.cstmqty/Tbl1.qty)) * Tbl2.qty ) as scost
    FROM
    Tbl2
    INNER JOIN
    Tbl3 ON tbl2.art = Tbl3.art
    INNER JOIN
    Tbl5 ON a.art = Tbl5.art
    INNER JOIN
    Tbl4 ON a.art = Tbl4.art
    INNER JOIN
    Tbl1 ON Tbl4.prcid = Tbl1.prcid
    AND Tbl4.art = Tbl1.art
    AND Tbl4.idimid = Tbl1.idimid
    WHERE
    Tbl3.ftg = 'Dat'
    AND Tbl1.ftg = 'Dat'
    AND Tbl1.ctype IN(1,3,4,5)
    AND Tbl4.ftg = 'Dat'
    AND tbl2.ftg = 'Dat'
    AND tbl2.idate >= '2012-01-01' AND tbl2.idate <= '2012-01-15'
    AND Tbl4.prcid = (SELECT MAX(prcid) FROM Tbl4 WHERE (ftg='DAT' AND Tbl4.PRICING = 0 AND Tbl4.art = Tbl1.art AND Tbl4.Tdate = Tbl5.pdate))
    AND Tbl5.Mtype = 0
    AND Tbl5.ftg = 'Dat'
    AND Tbl4.recid = (select max(Tbl4.recid) as Recid from Tbl4 where(Tbl4.art = Tbl1.art and Tbl4.prcid = Tbl1.prcid and Tbl4.ftg = 'Dat'))
    GROUP BY Tbl1.cstid
  2. Luis Martin Moderator

    Welcome to the forums!
    Did you full update statistics after restoring in new server?
  3. Shehap MVP, MCTS, MCITP SQL Server

    It sounds like here the root reason beyond these distinct performance reads is relevant to query analyzer of SQL Server 2008 which elected different query execution plan with different indexes .

    Therefore, I do prefer much to update statistics as Luis said , but if it still persist , you have to look for sufficient indexes , hence please work out the below ones implemented with 2008 techniques (Data Compression + Index Filters)

    Createnonclusteredindex Tbl1_index1 on Tbl1

    ( art,prcid,idimid,ftg,ctype,pdate)

    include(cstid,cstpqty,cstmqty,qty )

    where ( ftg ='Dat'AND ctype IN(1,3,4,5))with (data_compression=page)

    createNonclusteredindex Tbl5_index2 on Tb5( art ,Mtype,ftg ,pdate)

    where ( Mtype = 0 AND ftg ='Dat')with (data_compression=page)

Share This Page