John Russell's questions

Declaração do problema: Nossos engenheiros de suporte precisam obter backups de banco de dados de clientes. Atualmente, nosso banco de dados está contido em um arquivo/grupo de arquivos Primário/.mdf. Esses bancos de dados coletam dados históricos há vários anos e estão crescendo. Nossos engenheiros levam dia(s) para copiar os backups para nosso escritório.

Parece não ter havido provisões/pensamento para gerenciar o crescimento do banco de dados.

Os dados mais "valiosos" são "a configuração" do nosso sistema. Ele está contido em cerca de 50 tabelas que estamos considerando mover para um novo grupo de arquivos de configuração separado dos dados históricos (que seriam colocados no grupo de arquivos de dados) em uma tentativa de preparar o cenário para backups parciais ou de arquivo.

Mas não tenho certeza de qual estratégia de backup/restauração preciso para fazer isso - Backups parciais ou backups de arquivos ?

Para fins de P&D, tenho um banco de dados chamado FilegroupDemoque usa o SIMPLEmodelo de recuperação. FilegroupDemocontém 3 grupos de arquivos:

• Primário (mapeia para FilegroupDemo.mdf)
• Configuração (mapeia para FilegroupDemo_Configuration.ndf)
• Dados (mapeia para FilegroupDemo_Data.ndf)

Os dados no grupo de arquivos de configuração não são alterados com frequência e podem ser marcados como somente leitura (se isso ajudar), enquanto os dados no grupo de arquivos de dados são alterados a cada minuto.

Eu quero a flexibilidade para fazer backup/restaurar apenas os grupos de arquivos/arquivos primários e de configuração.

No artigo BOL para backups parciais do SQL Server , ele afirma:

Um backup parcial é semelhante a um backup de banco de dados completo, mas um backup parcial não contém todos os grupos de arquivos. Em vez disso, para um banco de dados de leitura/gravação, um backup parcial contém os dados no grupo de arquivos primário, cada grupo de arquivos de leitura/gravação e, opcionalmente, um ou mais arquivos somente leitura.

A maneira como isso é lido me faz pensar que os backups parciais são projetados para omitir grupos de arquivos somente leitura, provavelmente para grandes volumes de dados que não serão alterados e marcados como somente leitura. Estes não precisam ser copiados todas as vezes.

1. Estou correto ao dizer que existem backups parciais para fazer backup de todos os grupos de arquivos, EXCETO os marcados como somente leitura? Em outras palavras, se eu não estiver usando grupos de arquivos somente leitura, não há sentido em usar backups parciais - correto? E não acho que posso usar um backup parcial para fazer backup apenas de um grupo de arquivos somente leitura?

Portanto, não acho que backups parciais sejam o que eu preciso. Ingenuamente, tentei um backup/restauração de arquivo . Mudei meu banco de dados para FULLo modelo de recuperação e executei:

BACKUP DATABASE FilegroupDemo FILEGROUP = N'PRIMARY'
TO DISK = N'C:\Backups\FilegroupDemo_FG_Primary.bak'
WITH INIT
GO

BACKUP DATABASE FilegroupDemo FILEGROUP = N'Configuration'
TO DISK = N'C:\Backups\FilegroupDemo_FG_Configuration.bak'
WITH INIT
GO

BACKUP DATABASE FilegroupDemo FILEGROUP = N'Data'
TO DISK = N'C:\Backups\FilegroupDemo_FG_Data.bak'
WITH INIT
GO

<delete the database>

RESTORE DATABASE FilegroupDemo FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backups\FilegroupDemo_FG_Primary.bak'
WITH PARTIAL, NORECOVERY, REPLACE
GO

RESTORE DATABASE FilegroupDemo
FROM DISK = N'C:\Backups\FilegroupDemo_FG_Configuration.bak'
WITH NORECOVERY, REPLACE
GO

RESTORE LOG FilegroupDemo
FROM DISK = 'C:\Backups\FilegroupDemo_Log.trn'
WITH RECOVERY
GO

Para minha surpresa, isso realmente parecia funcionar. Posso consultar as tabelas em meu grupo de arquivos de configuração, enquanto quando consulto as tabelas em meu grupo de arquivos de dados, obtenho:

The query processor is unable to produce a plan for the table or view 'xxx' because the table resides in a filegroup that is not online.

2. Acho que estou surpreso por ter funcionado porque pensei que fosse uma "restauração ONLINE", que era um recurso Enterprise. Em outras palavras, meu banco de dados é funcional enquanto os grupos de arquivos auxiliares permanecem offline - não?

Definitivamente, estou usando a edição padrão:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)   Jun 17 2016 19:14:09   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) 

3. Uma restauração de arquivo requer um FULLmodelo de recuperação? Se eu não puder usar o modelo de recuperação SIMPLE para executar esse tipo de backup de arquivo e precisar mudar para COMPLETO, posso fazer isso temporariamente - apenas para obter uma cópia de backup - e depois voltar para SIMPLE? Há algum mal nisso? Além disso, alguma ideia de por que você deve usar FULL - apenas curioso?

Tenho um cliente executando o SQL Server 2012 Express Edition:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 10 de fevereiro de 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1)

O servidor é de 64 bits, executando o Windows 7 e possui 8 GB de RAM. O arquivo de paginação está configurado para ser gerenciado pelo sistema operacional.

No outro dia, eles começaram a notar entradas de "condição de memória virtual baixa" no Windows Event Viewer, como as seguintes:

O Windows diagnosticou com êxito uma condição de memória virtual baixa. Os seguintes programas consumiram mais memória virtual: sqlservr.exe (2792) consumiu 28103749632 bytes, svchost.exe (592) consumiu 617349120 bytes e w3wp.exe (7700) consumiu 609824768 bytes.

Eu loguei e dei uma olhada no Windows Resource Monitor e notei que o contador Commit (KB) para o processo sqlservr.exe estava "alto" enquanto o conjunto de trabalho estava relativamente "baixo":

Process Explorer confirmado:

Com base na documentação do Commit (KB):

Esta é a quantidade total de memória física e virtual (arquivo de página) que está confirmada para este processo específico.

Também monitorei o contador de MBytes disponíveis do Perfmon e ele lê consistentemente uma média em torno de 4,624 GB.

select * from sys.dm_os_process_memory retornou o seguinte:

Por que o sqlservr.exe estaria usando tanta memória "virtual" quando ainda há muita memória "física" disponível? Além disso, pensei que o SQL Server 2012 Express estava limitado a 1 GB de memória - isso é apenas um limite de memória física?

Por outro lado, vários links da Web mencionam que o McAfee Virus Scan Enterprise/McAfee Host Intrusion Prevention pode causar esse tipo de comportamento. Ambos foram instalados neste servidor por seu grupo de TI. Tentei adicionar as exclusões apropriadas, mas nunca vi o Commit (KB) cair. Como posso provar que é um problema da McAfee?

ATUALIZAÇÃO: Encontrei o que parece ser um problema semelhante com o McAfee Host Intrusion Prevention e o SQL Server na mesma caixa.

Pediram-me para examinar um servidor de banco de dados/aplicativo que está "desempenhando lentamente". Ninguém poderia realmente me dizer o que estava lento (exceto para os backups do banco de dados), então este foi um processo de descoberta.

Este servidor está em Dell PowerEdge R720execução Windows Server 2008 R2e SQL Server 2012 Standard (11.0.2100.60) (x64).

-CPU Intel(R) Xeon(R) E5-2640 0 @ 2,50 GHz, 2500 Mhz, 6 Núcleos, 12 Processadores Lógicos

-16 GB RAM

-Dispositivo de disco DELL PERC S110 SCSI em execução como um software RAID 5.

O disco é particionado em duas unidades locais: C e D. Os arquivos do sistema operacional estão todos em C. O SQL Server está instalado em D e os arquivos de banco de dados (.mdf e .ldf) também estão em D. Além disso, estamos gravando nossos backups noturnos na unidade D! :-)

O arquivo mdf do banco de dados tem cerca de 6 GB. Os backups estão demorando 30 minutos.

Uma rápida olhada nos tipos de espera revelou que o WRITELOGtipo de espera era o mais prevalente - consumindo 69%:

Suspeitando de problemas de desempenho de log/disco de transações, habilitei vários contadores PerfMon e estabeleci médias de linha de base em alguns dias. Os contadores PerfMon incluem, mas não estão limitados a:

\PhysicalDisk(0 C: D:)\Avg. Disk sec/Write - Avg = 0.073
\SQLServer:Databases\Log Flush Wait Time - Avg = 71.403
\SQLServer:Databases\Log Flushes/sec - Avg = 1.910
\SQLServer:Databases\Log Bytes Flushed/sec - Avg = 1724.604

O log de transações foi originalmente configurado com um tamanho inicial de 100MBe definido para crescer em 10%. Quando entrei na máquina pela primeira vez, o log de transações havia aumentado, 5GBsugerindo 256 VLFsalguma fragmentação.

A consulta de Paul Randall sys.dm_io_virtual_file_statsrevela uma séria latência de gravação com os arquivos de dados e de log - embora, ao contrário do tipo de espera WRITELOG, a latência de gravação do arquivo de dados seja um pouco maior:

Data File: 1369
Log File: 66

Com base no artigo de Kimberly Tripp, "reconstruí" o log de transações e atribuí a ele um tamanho de 2 GB (um tanto arbitrariamente), gerando 24 VLFs.

Isso pareceu diminuir a WRITELOGporcentagem do tipo de espera (agora em ~61%), bem como \SQLServer:Databases\Log Flush Wait Time(agora em ~48ms).

Ônibus isso ainda parece alto.

Percebo que os arquivos de dados e log (e backups) devem estar todos em unidades diferentes para leituras/gravações paralelas e taxa de transferência máxima. Deixando de lado as melhores práticas, temos outros sistemas de produção configurados da mesma forma que este, mas que estão rodando melhor, por exemplo, Avg Log Flush Wait Time = < 3 ms.

O que mais eu poderia examinar para descobrir por que esse servidor em particular é mais lento em relação ao tempo de resposta de leitura/gravação do disco e backups?

As configurações de fragmentação de índice/fator de preenchimento podem causar esse baixo desempenho? E a fragmentação do disco?

Se eu precisar dizer ao cliente que precisamos de outro disco, que assim seja - só quero ter certeza antes de gastar dinheiro.

Alguma outra ideia???

Estou tentando entender melhor (conceitualmente) a relação entre estatísticas, planos de execução, execução de stored procedures.

Estou correto ao dizer que as estatísticas são usadas apenas ao criar o plano de execução para um procedimento armazenado e não são usadas no contexto de execução real? Em outras palavras, se isso for verdade, uma vez que o plano é criado (e supondo que seja devidamente reutilizado), qual a importância das estatísticas "atualizadas"?

Fiquei particularmente motivado por um artigo que li ( Estatísticas, estimativas de linha e coluna de data ascendente ) que descreve um cenário muito semelhante a um que enfrento diariamente com vários bancos de dados de nossos clientes.

Temos uma coluna de data/hora ascendente em uma de nossas maiores tabelas que consultamos regularmente usando um procedimento armazenado específico.

Como você evita que os planos de execução fiquem obsoletos quando você tem cem mil linhas sendo adicionadas por dia?

Se estivermos atualizando as estatísticas com frequência para combater esse problema, faria sentido usar a dica OPTION (RECOMPILE) na consulta desse procedimento armazenado?

Qualquer conselho ou recomendação seria apreciado.

Atualização : estou usando o SQL Server 2012 (SP1).

Tenho observado várias métricas de PerfMon em uma das instâncias do SQL Server de nosso cliente, tentando obter uma boa medida de onde nosso banco de dados (ou servidor) poderia usar melhorias.

Uma medida que me deixou intrigado é a proporção de duas métricas de PerfMon: Compilações/seg e Solicitações de lote/seg.

De acordo com esta postagem , " é uma regra geral que as compilações/s devem ser de 10% ou menos do que o total de solicitações de lote/s ".

Nosso aplicativo tem um serviço do Windows que invoca cálculos agendados no banco de dados a cada hora. Como um relógio, em meus dados PerfMon CSV, posso ver os picos em Compilações/s e Solicitações em Lote/s. O que eu não esperava ver era que o número de compilações/seg excederia as solicitações de lote/seg.

Estou analisando amostras de 15 segundos do PerfMon centradas no momento em que nossos cálculos começam:

O que isso normalmente indica? Isso faz sentido? Por que estaríamos compilando mais instruções do que executando? Estou esquecendo de algo?

Recebi uma ligação ontem de um cliente reclamando do alto uso da CPU em seu SQL Server. Estamos usando o SQL Server 2012 SE de 64 bits. O servidor está executando o Windows Server 2008 R2 Standard, Intel Xeon de 2,20 GHz (4 núcleos), 16 GB de RAM.

Depois de ter certeza de que o culpado era de fato o SQL Server, dei uma olhada nas principais esperas da instância usando a consulta DMV aqui . As duas primeiras esperas foram: (1) PREEMPTIVE_OS_DELETESECURITYCONTEXTe (2) SOS_SCHEDULER_YIELD.

EDIT : Aqui está o resultado da "consulta de espera superior" (embora alguém tenha reiniciado o servidor esta manhã contra minha vontade):

Fazemos muitos cálculos/conversões intensos, para que eu possa entender SOS_SCHEDULER_YIELD. No entanto, estou muito curioso sobre o PREEMPTIVE_OS_DELETESECURITYCONTEXTtipo de espera e por que pode ser o mais alto.

A melhor descrição/discussão que posso encontrar sobre esse tipo de espera pode ser encontrada aqui . Ele menciona:

Os tipos de espera PREEMPTIVE_OS_ são chamadas que deixaram o mecanismo de banco de dados, geralmente para uma API Win32, e executam código fora do SQL Server para várias tarefas. Neste caso, está excluindo um contexto de segurança usado anteriormente para acesso remoto a recursos. A API relacionada é, na verdade, chamada DeleteSecurityContext()

Que eu saiba, não temos nenhum recurso externo, como servidores vinculados ou tabelas de arquivos. E não fazemos nenhuma representação, etc. Um backup pode ter causado esse pico ou talvez um controlador de domínio com defeito?

O que diabos poderia fazer com que esse seja o tipo de espera dominante? Como posso rastrear esse tipo de espera ainda mais?

Editar 2: verifiquei o conteúdo do log de segurança do Windows. Vejo algumas entradas que podem ser interessantes, mas não tenho certeza se são normais:

Special privileges assigned to new logon.

Subject:
    Security ID:        NT SERVICE\MSSQLServerOLAPService
    Account Name:       MSSQLServerOLAPService
    Account Domain:     NT Service
    Logon ID:       0x3143c

Privileges:     SeImpersonatePrivilege

Special privileges assigned to new logon.

Subject:
    Security ID:        NT SERVICE\MSSQLSERVER
    Account Name:       MSSQLSERVER
    Account Domain:     NT Service
    Logon ID:       0x2f872

Privileges:     SeAssignPrimaryTokenPrivilege
            SeImpersonatePrivilege

Editar 3 : @Jon Seigel, como você solicitou, aqui estão os resultados de sua consulta. Um pouco diferente do de Paulo:

Edit 4: Admito que sou um usuário de eventos estendidos pela primeira vez. Adicionei esse tipo de espera ao evento wait_info_external e vi centenas de entradas. Não há texto sql ou identificador de plano, apenas uma pilha de chamadas. Como posso rastrear ainda mais a fonte?

Tenho lido sobre o cache do plano do SQL Server e otimização para cargas de trabalho ad hoc, etc. Sou relativamente novo no assunto e tenho algumas perguntas gerais para ajudar a criar uma imagem melhor:

Suponha que eu tenha vários bancos de dados em execução em uma instância do SQL Server:

(1) Existe um cache de plano diferente por banco de dados? Mais especificamente, quando examino, sys.dm_exec_cached_plansestou olhando para informações específicas do banco de dados ou informações de toda a instância?

(2) É 'optimize for adhoc workloads'definido por banco de dados ou por instância?

Eu estava pesquisando as diferenças entre o SQL Server READ COMMITTED SNAPSHOTe os SNAPSHOTníveis de isolamento e me deparei com o seguinte recurso:

Escolhendo níveis de isolamento baseados em controle de versão de linha

Para a maioria dos aplicativos, o isolamento de confirmação de leitura usando controle de versão de linha é recomendado em vez do isolamento de instantâneo pelos seguintes motivos:

• Ele consome menos espaço tempdb do que o isolamento de instantâneo.

• O isolamento de instantâneo é vulnerável a conflitos de atualização que não são aplicáveis ​​ao isolamento confirmado de leitura usando controle de versão de linha. Quando uma transação executada sob isolamento de instantâneo lê dados que são modificados por outra transação, uma atualização da transação de instantâneo para os mesmos dados causa um conflito de atualização e a transação é encerrada e revertida. Isso não é um problema com isolamento de confirmação de leitura usando controle de versão de linha.

Sou um pouco novo nesses tópicos, mas não consigo entender os dois pontos do link acima.

1. Por que o espaço tempdb seria diferente para esses modos? Um armazena versões mais granulares do que o outro?

2. Por que o isolamento de instantâneo é mais vulnerável a conflitos de atualização?