SQL Server Performance

How to track down cause of performance issue

Discussion in 'Performance Tuning for DBAs' started by danielreber, Jun 16, 2004.

  1. danielreber New Member

    We have a have a set of procedures that run every night to load data. One of the steps takes about 3 minutes to run but on Sundays and Tuesdays it jumps to 4-6 hours. It isn't the data because the amount of data on Sunday is less than the rest of the week and Tuesday is a little than average. Our client says that they do not have anything running on the server at that time so I am at a loss as to why this is taking so long. Is there a monitoring tool that I can use to see if it is another process on the server that is causing it or if it is something in SQL Server?


    Daniel Reber
    Datamasters, Inc
  2. satya Moderator

    Recompile the stored procedures and schedule the db reindex regularly in order to get optimum performance.

    If you turn on the NOCOUNT option, stored procedures won#%92t return row-count information—and therefore, you#%92ll save the network overhead involved with communicating that information to the client.

    Using a return value is particularly useful when you#%92re inserting a new record. In
    http://www.sql-server-performance.com/stored_procedures.asp
    http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp

    The most common tools required to investigate high CPU utilization are the System Monitor (a.k.a. the Performance Monitor), SQL Profiler, and the Blocker Script. More information about the Blocker Script can be found from these articles:

    http://support.microsoft.com/?id=271509

    Resolving high CPU utilization issues can be very time-consuming, especially when you don't know where the problem lies. Try to solve the problem, and not the symptoms. With proper optimization techniques, such as adding proper indexes, redesigning badly written queries, and so on, you can avoid almost all of these issues.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page