January 23, 2020 at 11:37 am
Hi,
Queries are slow, cpu usage at 80%. ALLOC_CACHES_HASH backoffs start at same time as problems are seen in application.
"CACHES" would point to memory, but no obvious shortage there.
This document says "internal use only"...
What to check next?
spinlock_name delta_spins delta_backoff delta_minuntes delta_spins_per_millisecond_per_CPU
ALLOC_CACHES_HASH 72412901394 16796814 30156 100053
SOS_OBJECT_STORE 17456 1 30156 0
MUTEX 2250 1 30156 0
HOBT_HASH 1250 0 30156 0
LOGCACHE_ACCESS 769 2 30156 0
OPT_IDX_STATS 750 0 30156 0
DBTABLE 750 0 30156 0
SOS_SCHEDULER 500 0 30156 0
LOCK_HASH 500 0 30156 0
BLOCKER_ENUM 250 0 30156 0
COMPPLAN_SKELETON 250 0 30156 0
SOS_CACHESTORE 250 0 30156 0
LOGFLUSHQ 250 0 30156 0
SOS_TASK 250 0 30156 0
SQL_MGR 250 0 30156 0
January 23, 2020 at 1:46 pm
I'm not sure you have a spinlock problem based on what you've posted. I'd suggest more reading here and here (especially Paul's post).
In general, start with wait statistics to understand where things are running slow. Combine that with good query metrics from either Extended Events or Query Store (and sometimes both) in order to understand which queries are using the resources, which are called most frequently, which are running slow. Then, focus on those queries to determine what the problem is. Frequently this means using the execution plans to understand how the query in question is being resolved.
I'd gather all that stuff before I went tearing off down a hole worried about spinlocks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply