MSDN Documentation

SQL Server Management Studio (SSMS)

SSMS is the primary graphical tool for managing and interacting with SQL Server. It offers numerous features crucial for performance tuning, including query execution plans, activity monitor, and performance reports.

Dynamic Management Views (DMVs) and Functions (DMFs)

DMVs and DMFs provide real-time operational information about the SQL Server instance. They are indispensable for diagnosing performance issues by exposing metrics on query activity, resource utilization, locking, and much more.

Key DMVs for Performance:

• sys.dm_exec_requests: Information about currently executing requests.
• sys.dm_exec_sessions: Information about active user sessions.
• sys.dm_os_wait_stats: Information about waits that have occurred.
• sys.dm_db_index_usage_stats: Information about index usage.

SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'PAGEIOLATCH%';

SQL Server Profiler

Profiler allows you to trace events occurring on your SQL Server instance. You can capture queries, stored procedures, and other events to analyze execution times, identify inefficient queries, and understand resource consumption.

Note: While powerful, Profiler can introduce overhead. Consider using Extended Events for production environments.

Database Engine Tuning Advisor (DTA)

DTA analyzes a given workload (a trace file or a set of T-SQL statements) and provides recommendations for physical database design structures, such as indexes, indexed views, and partitioning, to improve query performance.

Query Store

Available in SQL Server 2016 and later, Query Store automatically captures a history of queries, execution plans, and runtime statistics. It helps identify performance regressions, track query plan changes, and force specific plans.

Extended Events

Extended Events is a more scalable and flexible tracing system than SQL Server Profiler. It provides a lightweight mechanism for capturing detailed information about SQL Server events, making it ideal for performance analysis without significant overhead.

Performance Monitor (PerfMon)

Windows Performance Monitor is a system utility that allows you to capture and view performance data from various system and SQL Server counters. It's useful for monitoring overall system health and identifying resource contention (CPU, Memory, Disk I/O).

Third-Party Tools

Numerous third-party tools offer advanced features for SQL Server performance monitoring, analysis, and alerting. These can range from comprehensive database performance suites to specialized query tuning and indexing tools.