When your application experiences a performance problem chances are some aspect of your application has or is hitting a bottleneck. What exactly is a bottleneck? Per Wikipedia a bottleneck is a phenomenon where the performance or capacity of an entire system is severely limited by a single component. A common single component in a SQL Server system could be any of the following:
There are several tools within SQL Server that you can use to monitor and identify which of these single components are the bottleneck. You can use System Montior, SQL Profiler, sp_who or sp_who2. You can now access the sys.dm (Dynamic Management) procedures and write SQL queries to obtain system information in SQL Server 2005. In my upcoming blog posts I will cover these in more detail so please check back often!