Início / dba / Perguntas / 182227
Accepted
jitbit
Asked: 2017-07-31 23:22:25 +0800 CST

O SQL Server limpa o cache do plano e as estatísticas de execução periodicamente

  • 772

Após atualizar o SQL Server 2014 para 2016, o servidor continua redefinindo os planos de execução em cache e dm*exibições (como dm_exec_query_stats) etc. a cada duas horas

Como se alguém executasse DBCC FREEPROCCACHEe DBCC DROPCLEANBUFFERSmanualmente (exceto por ninguém, isso acontece automaticamente).

O mesmo banco de dados funcionou bem no SQL Server 2014 e no Windows Server 2012, as coisas deram errado depois de mudar para o SQL Server 2016 (e o Windows Server 2016)

Coisas que verifiquei: o banco de dados não possui o sinalizador "fechamento automático". O servidor SQL está ad hoc optimizedconfigurado para true(pensei que ajudaria, não ajudou). O "repositório de consultas" está "desativado". O servidor tem 16 GB de memória.

Nada útil no "log do SQL Server" também. Apenas uma mensagem de backup semanal...

Eu também verifiquei este artigo https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options (role para baixo até a seção "Exemplos" e logo acima it) há uma lista de situações em que o plano é liberado automaticamente. Nenhuma delas se aplica.

ATUALIZAR:

Infelizmente, nenhuma das sugestões ajudou. Conceder permissões LPIM, detectar e corrigir consultas não parametrizadas que geraram toneladas de planos para a mesma consulta, diminuindo a "memória máxima do servidor"... Os planos continuam sendo redefinidos aleatoriamente, de duas em duas horas a cada 5-10 minutos. Se o servidor estava "sob pressão de memória", como é que a versão 2014 estava funcionando bem na mesma máquina.

Aqui está a saída sp_Blitz conforme solicitado

**Priority 10: Performance**:

- Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.

    * xxx


**Priority 50: Server Info**:

- Instant File Initialization Not Enabled  - Consider enabling IFI for faster restores and data file growths.


**Priority 100: Performance**:

- Resource Governor Enabled  - Resource Governor is enabled.  Queries may be throttled.  Make sure you understand how the Classifier Function is configured.


**Priority 120: Query Plans**:

- Implicit Conversion Affecting Cardinality - One of the top resource-intensive queries has an implicit conversion that is affecting cardinality estimation.

    * 

- Missing Index - One of the top resource-intensive queries may be dramatically improved by adding an index.

    * 

- RID or Key Lookups - One of the top resource-intensive queries contains RID or Key Lookups. Try to avoid them by creating covering indexes.

    * 

**Priority 170: File Configuration**:

- System Database on C Drive
    * master - The master database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    * model - The model database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    * msdb - The msdb database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.


**Priority 200: Backup**:

- MSDB Backup History Not Purged msdb - Database backup history retained back to Jun 10 2017  9:47PM


**Priority 200: Informational**:

- Backup Compression Default Off  - Uncompressed full backups have happened recently, and backup compression is not turned on at the server level. Backup compression is included with SQL Server 2008R2 & newer, even in Standard Edition. We recommend turning backup compression on by default so that ad-hoc backups will get compressed.


**Priority 200: Non-Default Server Config**:

- Agent XPs  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- max server memory (MB)  - This sp_configure option has been changed.  Its default value is 2147483647 and it has been set to 15000.

- optimize for ad hoc workloads  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- show advanced options  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- xp_cmdshell  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.


**Priority 200: Performance**:

- Buffer Pool Extensions Enabled  - You have Buffer Pool Extensions enabled, and one lives here: Z:\sql_buffer_pool.BPE. It's currently 60.00000000000 GB. Did you know that BPEs only provide single threaded access 8KB (one page) at a time?

- cost threshold for parallelism  - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.

**Priority 240: Wait Stats**:

- No Significant Waits Detected  - This server might be just sitting around idle, or someone may have cleared wait stats recently.

**Priority 250: Informational**:

- SQL Server Agent is running under an NT Service account  - I'm running as NT Service\SQLSERVERAGENT. I wish I had an Active Directory service account instead.

- SQL Server is running under an NT Service account  - I'm running as NT Service\MSSQLSERVER. I wish I had an Active Directory service account instead.

**Priority 250: Server Info**:

- Default Trace Contents  - The default trace holds 125 hours of data between Aug 19 2017 11:55AM and Aug 24 2017  4:59PM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

- Hardware  - Logical processors: 2. Physical memory: 15GB.

- Hardware - NUMA Config  - Node: 0 State: ONLINE Online schedulers: 2 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 29

- Locked Pages In Memory Enabled  - You currently have 12.02534484863 GB of pages locked in memory.

- Memory Model Unconventional  - Memory Model: LOCK_PAGES

- Server Last Restart  - Aug 20 2017 12:32PM

- Server Name  - xx

- Services
 - Service: SQL Full-text Filter Daemon Launcher (MSSQLSERVER) runs under service account NT Service\MSSQLFDLauncher. Last startup time: not shown.. Startup type: Manual, currently Running.

 - Service: SQL Server (MSSQLSERVER) runs under service account NT Service\MSSQLSERVER. Last startup time: Aug 20 2017 12:32PM. Startup type: Automatic, currently Running.

 - Service: SQL Server Agent (MSSQLSERVER) runs under service account NT Service\SQLSERVERAGENT. Last startup time: not shown.. Startup type: Automatic, currently Running.

- SQL Server Last Restart  - Aug 20 2017 12:33PM

- SQL Server Service  - Version: 13.0.4446.0. Patch Level: SP1. Edition: Enterprise Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 2

- Virtual Server  - Type: (HYPERVISOR)

- Windows Version  - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3


**Priority 254: Rundate**:

 - Captain's log: stardate something and something...
sql-server sql-server-2016

3 respostas

  1. Primeiro, obtenha os horários exatos em que o cache do plano está sendo limpo. Aqui está a maneira mais fácil de fazer isso - ele deve ser executado quase instantaneamente e não bloqueará ninguém:

    SELECT TOP 1 creation_time
FROM sys.dm_exec_query_stats WITH (NOLOCK)
ORDER BY creation_time;

    Se essa data/hora parecer mais antiga para você do que o esperado , apenas parte do cache do plano está sendo limpa. Por exemplo, talvez alguém esteja fazendo um trabalho de reconstrução de índice ou atualização de estatísticas, o que liberaria o cache do plano para os objetos específicos afetados - mas outros objetos ainda permanecerão. Eu vejo muito isso quando as consultas do sistema (como consultas DMV) permanecem, mas os planos de banco de dados do usuário desaparecem.

    Se essa data/hora for atualizada em intervalos específicos , como se parece atualizar exatamente a cada 2 horas para dizer 6:00, 8:00, 10:00, etc, então alguém provavelmente está executando um trabalho ou consulta que faz com que o cache do plano limpar. Depois de saber a frequência exata, você pode:

    • Veja seus cronogramas de trabalho para ver o que é executado nesse intervalo
    • Execute um rastreamento do Profiler ou um rastreamento de Eventos Estendidos durante esse período de tempo para descobrir o mistério (normalmente não sou fã de rastreamento em produção, mas se você sabe exatamente quando o assassino vai atacar, é fácil disparar um baixo -amostra aérea do que está sendo executado)
    • Faça login sp_WhoIsActiveem uma tabela durante esse período (o método mais fácil, mas o menos provável de reduzi-lo à consulta exata que o causa)

    Se essa data/hora continua mudando cada vez que você executa a consulta , seu servidor provavelmente está sob pressão de memória. Execute isso para gerar informações básicas de verificação de integridade e, em seguida, copie/cole em sua pergunta do Stack para que possamos diagnosticá-la:

    sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1, @CheckUserDatabaseObjects = 1

    (Divulgação: sou um dos autores de sp_Blitz.)

    Atualizado em 25/08/2017 com seus dados sp_Blitz - obrigado por executar sp_Blitz e adicioná-lo à sua pergunta, e isso realmente ajuda a mostrar algumas coisas. Você está executando o SQL Server 2016 Enterprise Edition em uma VM com 2 núcleos e 16 GB de RAM. Primeiro, uma observação rápida sobre o licenciamento: se você estiver licenciando pelo convidado, o requisito mínimo de compra é de 4 núcleos, não 2. (Consulte o Guia de licenciamento do SQL Server para obter mais detalhes.) 4 núcleos da Enterprise Edition custam cerca de US$ 28 mil , e é bastante incomum ver tanto dinheiro de licenciamento gasto em apenas 16 GB de RAM. Se você estiver licenciando o SQL Server Enterprise Edition no nível do host, poderá ignorar isso e executar VMs menores.

    Parece que seu SQL Server está sob pressão de memória externa. Você tem 16 GB de RAM e definiu a memória máxima do servidor em 15 GB. Infelizmente, 1 GB não é suficiente para o sistema operacional (além de tudo o que você vai executar lá, como software de backup e SSMS). Em nosso Guia de configuração do SQL Server, sugerimos deixar 4 GB ou 10% livres, o é maior - no seu caso, isso seria 4 GB, portanto, a configuração máxima de memória do servidor deve ser 12 GB em vez de 15 GB.

    Mais evidências aparecem em suas alocações de memória atuais: você tem páginas bloqueadas na memória (LPIM) ativadas, mas você tem apenas 12,02 GB de páginas bloqueadas na memória. Isso provavelmente (mas não garantido) significa que algum outro aplicativo precisava de memória, então o Windows enviou uma notificação de pressão de memória e o SQL Server cedeu os outros 3 GB de memória para permitir que o outro aplicativo fizesse sua parte. Isso é mais uma prova de que você realmente não pode usar um máximo de 15 GB - você precisa de memória para outras coisas.

    Quando seu SQL Server estiver sob essa pressão de memória externa e precisar liberar memória para outros aplicativos, o cache do seu plano sofrerá.

    Então você tem algumas opções:

    • Defina a memória máxima apropriadamente - digamos, 12 GB (ou até menos se você for executar outros aplicativos no servidor). aplicativo precisa de 2-3 GB de RAM - ele já estará disponível
    • Pare de executar outros aplicativos no servidor - isso pode ser difícil se forem outros administradores de sistema remotos e executando coisas como SSMS. Configurei alarmes de contador Perfmon para o número de sessões RDP abertas e alertei quando for diferente de 0 - isso pode ajudar a capturar o culpado em ação.
    • Adicione mais memória à VM - mas não acho que você realmente precise. Algumas evidências são mostradas pelo relatório sp_Blitz de "nenhuma espera significativa detectada". Eu não acho que você esteja sob pressão de memória frequente, especialmente porque você relata que isso só acontece de vez em quando. Esta é a opção menos econômica.
    • 34
  2. Best Answer

    OK, OP aqui, finalmente corrigi esse problema atualizando o SQL Server 2016 para a versão mais recente. Eu tinha SP1e ontem instalei Cumulative Update 6.

    Também defino "memória máxima" apropriadamente, como sugere a resposta de Brent. A propósito, ótima resposta, peço a todos que a votem positivamente.

    Já se passaram 36 horas e contando, os planos não estão sendo redefinidos.

    Brent Ozar também tem um site muito legal aqui: https://sqlserverupdates.com/ para ajudar a determinar quais atualizações você precisa.

    Uma outra coisa que ajudou foi detectar e resolver o problema de "chaves estrangeiras não confiáveis". Brent tem um artigo muito legal (hahah, sim, Brent de novo, eu sei direito) sobre como resolver, é só pesquisar no google, ele é o resultado número 1

    • 6

  3. Eu tive esse problema na minha caixa de teste doméstica e descobri que adicionar a permissão 'Bloquear páginas na memória' à conta de serviço do SQL Server resolveu o problema, mas não tenho certeza se esse é o melhor conselho.

    Consulte Ativar a opção Bloquear páginas na memória (Windows)

    • 0

relate perguntas