SQL query to Identify the most resource intensive queries in sql server database

You can use the “sys.dm_exec_query_stats” dynamic management view in SQL Server to identify the most resource-intensive queries. This view contains performance statistics for cached query plans, such as CPU time, execution count, and logical reads. Here’s an example SQL query that uses this view to identify the top 10 most resource-intensive queries:

SELECT TOP 10 
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.TEXT)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1) AS [Query Text], 
    qs.total_worker_time AS [Total Worker Time],
    qs.execution_count AS [Execution Count], 
    qs.total_elapsed_time AS [Total Elapsed Time],
    qs.total_logical_reads AS [Total Logical Reads]
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY 
    qs.total_worker_time DESC;

In this query, the “sys.dm_exec_query_stats” view is joined with the “sys.dm_exec_sql_text” view using a CROSS APPLY function to get the query text associated with each cached query plan. The columns returned by the query include the query text, total worker time, execution count, total elapsed time, and total logical reads. The results are sorted in descending order by total worker time, which is a measure of the CPU time used by each query. The TOP 10 clause limits the output to the top 10 most resource-intensive queries.