I read a great article regarding of how to optimize the performance of queries (that you or your DBA had written) in SQL Server 2005.

I doesn't matter if your server is 'strong', or if your object oriented code is perfect or even if you're using tools to maintain good performance to the customer, in the bottom line, if you are handling and working with a BIG amount of records in your database, those list of things are worthless if you database SQL queries are being written bad.

The following article outlines some good points and issues regarding this problem and maybe will help you to solve these problems and to maintain good performance.