Diagnosing CPU performance issues with SQL Server can be a bit tricky, particularly if you have a system that exhibit a CPU spikes pattern throughout the day. You could run some SQL DMVs in order to identify holistically how much CPU time each query is consuming, but that does not give you a real-time way of tracking CPU resource starvation on SQL Server.

Recently I have been asked to devise a system on a few of our servers that are experiencing CPU resource bottleneck issues, the monitoring system needs to identify when SQL Server is suffering from CPU bottlenecks, and report the queries currently running on the server via email alerts, along with performance statistics that can help you diagnose which queries are consuming most of the resources, and why are they doing so.
Read more →