What's the right way of capturing expensive queries?

Question

Post reply

What's the right way of capturing expensive queries?

vsamantha35

SSChampion

Points: 11551

August 17, 2023 at 7:29 am

Go to Answer

#4263350

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

Jeff Moden

SSC Guru

Points: 1004324
More actions
August 17, 2023 at 5:26 pm
Answer
#4263821

It's like the ORDER BY in the second query. Do the ORDER BY using the column aliases for the SUMs rather than the SUMS themselves.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

the builtin Query Store will be of great help

A great starting ref can be found here: SQLSkills Blogs Erin

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

Hi Johan, Thank you. Actually, I wanted to know the reason the difference between the two methods.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

my bad. Sorry