Don't be greedy in T-SQL queries

I am working on improving the performance of some stored procedures.  The queries all make use of index seeks and index scans to get to the data, so, nothing I can improve there.
The procs call other procs which call some more procs.  A join to a large table (+/-2million records) gets repeated in several of these procs.

So, I changed the deepest nested proc to return all the data which will be needed by the other procs.  I removed the repeated join; and the performance problem is solved.

So, two very important tips for performance tuning queries:

1. Only return the records you are interested in; and
2. Only touch a record once if possible.

SQL Server 2005 Performance Dashboard Reports

Last week our dba resolved serious SQL Server performance issues very effectively using SQL Server Performance Dashboard Reports.
Our server's four dual core processors were averaging 99.73% for a couple of days. After monitoring the situation with SQL Server 2005 Performance Dashboard Reports and implementing the suggested performance enhancements, our server's CPUs are now working at between 12% and 60%.

More about Microsoft SQL Server 2005 Performance Dashboard Reports:

"The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server."

"The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server."

Download it here.

Best Practices for Troubleshooting Slow Running Queries

A colleague forwarded this article to me. Although the article contains very useful and valid tips, he pointed out that it does not discuss poorly written T-SQL as a possible cause for slow running queries.

Poorly written T-SQL is probably such a huge topic that it deserves an article of its own.

Anyhow, I hope you can use some of the troubleshooting tips.