none
High stolen memory RRS feed

  • שאלה

  • Hey Guys,

    I'm running on SQL Server 2016 SP2 in Azure.

    Recently I'm having more and more resource_semaphore waits.

    When I reviewed the memory via this query:

    SELECT Now = GETDATE()
        ,StolenMemory = (
            SELECT cntr_value
            FROM sys.dm_os_performance_counters
            WHERE [counter_name] IN ('Stolen Server Memory (KB)')
            )
        ,StolenMemoryPercent = 100.0 * (
            SELECT cntr_value
            FROM sys.dm_os_performance_counters
            WHERE [counter_name] IN ('Stolen Server Memory (KB)')
            ) / (
            SELECT cntr_value
            FROM sys.dm_os_performance_counters
            WHERE [counter_name] IN ('Total Server Memory (KB)')
            )
    , BatchRequestsSec = (
            SELECT cntr_value
            FROM sys.dm_os_performance_counters
            WHERE [counter_name] IN ('Batch Requests/sec')
            )

    I noticed that stolen memory takes more than 70% of my total memory.

    Running DBCC MEMORYSTATUS:

    Even when I killed all existing sessions, the Stolen memory high remained as is.

    I'm running a single instance on a VM in Azure.

    VM total memory is 256GB, Max memory configured for the SQL Server is  190GB.

    Q1:

    Is there a prefered way for identifying which process/es cause this Stolen memory increase?

    Q2:

    Is there a way for releasing/ shrinking the Stolen memory manually or it's automatically managed?

    Can someone please help me understand this matter?

    יום רביעי 15 מאי 2019 15:45

תשובות