MSDN Tutorials

Introduction to SQL Server Performance Monitoring

Understand the fundamental concepts, key metrics, and essential tools for monitoring SQL Server health and performance.

Using Dynamic Management Views (DMVs)

Learn how to leverage SQL Server's powerful DMVs to gain real-time insights into server activity, query performance, and resource utilization.

SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_elapsed_time,
    qs.execution_count,
    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 AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_elapsed_time DESC;

SQL Server Profiler and Extended Events

Explore the capabilities of SQL Server Profiler and the more modern and efficient Extended Events for capturing detailed performance data.

Performance Tuning with Query Store

Discover how to use the Query Store feature to track query performance history, identify regressions, and enforce query plan stability.

Monitoring Index Performance

Learn to identify missing or underutilized indexes and understand how to maintain and optimize your database indexes for better query performance.

Performance Analysis with Performance Monitor (PerfMon)

Utilize Windows Performance Monitor to track system-level counters and SQL Server-specific counters for a comprehensive view of performance.

Common Performance Bottlenecks and Solutions

Understand common performance issues such as blocking, deadlocks, CPU pressure, and I/O bottlenecks, and learn effective strategies to address them.