SQL Server

Q. I have SQL Server 2000 with approximately 45 databases on a single instance. These databases are used by multiple ASP applications running on my public Web site. SQL Server regularly reaches 100 percent CPU utilization and requires reboots. How should I start troubleshooting and repairing this problem?
A Start with rebuilding all of your indexes. It’s easy, assuming you have memory configured correctly. Then run SQL Profiler for a few hours and capture a snapshot of your standard business T-SQL traffic. Run this trace through the Index Tuning Wizard (ITW) that comes built into SQL Server 2000. It will take a look at the queries and make recommendations for indexes to improve performance.

An article, called 25 SQL Commandments, is a list of tips to help a developer avoid the plague of poorly written SQL statements. The article itself is written for Oracle with some specific Oracle commandments, but a lot of the commandments hold true for SQL Server, too. The 25 SQL Commandments briefly are:

Know your data and business application well.
Test your queries with realistic data.