SQL Server Performance

very slow stored proc

Discussion in 'T-SQL Performance Tuning for Developers' started by tracyk, Dec 1, 2003.

  1. tracyk New Member

    I have a complex stored proc that was taking 35-40 secs to execute.

    It is now taking 3min 30+secs to execute.

    This stored proc still runs in 35 secs on my test sql server box.

    Nothing has changed on the production configuration. The indexes on the production server are updated every Sunday.

    Any clues???
  2. joechang New Member

    what are the system details for each machine: #, MHz, type of CPU, mem etc
    are there differences in the execution plan?
    what is the actual query?
  3. Luis Martin Moderator

    What means indexes on the production server are updated?.
    DBCC RBINDEX ?.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  4. tracyk New Member

    Production machine:

    8 550MHz Intel PIII Xeon
    2 G RAM
    WIN NT 4.0 SP6

    Test machine:

    4 2.8 GHz Intel P4
    2 G RAM
    Server 2000 - SP3

    Executaion plans seem to be exactly the same

    I ran the DBCC RBREPAIR during the day yesterday and I have a Database Maintenence plan that runs on Sunday that Reorganizes the Indexes based on 10%.

    Last night I ran the maintenance plan again. I also added a plan to update the stats based on 70% of database. In addition I used the database maintenance to check the database integrity with index and repair.

    This brought execution down to 1-2 minutes - which is still not good for the users.
  5. satya Moderator

    Try to update statistics on the tables/indexes involved on production box and execute the sp.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. Luis Martin Moderator

    Yes, also run DBREINDEX on critical tables, that include update statistics for that tables.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  7. joechang New Member

    does the execution plan show parallel operations?
    if so, try various values for OPTION (MAXDOP x)
    from 1,2,4, & 0 (use all procs)

    i find it rather suspicious that what once ran in 35-40sec on a 8xPIII-550 also runs in ~35sec on a 4xXeon MP2.8
    ( do you mean there are four physical Xeon MP 2.8 processors in your test system or are there actually two Xeon 2.8 processors with HT enabled?)
  8. tracyk New Member

    I have tried the sp_updatestats on the databases and DBCC DBREINDEX

    I have also run my maintenance plans overnight which do the same thing

    Neither of these have helped the problem

    I am checking the execution plan again for parallel operations to see if we can configure

    Thanks for you input so far....any other hints would be greatly appreciated!
  9. Luis Martin Moderator

    Beside Joe said (I think this is the point), look for hypothetical index. If there delete it.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  10. tracyk New Member

    There were no hypothetical indexes on the database...darn!<br /><br /><br />I ran a trace on each machine while running this stored proc. Below is one area where I saw differences between the production and development machines. Since this Fetch happens anywhere from 500 to 10,000 times depending on the parameters and data - I think this is where my problem is.<br /><br />FETCH NEXT FROM tempResultsWithVendRamp_Cursor INTO @Railroad1, @Railroad2, @Railroad3, @Railroad4, @Railroad5, @Railroad6, @drayRailroad1, @drayRailroad2, @drayRailroad3 SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted150170<br /><br /><br />Duration of 15 and Reads of 17 on the produstion machine.<br /><br />On the deveolpment box the duration is always 0 and reads of 13. <br /><br />Does this help? Any clues? (Again this query worked fine last week)
  11. satya Moderator

    What type of cursor is this server or client based?
    Try to submit the trace to index tuning wizard for any recommendation on index that may help to fine tune execution.

    Besides try to use temp tables instead of cursor, as they're prone to use lot of resources on SQL.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  12. tracyk New Member

    Last night I rebooted the production server and the query is now running in 12sec to 60 secs. I will wait and see if it gets worse as the load increases before claiming a small victory....though 60 secs is still too long.

    Any opinions on why a reboot would help?

    2 cursors are created in the stored proc to scroll through and update the 2 temp tables used in the stored proc....so server side.

    I will run the Index Tuning wizard to see if I can get better results.

    Thanks for your help!
  13. satya Moderator

    A reboot helps to drain few values in memory which helps to re-initialise the process to gain the performance.

    This may not help at all the times, better to workout from code side or add-up more resources

    HTH.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page