quarta-feira, 17 de julho de 2013

Uso de memória no SQL Server

O SQL Server, por padrão, tenta colocar o máximo possível de páginas na memória RAM.
Esse comportamento é padrão do SQL Server e é o esperado. Isso serve para assegurar o menor tempo de resposta possível na consulta.
Logicamente esse comportamento pode ser limitado. Podemos configurar o máximo de memória que o SQL Server pode consumir com paginação, deixando uma margem para outros serviços, no entanto isso não é recomendado pela Microsoft. A recomendação é que o SQL Server seja um servidor dedicado.
Recorri à documentação do SQL Server disponível no MSDN e outras fontes para fazer o performance tunning do servidor que eu administro e encontrei um artigo muito esclarecedor, gostaria de compartilhá-lo com vocês:
                Resumindo:
                1) O sql server sempre tentará usar TODA a memória disponível para cache de páginas
                2) Deveria ser exclusivo
               
                Indícios de que você precisa de mais memória:
                1) Seus mdf's tem mais que o dobro da ram disponível
                2) O tempo médio que uma página fica no cache é menor que 300 segundos
                3) Você não tem um DBA no time
                4) Você tem menos de 32 GB
                5) O preço da memória é menor do que R$ 2000
               
Dois fatos importantes é que deveríamos ter de RAM pelo menos a metade do tamanho do  arquivo do banco de dados, para que o tempo de vida das páginas em cache fosse mais alto (a troca dessas páginas onera o processador também).

Separei algumas queries úteis para verificar o consumo de memória do banco:

--queries usando mais memória
select memory_usage, host_name, program_name, host_process_id, login_name,original_login_name from sys.dm_exec_sessions order by memory_usage desc --uso de memória do servidor, por login e user

select granted_query_memory, * from sys.dm_exec_requests order by granted_query_memory desc  --uso de memória nas queries


--memória consumida pelo sql server atualmente
select
      CONVERT (VARCHAR,CAST(bpool_committed *8 AS MONEY),1)AS [SIZE],
      bpool_committed,
      bpool_commit_target
from
      sys.dm_os_sys_info     


--banco de dados com mais páginas alocadas no cache (use para descobrir em que banco rodar a query abaixo)
SELECT
      count(*)AS cached_pages_count,
      CASE database_id
            WHEN 32767 THEN 'ResourceDb'
            ELSE db_name(database_id)
    END AS Database_name
FROM
      sys.dm_os_buffer_descriptors
GROUP BY
      db_name(database_id) ,database_id
ORDER BY
      cached_pages_count DESC


--tabela com mais páginas alocadas no cache (use no baco indicado na query acima)
SELECT
      count(*)AS cached_pages_count,
      name,
      index_id
FROM
      sys.dm_os_buffer_descriptors AS bd INNER JOIN
        (
            SELECT
                  object_name(object_id) AS name,
                  index_id,
                  allocation_unit_id
            FROM
                  sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
            UNION ALL
            SELECT
                  object_name(object_id) AS name, 
                  index_id,
                  allocation_unit_id
            FROM
                  sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
        ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE
      database_id = db_id()
GROUP BY
      name,
      index_id
ORDER BY
      cached_pages_count DESc
     



--expectativa de vida das páginas em cache (deve ser maior que 5 minutos ou 300 segundos)     
SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
              
Em anexo mando também um poster feito pela Quest Software (adquirida pela Dell) em parceria com a Dell e a IBM que mostra, de acordo com resultados de queries de sistema e de variáveis do performance monitor, qual é o componente sendo mais pressionado (memória, processador, disco) e as ações que devem ser tomadas para solucionar o problema.

Outros artigos interessantes:

Não podemos esquecer das pequenas queries do dia a dia. Dois problemas de lentidão que enfrentei nos meus servidores não foram referentes ao uso de memória. Em um deles, compartilhado com o IIS e uma aplicação asp.net, o problema era de CPU. Enquanto eu ganhava tempo otimizando a aplicação, configurei no application pool da aplicação asp.net para usar apenas 2 núcleos do processador de 4 núcleos (affinity mask) para que o SQL Server tivesse folga para processar. Configurei o máximo de memória que poderia ser usada pelo SQL Server e  o máximo que poderia ser usado pelo app pool, assim o SQL Server não deixaria o app pool sem memória. Esse foi um contexto onde os serviços não poderiam ser separados em servidores diferentes por uma questão de custo/benefício: não era uma aplicação vital para o negócio da empresa.
Numa outra ocasião, porém, o sistema permitia a inclusão de queries do usuário, tanto em relatórios como em cálculos e processamentos em lote ou fórmulas. Essas queries dos usuários avançados tiveram de ser revistas, pois era completamente esperado que esses usuários, por mais que sejam avançados, não tem conhecimento suficiente para evitar multiplicações cartesianas, subqueries desnecessárias, campos não usados e tirar o máximo proveito dos índices. 

Esse post foi para alertar que se o SQL Server estiver consumindo grandes quantidades de memória esse definitivamente NÃO É O MOTIVO  de alguma lentidão, mas que essa lentidão pode ser causada por n outros fatores. Na verdade quanto mais informação (relevante e frequentemente acessada) estiver na memória RAM, melhor. 

2 comentários:

  1. Parabéns pelo tópico!!! Andei pesquisando e encontrei algo sobre Buffer Pool Extension in SQL Server 2014, será uma boa solução para grandes bancos de dados?? Saberia me dizer se na versão atual do SQL é possível alocar todo mdf na memória RAM?? Seria interessante utilizar esses recursos em ferramentas de BI. Abraço!

    ResponderExcluir

Postagens populares

Marcadores

delphi (60) C# (31) poo (21) Lazarus (19) Site aos Pedaços (15) sql (13) Reflexões (10) .Net (9) Humor (9) javascript (9) ASp.Net (8) api (8) Básico (6) Programação (6) ms sql server (5) Web (4) banco de dados (4) HTML (3) PHP (3) Python (3) design patterns (3) jQuery (3) livros (3) metaprogramação (3) Ajax (2) Debug (2) Dicas Básicas Windows (2) Pascal (2) games (2) linguagem (2) música (2) singleton (2) tecnologia (2) Anime (1) Api do Windows (1) Assembly (1) Eventos (1) Experts (1) GNU (1) Inglês (1) JSON (1) SO (1) datas (1) developers (1) dicas (1) easter egg (1) firebird (1) interfaces (1) introspecção (1) memo (1) oracle (1) reflexão (1)