All perguntas(dba)

Olá a todos e desde já agradeço a vossa ajuda. Estamos enfrentando desafios com os Grupos de Disponibilidade do SQL Server 2017.

Fundo

A empresa é um software de back-end B2B de varejo. Cerca de 500 bancos de dados de locatário único e 5 bancos de dados compartilhados usados ​​por todos os locatários. A característica da carga de trabalho é lida principalmente e a maioria dos bancos de dados tem atividade muito baixa.

Servidores de produção física hospedados em co-localização foram recentemente atualizados do SQL Server 2014 Enterprise no Windows Server 2012 em uma configuração de SAN/FCI compartilhada, para o SQL Server 2017 Enterprise no Windows Server 2016 em 2 soquetes/32 núcleos/768 GB de RAM e local Unidades SSD usando AlwaysOn AG. O tráfego AG usa portas NIC 10G dedicadas com conexão de cabo cruzado.

O requisito deles é que todos os bancos de dados façam failover juntos, então eles tiveram que colocá-los todos em um único AG. É uma réplica síncrona única e não legível em um servidor idêntico.

Os novos servidores estão em produção desde junho de 2018. A última CU (CU7 na época) e as atualizações do Windows foram instaladas e o sistema estava funcionando bem. Cerca de um mês depois, após atualizar os servidores de CU7 para CU9, eles começaram a perceber os seguintes desafios, listados em ordem de prioridade.

Temos monitorado os servidores usando o SQL Sentry e não observamos gargalos físicos. Todos os principais indicadores parecem bons. A CPU tem uma média de 20%, tempos de E/S normalmente inferiores a 1 ms, RAM não totalmente utilizada e rede <1%.

Desafios

Os sintomas parecem melhorar após o failover, mas voltam em alguns dias, independentemente do servidor principal - os sintomas são idênticos em ambos os servidores.

1. Tempos limite esporádicos do cliente e falhas de conectividade, como

   ...ocorreu um erro ao estabelecer a conexão...

   ou

   O tempo limite de execução expirou

   Às vezes, eles duram até 40 segundos e depois diminuem.

2. O trabalho de backup do log de transações leva 10 vezes mais para ser concluído do que antes. Anteriormente, levava de 2 a 3 minutos para fazer backup dos logs de todos os 500 bancos de dados, agora leva de 15 a 25. Verificamos que o próprio Backup funciona bem com boa taxa de transferência. No entanto, há um pequeno atraso após a conclusão do backup de um log e antes de iniciar o próximo. começa muito baixo, mas dentro de um dia ou dois chega a 2-3 segundos. Multiplicado por 500 bancos de dados, e aí está a diferença.

3. Ocasionalmente, alguns bancos de dados aparentemente aleatórios ficam presos no estado "Não sincronizando" após o failover manual. A única maneira de resolver isso é reiniciar o SQL Server Service na réplica secundária ou remover e reingressar esses bancos de dados no AG.

4. Outro problema introduzido pelo CU10 (e não resolvido no CU11): conexões com o tempo limite secundário no bloqueio em master.sys.databases e até mesmo incapaz de usar o explorador de objetos SSMS para réplica secundária. A causa raiz parece estar bloqueando pelo gravador VSS do Microsoft SQL Server emitindo a seguinte consulta:

   select name, 
          recovery_model_desc, 
          state_desc, 
          CONVERT(integer, is_in_standby), 
          ISNULL(source_database_id,0) 
     from master.sys.databases
   

Observações

Acredito que encontrei a arma fumegante nos logs de erro. Os logs de erros estão cheios de mensagens AG, que são rotuladas como 'somente informativas', mas parece que não são nada normais, e há uma correlação muito forte de sua frequência com os erros do aplicativo.

Os erros são de vários tipos e vêm em sequências:

• DbMgrPartnerCommitPolicy::SetSyncState: GUID

• DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: GUID

• A conexão dos Grupos de Disponibilidade AlwaysOn com o banco de dados secundário foi encerrada para o banco de dados primário 'XYZ' na réplica de disponibilidade 'DB' com ID da réplica: {GUID}. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

• Conexão dos Grupos de Disponibilidade AlwaysOn com o banco de dados secundário estabelecido para o banco de dados primário 'ABC' na réplica de disponibilidade 'DB' com ID da réplica: {GUID}. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

Alguns dias há 10 de milhares desses.

Este artigo discute o mesmo tipo de sequência de erros no SQL 2016 e lá diz que é anormal. Isso também explica o fenômeno de 'não sincronização' após o failover. O problema discutido era de 2016 e foi corrigido no início deste ano em uma UC. no entanto, é a única referência relevante que pude encontrar para os 2 primeiros tipos de mensagens, além de referências a mensagens de propagação inicial automática, o que não deve ser o caso aqui, pois o AG já está estabelecido.

Aqui está um resumo dos erros diários da semana passada, para dias com mais de 10 mil erros por tipo no PRIMARY (o secundário mostra 'perdendo a conexão com o primário...'):

Date        Message Type (First 50 characters)                  Num Errors
10/8/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  61953
10/3/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  56812
10/4/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  27951
10/2/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  24158
10/7/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  14904
10/8/2018   Always On Availability Groups connection with seco  13301
10/3/2018   DbMgrPartnerCommitPolicy::SetSyncState: 783CAF81-4  11057
10/3/2018   Always On Availability Groups connection with seco  10080

Também ocasionalmente vemos mensagens "estranhas", como:

O banco de dados do grupo de disponibilidade "DB" está alterando as funções de "SECONDARY" para "SECONDARY" porque a sessão de espelhamento ou o grupo de disponibilidade falhou devido à sincronização de função. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

... entre uma série de estados em mudança de "SECUNDÁRIO" para "RESOLVIDO".

Após o failover manual, o sistema pode ficar vários dias sem uma única mensagem desses tipos e, de repente, sem motivo aparente, receberemos milhares de uma vez, o que, por sua vez, faz com que o servidor pare de responder e faça com que o aplicativo tempos limite de conexão. Este é um bug crítico, pois alguns de seus aplicativos não incorporam um mecanismo de repetição e, portanto, podem perder dados. Quando ocorre uma explosão de erros, os tipos de espera a seguir disparam. Isso mostra as esperas logo após o AG parecer ter perdido a conexão com todos os bancos de dados de uma só vez:

Cerca de 30 segundos depois, tudo volta ao normal em termos de esperas, mas as mensagens AG continuam inundando os logs de erros em taxas variadas e durante diferentes horários do dia, horários aparentemente aleatórios, incluindo horários fora de pico. O aumento simultâneo na carga de trabalho durante essas rajadas de erro piora as coisas, é claro. Se apenas alguns bancos de dados forem desconectados, isso normalmente não causa o tempo limite das conexões, pois é resolvido com rapidez suficiente por conta própria.

Tentamos verificar se foi realmente o CU9 que iniciou o problema, mas conseguimos fazer o downgrade de ambos os nós apenas para o CU9. As tentativas de fazer o downgrade de qualquer nó para CU8 resultaram em que o nó ficasse preso no estado 'Resolvendo' mostrando o mesmo erro no log:

Não é possível ler a configuração persistente do grupo de disponibilidade Always On com a ID de recurso correspondente '…. A configuração persistente é gravada por um SQL Server de versão superior que hospeda a réplica de disponibilidade primária. Atualize a instância local do SQL Server para permitir que a réplica de disponibilidade local se torne uma réplica secundária.

Isso significa que teremos que introduzir tempo de inatividade para poder fazer o downgrade de ambos os nós para CU8 ao mesmo tempo. Isso também sugere que houve alguma atualização importante no AG que pode explicar o que estamos enfrentando.

Já tentamos ajustar o max_worker_threads do padrão de 0 (=960 em nossa caixa com base neste artigo ) gradualmente até 2.000 sem impacto observado nos erros.

O que podemos fazer para resolver essas desconexões AG? Alguém aí está passando por problemas semelhantes? Outras pessoas com grande número de bancos de dados em um AG talvez vejam mensagens semelhantes no log de erros SQL começando com CU9 ou CU8?

Agradecemos antecipadamente por qualquer ajuda!

Eu tenho um banco de dados SQL Server onde as consultas são muito lentas e há muitos bloqueios e bloqueios.

Quando olho para os DMVs de índice ausentes e os planos de consulta, não há sugestões.

Por que é que?

Como faço para verificar o uso de memória pelo meu servidor SQL na caixa de produção. Estou usando o SQL Server 2016. Sempre que verifico o gerenciador de tarefas, ele mostra acima de 90%. Eu não acho que seja o uso real de memória pelo servidor sql.

Eu tenho uma ferramenta de desempenho SQL grafana que mostra o uso da CPU muito menos do que vejo no gerenciador de tarefas. Verifiquei o Monitor de Recursos, pode-se ver o valor médio da CPU. Estou confuso sobre qual é o uso de memória do servidor SQL. Estou tentando determinar se a pressão da memória é um problema para alguns dos meus problemas.

Alguém pode direcionar para uma explicação boa/adequada.

Estou tentando entender a melhor maneira de decidir o tamanho das colunas varchar, tanto das perspectivas de armazenamento quanto de desempenho.

Desempenho
Da minha pesquisa, pareceque varchar(max) só deve ser usado se você realmente precisar; isto é, se a coluna deve acomodar mais de 8000 caracteres, uma razão é a falta de indexação (embora eu suspeite um pouco da indexação em campos varchar em geral. Eu sou muito novo nos princípios de banco de dados, então talvez isso seja infundado ) e compressão (mais uma preocupação de armazenamento). Na verdade, em geral as pessoas parecem recomendar apenas usar o que você precisa, ao fazer varchar(n).... oversizing é ruim, porque as consultas devem levar em conta o tamanho máximo possível. Mas também foi afirmado que o mecanismo usará metade do tamanho indicado como uma estimativa do tamanho real médio dos dados. Isso implicaria que se deve determinar, a partir dos dados, qual é o tamanho médio, dobrá-lo e usá-lo como n. Para dados com variabilidade muito baixa, mas diferente de zero, isso implica em um superdimensionamento de até 2x sobre o tamanho máximo, o que parece muito, mas talvez não seja? Insights seriam apreciados.

Armazenamento
Depois de ler sobre como funciona o armazenamento em linha vs. fora de linha, e tendo em mente que o armazenamento real é limitado aos dados reais, parece-me que a escolha de n tem pouca ou nenhuma influência no armazenamento (além certificando-se de que é grande o suficiente para conter tudo). Mesmo usando varchar(max) não deve ter nenhum impacto no armazenamento. Em vez disso, um objetivo pode ser limitar o tamanho real de cada linha de dados a ~8.000 bytes, se possível. Isso é uma leitura precisa das coisas?

Contexto
Alguns dos dados de nossos clientes flutuam um pouco, então geralmente fazemos colunas um pouco mais largas do que precisam ser, digamos 15-20% maiores, para essas colunas. Eu queria saber se havia outras considerações especiais; por exemplo, alguém com quem trabalho me disse para usar tamanhos 2^n - 1 (não encontrei evidências de que isso seja uma coisa ....)

Estou falando da criação inicial da tabela. Um cliente nos dirá que vai começar a nos enviar uma nova tabela e enviar dados de amostra (ou apenas o primeiro conjunto de dados de produção), que analisamos e criamos uma tabela do nosso lado para armazenar os dados. Queremos fazer a tabela do nosso lado para lidar com importações futuras, bem como o que está na amostra. Mas, certas linhas tendem a ficar mais longas, então as preenchemos.

A questão é quanto, e existem diretrizes técnicas?

Quando estou rastreando esperas com sp_BlitzFirst, recebo este detalhe:

<?ClickToSeeDetails -- 
For 20 seconds over the last 5 seconds, SQL Server was waiting on this 
particular bottleneck.


 -- ?>

Deveria ler "por 20 vezes nos últimos 5 segundos?" A descoberta foi CLR_SEMAPHORE.

Existe uma maneira de criar uma tabela que tenha a mesma estrutura de outra tabela, mas com colunas adicionais? A partir desta pergunta , posso copiar a estrutura de uma tabela com:

CREATE TABLE new_table_name ( like old_table_name including all)

E então eu posso adicionar colunas uma de cada vez, ALTER TABLE new_table_name ADD COLUMN...mas estou procurando uma maneira de fazer isso de forma mais sucinta, como:

CREATE TABLE new_table_name ( like old_table_name including all), new_col1 new_col1_type, new_col2 new_col2_type,...

Como todos sabemos, READ UNCOMMITTED é o nível de isolamento mais baixo no qual coisas como leituras sujas e leituras fantasmas podem ser acumuladas. Qual é o melhor momento para usar esse nível de isolamento e por quais motivos ele pode ser usado?

Na verdade eu li as respostas antes, mas não consegui entender completamente porque não havia exemplos suficientes.

De acordo com os documentos:

CONCORRENTEMENTE Atualize a visualização materializada sem bloquear seleções simultâneas na visualização materializada. (...)

... OUTROS CONTEÚDOS ...

Mesmo com esta opção, apenas um REFRESH de cada vez pode ser executado em qualquer visualização materializada .

Eu tinha uma função que verificava o tempo da última atualização para uma MATERIALIZED VIEW e, se tivessem passado mais de 60 segundos, seria para atualizá-la.

No entanto, o que aconteceria se eu tentasse atualizar uma visão materializada de dois processos separados ao mesmo tempo? eles enfileirariam ou gerariam um erro?

Existe uma maneira de detectar quando uma MATERIALIZED VIEW está sendo atualizada e, portanto, evitar tocá-la?

Atualmente, recorri a preencher um registro de tabela antes de atualizar (definir refreshingcomo true) e, em seguida, defini-lo para falsequando o processo for concluído.

EXECUTE 'INSERT INTO refresh_status (last_update, refreshing) 
         VALUES (clock_timestamp(), true) RETURNING id') INTO refresh_id;
EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY my_mat_view';
EXECUTE 'UPDATE refresh_status SET refreshing=false WHERE id=$1' USING refresh_id;

Então, sempre que chamo esse procedimento, verifico o mais recente last_updatee seu refreshingvalor. Se refreshingfor true, não tente atualizar a visualização materializada.

EXECUTE 'SELECT 
           extract(epoch FROM now() - (last_update))::integer, 
           refreshing
         FROM refresh_status
         ORDER BY last_update DESC
         LIMIT 1' INTO update_seconds_ago, refreshing;

IF(updated_seconds_ago > 60 AND refreshing = FALSE) THEN
  -- the refresh block above
END IF;

No entanto, não tenho certeza se o sinalizador de atualização está sendo atualizado de forma síncrona (quer dizer, ele realmente espera que a atualização seja realmente concluída)

Essa abordagem é racional ou estou perdendo alguma coisa aqui?

No arquivo de log de erros do SQL Server encontrei as seguintes linhas:

2018-02-22 14:10:58.95 spid17s     Starting up database 'msdb'.
2018-02-22 14:10:58.95 spid16s     Starting up database 'ReportServer'.
2018-02-22 14:10:58.95 spid18s     Starting up database 'ReportServerTempDB'.
2018-02-22 14:10:58.95 spid19s     Starting up database 'XYZ'.

Se eu verificar o status do banco de dados XYZ antes desse horário, ele está ONLINEusando a seguinte instrução:

SELECT state_desc FROM sys.databases WHERE name='XYZ'

...mas quando tento me conectar a esse banco de dados usando um aplicativo C#, ele não consegue se conectar ao banco de dados.

O erro é:

Falha no login para o usuário 'asd'.
Motivo: Falha ao abrir o banco de dados explicitamente especificado.

Eu tentei três usuários diferentes (usuário do Windows, sa, usuário do SQL Server definido para o aplicativo). O problema acontece quando executo o aplicativo na inicialização do SO, mas se eu iniciá-lo manualmente após a inicialização, nenhum erro acontece, então acho que todas as configurações do SQL Server e do firewall estão corretas.

Eu também verifiquei antes disso se o status do serviço está em execução.

O que mais devo verificar para garantir que o banco de dados esteja realmente online e pronto para consultas?

Estou procurando uma chave que me diga que não há problema em consultar o banco de dados, em vez de atrasar por um tempo (mesmo não com base em um motivo claro).

Pensei em verificar o log de erros para o texto "Iniciando o banco de dados 'XYZ'", mas isso significa que tenho que adicionar uma configuração para o aplicativo para o caminho do log de erros do SQL Server. Significa também ler o arquivo muitas vezes até encontrar essa frase.

Parece definir um grupo de arquivos para read_onlyimpedir dbcc checkdbtodo o banco de dados se o grupo de arquivos contiver um índice columnstore. Ao tentar executar checkdbou checkfilegroup( para qualquer grupo de arquivos no banco de dados, incluindo secundários de leitura/gravação e[PRIMARY] ), o erro abaixo é retornado...

Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts. 
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Existe um método com suporte para ter dados columnstore em um grupo de arquivos somente leitura? Ou estou impedido de realizar verificações de integridade neste cenário?

Reprodução

create database check_fg_ro
go
use check_fg_ro
go
exec sp_changedbowner 'sa';
go
alter database check_fg_ro add filegroup check_fg_ro_2;
alter database check_fg_ro
    add file (
         name='check_fg_ro_2'
        ,filename='C:\check_fg_ro_2.ndf'
    ) to filegroup check_fg_ro_2;
go
create table foo ( 
    i int not null primary key
) on check_fg_ro_2;
go
create columnstore index ccix_foo on foo(i);
go
use master
go
alter database check_fg_ro modify filegroup check_fg_ro_2 read_only;
go
dbcc checkdb( check_fg_ro ) with no_infomsgs, all_errormsgs, extended_logical_checks;
/*
Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts. 
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
*/
go

_{Isenção de responsabilidade: postado em fóruns da technet}