This sample app helps with understanding and t-shooting SQL Server Spinlock issues
To invoke the application, go to a command prompt and chang directory to the folder where you have downloaded or compiled \Release folder. Then do this:
SpinlockSimulatorSingleThread.exeHere are the sample results from multiple different machines with different CPU models/speeds
Sample Results 1 (Intel i7 2.7 Ghz)
=======================
SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 5031, Spins=102023750, Backoffs=112
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=20279
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=20315
Simple Loop: Millisecond elapsed (QPC)=4, Loops=10000000
Simple Loop: Spins/Millisecond (QPC)=2500000
Press ENTER to end...
Sample Results 2 (AMD A6-6310 APU 1.8 Ghz)
===============================
SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 5016, Spins=187023750, Backoffs=197
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=37285
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=37292
Simple Loop: Millisecond elapsed (QPC)=11, Loops=10000000
Simple Loop: Spins/Millisecond (QPC)=909090
Press ENTER to end...
Sample Results 3 (Intel Q6600 2.4 Ghz)
===========================
SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 5015, Spins=916023750, Backoffs=926
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=182656
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=183058
Simple Loop: Millisecond elapsed (QPC)=4, Loops=10000000
Simple Loop: Spins/Millisecond (QPC)=2500000
Press ENTER to end...
Sample Results 4 (Intel Xeon E5-2620 v4 2.10 Ghz)
==================================
SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 5016, Spins=81123750, Backoffs=821
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=161687
SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=161977
Simple Loop: Millisecond elapsed (QPC)=4, Loops=10000000
Simple Loop: Spins/Millisecond (QPC)=2500000
Press ENTER to end...
Since the above example is designed to perform a little more than a loop with periodic pauses until the lock is acquired, it would drive CPU utilization to 100% on a single CPU. Based on these values one can conclude that if spinlock statistics show values greater than about 20000 spins per millisecond per CPU, then the CPU usage is likely fairly high and close to 100% capacity.
Naturally, CPU clock speeds would impact the results and faster CPUs would be able to perform more spins per millisecond. Other CPU-related specifications like L1, L2, L3 caches may also play somewhat of a role here. However, the goal of this post is to provide guidance, a "ballpark" figure so to speak. Please feel free to report your test results in the Comments section.
There is a multi-threaded project in the Github repository SpinlockSimulatorMultiThread. That is you can run it on multiple threads/CPUs in parallel and threads compete for a single spinlock. However, the key is to test CPU and its performance is to use SINGLE processor. Therefore, use for most scenarios you would benefit from using the SpinlockSimulatorSingleThread project from the repro. A single processor test is the best approach because the multi-threaded (multi-CPU) introduces the lock waits and thus spins will vary between threads due to how long they waited on a spinlock. But if you only look at the Simple Loop output, there the loops are consistently the same and thus the spins/millisecond would be the same on all threads. Or if you take into cosideration the spins, the calculations will make sense. In some scenarios, you may benefit from testing with the multi-threaded application. The way to use the multi-threaded app by is by calling the executable from command prompt. Without parameters it will default to 4 threads. If you pass a single-digit parameter, you can tell the app to run 1 or more threads:
SpinlockSimulatorMultiThread.exe 8Sample output may look like this:
Launching 8 thread(s)...
Thread 28700 started.
Thread 28700 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 6756 started.
Thread 22472 started.
Thread 30632 started.
Thread 22176 started.
Thread 6552 started.
Thread 32968 started.
Thread 15996 started.
Thread 28700: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 3016, Spins=250, Backoffs=0
Thread 28700: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=0
Thread 28700: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=0
Thread 6552 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 28700: Simple Loop - Millisecond elapsed (QPC)=8, Loops=10000000
Thread 28700: Simple Loop - Spins/Millisecond (QPC)=1250000
Thread 28700 completed. Local Spins: 250
Thread 6552: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 7078, Spins=49023750, Backoffs=59
Thread 6552: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=6926
Thread 6552: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=6920
Thread 6756 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 6552: Simple Loop - Millisecond elapsed (QPC)=3, Loops=10000000
Thread 6552: Simple Loop - Spins/Millisecond (QPC)=3333333
Thread 6552 completed. Local Spins: 49023750
Thread 6756: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 10125, Spins=114023750, Backoffs=124
Thread 6756: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=11261
Thread 22472 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 6756: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=11274
Thread 6756: Simple Loop - Millisecond elapsed (QPC)=3, Loops=10000000
Thread 6756: Simple Loop - Spins/Millisecond (QPC)=3333333
Thread 6756 completed. Local Spins: 114023750
Thread 22472: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 13157, Spins=163023750, Backoffs=173
Thread 22472: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=12390
Thread 22472: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=12392
Thread 22472: Simple Loop - Millisecond elapsed (QPC)=4, Loops=10000000
Thread 22472: Simple Loop - Spins/Millisecond (QPC)=2500000
Thread 22472 completed. Local Spins: 163023750
Thread 32968 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 32968: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 16156, Spins=215023750, Backoffs=225
Thread 32968: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=13309
Thread 32968: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=13310
Thread 32968: Simple Loop - Millisecond elapsed (QPC)=4, Loops=10000000
Thread 32968: Simple Loop - Spins/Millisecond (QPC)=2500000
Thread 32968 completed. Local Spins: 215023750
Thread 22176 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 22176: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 19188, Spins=261023750, Backoffs=271
Thread 22176: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=13603
Thread 22176: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=13603
Thread 22176: Simple Loop - Millisecond elapsed (QPC)=3, Loops=10000000
Thread 22176: Simple Loop - Spins/Millisecond (QPC)=3333333
Thread 22176 completed. Local Spins: 261023750
Thread 15996 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 15996: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 22156, Spins=312023750, Backoffs=322
Thread 15996: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=14083
Thread 15996: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=14074
Thread 15996: Simple Loop - Millisecond elapsed (QPC)=3, Loops=10000000
Thread 15996: Simple Loop - Spins/Millisecond (QPC)=3333333
Thread 15996 completed. Local Spins: 312023750
Thread 30632 acquired the lock. Simulating work by sleeping 3 seconds...
Thread 30632: SpinToAcquireLockWithExponentialBackoff: Milliseconds elapsed = 25219, Spins=359023750, Backoffs=369
Thread 30632: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond=14236
Thread 30632: SpinToAcquireLockWithExponentialBackoff: Spins/Millisecond(QPC)=14237
Thread 30632: Simple Loop - Millisecond elapsed (QPC)=3, Loops=10000000
Thread 30632: Simple Loop - Spins/Millisecond (QPC)=3333333
Thread 30632 completed. Local Spins: 359023750
All threads completed. Total Spins: 1473166500
Press ENTER to end...