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?