SQL Server Profiler
I do not want to cover everything that can be done with the SQL Server Profiler here, but I do hope that most of the advanced developers out there who use SQL Server regularly are familiar with it. It has many uses, but for me it has become an invaluable tool to help find some of the bottlenecks in an application. We have all inherited many applications in our days that were written by a coworker, or even developers at another company. It's always interesting to dive into the code and see what is going on. It will be fairly obvious from looking at a few pieces of the application whether the developer was a novice or really advanced. This not only holds true for the application code, but the SQL queries that reside in the application. If you want to really bog down an application, throw some horrible SQL or some really bad looping over a query in there. The results will not be pretty!
This brings me to the use of the SQL Server Profiler. This tool will allow you to watch in real time the queries that are hitting your SQL Server instance. You can set some filters and other things like that to get a quick view of the queries that are long running. That is a good place to start looking if you are experiencing some slowdown in a database intensive application. You can also copy and paste the query into Query Analyzer and being your optimization from there. Below is a link to a video from SQLServerCentral.com that describes the basics of using the Profiler. If you have never used this tool before, check it out and make it part of your debugging process.