I've written several articles on SQL optimization, the most complete is here.
http://4penny.net/SQL-Server-Troublshooting.aspx
but I've recently come across some other useful tips that I'm going to post here until I have the time to organize them into an article.
If you are experiencing speed issues with SQL Server:
Check physical disk fragmentation (Windows system not SQL)
Check what SQL maintenance routines are being run automatically and how frequently they are run. I would recommend the following:
1. Reorganize Data and Index pages, changing free space percentage to 25% (Weekly)
2. Check Database Integrity, do this prior to backup schedule.
3. Update Statistics used by query optimiser (Daily/Nightly depending on maintenance window available). There is also a setting to see how much of the data is sampled during this procedure, you need to set this to the maximum you can I would suggest at least a 50% sample. The higher the sample percentage, the longer the maintenance job will run, so this again depends on the amount time you have available in a maintenance window.
4. Ensure Auto update statistics is set against the databases.
5. If it is a dedicated SQL server, enable the Boost SQL server priority on windows at the server level. Note: On some machines, this can have a detrimental effect as it can sometimes boost the priority that high that system processes do not get much processor time. Basically this setting is one of those that try in isolation so the effects are distinct. If enabling this is detrimental disable the option. The same applies if this option is already enabled, try disabling it. I know this sounds odd, but I have seen it help some systems and hurt others.
6. Revaluate hardware infrastructure, if the SQL server is stressing, maybe time to put in more memory and if required install Win Server 2003 enterprise and SQL Enterprise to take advantage of memory above the 3-4 GB limit. Or if you already have enterprise versions of these servers, with additional memory, then ensure the options to use memory in excess of 3-4GB are enabled. I believe there are a couple of settings, some in SQL and some in the operating system.