SQL Server Performance

Performance loss over time

Discussion in 'Performance Tuning for DBAs' started by pblsoft, Jun 7, 2004.

  1. pblsoft New Member

    I have a SQL Server 2000 database that over a period of 2 to 3 weeks gets slower and slower.
    After a stop/start of the server it works fine again.
    Where should I look to find the solution to this problem?
  2. satya Moderator

    Refer the articles below to troubleshoot:

    See if SQL Server is doing a lot of I/O. Tools like SQL Profiler and Blocker Script output can tell you if the query(s) are resulting in these large I/Os, and tuning or optimizing these individual queries from a statistics and indexes point of view may help from the disk I/O perspective. An improper index strategy can add to a disk subsystem workload. For example, instead of doing an Index Seek, the SQL Server Optimizer may choose to do a Table/Index Scan, resulting in a lot of unnecessary I/O.

    If the disk subsystem is a bottleneck, use the Index Tuning Wizard to go through a proper workload of the production load (captured in the form of a trace file) and see if it recommends any additional indexes.

    You have seen that adding appropriate indexes reduces load on disks, thus reducing the disk I/O. If the proper indexes do exist, adding more spindles to the RAID configuration may also help.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page