What is spinlock ALLOC_CACHES_HASH?

  • My_Sky

    SSC Veteran

    Points: 219

    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"...

    https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-spinlock-stats-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-previousversions

    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

  • Grant Fritchey

    SSC Guru

    Points: 396339

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply