如何修改SQL查询以筛选最近两周的运行统计数据？

Hey there! I've adjusted your query to only include data from the last two weeks by adding a filter on the last_execution_time column from sys.dm_exec_query_stats. Here's the modified version:

SELECT TOP 10 
    total_worker_time/execution_count AS Avg_CPU_Time,
    execution_count,
    total_elapsed_time/execution_count AS AVG_Run_Time,
    (
        SELECT SUBSTRING(
            text,
            statement_start_offset/2,
            CASE WHEN statement_end_offset = -1 
                THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                ELSE statement_end_offset 
            END - statement_start_offset
        )/2 
        FROM sys.dm_exec_sql_text(sql_handle)
    ) AS query_text 
FROM sys.dm_exec_query_stats 
WHERE last_execution_time >= DATEADD(week, -2, GETUTCDATE()) -- Only include queries run in the last 2 weeks
ORDER BY AVG_Run_Time DESC;

What changed? #

• I added a WHERE clause that checks if the query was last executed within the past two weeks. Using GETUTCDATE() instead of GETDATE() is a good practice here to avoid timezone or daylight saving time discrepancies.
• This filters out any queries that haven't been active in the last 14 days, so your results only reflect recent query performance.

Quick note: #

Remember that sys.dm_exec_query_stats keeps aggregated stats for all runs of a query plan since it was cached. If a query was run both before and during the two-week window, the averages shown will include all those executions, not just the recent ones. If you need to isolate stats only for the last two weeks (excluding older runs), you'll need to use tools like Query Store or Extended Events, as sys.dm_exec_query_stats doesn't track individual execution timestamps beyond the last one.

内容的提问来源于stack exchange，提问作者shaj004