P&R do SQLRecriando índices, comprimento da fila de disco e muito mais

Editado por Nancy Michell

P Como são recriados os índices do SQL Server? Preciso saber como o DBCC DBREINDEX consome todo o meu espaço em disco e por que ele não é liberado quando o comando falha. O tamanho do meu banco de dados é de 90 GB e a maior tabela é de 70 GB.

Quando executo o DBCC DBREINDEX, deixando 10% de espaço livre, ele consome todo o espaço disponível em disco e o comando falha. Embora inicialmente o arquivo do banco de dados seja de 90 GB, quando o comando do DBCC falha, o arquivo do banco de dados cresce para 160 GB e os 70 GB adicionais nunca são liberados; eu preciso reduzir manualmente o banco de dados várias vezes para recuperá-lo.

Quando o comando funciona, o tamanho do arquivo do banco de dados também é de 160 GB, mas o espaço é automaticamente liberado poucas horas depois da redução automática. Seria aceitável executar uma desfragmentação em vez de DBREINDEX para consumir menos espaço? Não tenho índices agrupados e estou usando o modo de recuperação simples.

R No nível mais simples, os índices são recriados construindo-se uma nova cópia do índice e, em seguida, excluindo a antiga. Isso significa que, essencialmente, há duas cópias do índice por um breve período de tempo. A criação do novo índice exigiria tanto espaço do arquivo de banco de dados quanto o índice original e, se a recriação exigisse uma operação de classificação, um adicional de 20% do tamanho do índice seria necessário para a classificação.

Portanto, o pior caso seria que a recriação de um índice exigiria 1,2 a mais de espaço que o índice antigo. Se o arquivo do banco de dados não tiver espaço livre suficiente, o arquivo precisará crescer para que a operação continue. É possível que, se o crescimento automático não estiver habilitado ou não houver espaço suficiente no volume do disco, não haja espaço livre suficiente disponível e a operação de recriação falhará.

Independentemente de a operação falhar ou não, o espaço adicional alocado para o arquivo do banco de dados não é liberado depois da conclusão da operação de recriação. A suposição é de que o espaço será utilizado para operações regulares de banco de dados.

A execução da redução (manual ou automaticamente) é quase garantida para criar a fragmentação de índice devido ao modo como o algoritmo funciona. Para obter mais detalhes, consulte Mecanismo de armazenamento do SQL Server. A redução automática pode ser especialmente prejudicial se o banco de dados precisar liberar espaço para operações regulares, pois você pode obter um ciclo de crescimento automático-redução automática-crescimento automático-redução automática, o qual pode causar estragos na fragmentação e no desempenho.

O uso de DBCC INDEXDEFRAG (ou ALTER INDEX ... REORGANIZE no SQL Server™ 2005) apresenta a vantagem de praticamente não utilizar espaço adicional de arquivo do banco de dados, mas pode demorar mais e gerar muito mais registro em log da transação do que uma recriação de índice. O DBCC INDEXDEFRAG é sempre registrado em log por completo, independentemente do modo de recuperação em uso, enquanto que no modo de recuperação simples uma recriação de índice será sempre registrado em log em massa. Há uma variedade de prós e contras para cada método e eles são explicados mais detalhadamente no whitepaper sobre práticas recomendados de desfragmentação de índice do SQL Server.

Antes de tomar a decisão de como corrigir a fragmentação, primeiramente decida se deseja realmente corrigi-la. Dependendo do tipo de operações para as quais o índice for utilizado, a fragmentação pode não ter efeito no desempenho e, portanto, corrigi-la será uma perda de recursos. O whitepaper apresenta isso em detalhes.

Resumindo: certifique-se de que você escolha o método mais apropriado de remoção de fragmentação para o seu ambiente e que essa remoção esteja auxiliando no desempenho de consulta.

P Configurei o espelhamento do banco de dados com êxito entre as duas instâncias do SQL Server 2005. Meu aplicativo está conectando-se ao SQL Server usando um login do SQL Server e foi criado usando ADO e cliente nativo SDL. Minhas configurações de conexão e da cadeia de caracteres da conexão especificam as informações corretas, inclusive o parceiro apropriado de failover. Além disso, criei todos os mesmos login no servidor de espelhamento, como eles existem no servidor principal. Mediante o teste de uma falha do banco de dados, o espelhamento supõe com êxito que a função principal e tudo o mais estão corretos na instância do SQL Server. (Eu posso até mesmo me conectar ao espelhamento usando o meu login do Windows®.) No entanto, a reconexão do aplicativo falha com o seguinte erro:

Cannot open database "<db name>" requested by the login. The login failed. 

Parece que o login não está associado ao usuário no novo banco de dados principal (originalmente o espelhamento). Executo sp_change_users_login para sincronizar os usuários e logins do banco de dados e obtenha uma mensagem afirmando que corrigi vários usuários órfão. Então, o meu aplicativo se reconecta com êxito ao novo servidor principal. Tentei vários failovers e sempre vejo o mesmo comportamento – a associação entre o login e o usuário é perdida.

Há uma forma de configurar a instalação do espelhamento para que isso não seja um problema?

R Sim. O problema é devido ao fato de que os SIDs (Security Identifiers, identificadores de segurança) do SQL Server que efetuam login em cada servidor não correspondem, mesmo que os nomes de login sejam os mesmos. Isso não é um problema com os logins Windows/usuário do domínio/grupo, pois os SIDs para esses logins são criados com base no SID de domínio para usuário/grupo e, portanto, serão os mesmos para determinado usuário/grupo não importando a qual SQL Server o usuário/grupo foi adicionado.

Para fazer com que a etapa de sincronização sp_change_users_login não seja necessária, você precisará criar os logins do SQL Server no servidor de espelhamento, não somente com o mesmo nome, mas também com o mesmo SID como no servidor principal. Você fará isso usando a especificação de SID na instrução CREATE LOGIN ao criar os logins no servidor de espelhamento, como a seguir:

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

Você pode recuperar o SID para cada login do servidor principal ao consultar a exibição de catálogo sys.sql_logins. Um exemplo de uma consulta que irá gerar uma instrução CREATE LOGIN real para cada login SQL Server/Windows em determinado servidor é mostrada na Figura 1.

Figure 1 Geração da instrução CREATE LOGIN

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

P Qual deve ser o meu comprimento médio da fila do disco? Por exemplo, se eu tenho 20 eixos físicos separados da SAN (Storage Area Network, rede da área de armazenamento) com configuração RAID 01, como faço para calcular o comprimento médio da fila do disco? Seria o comprimento médio da fila do disco/20 ou o comprimento médio da fila do disco/2?

R Primeiramente, você deve verificar a latência de disco antes de perder tempo com o comprimento médio da fila do disco em um ambiente SAN. Mas isso realmente depende do que você está tentando descobrir. Veremos o porquê em um momento.

A explicação desse contador (do Perfmon) é "O comprimento médio da fila do disco é o número médio de solicitações de leitura e gravação que são colocadas em fila para o disco selecionado durante o intervalo de amostra”. Será um disco físico ou contador de disco lógico, de forma que o número obtido dependerá de como o armazenamento subjacente é apresentado ao sistema operacional.

Vejamos o seu caso agora: você tem 20 eixos em uma configuração RAID 01, o que significa que você distribuiu e colocou em espelhamento (ou colocou em espelhamento e distribuiu, dependendo de como você lê 01 ou 10). O ponto essencial sobre a sua matriz de armazenamento é que há 10 eixos no conjunto de distribuição.

Mas está faltando a informação essencial, como qual o tamanho da distribuição, o quão grandes são as suas gravações e que tipo de E/S você está emitindo (leitura, gravação, seqüencial ou aleatória).

Ignore essas informações faltando por enquanto, se o comprimento médio da fila do disco for 10, então o sistema operacional colocará em fila 10 E/Ss para a matriz do disco. Teoricamente, isso poderia ser uma E/S para cada um dos 10 conjuntos espelhados na distribuição ou poderia ser 10 E/Ss todas em um disco. Você não tem como saber isso.

É aí que a informação faltando entraria. Vamos supor que o tamanho da sua distribuição seja de 64 KB, o tamanho da sua gravação seja de 8 KB e você fará um bloco completo de gravações seqüenciais. Esse é um cenário típico de atividade de armazenamento do SQL Server. Nesse caso, há uma boa chance de que oito dos E/Ss sejam encaminhados todos para o primeiro disco e os dois restantes sejam encaminhados para o próximo disco. Portanto, se você estiver tentando imaginar o comprimento da fila do disco por disco para esse cenário, seria 8 para o primeiro disco, 2 para o segundo disco e 0 para os oito discos restantes na matriz.

Agora, vamos mudar o cenário para um tamanho de distribuição teórico de 8 KB e um tamanho de bloco de gravação de 64 KB – e manter o comprimento da fila do disco em 10. Nesse caso, cada bloco de 64 KB é distribuído em 8 discos, de forma que um E/S é gravado em 8 discos e 10 E/Sss em fila são distribuídos em 80 gravações de disco em todos os 10 discos na matriz. Se você estiver tentando calcular o comprimento da fila do disco na matriz, será de 8 para cada disco na matriz.

Sejamos realistas e vamos acrescentar outro nível de incerteza nesse cenário. Na maioria dos casos, o seu armazenamento SAN estará conectado ao servidor usando uma ou mais HBAs no servidor, um número de fibras para conectar a HBA à SAN, um número de portas na extremidade dianteira da SAN e talvez algum tipo de comutador de fibra na malha entre o servidor e a SAN. Então, acessaremos a estrutura interna dos barramentos na SAN e veremos como os discos estão conectados às portas na frente da SAN.

Qualquer fila que você estiver vendo relatada no Perfmon pode ser um sintoma de alta latência ou fila em qualquer um desses pontos entre onde o sistema operacional avalia o comprimento da fila do disco e a superfície dos discos. É por isso que você deve verificar a latência e basear quaisquer decisões nesse contador em vez de no comprimento médio da fila do disco.

P Estou usando a replicação transacional e sei que muitas filas estão sendo alteradas manualmente em uma tabela no assinante, de forma que estou obtendo erros quando o editor está tentando atualizar uma fila que não existe mais no assinante.

Eu preciso saber se há alguma forma por meio da replicação para apenas reinicializar essa tabela do editor, em vez de reaplicar todo o instantâneo novamente. Verifiquei a função tablediff, que parece ser o que eu preciso, mas não sei se ela interage com a replicação.

Por exemplo, a tablediff obtém um instantâneo da tabela do editor e a compara com o equivalente da assinatura? Preciso interromper a replicação para usar o utilitário tablediff para garantir a consistência dos dados? O mais preciso saber?

R Primeiramente, a tablediff não obtém um instantâneo literal da tabela de publicação ou assinatura. Em relação ao seu cenário específico, há algumas opções que você pode analisar.

A primeira seria interromper temporariamente a replicação e executar o utilitário. Se você estiver preocupado com o fato de os usuários tentarem efetuar modificações nos dados, você pode usar os parâmetros -sourcelocked e –destinationlocked, os quais aplicam um bloqueio exclusivo nas tabelas quando o utilitário estiver em execução. Se isso for inaceitável, outra opção seria verificar os parâmetros -rc e -ri enquanto a execução de replicação é mantida. Isso executaria a tablediff uma só vez e, depois, ela seria executada consecutivamente nos erros que detectar, eliminando os erros que poderiam ser decorrentes dos atrasos na propagação da replicação. No entanto, esteja atento, pois com essa opção, dependendo do seu atraso de replicação, você pode não necessariamente obter todas as linhas alteradas pelo assinante.

Meus agradecimentos aos seguintes profissionais de TI da Microsoft por seus conhecimentos técnicos: Sunil Agarwal, Chad Boyd, David Browne, Gilles Comeau, Emmanuel Dreux, Amanda Foote, Matt Hollingsworth, Paul Mestemaker, Uttam Parui, Paul Randal, Dennis Tighe e Steven Wort.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..