SQL SERVER – Visible Offline Scheduler and Performance

Not every problem is complex but all the problems are for sure interesting when I am working with my client on Comprehensive Database Performance Health Check. Today we will discuss Visible Offline Scheduler and Performance.

If you have worked with me before you probably know that during my health check service, I do not take control of the system. The way I work is that we share screen via GoToMeeting and I watch you going through various steps. My goal is to help you learn the solution to the problem which you are facing so next time when you face the problem, you can solve it without reaching out to me. Ultimately it is your server and you should know the solution to the problems.

One of the clients recently upgraded their server from 2 CPU to 8 CPU and after upgrading their system they were not getting any better performance. I suggested he go to the SQL SERVER – Query for CPU Pressure and run the query on the screen and send it to me. From the results it was very clear to me that client was facing extreme CPU pressure and it was surprising for sure, considering they have just upgraded the CPU to almost 4 times.

Visible Offline Scheduler

I suggested to him that he should send me the results of the following query:

SELECT *
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255;

Here is the partial result he shared with me.

I would like to pay attention to the column status. It was very clear from the result that out of 8 CPU only 1 CPU was online and the rest 7 was actually offline and that was the primary reason for the slowness of the system.

I suggested he go to Server Properties and go to the Processor tab. Over there, it was very clear that Processor Affinity was selected and only one CPU was selected and the rest of the CPUs were not selected at all.

I suggested checking the top checkbox next to the Automatically set processor affinity mask for all processors to bring all the processors online.

Once that was done, my client was able to see all the schedulers online and the performance of their system was improved significantly which they were expecting.

Note: Restart is NOT required for this setting to take effect. It takes effect immediately.

As I said there is sometimes a very simple solution but the fun is figuring out the problem, the journey is always more fun than the destination.

Reference: Pinal Dave (https://blog.sqlauthority.com)