What is the right way of capturing expensive queries?

Question

Hi All, 

While capturing expensive queries by CPU or IO, why it is important to group queries by query_hash and then tune them instead of directly pulling top 10 queries from sys.dm_exec_query_stats DMV?. 
What am I missing ? is it wrong way to capture expensive queries> if so, I want to understand why?
please share your thoughts.

For example, I want top 10 I/O driving queries
--way1:direct method 
SELECT  top 10 
        qs.execution_count,
        qs.min_logical_reads,
        qs.max_logical_reads,
	(qs.total_logical_reads/qs.execution_count) AS AvgLogicalReads,
        qs.min_elapsed_time,
        qs.max_elapsed_time,
		(qs.total_elapsed_time/qs.execution_count) AS AvgElapsedTime,
        OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
        SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE 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
order by qs.total_logical_reads desc;


--way2:using query_hash 
SELECT TOP 10
        [qs].[query_hash],
        SUM([qs].[total_logical_reads]) total_logical_reads,
        SUM([qs].[execution_count]) total_execution_count
FROM    [sys].[dm_exec_query_stats] qs
CROSS APPLY [sys].[dm_exec_sql_text]([qs].[sql_handle]) AS qt
GROUP BY [qs].[query_hash]
--HAVING  SUM([qs].[execution_count]) > 100
ORDER BY SUM([qs].[total_logical_reads]) DESC;

Regards,
Sam 

Answer 1

The point with using query hash, is that a misbehaving clients may submit queries like:

SELECT * FROM tbl WHERE id = 12
SELECT * FROM tbl WHERE id = 233
SELECT * FROM tbl WHERE id = 11

rather than using a parameterised statement. The hash will group those queries with the same shape together.

I echo Yunus's suggestion that you should use Query Store rather than the DMVs. But you need to use the query hash in Query Store as well.

Answer 2

Hi Samantha , you can use the query store for expensive queries. You may see more meaningful results

Answer 3

Hello

you should definitively use Query Store to identify most expensive queries. here is the documentation on how to configure and use it

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16

Answer 4

Hi @Samantha r

Generally, we generate queries based on the dynamic management view sys.dm_exec_query_stats to capture the most resource hungry queries across a SQL instance. That approach has some drawbacks though. First, it is cleared out every time an instance restarts, and second it only keeps figures for currently cached plans, so when a query recompiles, data is lost.

Alternatively we can use SQL Server Query Store option for this purpose. One of the great features of it is that performance stats are stored in the database, so they aren’t lost in either of the above scenarios.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

There are few issues with a query store:

1. The logins won't be captured.
2. You are stuck with only one database at a time. (The query store needs to be enabled for each database)
3. No visibility of currently executing queries.

In our situation we needed to capture the users who executed or currently executing the query.