Using sys.dm_exec_session_wait_stats and sys.dm_exec_query_stats together provides a powerful approach to troubleshooting SQL Server performance issues by offering insights into wait statistics at the session level and performance details of executed queries. Here’s how to leverage these Dynamic Management Views (DMVs) effectively:

Step 1: Identify Sessions with High Wait Times

Start with sys.dm_exec_session_wait_stats to pinpoint sessions experiencing significant waits, which can highlight potential bottlenecks.

SELECT session_id, wait_type, SUM(wait_time_ms) AS total_wait_time_ms
FROM sys.dm_exec_session_wait_stats
GROUP BY session_id, wait_type
ORDER BY total_wait_time_ms DESC;

Step 2: Analyze Query Performance Statistics

Utilize sys.dm_exec_query_stats to obtain performance statistics for queries executed by these sessions. This DMV provides information like CPU time, execution count, and total elapsed time.

SELECT
    qs.sql_handle,
    qs.execution_count,
    qs.total_logical_reads, qs.total_logical_writes,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.total_rows
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_elapsed_time DESC;

Step 3: Correlate Wait Events with Query Performance

Combine the two DMVs to correlate wait events with specific queries, offering a comprehensive view of how waits affect query performance.

SELECT
    sws.session_id,
    sws.wait_type,
    sws.total_wait_time_ms,
    qs.sql_handle,
    qs.execution_count,
    qs.total_elapsed_time
FROM
    (SELECT session_id, wait_type, SUM(wait_time_ms) AS total_wait_time_ms
     FROM sys.dm_exec_session_wait_stats
     GROUP BY session_id, wait_type) sws
JOIN sys.dm_exec_requests er ON sws.session_id = er.session_id
JOIN sys.dm_exec_query_stats qs ON er.plan_handle = qs.plan_handle
ORDER BY total_wait_time_ms DESC;

Step 4: Retrieve the SQL Text of Problematic Queries

To get the actual SQL text for the queries identified, use sys.dm_exec_sql_text.

SELECT
    qs.plan_handle,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset
      END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.plan_handle = 'YourPlanHandleHere';

Step 5: Optimize Based on Findings

Based on the analysis:

• Optimize queries with high waits or poor performance stats.

• Consider indexing strategies, query rewriting, or database configuration adjustments.

• Monitor changes to assess the impact of optimizations.

Step 6: Continuous Monitoring

Regularly monitor both DMVs to preemptively identify and address emerging performance issues, maintaining an optimized SQL Server environment.

By integrating insights from sys.dm_exec_session_wait_stats and sys.dm_exec_query_stats, you can effectively troubleshoot and optimize SQL Server performance, targeting both the session level and individual query executions for comprehensive performance tuning.