Frederik Vanderhaegen's questions

Temos um SQL Server 2014 Enterprise onde os backups DIFF falham.
Esta é a mensagem de erro que recebemos:

Msg 3035, Level 16, State 1, Server sqltest, Line 1
Não é possível realizar um backup diferencial para o banco de dados 'database1', pois não existe um backup de banco de dados atual. Execute um backup completo do banco de dados emitindo novamente BACKUP DATABASE, omitindo a opção WITH DIFFERENTIAL.

Depois de analisar a saída da consulta a seguir, notamos que uma ferramenta de terceiros estava fazendo backups de instantâneos.

    select top 20 bs.type,bs.database_backup_lsn,bs.checkpoint_lsn,bs.backup_start_date,bs.is_snapshot,
    bs.is_copy_only,bs.user_name
    from dbo.backupset bs
    where bs.database_name = 'database1'
    order by backup_start_date desc

De acordo com Pinal Dave , essas ferramentas usam o VSS para fazer um backup que não é um backup completo normal.

O que eu não entendo é por que os backups de LOG são bem-sucedidos? Que eu saiba, eles também são baseados no último backup COMPLETO.
Alguém pode me explicar essa diferença?

Ontem recebi um e-mail de um de nossos fornecedores mencionando que havia problemas de desempenho com seu aplicativo.

Ainda não sei quais são os problemas, mas a solução que eles propõem é reduzir o banco de dados (porque há muito espaço livre). Pelo meu conhecimento, isso não pode resultar em uma melhoria de desempenho, mas aparentemente eles tiveram sucesso com esse método ao aplicar em outros clientes.

A maneira como eu acho que o encolhimento acontece é movendo as páginas de trás para a frente do arquivo e compactando o banco de dados e causando uma fragmentação massiva.

É possível que as varreduras de intervalo aproveitem a compactação do banco de dados?
Ou existem outros casos extremos que podem se beneficiar da redução de um banco de dados?

Nossos 5 bancos de dados principais são executados em um SQL Server 2016 SP2 Enterprise físico (2 * 8 núcleos, 512 GB, Hypertreading) em um único Grupo de Disponibilidade e, às vezes, recebemos erros de que o tempo limite de concessão expirou. Meu entendimento é que, se a concessão não puder ser atualizada, haverá um problema em todo o sistema.

Quando verifico a saída de sp_server_diagnostics(arquivos *SQLDIAG*.xel), na pasta de log da réplica primária, na hora do tempo limite sempre encontro operações de E/S pendentes.

<ioSubsystem ioLatchTimeouts="0" intervalLongIos="0" totalLongIos="1">
<longestPendingRequests>
<pendingRequest duration="26566" filePath="\?\F:\SqlLogs\db1.ldf" offset="80824832" handle= "0x8d10" /> <pendingRequest duration="1987" filePath="\?\O:\SqlLogs\db2.ldf" offset="3880740352" handle="0x1330" /> <pendingRequest duration="1093" filePath="\ ?\O:\SqlLogs\db3.ldf" offset="288143360" handle="0x132c" /> <pendingRequest duration="974" filePath="\?\O:\SqlLogs\db3.ldf" offset="288145408" handle="0x132c" /> <pendingRequest duration="937" filePath="\?\O:\SqlLogs\db3.ldf"offset="288146944" handle="0x132c" />
</longestPendingRequests>
</ioSubsystem>

Isto é o que encontro no clusterlog da réplica primária:

WARN [RES] Grupo de Disponibilidade do SQL Server: [hadrag] Falha ao recuperar a coluna de dados. Código de retorno -1
ERR [RES] Grupo de Disponibilidade do SQL Server: [hadrag] Falha detectada, pulsação de diagnóstico perdida
ERR [RES] Grupo de Disponibilidade do SQL Server <AG_Name>: [hadrag] O Grupo de Disponibilidade não está íntegro com HealthCheckTimeout e FailureConditionLevel
ERR [ RES] Grupo de Disponibilidade do SQL Server <AG_Name>: [hadrag] Resource Alive resultado 0.
ERR [RES] Grupo de Disponibilidade do SQL Server: [hadrag] Falha detectada, pulsação de diagnóstico perdida
ERR [RES] Grupo de Disponibilidade do SQL Server <AG_Name>: [ hadrag] O Grupo de Disponibilidade não está íntegro com HealthCheckTimeout e FailureConditionLevel fornecidos
ERR [RES] Grupo de Disponibilidade do SQL Server <AG_Name>: [hadrag] Resource Alive resultado 0.
WARN [RHS] O recurso AG_Name IsAlive indicou falha.

Estes são os erros no log de erros do SQL Server:

Erro: 19407, gravidade: 16, estado: 1
grupo de disponibilidade de hospedagem do SQL Server 'AG_Name' não recebeu um sinal de evento de processo do cluster de failover do Windows Server dentro do período de tempo limite de concessão.

Erro: 19407, gravidade: 16, estado: 1
A concessão entre o grupo de disponibilidade 'AG_Name' e o cluster de failover do Windows Server expirou. Ocorreu um problema de conectividade entre a instância do SQL Server e o cluster de failover do Windows Server. Para determinar se o grupo de disponibilidade está fazendo failover corretamente, verifique o recurso de grupo de disponibilidade correspondente no cluster de failover do Windows Server.

Always On: a réplica local do grupo de disponibilidade 'AG_Name' está ficando offline porque a concessão expirou ou a renovação da concessão falhou. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

Esta é a saída de SELECT @@version:

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) 17 de setembro de 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: licenciamento baseado em núcleo (64 bits) no Windows Server 2012 R2 Padrão 6.3 (Build 9600: )

Em nosso monitoramento não há sinais de alto uso de CPU. Além disso, nenhum despejo de memória é criado no momento do problema.

Como resultado desse tempo limite, o serviço WSFC reinicia o recurso de cluster 'AG_Name'. Depois que este recurso é reiniciado, tudo funciona perfeitamente novamente.
O que não entendo é: como as solicitações de IO lentas podem causar um tempo limite de concessão? Muitas solicitações de E/S pendentes podem causar um tempo limite de concessão?

Temos um SQL Server 2016 SP2 Enterprise com CU mais recente com os arquivos de banco de dados espalhados por diferentes discos.
Portanto, temos dados, log, tempdb e db do sistema, cada um com sua própria unidade. Data e log contêm apenas um arquivo.
Cada uma dessas unidades tem seu próprio LUN em uma SAN totalmente flash.

Para monitorar a latência, capturo a sys.dm_io_virtual_file_statscada 15 minutos e calculo a latência usando o instantâneo anterior.

Para a latência de gravação, estou usando o seguinte cálculo:

(io_stall_write_ms - lag (io_stall_write_ms,1,0) over (order by checkdate))/(num_of_writes - lag (num_of_writes,1,0) over (order by checkdate)) write_latency 

Estou obtendo uma latência média de gravação de 10ms, mas quando estou iniciando o perfmon (com duração definida para 900 segundos) e monitoro o avg. Disk sec/Write no mesmo período para a mesma unidade Estou obtendo apenas uma latência de gravação média de 3 ms.

Também estou capturando as estatísticas de espera para o mesmo período, quando olho para as esperas PAGEIOLATCH_EX e calculo quanto tempo cada espera levou, também estou obtendo um valor de aproximadamente 3ms.

Eu pensei que io_stall_write_ms representasse o mesmo que avg. Disk sec/Write ou estou perdendo alguma coisa?
Alguém pode explicar esse comportamento?

Temos uma consulta em um SQL Server 2016 SP2 CU12 Enterprise em que o Query Optimizer estima que apenas 1 linha sairia do operador Nested Loops Join , na realidade 108501 linhas voltaram. Isso fez com que o Sortoperador vazasse para o TempDB.

As estimativas na entrada interna (busca de índice) e externa (busca de índice) da junção de loops aninhados estão corretas.

Adicionei os sinalizadores de rastreamento 2363 (Cálculo de Seletividade) e 3604 (redirecione a saída para a janela de mensagens) e aqui descobri que havia um histograma mal formado:

Plan for computation:

  CSelCalcExpressionComparedToExpression( QCOL: [Object1].Column1 x_cmpEq QCOL: [Object3].Column18 )

Loaded histogram for column QCOL: [Object1].Column1 from stats with id 1  *** WARNING: badly-formed histogram ***

Loaded histogram for column QCOL: [Object3].Column18 from stats with id 9

Selectivity: 1.07973e-009

Stats collection generated: 

  CStCollJoin(ID=4, CARD=1 x_jtLeftSemi)

      CStCollBaseTable(ID=1, CARD=5.01133e+007 TBL: Schema1.Table2 AS TBL: AA)

      CStCollFilter(ID=3, CARD=108210)

          CStCollBaseTable(ID=2, CARD=2.00511e+006 TBL: Schema1.Table1 AS TBL: A)

End selectivity computation

Acima é apenas uma parte da saída, o texto completo pode ser encontrado aqui

Quando atualizei o histograma mal formado com fullscan, as estimativas estão corretas (sem fullscan, esse problema não é resolvido).

Mas assim que um registro é inserido na tabela, o histograma está mal formado novamente.

O plano de consulta (com histograma mal formado) pode ser encontrado aqui e aqui você pode encontrar o plano de consulta após a atualização das estatísticas.

Nenhuma correção do otimizador de consulta está habilitada. Quando habilito o estimador de cardinalidade original para esta consulta, usando o sinalizador de rastreamento 9481, estou obtendo o mesmo plano de consulta após as estatísticas de atualização.

O que pode causar o histograma mal formado?

Existe uma maneira de resolver este problema?

Tentei a PERSIST_SAMPLE_PERCENTopção mas não fez diferença, o histograma também fica mal formado.

Estou lutando com o ajuste de uma das consultas em nosso SQL Server 2016 SP2 de produção. Eu precisava anonimizar a consulta devido às políticas de segurança. A consulta é assim:

SELECT TOP 1000 Object1.Column1,Object1.Column2,Object1.Column3,Object1.Column4,Object1.Column5,Object1.Column6,Object1.Column7,Object1.Column8,Object1.Column9,Object1.Column10,Object1.Column11,Object1.Column12,Object1.Column13,Object1.Column14,Object1.Column15,Object1.Column16,Object1.Column17,Object1.Column18,Object1.Column19,Object1.Column20,Object1.Column21,Object1.Column22,Object1.Column23,Object1.Column24,Object1.Column25,Object1.Column26,Object1.Column27,Object1.Column28,Object1.Column29,Object1.Column30,Object1.Column31,Object1.Column32,Object1.Column33,Object1.Column34,Object1.Column35,Object1.Column36,Object1.Column37,Object1.Column38,Object1.Column39,Object1.Column40,Object1.Column41,Object1.Column42,Object1.Column43,Object1.Column44,Object1.Column45,Object1.Column46,Object1.Column47,Object1.Column48,Object1.Column49,Object1.Column50,Object1.Column51,Object1.Column52
FROM Schema1.Object2 Object1
LEFT OUTER JOIN Schema1.Object3 Object4 ON Object4.Column3 = Object1.Column3
WHERE ((Object1.Column43 IS NULL OR Object1.Column43='') AND (Object4.Column54 = 0 OR Object4.Column54 IS NULL) AND Object1.Column3 = N'SomeValue')
ORDER BY Object1.Column3 ASC,Object1.Column8 ASC,Object1.Column7 ASC,Object1.Column12 ASC,Object1.Column13 ASC,Object1.Column1 ASC

O plano de consulta real pode ser encontrado aqui
Como você pode ver, uma busca de índice é feita em Object2.Index1.Object1, mas as estimativas são terríveis. O SQL Server estimou que 194.907 linhas retornariam, mas apenas 710 linhas são retornadas.

Quando removo a ORDER BYcláusula as estimativas são muito melhores, o SQL Server esperava que apenas 1181 linhas voltassem. O plano pode ser encontrado aqui .

O que eu não entendo é por que um ORDER BYpode influenciar as estimativas de um índice de busca. Ambas as consultas são executadas com o mesmo valor para column3.
Alguém pode me explicar isso?

Eu tenho um servidor virtual executando o SQL Server 2016 (4 vcores) onde na maioria das vezes há um comprimento de fila do processador de 4 (às vezes até 15), mas a CPU está usando 25% em média. Existem cerca de 3000 lotes/s.

Usando uma consulta encontrada em um artigo de Glenn Berry encontrado no SQLSkills.com, descobri que existe um avg_task_countde 15 e um avg_runnable_task_countde 2 (mas não constantemente):

SELECT AVG(current_tasks_count) AS [Avg Task Count], 
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);

É correto dizer que o tamanho da fila do processador e o avg_task_countsão uma indicação de que há muitas solicitações e o servidor tem problemas para processá-las? Mas o que eu não entendo é por que o uso da CPU não é maior?

O host físico tem um uso de CPU de 25%, não há reserva de memória, mas não há comprometimento excessivo de memória no host.

Temos dois SQL Server 2016 SP2 CU6 Enterprise em execução em hardware físico (32 núcleos) com um AG que contém 5 db's. O secundário não é legível. Hoje fizemos um failover manual planejado, para que pudéssemos fazer manutenção no outro servidor. Fizemos o failover em um momento em que não havia carga pesada.

O failover foi executado por meio da GUI do SSMS e não apresentou erros. Mas, vários minutos depois, recebemos muitos telefonemas que os usuários não conseguiram fazer login. Nossa primeira tentativa de solucionar o problema foi fazer uma conexão com o SSMS, mas isso deu um erro de que não conseguimos conectar, a mensagem de erro exata que não me lembro.

Em seguida, demos uma olhada no arquivo errorlog no bloco de notas e foi assim que o SQL Server Engine travou. Não houve novas entradas adicionadas ao log após o failover.

Devido à alta urgência do problema, reiniciamos o SQL Server Service no primário e o problema desapareceu.
Poderíamos ter tentado fazer uma conexão DAC para ver qual era o problema, mas o objetivo era colocar o servidor online novamente o mais rápido possível.

Depois que tudo estava novamente online, começamos a analisar os arquivos de log.
No log de erros do primário antigo, encontramos vários erros 35278. Nenhuma transação de longa execução estava sendo executada no momento do failover.

Em seguida foi o arquivo de evento AlwaysON_health, aqui as entradas apenas pararam após o failover.

Em seguida, demos uma olhada no *_*_SQLDIAG_*_*.xelarquivo. Aqui tivemos sorte.

A primeira coisa que notamos foi:

<queryProcessing maxWorkers="960" workersCreated="1064"
 workersIdle="23" tasksCompletedWithinInterval="19" pendingTasks="34"
 oldestPendingTaskWaitingTime="1316776"

Por algum motivo, o número máximo de trabalhadores foi excedido, isso pode explicar por que ninguém conseguiu se conectar.

Estas foram as tarefas pendentes:

<pendingTasks><br>
   <entryPoint name="Process Command" count="14" /><br>
   <entryPoint name="SNI New Connection" count="2" /><br>
   <entryPoint name="SNI Accept Done" count="3" /><br>
   <entryPoint moduleName="sqlmin.dll" imageBase="0x7ff827e80000" size="0x251e000" address="0x7ff8286622e0" count="6" /><br>
   <entryPoint moduleName="sqlmin.dll" imageBase="0x7ff827e80000" size="0x251e000" address="0x7ff8292b1190" count="2" /><br>
   <entryPoint moduleName="sqldk.dll" imageBase="0x7ff827120000" size="0x4c9000" address="0x7ff827125e60" count="7" />  <br>
</pendingTasks>

Havia também vários processos bloqueados nesse arquivo. Todos eles tinham um waitresource como DATABASE: 5:5, DATABASE: 5:15, DATABASE: 5:26, ...
Todos esses processos tinham lastbatchstartede lastbatchcompletedconfigurados para 1900-01-01. O tempo de espera foi igual a quanto tempo o failover ocorreu.
Em nosso monitoramento vimos que havia um total de 950 processos bloqueados.

Esta é uma amostra de um relatório de processo bloqueado:

<blocked-process-report monitorLoop="0">
 <blocked-process>
  <process id="process13f08032ca8" taskpriority="0" logused="10000" waitresource="DATABASE: 5:3 " waittime="1334205" schedulerid="4" kpid="11752" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="our_app" hostname="host2021" hostpid="14196" loginname="user1" isolationlevel="read committed (2)" xactid="0" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="DATABASE: 5:3 " waittime="1335893" spid="70" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="our_app" hostname="host1" hostpid="1324" loginname="user1" isolationlevel="read committed (2)" xactid="0" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>
<blocked-process-report monitorLoop="0">
 <blocked-process>
  <process id="process13f08033848" taskpriority="0" logused="10000" waitresource="DATABASE: 5:3 " waittime="1335893" schedulerid="4" kpid="12004" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="our_app" hostname="host1" hostpid="1324" loginname="user1" isolationlevel="read committed (2)" xactid="0" currentdb="1" currentdbname="master" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="background" waittime="1139955" spid="141" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

Um dos processos de bloqueio foi executado como usuário SAe teve o comando UNKNOWN TOKEN. O last_wait_typetempo dessas sessões foi PARALLEL_REDO_WORKER_WAIT_WORKe teve um last_request_start_timetempo igual ao tempo em que fizemos o failover.
Eu não esperava encontrar esse tipo de espera no primário.
É correto dizer que isso PARALLEL_REDO_WORKER_WAIT_WORKnão deveria ser esperado em uma primária?

Também em nosso monitoramento encontramos aproximadamente 1000 sessões que tinham o status SLEEPINGe tinham um last_request_start_timede 01/01/1900.
Isso não parece nada normal.

Alguém pode explicar quais são essas tarefas pendentes?
Meu palpite da causa raiz são as 1000 sessões que tiveram o status SLEEPING, todas usaram um trabalhador. Isso causou as tarefas pendentes e, devido ao processo de bloqueio, nenhum trabalhador pôde fazer nenhum trabalho. Isso fez com que o failover em algum lugar ficasse preso.
Isso pode estar correto?

Recentemente, li uma postagem no blog mssqltips.com sobre gargalos de memória no SQL Server. Neste artigo li o seguinte:

Os seguintes contadores de desempenho no SQL Server: O objeto Buffer Manager também pode indicar pressão de memória:

• Alto número de páginas de ponto de verificação/s
• Alto número de gravações preguiçosas/s
• Alto número de leituras de página/s
• Baixa taxa de acerto do cache de buffer
• Baixa expectativa de vida da página

O que me chamou a atenção foi que um número alto de 'páginas de checkpoints/s' pode indicar pressão de memória.

Meu entendimento era que um ponto de verificação grava páginas 'sujas' no disco. Isso pode ser acionado de diferentes maneiras.

• automático (para manter o intervalo de recuperação)
• indireto (para manter o tempo de recuperação alvo)
• manual
• interno

Um número tão alto de checkpoints indica um sistema muito ocupado (no caso de checkpoints automáticos e indiretos).

Como um ponto de verificação nunca remove uma página do cache de buffer, não entendo bem como o alto número de pontos de verificação/s pode indicar pressão de memória. Se houver pressão de memória, eu gostaria de ver um alto número de 'gravações preguiçosas/s'. O escritor preguiçoso remove 'páginas frias' da memória, para dar lugar a novas páginas.

Como um alto número de páginas de ponto de verificação/s indica pressão de memória?

Hoje eu estava solucionando um problema no TempDB no SQL Server 2016 SP2 usando eventos estendidos e adicionei o evento mixed_extent_allocation e transaction_log ao meu rastreamento.

Eu esperava que mixed_extent_allocation não aparecesse nos meus resultados porque a seguinte consulta retornou 0 como resultado:

select is_mixed_page_allocation_on  
from sys.databases 
where database_id=2

Mas para minha surpresa este evento apareceu várias vezes. Isso foi precedido por um evento transaction_log com SGAM como contexto e operação LOP_SET_BITS.

Isso me deixou curioso e verifiquei o conteúdo da primeira página SGAM do TempdB:

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

Este é um trecho do resultado:

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

Isso me fez concluir que extensões mistas ainda são usadas. Eu pensei que o SQL Server 2016 usa apenas extensões uniformes (exceto master, msdb e model). Ou minha conclusão está errada?

Por que ainda estou vendo o evento mixed_extent_allocation aparecendo?

Em um SQL Server 2016 SP2 com memória máxima definida como 25 GB, temos uma consulta que é executada cerca de 80 vezes em um minuto. A consulta derrama cerca de 4.000 páginas para tempdb. Isso causa muito IO no disco do tempdb.

Ao examinar o plano de consulta (consulta simplificada), você verá que o número de linhas estimadas é igual ao número de linhas reais, mas ainda ocorrem derramamentos. Portanto, estatísticas desatualizadas não podem ser a causa do problema.

Fiz alguns testes e os seguintes vazamentos de consulta para o Tempdb:

select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

Mas se eu selecionar uma coluna diferente, não ocorrerão derramamentos:

select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc 
option (maxdop 1)

Então eu tentei 'ampliar' o tamanho da coluna id:

select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc 
option (maxdop 1)

Então também não ocorre nenhum derramamento.

Por que o uniqueidentifier está se espalhando para tempdb e uma coluna de datatime não? Quando excluo cerca de 20.000 registros, também não ocorre derramamento quando seleciono a coluna id.

Com o seguinte script, você pode reproduzir o problema:

CREATE TABLE SortProblem
  (
     id             UNIQUEIDENTIFIER,
     startdate      DATETIME,
     sequencenumber BIGINT,
     status         VARCHAR(50),
     PRIMARY KEY CLUSTERED(id)
  )

SET nocount ON;

WITH nums(num)
     AS (SELECT TOP 103000 ROW_NUMBER()
                             OVER (
                               ORDER BY 1/0)
         FROM   sys.all_objects o1,
                sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
       DATEADD(millisecond, num, GETDATE()),
       num,
       CASE
         WHEN num <= 100000 THEN 'A'
         WHEN num <= 101000 THEN 'B'
         WHEN num <= 102000 THEN 'C'
         WHEN num <= 103000 THEN 'D'
       END
FROM   nums

CREATE NONCLUSTERED INDEX [IX_Status]
  ON [dbo].[SortProblem]([status] ASC)
  INCLUDE ([sequencenumber]) 

No SQL Server 2016 SP2, temos uma consulta que tem uma estimativa muito baixa no operador de loop aninhado. Devido à estimativa baixa, essa consulta também é derramada no tempdb.

Se estiver correto, o SQL Server 2014+ usa a estimativa de histograma grosseiro para calcular o número estimado de linhas em uma junção.
Mas quando executo a consulta, o SQL Server usa o vetor de densidade para calcular o número de linhas estimadas.
O SQL Server está usando apenas a estimativa de histograma grosseiro se não houver nenhuma wherecláusula?

Normalmente eu usaria estatísticas filtradas para melhorar as estimativas quando tenho uma tabela com dados distorcidos. Mas neste caso isso não parece funcionar.

Existe uma maneira de melhorar as estimativas no loop aninhado?

Usando o código a seguir, você pode reproduzir os dados:

create table MyTable
(
    id int identity,
    field varchar(50),
    constraint  pk_id primary  key clustered (id)
)
go

create table SkewedTable
(
    id int identity,
    startdate datetime,
    myTableId int,
    remark varchar(50),
    constraint  pk_id primary  key clustered (id)
)

set nocount on

insert into MyTable select top 1000 [name] from master..spt_values
go

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 1000

insert into SkewedTable select GETDATE(),FLOOR(RAND()*(1000))+1,REPLICATE(N'A',FLOOR(RAND()*(40))+1)
go 

CREATE NONCLUSTERED INDEX [ix_field] ON [dbo].[MyTable]([field] ASC)
go

CREATE NONCLUSTERED INDEX [ix_mytableid] ON [dbo].[SkewedTable]([myTableId] ASC)
go

--95=varchar in sys.messages
set nocount off

;with cte as
( 
    select GETDATE() as startdate ,95 as myTableId, REPLICATE(N'B',FLOOR(RAND()*(40))+1) as remark
    union all
    select * from cte
)
insert into skewedtable select top 40000 * from cte
option(maxrecursion 0)
go

update statistics mytable with fullscan
go

update statistics skewedtable with fullscan
go

Em um servidor com 32GB estamos executando o SQL Server 2014 SP2 com memória máxima de 25GB temos duas tabelas, aqui você encontra uma estrutura simplificada de ambas as tabelas:

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceId] [int] NULL,
    [typeID] [int] NULL,
    [remark] [varchar](max) NULL,
    CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceUID] [int] NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

com os seguintes índices não agrupados:

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
    [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
    [resourceId] ASC,
    [typeID] ASC
)

O banco de dados está configurado com compatibility level120.

Quando executo essa consulta , há vazamentos para arquivos tempdb. É assim que executo a consulta:

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

Se não selecionar o [remark]campo não ocorre nenhum derramamento. Minha primeira reação foi que os vazamentos ocorreram devido ao baixo número de linhas estimadas no operador de loop aninhado.

Então eu adiciono 5 colunas datetime e 5 integer à tabela de configurações e as adiciono à minha instrução select. Quando executo a consulta, não ocorrem derramamentos.

Por que os derramamentos só acontecem quando [remark]é selecionado? Provavelmente tem algo a ver com o fato de que este é um arquivo varchar(max). O que posso fazer para evitar derramar tempdb?

Adicionar OPTION (RECOMPILE)à consulta não faz diferença.

No BOL eu li o seguinte sobre MultiSubnetFailover=True:

A opção de conexão MultiSubnetFailover deve ser definida como True mesmo se o grupo de disponibilidade abranger apenas uma única sub -rede . Isso permite pré-configurar novos clientes para dar suporte à expansão futura de sub-redes sem a necessidade de futuras alterações na cadeia de conexão do cliente e também otimizar o desempenho de failover para failovers de sub-rede única.

Pelo que entendi o funcionamento do MultiSubnetFailover, com esta opção definida o driver cliente configura um socket para cada endereço IP associado ao listener. Todos eles são verificados em paralelo para agilizar o processo de encontrar o IP online e o primeiro que responder será o utilizado para a conexão. Aqui eu vejo um ganho de desempenho.

Mas onde está o ganho de desempenho em uma única sub-rede? Existe apenas o IP associado ao ouvinte.

Estou fazendo algumas pesquisas sobre como solucionar problemas de pressão de memória local. Corrija-me se estiver errado, mas pelo que entendi é que o SQL Server usa um algoritmo CLOCK para o cache e userstores. Cada relógio tem dois ponteiros, um externo e um interno.

Meu interesse é pelo ponteiro do relógio interno. Se um trabalhador que acessa um cache percebe que um cache é maior que uma certa porcentagem de memória, o ponteiro do relógio interno começa a liberar memória para esse cache.

Usando o dmv dm_os_memory_cache_clock_handsposso ver informações sobre os ponteiros do relógio. A coluna rounds_countrepresenta quantas vezes os ponteiros do relógio varreram todo o cache. last_tick_timeé a hora em que o ponteiro se moveu pela última vez. round_start_timeé a hora da última varredura.

Qual é a diferença entre last_tick_timee round_start_time? A cache não é limpa quando a mão é movida?

Quantas entradas são removidas a cada tique-taque do relógio?

Alguém pode me ajudar a entender o funcionamento desse algoritmo de relógio no SQL Server?

Temos um SQL Server 2016 SP1 com memória máxima definida para 24 GB.
Este servidor tem um alto número de compilações, apenas 10% dessas compilações são de consultas Ad-Hoc. Portanto, os planos recém-compilados devem ser armazenados no cache do plano, mas o tamanho do cache do plano não está aumentando (aproximadamente 3,72 GB).

Suspeito que haja pressão de memória local que leve à remoção de planos do cache. O limite de pressão do cache do plano é de 5 GB. (75% de memória de destino visível de 0-4GB + 10% de memória de destino visível de 4GB-64GB + 5% de memória de destino visível>64GB). Quando um cachestore atinge 75% do limite de pressão, os planos devem ser removidos do cache. No meu caso, 75% de 5 GB são 3,75 GB. Portanto, é plausível que esta seja a causa das compilações altas.

Existe uma maneira de medir (perfmon, eventos estendidos, ...) a remoção de planos fora do cache? Então, posso ter certeza de que a pressão da memória local é realmente a causa das compilações altas?

Eu tenho um servidor executando o SQL Server Enterprise 2016 SP2 e notei que a cada minuto várias centenas de planos de consulta são invalidados. Eu corro sp_BlitzCache @expertmode=1para verificar a coluna 'Created At' para ver quando o plano é gerado.
A cada minuto, cerca de 500 planos são recriados e isso continua o dia inteiro e são sempre planos diferentes que são recompilados.

Quando uso o DMV sys.dm_db_index_usage_statspara verificar as user_updates vejo que acontecem entre 10.000 e 1.000.000 atualizações nos índices. Algumas tabelas têm mais de 10.000.000 registros, mas a maioria delas tem entre 100.000 e 2.000.000 registros.

Tanto quanto sei, um plano de consulta é invalidado nas seguintes circunstâncias:

• Modificações feitas na definição de tabela/visualização por ALTER TABLE/ALTER VIEW
• Alterações feitas em quaisquer índices usados ​​pelo plano de execução
• Atualizações das estatísticas utilizadas pelo plano de execução (manual ou automático)
• Descartando um índice usado pelo queryplan
• sp_recompile / com recompilação
• Grande número de alterações na tabela
• Adicionando/eliminando um gatilho em uma tabela usada pelo plano de execução
• Pressão de memória

Todas as consultas usam KEEP PLANe KEEPFIXED PLAN, então acho que a atualização automática ou manual das estatísticas não invalidará o plano. Este é um aplicativo de terceiros, portanto, não posso alterar as consultas. Quando executo UPDATE STATISTICS [TableTest] WITH FULLSCANnão vejo uma recompilação das consultas que usam essa tabela.

Em seguida, usei um script de Jonathan Kehayias para verificar se havia pressão de memória, mas o script só retornou RESOURCE_MEMPHYSICAL_HIGH, então não acho que seja esse o problema.

Nenhuma modificação na definição da tabela, índices aconteceram nem a consulta foi executada com sp_recompile / com recompile

Existe algo mais que pode causar a invalidação de um plano de consulta?

Atualização:
Depois de mais algumas pesquisas, acho que as recompilações são acionadas pela pressão da memória no plancache. Este artigo fornece muitas informações sobre os componentes internos do cache do plano. Trata-se do SQL Server 2005, mas esperamos que isso ainda se aplique ao SQL Server 2016.
De acordo com este artigo, você pode calcular o limite de pressão com base na quantidade total de memória de destino visível.

Minha pesquisa avança lentamente porque é difícil encontrar informações sobre pressão no cache do plano.
Vou mantê-lo informado sobre o progresso

Temos um servidor SQL Server 2016 SP1 CU7 Enterprise onde habilitamos o repositório de consultas em um banco de dados. Este é um banco de dados muito usado.
O Max Sizefoi definido para 200 MB e Stale Query Thresholdé de 1 dia.
Eu costumava sys.database_query_store_optionsverificar as propriedades do repositório de consultas e tudo funciona perfeitamente, desde que the current_storage_size_mbfique abaixo dos 200 MB.
Eu configurei Size Based Cleanup Modepara Auto, mas nenhuma limpeza acontece, normalmente uma limpeza deve ser acionada quando o repositório de consultas estiver 90% cheio. O que acontece a seguir é que o repositório de consultas entra no modo somente leitura porque não há mais espaço disponível.
Eu tentei mudar Query Store Capture Modepara Auto, mas nada muda.
Se eu mudar Stale Query Thresholdpara 30 dias, as actual_state_descmudanças para READ_WRITE e o repositório de consultas começarão a capturar novamente. ocurrent_storage_size_mbapenas se torna maior que o max_storage_size_mbe depois de um tempo actual_state_descele volta para READ_ONLY.

Alguém pode explicar esse comportamento?

Estou executando alguns testes para descobrir quando os planos de consulta são invalidados após uma atualização de estatísticas. A máquina que estou usando para teste é uma SQL Server Developer 2016 SP1 CU7.

Encontrei um artigo no BrentOzar.com como posso rastrear as recompilações, mas nunca consigo uma recompilação. Auto Update Statisticse Auto Create Statisticsambos estão habilitados

Este é o meu teste:

/* Create a table and put over 1k rows in it (to get past the 500 row stats threshold) */
CREATE TABLE dbo.MyTable (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StringField VARCHAR(50));
GO
INSERT INTO dbo.MyTable(StringField) 
SELECT 'Stuff' FROM sys.all_objects;
GO

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

Pelo que entendi, o plano deve ser invalidado quando você liga UPDATE STATISTICS WITH FULLSCANe há linhas alteradas. Ou eu estou esquecendo de alguma coisa?

Temos um banco de dados em execução no SQL Server 2016 SP1 Enterprise e no qual Allow Snapshot Isolationestá habilitado. Nesse cenário, os leitores não bloqueiam os escritores e os escritores não bloqueiam os leitores.

Hoje vimos que uma SELECT-query estava bloqueando uma UPDATE-query. Estas são as consultas em questão:

Primeira consulta:

IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT TOP 10 Field1, Field2, Field3 FROM [dbo].[TABLE1] 
WHERE ([Field1] = @SV1 AND (1=1))
ORDER BY A.[Field1] ASC,A.[Field2] ASC
OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

Segunda consulta:

UPDATE [dbo].[Table1] SET [Field4]= @V128, [Field5]= @V140, [Field6]= @V141 WHERE [Field3] = @ID0

Se entender o versionamento corretamente, uma SELECT-query não usa nenhum bloqueio porque funciona em um instantâneo dos dados (tirado no início da transação). Como é possível que a SELECT-query bloqueie o UPDATE?