[20140829] high CPU usage caused by spinlock

Background:

An alarm is reported when the CPU usage is higher than the standard

Troubleshooting:

1. No queries with extremely high CPU performance were found during tracking

2. Check the CPU usage of the kernel to see if it is caused by SQL Server.

3. Check whether the load is extremely high. Use batch request here.

4. No solution is available, and SQL runs normally. No problem is found at the thread, worker, and task levels.

5.open procexp.exe and hope to find the full trace. Click it to find that all high-CPU threads are called by keacquirespinlockatdpclevel and start to suspect whether the thread is a spinlock problem.

6. query spinlock stats, sys. dm_ OS _spinlock_stats: Check spins. The largest spins is lock_hash. Because there is no reference value, we found a service a that is more busy and found to be an order of magnitude higher than.

Solution:

No. It is estimated that only nolock can be used to reduce access to the lock data structure.

An official Article: diagnosing and resolving spinlock contention on SQL Server

I did not read it carefully. There are specific diagnostic methods in it.

 

[20140829] high CPU usage caused by spinlock