query is running fine few days before but now its taking more time in sql server

  • Dear All,

    we have an complex select query which is running fine few days before but now its taking more time.

    Also the same query is running fine in the DEV environment(taking 52 seconds) where the server has less configuration then the PROD environment(taking 10mins).

    I did the following thing and it was not working..

    1) Updating the statistics.

    2) Re-indexing

    when I tried using MAXDOP query hint and set the DOP to 1, the query took 4mins in PROD.

    some other details:

    Dev server have 2 processor and duel core 32 GB of RAM

    PROD has 132GB RAM and 2 processor (totally 40CUP's)

    SQL Server 2008R2

    OS: windows Server 2008

    DB size:~ 400GB

    MAXDOP set to 8

    TIA.

    regards,

    Kesavan

  • First thing that comes to mind is to compare the execution plans from both servers.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • And to check indexes on both and data volumes. If the data volumes are different between the servers, a different plan is expected.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Koen Verbeeck for ur reply.

    I will check the execution plan.

    Thanks GilaMonster for ur reply:

    the data volume is same also the index(recently we restored the PROD DB into DEV).

  • What do you mean by 'recently'? More than a few days? From before the prod server started running slow?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster,

    the restoration happen one month back.

    this both the server are new and the DB is related to People soft.

    Recently we upgraded the DB from 2005 to 2008R2(in new server).

    after the completion of the upgrade, we restored the PROD into DEV

    Initially i.e. last month the query was running fine (taking ~52 seconds) and now its running slow.

    regards,

    Kesavan.

  • Hi Koen Verbeeck,

    both the execution plans are not the same..

    one of the clustered index scan having cost as 56% in PROD but in DEV its 14%

    clustered index key lookup have 28% in PROD and DEV having cost at 9%.

    this clustered index are created on a computer column.

    can you please help me in finding

    1.how come the execution plan are not same as the index, data are same

    Regards,

    Kesavan

  • Post the plans please, descriptions of them don't help.

    Plus table definitions, index definitions and the query itself.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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