locked
Memoria utilizada pelo SQL Server RRS feed

  • Pergunta

  • Bom dia a todos,

    Aqui na empresa temos um servidor Windows Server com 16 GB de memória e o SQL 

    Server esta com o "max server memory" definido com 15 GB deixando 1 GB para o SO.

    Temos uma unica aplicação que consome o bd neste servidor e acreditamos que a mesma não utiliza os 15 GB de memoria, porem o SQL Server através do Gerenciador de Tarefas esta alocando 99% da memoria e não libera, acredito que este seja um comportamento padrão do SQL Server deixando alocado o maximo de memoria definida.

    Gostaria de saber qual query, dmv ou filtro do PerfMon consigo utilizar para verificar o real uso de memoria no SQL Server, pois pretendo deixar esta máquina com uma quantidade menor de memoria.


    quarta-feira, 9 de março de 2016 18:53

Respostas

  • Ramos_sa,

    Veja estes exemplos:

    -- Alocação de memória através dos ring buffers --
    WITH RingBuffer
    
    AS (SELECT CAST(dorb.record AS XML) AS xRecord,
    
    dorb.timestamp
    
    FROM sys.dm_os_ring_buffers AS dorb
    
    WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
    
    )
    
    SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
    
    xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,
    
    xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,
    
    DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime,
    
    xr.value('(MemoryNode/TargetMemory)[1]','bigint') AS TargetMemory,
    
    xr.value('(MemoryNode/ReserveMemory)[1]','bigint') AS ReserveMemory,
    
    xr.value('(MemoryNode/CommittedMemory)[1]','bigint') AS CommitedMemory,
    
    xr.value('(MemoryNode/SharedMemory)[1]','bigint') AS SharedMemory,
    
    xr.value('(MemoryNode/PagesMemory)[1]','bigint') AS PagesMemory,
    
    xr.value('(MemoryRecord/MemoryUtilization)[1]','bigint') AS MemoryUtilization,
    
    xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') AS TotalPhysicalMemory,
    
    xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') AS AvailablePhysicalMemory,
    
    xr.value('(MemoryRecord/TotalPageFile)[1]','bigint') AS TotalPageFile,
    
    xr.value('(MemoryRecord/AvailablePageFile)[1]','bigint') AS AvailablePageFile,
    
    xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]','bigint') AS TotalVirtualAddressSpace,
    
    xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]','bigint') AS AvailableVirtualAddressSpace,
    
    xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]','bigint') AS AvailableExtendedVirtualAddressSpace
    
    FROM RingBuffer AS rb
    
    CROSS APPLY rb.xRecord.nodes('Record') record (xr)
    
    CROSS JOIN sys.dm_os_sys_info AS dosi
    
    ORDER BY RmDateTime DESC;

    -- Uso de memória por banco de dados e objetos --
    
    --Parte 1: --
    
    --Declarando a varíavel @Buffers_EmUso
    Declare @Buffers_EmUso Int;
    
    /* Acumando o valor dos contadores na variável @Buffers_EmUso, filtrando pelo 
    Object_Name=Buffer Manager e Counter_Name=Total Pages*/
    
    Select @Buffers_EmUso = cntr_value From Sys.dm_os_performance_counters 
    Where Rtrim(Object_name) LIKE '%Buffer Manager'
    And counter_name = 'Total Pages';
    
    -- Declarando a CTE Buffers_Pages para contagem de Buffers por página --
    ;With DB_Buffers_Pages AS
    (
       SELECT database_id, Contagem_Buffers_Por_Pagina  = COUNT_BIG(*)
       From Sys.dm_os_buffer_descriptors
       Group By database_id
    )
    
    -- Retornando informações sobre os pools de Buffers por Banco de Dados com base na CTE DB_Buffers_Pages --
    Select Case [database_id] 
                WHEN 32767 Then 'Recursos de Banco de Dados' Else DB_NAME([database_id]) 
               End As 'Banco de Dados',
              Contagem_Buffers_Por_Pagina,
              'Buffers em MBs por Banco' = Contagem_Buffers_Por_Pagina / 128,
              'Porcentagem de Buffers' = CONVERT(DECIMAL(6,3), Contagem_Buffers_Por_Pagina * 100.0 / @Buffers_EmUso)  
    From DB_Buffers_Pages
    Order By 'Buffers em MBs por Banco' Desc;
    
    -- Parte 2: -- 
    USE CRIPTOGRAFIA
    GO
    
    -- Declarando a CTE Buffers_Pages para retorno dos Objetos alocados em Pool --
    ;WITH DB_Buffers_Pages_Objetos AS
    (
       Select
           SO.name As Objeto,
           SO.type_desc As TipoObjeto,
           COALESCE(SI.name, '') As Indice,
           SI.type_desc As TipoIndice,
           p.[object_id],
           p.index_id,
           AU.allocation_unit_id
       From sys.partitions AS P INNER JOIN sys.allocation_units AS AU
                                                  ON p.hobt_id = au.container_id
                                                 INNER JOIN sys.objects AS SO
                                                  ON p.[object_id] = SO.[object_id]
                                                 INNER JOIN sys.indexes AS SI
                                                  ON SO.[object_id] = SI.[object_id]
                                                  AND p.index_id = SI.index_id
       Where AU.[type] IN (1,2,3)
        And SO.is_ms_shipped = 0
    )
    
    -- Retornando informações sobre os pools de Buffers de Objetos por Banco de Dados com base na CTE DB_Buffers_Pages_Objetos --
    Select Db.Objeto, Db.TipoObjeto  As 'Tipo Objeto', 
                 Db.Indice, 
                 Db.TipoIndice,
                 COUNT_BIG(b.page_id) As 'Buffers Por Página',
                 COUNT_BIG(b.page_id) / 128 As 'Buffers em MBs'
    From DB_Buffers_Pages_Objetos Db INNER JOIN sys.dm_os_buffer_descriptors AS b
                                                    ON Db.allocation_unit_id = b.allocation_unit_id
    Where b.database_id = DB_ID()
    Group By Db.Objeto, Db.TipoObjeto, Db.Indice, Db.TipoIndice
    Order By 'Buffers Por Página' Desc, TipoIndice Desc;

    -- Quantidade de memória disponível --
    SELECT  cntr_value/1024 AS 'Memory (MB)'
     
    FROM master.dbo.sysperfinfo
     
    WHERE object_name = 'SQLServer:Memory Manager'
     
    AND counter_name = 'Total Server Memory (KB)'

    Em relação ao Perfmon você deverá utilizar o Contador: Total Server Memory dentro da categoria SQLServer:Memory Manager


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 10 de março de 2016 15:40
    Moderador