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