Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    10

    Unanswered: Stored Procedure taking forever to run

    Hi there i have a sql server 7 database that i copied across to another server this time running windows 2003 and sql server 8. I have a routine that runs every night on each machine. on the old machine it take about 2 hours to do. on this new machine it is taking up to 5.5 hours to do the exact same job. the results are the same but the time delay could become an issue later on so i would like to nip it in the bud now.

    Does anyone ahe any suggestions as to why the code would run so much slower on a newer and better spec machine.

    I have copied everything across so there is no difference in tables or stored procedures. is there an optimisation tool i can run ??

    has anyone got any ideas ?

  2. #2
    Join Date
    Feb 2008
    Posts
    10
    the step is suceeding but i am getting the following errors when running the procedure. I was also getting these on the old database.

    Executed as user: NT AUTHORITY\SYSTEM. ... operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or ot... The step succeeded.

  3. #3
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Quote Originally Posted by Jim O Sullivan
    is there an optimisation tool i can run ??
    There are a couple:

    Index Tuning Wizard
    http://msdn2.microsoft.com/en-us/library/aa902645.aspx

    sp_updatestats
    http://msdn2.microsoft.com/en-us/library/ms173804.aspx

    DBCC SHOWCONTIG
    http://msdn2.microsoft.com/en-us/library/aa258803.aspx

    ...which can help determine if you want to use

    DBCC DBREINDEX
    http://msdn2.microsoft.com/en-us/lib...8(SQL.80).aspx

    or

    DBCC INDEXDEFRAG
    http://msdn2.microsoft.com/en-us/lib...6(SQL.80).aspx

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Set Showplan_text On

    SET STATISTICS IO ON

    SET STATISTICS TIME ON
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    And if you don't need it

    SET NOCOUNT ON

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ignore the warnings about NULLs. Probably irrelevant to you.

    Does your process use a cursor? If so, the first thing you should do is rewrite it as a set-based process.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK

    Post the code

    my esp usb port is clogged
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •