SQL Server high CPU and I/O activity database tuning

Question

Our application tends to be running very slow recently. On debugging and tracing found out that the process is showing high cpu cycles and SQL Server shows high I/O activity. Can you please guide as to how it can be optimised?

The application is now about an year old and the database file sizes are not very big or anything. The database is set to auto shrink. Its running on win2003, SQL Server 2005 and the application is a web application coded in c# i.e vs2005

Answer 1

Run SQL Profiler on your database for a while to see if the "slowness" is due to any problem queries. Then you can analyize these queries in order to run any indexes or statistics to increase performance.

As the comment suggests though, auto shrink can result in a very fragmented database. The database will generally grow as it needs to and its usually best not to worry about how big it wants to be. As long as you perform regular transaction log backups then you're better off letting it grow. You might need to ask yourself is performance is more important than having to buy new.more disks.

You can also run some maintenance plans against the database to rebuild the indexes and statistics. This might sort things out in the short term.

Answer 2

1. defrag your harddisk (or at least the mdf/ldf) files.
2. put the ldf file on a separate harddisk than mdf, if possible
3. use the the profiling tool from SQL 2005; it will tell you which requests last most; then use the "show execution plan" tool to see the steps of execution; maybe you will get a hint on what indexes should be added; for example, full table scan should be avoided for large tables.