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:

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

                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:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

http://sqlcat.com/sqlcat/b/top10lists/archive/2007/11/21/top-10-sql-server-2005-performance-issues-for-data-warehouse-and-reporting-applications.aspx

http://sqlcat.com/sqlcat/b/top10lists/archive/2007/11/21/top-sql-server-2005-performance-issues-for-oltp-applications.aspx

http://msdn.microsoft.com/en-us/library/ms176018.aspx

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.