While SQL Profiler and Perfmon are and will always be valuable tools, a SQL Server 2005 instance already stores a huge amount of information on its operational details in memory and exposes it through dynamic management views (DMVs) and functions (DMFs).
I would highly recommend that you go through the following 2 articles (there are others as well, but these 2 are an excellent starting point):SQL Server - Uncover Hidden Data to Optimize Application Performance:http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspxSQL Server 2005 Waits and Queues - SQL Server Best Practices Article:http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
The first article shows you how to use the new powerful capabilities of SQL Server DMVs and DMFs to get information on the costliest code running in your instance, in terms of CPU, memory, file I/O etc.
The second article shows how you can track down the main bottleneck(s) in your system. As was pointed out earlier, you cannot plan for improved performance without first knowing where the issues are, ie. which resources are experiencing the heaviest load.
Run the Track_waitstats_2005
sproc, mentioned in the 2nd link, on your system to get the wait types ranked by level of importance in your system. Get the sproc from http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/waitstats/sql05vb049.mspx
Once you get info on the top few wait types (bottlenecks) in your system, you will be able to plan your next step.
Note that the results of these types of analysis are sampled since the latest instance restart, so make sure you allow for a few days' time (at least) to collect enough statistics.
__________________________________________________________________________________SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based TablesPersisting SQL Server Index-Usage Statistics with MERGETurbocharge Your Database Maintenance With Service Broker: Part 2