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
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.
Also for frequently used queries, you can analyze them using the EXPLAIN statement. That will tell you if the appropriate indexes are being used and how many rows are being scanned.
Next to looking at performance issues with queries, I would also check whether the DB and the tables within the DB are not to much fragmented.
You can issue the DBCC showcontig statement to check this. If it shows that the tables are heavily fragmented, you should consider creating a maintenance plan that is regularly executed. In that maintenance plan, you should specify that indexes should be rebuild. By doing so, the tables will be defragged.
One problem with Auto-shrink is that you have no control over when it kicks in, so it can run during live usage and make everything grind to a halt. If you really want your database to be shrunk, set a maintenance plan to do this out of hours. But I'm not sure when you'd want this option set, actually. If there's plenty of disk space, let the DB grow naturally. If there isn't, then get more. Disk space is cheap, but delays from a slow app can get expensive. Also turn off auto-close.
Refer to this serverfault question on why having autoshrink turned on is a bad idea.