question

kpsenthilkumar86 avatar image
kpsenthilkumar86 asked

How to interpret performance counters with poorly performing queries

We have developed an in-house SQL monitoring tool.Technique used in the tool is, capturing data from sys.dm_os_performance_counters and sys.dm_os_wait_stats for every 5 minutes, doing some manipulation, and interpretation. In that, some spikes could be seen on wait types like CXPACKET, and on performance counters like page lookups/sec. How it is possible to confirm that, this is what the query made that spike? Anymore data need to be gathered to identify this?
performance-monitorwait-typeperformance counterscxpacket
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
You would have to do live query monotiring. You can't capture what specific wait type was mapped to a query without capturing every query.
0 Likes 0 ·

1 Answer

·
JohnM avatar image
JohnM answered
Jonathan Kehayias wrote a query that would search the plan cache for execution plans that ran in parallel. You can find it here: https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/ This would at least give you an idea on what queries are causing the CXPACKET wait type. Hope that helps!
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kpsenthilkumar86 avatar image kpsenthilkumar86 commented ·
Thanks John, but, If any queries to pin point the spike would be fine.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.