Início / dba / Perguntas / 84574
Accepted
Fandango68
Asked: 2014-12-08 23:09:24 +0800 CST

Uma maneira de limpar/reiniciar o deslizamento de memória virtual automaticamente?

  • 772

Sei que não é uma boa prática fazer isso, mas minha empresa não pode me pagar tentando encontrar uma solução adequada para o nosso problema. Eu li isso e poderia fazer o mesmo com o SQL Server 2005, mas esperava que houvesse uma maneira melhor (mais rápida?).

O problema é o seguinte:

Temos um site ASP.net (ASP2) em execução no Windows Server 2003, que também hospeda nosso banco de dados SQL Server 2005 e instala.

Ultimamente, notamos que o espaço de memória VM Committed para nossa instância do SQL Server 2005 cresce e está quase no ponto de VM Reserved space que definimos em 1,7 GB (hmm demais?).

O servidor de PC em que nosso SQL Server está sendo executado possui 4 GB de RAM e até 3,5 GB de espaço livre em disco para a instalação do SQL Server 2005. O próprio banco de dados está em um compartilhamento de 350 GB (existem duas partições para isso: unidade C e D, respectivamente).

Portanto, em termos de espaço da VM, eu teria pensado que tínhamos espaço suficiente e ter definido nosso limite de VM para 1,7 GB (idealmente 2 GB) seria suficiente.

O próprio banco de dados tem menos de 500 MB de tamanho.

O serviço diminui consideravelmente quando nosso valor VM Committed se aproxima do valor VM Reserved. A única maneira que conheço de desativar isso é interromper todos os serviços do SQL Service e reiniciá-los novamente. Mas dentro de 24 horas, o VM Committed ainda se arrasta!

Existe uma maneira de tentar encontrar a causa do problema, mas simplesmente reiniciar todos os serviços durante a noite automaticamente? Ou eu realmente deveria estar tentando encontrar a causa do nosso problema?

Fiz algumas investigações (mas como disse, estamos com pouco tempo no momento) e encontrei esta consulta SQL (veja abaixo), que me diz qual consulta real parece monopolizar mais a CPU (não tenho certeza se isso tem algo a ver ver com meu problema original de VM acima).

Ele mostra uma consulta que por acaso é executada durante a noite, e pode ser que essa consulta esteja simplesmente sobrecarregando a memória e não a liberando de volta para o pool?

Existe uma maneira de "fechar" threads/alocação de memória etc no SQL Server para que a memória seja liberada?

Aqui está a consulta de "pesquisa de porco" da CPU (cortesia disso )

select  
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.dbid, 
    q.objectid, 
    q.number, 
    q.encrypted, 
    q.[text] 
from  
    (select top 50  
        qs.plan_handle,  
        qs.total_worker_time 
    from  
        sys.dm_exec_query_stats qs 
    order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q 
order by highest_cpu_queries.total_worker_time desc

Editar, responde às perguntas de Pathum:

Resultados da execução da consulta:

---------------------------------------------------------------------------------------------------- 
Memory usage details for SQL Server instance SERVER9 (9.00.4053.00 - X86) - Standard Edition)
----------------------------------------------------------------------------------------------------

Memory visible to the Operating System
Physical Memory_MB|Physical Memory_GB|Virtual Memory GB 4091|4|2
Buffer Pool Usage at the Moment
BPool_Committed_MB|BPool_Commit_Tgt_MB|BPool_Visible_MB
519.250000|1599.875000|1599.875000

Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters
Mem_KB|Mem_MB|Mem_GB 531712|519.250000|0.507080078

Memory needed as per current Workload for SQL Server instance    
Mem_KB|Mem_MB|Mem_GB 1638272|1599.875000|1.562377929

Total amount of dynamic memory the server is using for maintaining connections
Mem_KB|Mem_MB|Mem_GB 560|0.546875|0.000534057

Total amount of dynamic memory the server is using for locks
Mem_KB|Mem_MB|Mem_GB 968|0.945312|0.000923156

Total amount of dynamic memory the server is using for the dynamic SQL cache
Mem_KB|Mem_MB|Mem_GB 1120|1.093750|0.001068115

Total amount of dynamic memory the server is using for query optimization
Mem_KB|Mem_MB|Mem_GB 552|0.539062|0.000526428

Total amount of dynamic memory used for hash, sort and create index operations.
Mem_KB|Mem_MB|Mem_GB 0|0.000000|0.000000000

Total Amount of memory consumed by cursors
Mem_KB|Mem_MB|Mem_GB 480|0.468750|0.000457763

Number of pages in the buffer pool (includes database, free, and stolen).
8KB_Pages|Pages_in_KB|Pages_in_MB 66464|531712.000000|519.250000000

Number of Data pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 50135|401080.000000|391.679687500

Number of Free pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 895|7160.000000|6.992187500

Number of Reserved pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 0|0.000000|0.000000000

Number of Stolen pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 15434|123472.000000|120.578125000

Number of Plan Cache pages in the buffer pool
8KB_Pages|Pages_in_KB|Pages_in_MB 11953|95624.000000|93.382812500

Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references
Page Life in seconds|PLE Status 47011|PLE is Healthy

Number of requests per second that had to wait for a free page
Free list stalls/sec 0


Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed
Checkpoint pages/sec 572

Number of buffers written per second by the buffer manager"s lazy writer
Lazy writes/sec 0

Total number of processes waiting for a workspace memory grant
Memory Grants Pending 0

Total number of processes that have successfully acquired a workspace memory grant
Memory Grants Outstanding 0

Segunda consulta, verificando a pressão da memória:

SELECT object_name, cntr_value 
FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

Resultados:

object_name|cntr_value
SQLServer:Memory Manager        |1638272
SQLServer:Memory Manager        |531712
(2 row(s) affected)

  • Meu servidor é 32 bits. Não iremos mudar para 64 bits por qualquer motivo.

  • Valores de sp_configure informações não podem ser fornecidos, isso é confidencial. :)

sql-server sql-server-2005

3 respostas

  1. Best Answer

    Primeiro, sempre que você postar uma pergunta relacionada ao SQL Server, poste 'versão e edição do SQL Server', você não acreditará que a resposta pode mudar completamente com versões diferentes.

    Ultimamente, notamos que o espaço de memória VM Committed para nossa instância do SQL Server 2005 cresce e está quase no ponto de VM Reserved space que definimos em 1,7 GB (hmm demais?).

    Por que você está olhando VM committede VM reserved, você realmente sabe o que isso diz. O que você está tentando deduzir da VM confirmada/reservada. A VM reservada é a quantidade total de espaço de endereço virtual (VAS) que o SQL Server reservou. O SQL Server pode reservar VAS de acordo com seu limite de VAS, que é de 8 TB em 64 bits e, por padrão, 2 G em 32 bits. Portanto, não há problema quando o VM reservado é alto. Este é um comportamento normal. A memória reservada é, na verdade, o que o SQL Server "pensa" que pode usar no futuro. E como a VM reservada é de 1,7 GI, não pense que há problema com isso

    VM confirmada significa a quantidade total de espaço de endereço virtual (VAS) ou memória virtual que o SQL Server confirmou ou memória com backup physical RAMno sistema. Uma memória confirmada tem memória física associada a ela. Confirmado significa o total de memória virtual que o SQL Server está usando no momento.

    O serviço diminui consideravelmente quando nosso valor VM Committed se aproxima do valor VM Reserved. A única maneira que conheço de desativar isso é interromper todos os serviços do SQL Service e reiniciá-los novamente. Mas dentro de 24 horas, o VM Committed ainda se arrasta!

    No SQL Server, a VM confirmada pode se tornar igual à VM reservada, não é necessariamente um problema. E, por favor, não desenvolva o hábito de reiniciar o SQL Server IMO, isso é o pior que você pode fazer. Agora, o que eu acho é que você está enfrentando um problema de desempenho no SQL Server e precisa descobrir primeiro o que realmente está causando a lentidão do seu servidor. Para começar, você pode consultar Como analisar o desempenho do SQL Server

    Isso realmente ajudaria você a descobrir onde está o problema. Acredite em mim, pelo que você postou, não me parece um problema de memória, novamente você forneceu informações limitadas sobre a versão do SQL Server, então minha resposta é limitada.

    Por favor, adicione a saída abaixo em sua pergunta

    Select @@Version

    O seu SQL Server está corrigido para o SQL Server 2005 SP4. Deve ser. Você pode ler o errorlog usando o comando abaixo e postar seu conteúdo em algum local compartilhado e anexar o link em questão. Gostaria de ver o que há no errorlog.

    sp_readerrorlog

    Você disse que tem apenas 4 G para o SQL Server, você definiu a memória máxima do servidor para o SQL Server?

    O Windows Server 2003 está atualizado com o Service Pack mais recente. Certifique-se de que houve um bug no Windows Server que cortou excessivamente a memória do SQL Server.

    Editar:

    .Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 26 de maio de 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition no Windows NT 5.2 (Build 3790: Service Pack 2)

    Seu SQL Server é de 32 bits e o Windows Server é a edição padrão de 32 bits. Você não pode, de forma alguma, forçar o Windows Server a usar mais de 4 G de RAM e, portanto, não faz sentido habilitar o AWE no SQL Server, o que poderia permitir que os dados e as páginas de índice vissem a memória além da limitação do VAS, que é 2 G. Só posso sugerir você atualizar o SQL Server e o sistema operacional Windows para 64 bits. Eu recomendaria SQL Server 2012 com SP2 e Windows Server 2012 ou Windows Server 2008 r2, mas verifique se ambos são de 64 bits.

    Se isso não for possível, você pode atualizar o Windows Server 2003 de padrão para corporativo, então você pode habilitar o PAE e o servidor de janela pode ver mais de 4 G. Depois disso, habilite o AWE para permitir que os dados do SQL Server e as páginas de índice vejam a memória maior que 2 G. A partir de agora, em qualquer caso, o SQL Server VAS está limitado a 2 G e, portanto, não pode acessar a memória além de 2 G

    Por favor, leia este artigo se você quiser entender sobre a configuração de memória de 32 bits

    • 5

  2. Comece verificando a memória máxima. Instruções aqui: http://technet.microsoft.com/en-us/library/ms191144(v=sql.90).aspx

    Você deve definir o máximo para ser baixo o suficiente para deixar espaço para o sistema operacional e quaisquer outros aplicativos. Que no seu caso vai ser muito baixo. Talvez 500mb-1gb

    • 1

  3. Você poderia postar o seguinte resultado da consulta? Esses pontos que forneci abaixo podem ajudá-lo a diagnosticar o problema

    Script para relatar detalhes de uso de memória de uma instância do SQL Server Autor original Sakthivel Chidambaram Este script ajudará você a diagnosticar o problema

                /*============================================================================
               Script to report Memory usage details of a SQL Server instance
               Author: Sakthivel Chidambaram, Microsoft http://blogs.msdn.com/b/sqlsakthi 

               Date: June 2012
               Version: V2

               V1: Initial Release
               V2: Added PLE, Memory grants pending, Checkpoint, Lazy write,Free list counters

               THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
               ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
               TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
               PARTICULAR PURPOSE. 

            ============================================================================*/ 
            -- We don't need the row count
            SET NOCOUNT ON

            -- Get size of SQL Server Page in bytes
            DECLARE @pg_size INT, @Instancename varchar(50) 
            SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E' 

            -- Extract perfmon counters to a temporary table
            IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
            SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters

            -- Get SQL Server instance name
            SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM #perfmon_counters WHERE counter_name = 'Buffer cache hit ratio' 

            -- Print Memory usage details
            PRINT '----------------------------------------------------------------------------------------------------' 
            PRINT 'Memory usage details for SQL Server instance ' + @@SERVERNAME + ' (' + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' + SUBSTRING(@@VERSION, CHARINDEX('X',@@VERSION),4) + ' - ' + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')' 
            PRINT '----------------------------------------------------------------------------------------------------' 
            SELECT 'Memory visible to the Operating System' 
            SELECT CEILING(physical_memory_in_bytes/1048576.0) as [Physical Memory_MB], CEILING(physical_memory_in_bytes/1073741824.0) as [Physical Memory_GB], CEILING(virtual_memory_in_bytes/1073741824.0) as [Virtual Memory GB] FROM sys.dm_os_sys_info 
            SELECT 'Buffer Pool Usage at the Moment' 
            SELECT (bpool_committed*8)/1024.0 as BPool_Committed_MB, (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,(bpool_visible*8)/1024.0 as BPool_Visible_MB FROM sys.dm_os_sys_info 
            SELECT 'Total Memory used by SQL Server Buffer Pool as reported by Perfmon counters' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Total Server Memory (KB)' 
            SELECT 'Memory needed as per current Workload for SQL Server instance' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Target Server Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for maintaining connections' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Connection Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for locks' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Lock Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for the dynamic SQL cache' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'SQL Cache Memory (KB)' 
            SELECT 'Total amount of dynamic memory the server is using for query optimization' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Optimizer Memory (KB) ' 
            SELECT 'Total amount of dynamic memory used for hash, sort and create index operations.' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Granted Workspace Memory (KB) ' 
            SELECT 'Total Amount of memory consumed by cursors' 
            SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM #perfmon_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total' 
            SELECT 'Number of pages in the buffer pool (includes database, free, and stolen).' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages' 
            SELECT 'Number of Data pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages' 
            SELECT 'Number of Free pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages' 
            SELECT 'Number of Reserved pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages' 
            SELECT 'Number of Stolen pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages' 
            SELECT 'Number of Plan Cache pages in the buffer pool' 
            SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM #perfmon_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'
            SELECT 'Page Life Expectancy - Number of seconds a page will stay in the buffer pool without references' 
            SELECT cntr_value as [Page Life in seconds],CASE WHEN (cntr_value > 300) THEN 'PLE is Healthy' ELSE 'PLE is not Healthy' END as 'PLE Status'  FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Page life expectancy'
            SELECT 'Number of requests per second that had to wait for a free page' 
            SELECT cntr_value as [Free list stalls/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free list stalls/sec'
            SELECT 'Number of pages flushed to disk/sec by a checkpoint or other operation that require all dirty pages to be flushed' 
            SELECT cntr_value as [Checkpoint pages/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Checkpoint pages/sec'
            SELECT 'Number of buffers written per second by the buffer manager"s lazy writer'
            SELECT cntr_value as [Lazy writes/sec] FROM #perfmon_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Lazy writes/sec'
            SELECT 'Total number of processes waiting for a workspace memory grant'
            SELECT cntr_value as [Memory Grants Pending] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Pending'
            SELECT 'Total number of processes that have successfully acquired a workspace memory grant'
            SELECT cntr_value as [Memory Grants Outstanding] FROM #perfmon_counters WHERE object_name=@Instancename+'Memory Manager' and counter_name = 'Memory Grants Outstanding'

    • Usando isso, você pode verificar a pressão da memória

      SELECT object_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

    • Se sua implantação do SQL Server em um sistema operacional de 32 bits. Considere movê-lo para um sistema operacional de 64 bits.
    • Também é melhor se você puder fornecer os valores das informações sp_configure

    Espero que meus pontos ajudem você :)

    Atualização em 2014-DEZ-10

    @Fernando68 Olá, você habilitou o AWE (Address Windowing Extensions) no seu SQL Server? e observe que isso não é necessário em uma máquina de 64 bits, pois os limites de memória do sistema operacional (um endereço de memória de 64 bits), o SQL Server sempre tentará usar o AWE se estiver habilitado e o SQL Server pode gerenciar dinamicamente sua memória AWE uso. Na inicialização, ele alocará a quantidade de memória necessária para a carga de trabalho de inicialização.

    Você pode consultar o artigo abaixo

    Como funciona: SQL Server PAE/AWE de 32 bits ativado (SQL 2005, 2008 e 2008 R2) – Não usa tanta RAM quanto o esperado!

    • 0

relate perguntas