Capturing query performance metrics in SQL Server is crucial for diagnosing performance issues and optimizing database operations. Several common methods are widely used to gather and analyze these metrics:

1. Dynamic Management Views (DMVs) and Functions: SQL Server provides a set of DMVs and functions that give insights into the server's health, performance, and optimization opportunities. Key DMVs for query performance metrics include:

   • sys.dm_exec_requests: Provides information about each request that is currently executing.

   • sys.dm_exec_sessions: Shows active user connections and sessions.

   • sys.dm_exec_query_stats: Offers aggregate performance statistics for cached query plans, including execution count, average execution time, and total logical reads/writes.

   • sys.dm_exec_sql_text(sql_handle): Retrieves the text of the SQL batch that is identified by the given sql_handle.

   • sys.dm_exec_query_plan(plan_handle): Provides the query execution plan for a batch that has been executed and is identified by a plan_handle.

2. SQL Server Profiler and SQL Trace: Both tools allow for capturing real-time data about server activity, including the execution of queries, their duration, and resources consumed. Although SQL Server Profiler is a GUI tool and SQL Trace is its script-based counterpart, they essentially gather the same types of data. These tools are more suitable for short-term monitoring due to their potential performance impact on the server.

3. Extended Events (XEvents): A lightweight, highly scalable, and comprehensive event-handling system for monitoring SQL Server performance. Extended Events provide a method to collect detailed data and can be used to track query execution metrics with minimal performance overhead. They are highly customizable and the preferred approach for monitoring in newer versions of SQL Server.

4. Query Store: Introduced in SQL Server 2016, the Query Store feature collects detailed performance metrics for queries, plans, and runtime statistics. It allows for tracking performance over time, identifying queries that have regressed in performance, and forcing SQL Server to use specific query plans. Query Store is especially useful for capturing performance data in a more persistent manner compared to DMVs, which reset their data upon server restarts.

5. Performance Monitor (PerfMon): A Microsoft Windows tool that tracks various system and SQL Server performance metrics over time. It can be used to gather performance counters related to SQL Server, such as batch requests/sec, SQL compilations/sec, and page life expectancy, among others.

6. Custom Scripts and Tools: Many DBAs write their own T-SQL scripts to capture specific metrics or use third-party monitoring tools designed for SQL Server to collect and analyze performance data.

When using these methods, it's important to balance the need for detailed performance data with the potential impact on server performance, particularly with tools like SQL Server Profiler and SQL Trace. Extended Events and Query Store are recommended for their lower impact and comprehensive data collection capabilities.