Скачать или смотреть Mastering SQL Server Query Performance Analysis A Comparison of sys dm exec query stats and sys dm e

What is the distinction between querying `sys.dm_exec_query_stats` and sorting by `total_elapsed_time` or `max_elapsed_time` columns, as opposed to querying `sys.dm_exec_requests` to identify queries currently in progress?


The two SQL Server queries you mentioned are used to analyze and troubleshoot performance issues in a SQL Server environment. They target different aspects of query execution and are particularly useful for identifying slow-running queries. Here's an explanation of each:

1. Querying `sys.dm_exec_query_stats` Ordered by `total_elapsed_time` or `max_elapsed_time` Columns

This query accesses the `sys.dm_exec_query_stats` dynamic management view, which provides aggregate performance statistics for cached query plans in SQL Server. When you order the results by `total_elapsed_time` or `max_elapsed_time`, you are essentially looking for queries that have historically taken the longest to run, either on average (`total_elapsed_time`) or at their peak (`max_elapsed_time`).

total_elapsed_time: This column indicates the total time spent executing a particular query plan since it was compiled, including all executions. Ordering by this column helps identify queries that are consistently slow or called frequently.
max_elapsed_time: This column shows the maximum time taken for a single execution of a query plan. Ordering by this column is useful to find queries that have spikes in execution time, which may indicate intermittent performance issues.

The query might look something like this:


SELECT *
FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC; -- or max_elapsed_time


This will return information about the most resource-intensive queries, which can then be further analyzed for optimization.

2. Querying `sys.dm_exec_requests` to Catch Queries in Execution

The `sys.dm_exec_requests` dynamic management view provides information about each request that is currently executing within SQL Server. This is particularly useful for catching slow-running queries in real-time, as it shows what is happening on the server at the moment.

When you query `sys.dm_exec_requests`, you can see details about currently executing requests, such as the SQL text being executed, the session ID, the current status of the request, how long it has been running, and more. This is especially useful in scenarios like yours, where users are actively experiencing delays, as it allows you to catch problematic queries while they are still running.

A typical query would look like this:

SELECT *
FROM sys.dm_exec_requests
WHERE status = 'running';


This query helps you identify long-running queries that are currently active, which might be contributing to the slowdown experienced by the users.


Here is my conclusion


Both of these queries are powerful tools for diagnosing performance issues in SQL Server. By analyzing historical performance data and current execution states, you can identify inefficient queries that may be causing system slowdowns, enabling you to take appropriate optimization steps