Thomas's questions

Temos 8 processos, cada um processando um conjunto separado de dados de uma tabela via cursor, para que várias CPUs possam ser usadas para iterar pelos dados. Isso é usado para migração de dados, portanto, neste ponto, a máquina do servidor não está sendo usada para mais nada.

Ocasionalmente, esse processo trava com o SQL voltando para efetivamente 0 uso da CPU. Cada um desses processos fica preso no estado WAITFOR, subindo até 30 segundos e voltando para 0 novamente. Não temos o comando WAITFOR em nosso SQL ou no lado do código, e o SQL não me diz o que está esperando no Activity Monitor ou nos DMVs. Parece acontecer mais em máquinas com recursos de menor especificação, mas sinto que o SQL deve eventualmente parar de esperar pela CPU quando o uso da CPU diminuir.

Estou faltando alguma coisa aqui, existe alguma maneira de desbloquear esses processos?

Estou migrando alguns dados armazenados no estilo "valor-chave", de uma tabela de atributos que usa um ID de objeto e um tipo de atributo como um índice clusterizado (também tentei como um índice não clusterizado):

CREATE TABLE [dbo].[#attrs](
    [DataMigrationEventObjectID] [int] NOT NULL,
    [AttributeType] [varchar](128) NOT NULL,
    [AttributeValue] [varchar](255) NULL
) 
CREATE CLUSTERED INDEX pk ON #attrs ([DataMigrationEventObjectID],AttributeType);

Adicionei o valor do atributo para selecionar valores porque a tabela de atributos no banco de dados tem muitos outros dados e posso selecioná-los apenas para este evento de migração. A consulta com meu conjunto de dados de teste para preencher esta tabela insere ~ 3k linhas e é executada em menos de um segundo (existem cerca de 50 objetos no total em meu conjunto de dados com vários atributos cada).

As junções à tabela na consulta têm esta aparência, juntando-se no índice clusterizado:

        INNER JOIN #attrs obj_gvn
        ON obj_gvn.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
        AND obj_gvn.AttributeType = 'GivenName'

Com 14 junções a esta tabela temporária, a consulta é concluída em alguns segundos. Com 15 junções, a consulta leva um minuto e, com 16+, ainda está sendo executada meia hora depois.

Verifiquei todas as junções em busca de condições acidentais que levariam ao retorno de muitas linhas e, quando ele retorna em 1 minuto, retorna apenas as linhas certas, então não acho que haja uma junção cartesiana acidental. Definir um valor MAXDOP não o afeta e o plano de consulta retornado quando a consulta é executada por um minuto não sinaliza nenhum problema.

O que eu perdi no SQL que faz com que ele se comporte dessa maneira com muitas junções em um índice clusterizado que em teoria deveria ser rápido, com um número tão pequeno de registros?

Não consigo obter um plano de execução real porque a consulta não é concluída e, como usa uma tabela temporária, não consigo obter um plano estimado para ela. Tentei falsificar a tabela temporária como uma tabela real no banco de dados e gerar um plano estimado e o plano ainda não foi gerado após 2 minutos, então parece que o atraso está no lado "criando um plano"

Cole o Plano de uma versão abreviada da consulta: brentozar.com/pastetheplan/?id=Hy76dd92i

Atualizei as estatísticas do banco de dados para garantir e ainda não está gerando um plano.

Eu trabalhei com consultas no passado com junções muito mais e muito mais problemáticas, onde a compilação do plano ainda é instantânea. Eu sinto que o fato de estar falhando na etapa "gerar plano" deve significar algo.

Atualizar para o CU mais recente infelizmente não ajudou. sp_whoisactiveapenas mostra o uso da CPU subindo e subindo ( captura de tela ), nada em outros recursos que pareça problemático.

É minha máquina de desenvolvimento, portanto, há apenas um processo ativo no SQL, que é a consulta que estou executando. Não há mais nada, então presumo que seja o SQL tentando gerar o plano.

Suspeito que, se eu implantasse isso, a produção funcionaria bem, mas é muito estranho ter um problema de 'muitas junções em um desempenho de eliminação de chave primária'. Posso pensar em acabar com o servidor de desenvolvimento e começar do zero.

Para tirar isso do caminho, neste cenário, estamos reparando um banco de dados numérico onde nenhum backup foi feito antes da corrupção, portanto, restaurar um backup não é uma opção. Não é meu banco de dados :)

Ao executar o DBCC CHECKDB com REPAIR_ALLOW_DATA_LOSS, obtemos milhares de erros parecidos com este:

Msg 8928, Level 16, State 1, Line 7
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24911) could not be processed.  See other errors for details.
        Repairing this error requires other errors to be corrected first.
Msg 8965, Level 16, State 1, Line 7
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data). The off-row data node at page (1:24911), slot 0, text ID 265289728 is referenced by page (1:24820), slot 0, but was not seen in the scan.
        Repairing this error requires other errors to be corrected first.
Msg 8928, Level 16, State 1, Line 7
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24912) could not be processed.  See other errors for details.
        Repairing this error requires other errors to be corrected first.
Msg 8965, Level 16, State 1, Line 7
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data). The off-row data node at page (1:24912), slot 0, text ID 265289728 is referenced by page (1:24820), slot 0, but was not seen in the scan.
        Repairing this error requires other errors to be corrected first.
Msg 8928, Level 16, State 1, Line 7
Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24913) could not be processed.  See other errors for details.
        Repairing this error requires other errors to be corrected first.

Executá-lo repetidamente não reduz os erros, portanto, parece que o DBCC não pode reparar isso.

Meu próximo pensamento foi tentar identificar e excluir as linhas problemáticas na tabela. Quando tentei excluir uma linha problemática conhecida, ela também errou, então meu pensamento atual é extrair linhas válidas conhecidas em uma nova tabela com o mesmo esquema da antiga, descartar a tabela antiga e renomear a nova para corresponder ao antigo.

O problema com isso é que o SQL Server, em vez de fornecer um erro detectável, simplesmente descarta a conexão sempre que uma linha com problema é encontrada, então não consigo encontrar uma maneira programática de identificar as linhas 'boas'.

Existe alguma maneira no T-SQL de forçá-lo a fornecer um bom erro capturável para que eu possa percorrer a tabela e extrair as linhas boas ou algum modo 'avançado' de DBCC CHECKDB que possa repará-lo que não é t óbvio em qualquer lugar na web?