CPU_ID Gaps in SQL Server sys.dm_os_schedulers

We all know (or should!) that SQL Server is NUMA aware. In fact, there are certain background processes that are created and run per NUMA node inside of SQL Server, which is why the soft-numa changes in 2016 can make a big performance gain. There was a need to affinitize certain cpus for a SQL…

0

Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for high Resource statements.

Extended events is a powerful feature that allows us to troubleshoot performance issues within SQL Server. One of the ways you can utilize extended events to aggregate statements is described in a blog post below by Bob Dorr SQL Server 2012: RML, XEvent Viewer and Distributed Replay Let’s apply that methodology to a contrived scenario,…


Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption

In most cases, people utilize SQL Server features and capabilities in a common and typical usage pattern.  But there are some instances where environments do something just a little different, or fill the bucket to the absolute brim.  With the advent of AlwaysOn Availability Groups, it is such a robust high availability and disaster recovery…


Troubleshooting SQL Server High CPU usage using Xperf

  Xperf/WPA is a powerful Windows tracing tool that can be used to assist in troubleshooting numerous issues on a Windows server.   The utility allows for in depth tracing without the high overhead typically associated with such tracing.   This is accomplished by making use of the Event Tracing for Windows architecture (ETW).     ETW is built…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…