SQL Server provides a number of tools you can use to carry out performance monitoring and tuning. Each tool is useful in certain scenarios and you will often need to combine several of them to achieve the optimal results.
1. Activity Monitor – A component of SQL Server Management Studio that enables DBAs to view details of current activity in the database engine.
2. Dynamic Management View and Functions – Database objects that provide insight into internal SQL Server operations.
3. Performance Monitor – A windows administrative tool that you can use to record values for multiple performance counters over a period of time, and analyze the results in a variety of chart and report formats.
4. SQL Server Profiler – A tracing and profiling tool that you can use to record details of Transact-SQL and other events in a SQL Server workload, and then replay the events or use the trace as a source for database tuning. Note that this tool has been deprecated for database engine workloads, and is replaced by extended events.
5. SQL Trace – A lightweight, Transact-SQL based programming interface for tracing SQL Server activity.
6. Database Engine Tuning Advisor – A tool provided with SQL Server for tuning indexes and statistics based on a known workload.
7. Distributed Replay – An advanced tool for replaying workloads across a potentially distributed set of servers.
8. SQL Server Extended Events – A lightweight eventing architecture.
9. SQL Server Data Collection – An automated system for collecting and storing and reporting performance data for multiple SQL Server instances.
10. SQL Server Utility Control Point – A centralized management portal for monitoring server health for multiple instances based on specific collection sets.
11. Microsoft System Center Operations Manager – An enterprise-wide infrastructure management solution that uses management packs to collect performance and health data from Windows and application services.