How to optimize the SQL Server database?
Q: I have an SQL 2005 SP2 server, which is bombarded with simple SQL queries (2queries/s). Database is about 1Mio records, Dual Xeon, Win2003 server, 4Gb RAM. DB is consistent, has indexes, is optimized etc etc. Problem: One or twice a day, server performance REALLY drops to dead slow. Sometimes it recovers, most of the time it doesnt and reboot is required. We’ve tried every tuning options that is and we have no viable solution. What could be causing this and what can I try to solve it?
Jason Hall says: You say that the database is optimized and has indexes but this is a very broad statement. Having indexes does not mean that the database has the correct indexes. It is also possible and very frequently seen that a database has far too many indexes which can adversely affect performance. It is also possible that the indexes are highly fragmented which could lead to high IO levels. To optimize the database I would first revisit your index strategy to determine if your indexes are appropriate. For assistance with this you can use the database tuning advisor inside of SQL Server Management Studio or the SQL Tuning component of Toad for SQL Server. You also say that the database is consistent, I’m guessing this means that you are not inserting records into this database. If this is the case I recommend leaving very little free space in the index pages. If your indexes are optimized and you are still seeing these performance problems you will need to capture some activity during the slowdown. You can either run a profiler trace and capture some relevant performance monitor counters or use a product like Spotlight or Performance Analysis from Quest Software. These products will help isolate the slowdown to a specific performance counter or problematic SQL Statement. If you can find a problematic SQL Statement you should then look at the execution plan for high cost operations that can be further tuned.
Technorati Tags:
SQL Server Management Studio, database performance, SQL