sp_detach_db

Esse é mais um artigo da série “Saga da otimização com comandos antigos”

• Parte 1: SET STATISTICS IO
• Parte 2: DBCC DROPCLEANBUFFERS
• Parte 3: DBCC SHOWCONTIG
• Parte 4: DBCC PAGE
• Parte 5: sp_spaceused
• Parte 6: DBCC IND
• Parte 7: DBCC INDEXDEFRAG
• Parte 8: DBCC DBREINDEX

No último artigo, comentei sobre as melhores práticas para escolha da chave do índice clustered. O objetivo é minimizar a fragmentação ao mesmo tempo que tentamos ter algum benefício com a ordenação.

Table Scan

Até agora falamos muito sobre as operação de Table Scan, seja ela usando Heap ou Index scan. Na realidade, vimos que o tempo é proporcional a quantidade de acesso ao disco. Por exemplo, se realizarmos 500 acessos ao disco com latência de 3ms, então teremos uma query demorando por volta de 1.500ms.

Falamos sobre o impacto das leituras read-ahead como otimização de disco. Isso significa que podemos tentar combinar 50 leituras de blocos de 8kb em uma única leitura de 400kb. A latência de disco geralmente varia pouco em relação ao tamanho do bloco.

Agora vamos mostrar o impacto de um disco muito lento sobre um table scan.

Storage

O primeiro passo será movimentar os dados para um disco supostamente mais rápido. Existem duas formas para fazer isso: backup/restore ou detach/attach. A forma mais fácil de copiar banco de dados entre máquinas é através do backup/restore, mas detach/attach é a forma mais simples para movimentar os arquivos para outro disco da mesma máquina.

Começaremos usando o comando sp_detach_db para remover a referência o banco de dados LOJADB sem apagar os arquivos.

Em muito casos, é recomendável configurar o banco de dados como SINGLE_USER. Se esse comando demorar muito, então é interessante forçar a desconexão dos demais usuários com WITH ROLLBACK IMMEDIATE.

ALTER DATABASE lojadb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Agora os arquivos do banco de dados podem ser copiados para outro disco.

Copiei os arquivos MDF e LDF para o disco F.

Em seguida, rodamos o comando de attach. Muitos podem pensar no sp_attach_db, que faz exatamente o que precisamos, mas prefiro usar o CREATE DATABASE. Eles fazem exatamente a mesma coisa.

Podemos fazer o teste e verificar o tempo total da query, que ficou em 395ms:

Esse tempo está melhor que o tempo original de 852ms. O tempo de latência desse novo storage foi de apenas 0,3ms!

Problema: Sobrecarga do Storage

Já passei por milhares de clientes e situações, mas a história é sempre igual: o novo storage tem uma performance excelente e resolverá todos os problemas. Essa é a frase que você ouve antes da venda. Depois da venda, o desempenho continua excelente e então conclui-se que a culpa era do storage antigo.

É aqui que nasce um novo problema: sobrecarga do storage.

Com o tempo, é comum que compartilhar os recursos de storage (disco, controladora, switch) entre os servidores da empresa. Não é difícil ver LUN’s alocadas dentro do mesmo Disk Array ou Hosts sobrecarregando os links de comunicação. Assim como é normal encontrar servidores VMWare armazenando imagens em controladoras compartilhadas com servidores de banco de dados. Essas são as situações de sobrecarga do storage.

Vou simular uma sobrecarga no storage fazendo duas coisas ao mesmo tempo:

• Executando a query no SQL Server
• Copiando arquivos entre discos do storage

Aqui está meu novo storage:

Iniciei a cópia de arquivo:

Iniciei a execução da query:

A query executou em 1812ms (4 vezes mais lento do que o baseline de 395ms).

Vamos supor que a tabela esteja fragmentada e, por isso, as operações de read-ahead estão desligadas:

Embora isso pareça uma trapaça, é bem provavel que isso esteja acontecendo no servidor de produção: forwarding records, extent fragmentation, page density, page splits. Então vamos simular qual seria o tempo em uma tabela fragmentada e com a cópia de dados ainda em progresso:

Certamente esse é nosso recorde: 58.987ms, ou seja, a query ficou rodando aproximadamente 1 minuto.

Podemos analisar o latência de disco para saber quem é o culpado: foram realizados 1257 I/O em um período de 58.987ms. Isso resulta em uma latência média de 47ms por leitura. Usando o checklist do windows, podemos classificar o tempo como no limite.

• <1ms : inacreditável
• <3ms : excelente
• <5ms : muito bom
• <10ms : dentro do esperado
• <20ms : razoável
• <50ms : limite
• >100ms : ruim
• > 1 seg : contenção severa de disco
• > 15 seg : problemas graves com o storage

Por que houve uma degradação tão grande? Porque saímos de uma latência de 0,3ms (inacreditável) para 47ms (limite). O problema não é exatamente o storage, mas a quantidade de I/O necessário para responder a query.

Conclusão

Nesse artigo chegamos ao limite da query:

SELECT * FROM produtos WHERE id = 1234

Ela pode realizar 1257 acessos ao disco para retornar apenas 1 registro. Existem várias formas de diminuir o impacto desses 1257 I/O:

• Agregar as operações de I/O usando o Read-ahead e blocos grandes
• Manter os dados em cache (memória)
• Atualizar o storage para uma versão mais moderna com maior capacidade de I/O

Essa é a análise de infraestrutura. E qual seria a análise de desenvolvimento? Será que é possível reduzir o número de I/O dessa query? A resposta é sim! Usando um método chamado SEEK ao invés de SCAN.

No próximo artigo vamos introduzir o conceito do Seek.