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:
    http://www.sql-server-performance.com/performance_introduction.asp
    http://support.microsoft.com/default.aspx?kbid=243589
    http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/7/
    http://www.winnetmag.com/Article/ArticleID/26260/26260.html
    http://www.codeproject.com/cs/database/sqldodont.asp

    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
    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