Pay particular attention to index and query tuning.  Make sure you have
indexes that the optimizer can use to generate the most efficient plan.
Prioritize tuning so that the most often executed and expensive queries are
addressed first.  Also consider indexed views, which are especially
appropriate for aggregated data.   Keep in mind that too many indexes can
hurt performance if you do a lot of inserts/updates so you'll need to
perform cost-benefit analysis.

I suggest you get a good book that covers query and index tuning in depth.
I recommend Inside Microsoft SQL Server 2005: T-SQL Querying, ISBN
9780735623132.

Use sp_executesql to execute your dynamic SQL (not EXEC). Even better,
try to use a prepared statement. In your queries, make sure to use the
indexes, avoid calling functions and do not sort in SQL unless it is
absolutely necessary (sort on client side instead).

You could also save typical queries and run them through the Database
Tuning Advisor, which will suggest how to index your tables. This
wizard is available with SQL Server 2005 in the Management Studio, but
it can help to tune SQL Server 2000 databases as well.

If you can afford it, use SQL 2005 Enterprise Edition, which will allow
you to partition your tables. Partitions can greatly improve speed.
Again save a typical query and run it through the Database Tuning
Advisor, which can suggest how to create optimal partitions.

This wizard is just awesome, but of course if your queries are
completely random and different it won't be of much help since it need
a specific workload to make suggestions.

Regard,
lucm