Earlier this week, I had posted two blog posts SQLOS Scheduler and the Process States and What is Latch. After reading both the blog post, I got another question from my client on What is Spinlock? Let us discuss today What is Spinlock.
Wait of Latches
Now that we have learned in our earlier article that latches which relatively lightweight can be still a performance troublemaker if there are many of them while dealing with multiple threads.
Now think about a scenario when a thread is acquiring a latch on the shared data structure in memory when another thread also wants to have access to the same resource. In that case, if the first thread has completed its task, it will give up the resources and the second thread can now have access to the resources. However, what would happen when the first thread still needs busy with the resources when the second thread needs the same resources. Honestly, in this scenario, the second thread has to wait in a suspended mode as discussed earlier.
Now thread will wait in the suspended mode till it will get learn that the necessary resource is available and at this time, it will go to ready queue or runnable state. Once the CPU is available for the thread, it will start processing after acquiring the latches on the resources.
Now, everything we discussed totally works, and that is how the SQL Server engine is programmed. However, there are still moments under heavy workload SQL Server may face slowness or performance degradation. In this scenario to overcome SQL Server Engine has another brilliant mechanism which is Spinlock.
Need for Spinlock
Now think of the scenario when your system is under heavy workload pressure. Latches can be very expensive due to context switching scenarios where a thread has to go to different states (running >> suspended >> runnable >> running etc) when the necessary resources are not available (or already busy). Scheduling the thread from running state to not running and again to the running can be very expensive. In this scenario, Spinlock can help big time.
The way the spinlock mechanism is built when the thread is in running state but does not have the necessary resources available, instead of going to the suspended state will wait inside the CPU staying active (spinning inside CPU). It will wait for the other thread to complete its task and it will keep on running active but doing nothing. It will keep on spinning and checking with the CPU if it can get a chance to run with the necessary resources. This way instead of going to a direct suspended state, the thread waits inside the CPU saving expensive context switching in SQL Server OS.
Now, while, this is brilliant most of the time, the fact also remains true while spinlock happens inside CPU, it actually wastes the CPU cycle which can be used by another thread that had available resources. In other words, too many context switches are not good as well as too many of the spinlocks are not good. The job of the SQL Server engine is indeed difficult to find the right balance between them.
In most of the cases, we should not be worried about either latches or spinlocks, unless they are too many and really negatively affecting the performance. However, that topic is out of the scope of this blog post and I will discuss them in the future.
I hope you have a clear idea of what is Spink lock in this blog post. I often discuss this concept in detail with my clients during the Comprehensive Database Performance Health Check.
Reference: Pinal Dave (https://blog.sqlauthority.com)
