Chris Woods's questions

Nossos desenvolvedores (antes de eu ser DBA) definem o tipo de dados para um campo VARCHAR(MAX)quando, na realidade, ele só precisa serVARCHAR(255). Isso só foi descoberto depois que nossa tabela cresceu para mais de vários milhões de registros e quase um terabyte de dados. Quando vou para o modo de design nessa tabela para testar essa alteração em nosso ambiente de teste, descubro que o script criado faz uma cópia da tabela _tmp da tabela e, em seguida, descarta o original e renomeia _tmp para o original. Parece que me lembro de ter aprendido que o motivo por trás disso tinha algo a ver com a necessidade de mover os dados da seção "LOB" de um registro para a seção de comprimento fixo de um registro (pelo menos é o que minha memória recorda). No entanto, não consigo encontrar nenhuma documentação sobre por que exatamente isso ocorre (em vez de apenas alterar o tamanho no local). Alguém pode me indicar a direção certa para que eu possa explicar melhor ao gerenciamento por que fazer essa alteração agora em uma tabela de vários milhões de registros levará muito tempo.

Eu tenho uma consulta com vários joins que não está funcionando bem. Depois de muita triagem, resolvi desmontar a consulta e olhar apenas a tabela de condução. Abaixo está um exemplo da consulta baseada apenas na tabela de direção.

DECLARE @VAR AS INT

SET @VAR = 11652862

SELECT Field1, Field2
FROM tablea
WHERE Field2 = @VAR

Quando executo o código, o plano de execução real retorna com 15 mil linhas estimadas e 1 milhão de linhas reais. Sim, isso é apenas um simples SELECT...FROM TABLE, mas lembre-se de que isso é apenas parte de uma consulta maior, portanto, essa diferença de estimativa aumenta muito à medida que adicionamos mais junções.

Eu sei que o problema é @VARporque o otimizador não sabe o valor da variável quando compila a consulta em um plano, então está voltando com o número médio de linhas para a estimativa do campo. Se eu adicionar OPTION (RECOMPILE), forçará o valor quando compilar e retornará com o número correto de linhas.

Aqui está o kicker: Este não é um código personalizado que minha empresa pode editar. Faz parte de um aplicativo de terceiros que não consigo editar. A atualização das estatísticas não ajudará (olhei o histograma e ele está listando corretamente o valor da minha variável). Já tenho um índice NC de cobertura nesses dois campos. A única opção que consigo pensar é usar um plano de execução forçada nessa consulta. Eu nunca fui capaz de fazer isso com sucesso, no entanto. Alguém tem alguma outra ideia ou uma boa referência de instruções sobre planos de execução forçada?

Como foi perguntado nos comentários, a instrução create para o índice é:

CREATE NONCLUSTERED INDEX idx ON tablea (Field2)

Estamos realizando uma migração de dados de terceiros e está travando.

Quando investigamos, vemos que o SPID 38 (um SPID do sistema/em segundo plano) é o bloqueio dos SPIDs do usuário tentando fazer a migração. Infelizmente, o SQL_Text de sp_whoisactive (ou mesmo o monitor de atividade) é NULL para SPID 38. O wait_info é PREEMPTIVE_OS_GENERICOPS.

Existe alguma maneira de dizer o que 38 está tentando fazer?

Ele está usando uma ferramenta baseada no Windows fornecida por terceiros. Fiz um backup dos dados, restaurei neste novo servidor (para que o banco de dados e seu conteúdo estejam acessíveis e funcionando). Em seguida, executamos esta ferramenta para fazer a atualização para a nova versão do software e é aí que estamos obtendo os blocos.

sp_WhoIsActive @get_locks = 1retorna um XML muito longo (4k caracteres muito longos para postar).

Estou usando os scripts de Ola para atualizações de estatísticas e, para um índice específico, quero fazer verificações completas ao atualizar as estatísticas (para resolver o problema da chave ascendente em conjunto com o sinalizador de rastreamento 2389). Temos milhares de bancos de dados idênticos (é assim que o software do nosso fornecedor funciona) e gostaria de ter certeza de que todas as estatísticas desse índice estão de fato fazendo varreduras completas, não amostrando. Como faço para executar uma consulta para verificar todos os bancos de dados?

Posso usar DBCC SHOW_STATISTICS(), mas isso retorna três conjuntos de resultados - é possível colocar apenas o primeiro conjunto de resultados em uma tabela e ignorar os outros dois? Existe uma opção melhor?

Tive a impressão de que, se somasse DATALENGTH()todos os campos de todos os registros em uma tabela, obteria o tamanho total da tabela. Estou enganado?

SELECT 
SUM(DATALENGTH(Field1)) + 
SUM(DATALENGTH(Field2)) + 
SUM(DATALENGTH(Field3)) TotalSizeInBytes
FROM SomeTable
WHERE X, Y, and Z are true

Usei esta consulta abaixo (que obtive online para obter tamanhos de tabela, índices clusterizados apenas para não incluir índices NC) para obter o tamanho de uma tabela específica em meu banco de dados. Para fins de cobrança (cobramos nossos departamentos pela quantidade de espaço que eles usam), preciso descobrir quanto espaço cada departamento usou nesta tabela. Tenho uma consulta que identifica cada grupo dentro da tabela. Eu só preciso descobrir quanto espaço cada grupo está ocupando.

O espaço por linha pode oscilar muito devido aos VARCHAR(MAX)campos da tabela, então não posso simplesmente pegar um tamanho médio * a proporção de linhas para um departamento. Quando uso a DATALENGTH()abordagem descrita acima, obtenho apenas 85% do espaço total usado na consulta abaixo. Pensamentos?

SELECT 
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8)/1024 AS TotalSpaceMB, 
(SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB, 
((SUM(a.total_pages) - SUM(a.used_pages)) * 8)/1024 AS UnusedSpaceMB
FROM 
    sys.tables t with (nolock)
INNER JOIN 
    sys.schemas s with (nolock) ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i with (nolock) ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p with (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a with (nolock) ON p.partition_id = a.container_id
WHERE 
    t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
    AND i.type_desc = 'Clustered'
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc

Foi sugerido que eu crie um índice filtrado para cada departamento ou particione a tabela, para que eu possa consultar diretamente o espaço usado por índice. Índices filtrados poderiam ser criados programaticamente (e descartados novamente durante uma janela de manutenção ou quando eu precisar realizar o faturamento periódico), em vez de usar o espaço o tempo todo (partições seriam melhores nesse aspecto).

Eu gosto dessa sugestão e normalmente faria isso. Mas, para ser sincero, uso "cada departamento" como exemplo para explicar por que preciso disso, mas, para ser sincero, não é exatamente por isso. Por motivos de confidencialidade, não posso explicar o motivo exato pelo qual preciso desses dados, mas é análogo a diferentes departamentos.

Em relação aos índices não clusterizados nesta tabela: Se eu pudesse obter os tamanhos dos índices NC, seria ótimo. No entanto, os índices NC respondem por <1% do tamanho do índice clusterizado, portanto, podemos não incluí-los. No entanto, como incluiríamos os índices NC de qualquer maneira? Não consigo nem obter um tamanho preciso para o índice Clustered :)

Quando executo o código a seguir, leva 22,5 minutos e faz 106 milhões de leituras. No entanto, se eu executar apenas a instrução de seleção interna por si só, levará apenas 15 segundos e fará 264k leituras. Como observação, a consulta de seleção não retorna nenhum registro.

Alguma ideia de por que o IF EXISTSfaria funcionar por muito mais tempo e fazer muito mais leituras? Eu também mudei a instrução select para fazer SELECT TOP 1 [dlc].[id]e a matei após 2 minutos.

Como uma correção temporária, alterei para fazer um count(*) e atribuir esse valor a uma variável @cnt. Em seguida, ele faz uma IF 0 <> @cntdeclaração. Mas eu pensei EXISTSque seria melhor, porque se houvesse registros retornados na instrução select, ele pararia de executar o scan/seeks assim que encontrasse pelo menos um registro, enquanto o count(*)concluiria a consulta completa. o que estou perdendo?

IF EXISTS
   (SELECT [dlc].[ID]
   FROM TableDLC [dlc]
   JOIN TableD [d]
   ON [d].[ID] = [dlc].[ID]
   JOIN TableC [c]
   ON [c].[ID] = [d].[ID2]
   WHERE [c].[Name] <> [dlc].[Name])
BEGIN
   <do something>
END

Eu tenho um servidor com 16 CPUs configurado com max degree of parallelism8 e max worker threadszero.

Por uma determinada hora, minhas esperas de sinal foram de 20%, mas a utilização da CPU do meu sistema operacional durante esse período nunca ultrapassou 25%. Alguém pode explicar por que minhas esperas de sinal eram tão altas?

Meu fornecedor tem o melhor sistema de pontuação da categoria que espera que tenhamos 10% ou menos de espera de sinal, ou seremos prejudicados. Como posso corrigir isso (sem adicionar CPUs adicionais)?

• Não temos mais de 8 CPUs por nó NUMA, portanto, o sinalizador de rastreamento 8048 não se aplica.
• As maiores esperas de instância são CXPACKET(70%), então PREEMPTIVE_OS_PIPEOPS(20%)
• cost threshold for parallelismé definido como 50. Devo aumentá-lo? Para quê?
• Esta é uma máquina física (não uma VM), dedicada ao SQL Server.
• Estou usando uma ferramenta de monitoramento para identificar as consultas e procedimentos executados com mais frequência. Eu quero olhar para alta CPU, alta E/S ou alta duração? Normalmente, nosso aplicativo é intensivo em I/O, então eu ajustei I/O alto. Mas, como o problema é a espera de sinal, preciso observar a CPU alta?
• Eu esperava evitar a recomendação de Max Vernon para reduzir MAXDOPpara 4, porque o aplicativo faz algumas consultas de estilo de armazém que precisam de threads extras.

Temos backups de log executados a cada 15 minutos e fulls/diffs noturnos (os fulls são uma vez por semana por banco de dados, mas são distribuídos por toda a semana para balanceamento de carga). Digamos que, para um banco de dados específico, eu tenha um diff que começa às 22h05 e termina às 22h18 e tenho backups de log às 22h00, 22h15 e 22h30. Se eu quiser restaurar para 22h24 naquela noite, sei que preciso restaurar o último full e o diff daquela noite, mas não tenho certeza de qual log de transações devo começar depois disso.

Peço desculpas se este não é o lugar correto para perguntar isso, mas algo está me incomodando. Eu poderia jurar que em um trabalho anterior eu poderia estar na última linha do código no SSMS e apertar a seta para baixo e o cursor se moveria para o final da linha. Agora ele só fica no meio dessa última linha. Estou usando o SSMS 11.0.2100.60. Existe uma configuração que estou perdendo ou imaginei isso acontecendo? :)

Sei que posso usar o SQL Server Configuration Manager para determinar 1) quais serviços estão em execução no momento e 2) quais serviços estão definidos para início automático. No entanto, tenho várias dezenas de servidores que gerencio. Gostaria de executar uma verificação trimestral para garantir que todos os serviços ainda estejam configurados para início automático e para ver quais serviços estão ativados no momento. Existe alguma tabela ou procedimento que me dará essa informação?

Eu perguntei algo semelhante a isso antes, mas antes eu havia perguntado sobre como mover backups para um local compartilhado. Desta vez estou curioso: se eu quiser restaurar um banco de dados em uma unidade compartilhada, preciso habilitar o IFI nesse servidor ou apenas no servidor que está executando o SQL Server?

A razão pela qual pergunto é que estou restaurando um banco de dados muito grande e ele ficou 100% restaurado nas últimas horas. O tipo de espera sp_whoisactiveé:

(28472716ms) `PREEMPTIVE_OS_WRITEFILEGATHER.

A única vez que vi isso é quando o IFI não está ativado, mas eu o habilitei no SQL Server, mas não está ativado no servidor da unidade compartilhada.

Estou no SQL 2014 Standard fazendo CHECKDBs DBCC offline (portanto, em uma caixa diferente da produção). Na maioria das vezes, meu processo está indo bem rápido, mas por algum motivo eu tenho esse pequeno banco de dados (6 GB) e está demorando horas para fazer o DBCC. Da última vez que funcionou, demorou 9 horas. Parece congelar na conclusão de % diferente ao verificar sys.dm_exec_requests. Neste mesmo servidor estou fazendo CHECKDBs em bancos de dados na faixa dos terabytes sem problemas. Estou usando as seguintes dicas no CHECKDB neste momento (e, sim, eventualmente farei completo em vez de apenas físico)

WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY, TABLOCK

O que poderia estar errado com este banco de dados que está fazendo com que demore uma eternidade?

Eu sei que o IFI (Instant File Initialization) é bom ter ativado em SQL Servers. Que tal locais de arquivamento de backup? Fazemos backup de nossos bancos de dados em uma unidade SSD local (para que o backup seja rápido) e, ao longo do dia, movemos lentamente os backups para um local de arquivo e executamos restaurações e DBCCs em um servidor não produtivo. Eu estava curioso para saber se aquele local de arquivo para o qual copiamos os backups deveria ter o IFI ativado?

Acabei de herdar um conjunto de novos servidores e quem os configurou deixou a conta "Log On As" como padrão "NT Service\MSSQLSERVER". Eu gostaria de mudar isso para um nome de serviço do Active Directory. Nunca tentei alterá-lo depois que já entramos em produção. Há algo que eu deva estar ciente? Isso exigirá uma reinicialização do serviço? Este servidor está usando apenas SQL, não SSIS, SSAS, SSRS. Vou garantir que o nome do serviço AD tenha acesso a todas as pastas necessárias para acessar os arquivos. Mais alguma coisa que estou perdendo?

Estamos configurando checkdbs DBCC offline em um servidor offline para que possamos fazer nossas verificações, mas não interferir na produção.

Quando fazemos backup do banco de dados mestre e o restauramos (mesmo diretamente em um SSD local na mesma máquina de produção), obtemos 36 erros de corrupção quando DBCC CHECKDB o banco de dados restaurado (master_restore).

Mas quando eu DBCC CHECKDB o banco de dados mestre original, não recebo erros. Qualquer ideia?

Check Catalog Msg 3851, State 1: An invalid row (class=12,depid=0,depsubid=0) was found in the system table sys.syssingleobjrefs (class=12).
Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13).
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=1,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=1,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=13,depid=2,depsubid=0) was found in the system table sys.syssingleobjrefs (class=13).
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=2,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=3,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=4,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Check Catalog Msg 3851, State 1: An invalid row (class=60,depid=5,depsubid=0) was found in the system table sys.syssingleobjrefs (class=60).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=261,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=262,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=263,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=264,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=266,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=268,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=269,depsubid=0,indepid=9,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=269,depsubid=0,indepid=10,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=273,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=274,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=277,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=278,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=279,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=280,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=26,depid=283,depsubid=0,indepid=3,indepsubid=0) was found in the system table sys.sysmultiobjrefs (class=26).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=101) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=103) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=104) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=105) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65536,depsubid=107) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65537,depsubid=101) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65537,depsubid=103) was found in the system table sys.syssingleobjrefs (class=76).
Check Catalog Msg 3851, State 1: An invalid row (class=76,depid=65537,depsubid=107) was found in the system table sys.syssingleobjrefs (class=76).
CHECKDB found 0 allocation errors and 32 consistency errors not associated with any single object.
Page (1:10) in database ID 13 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED   0_PCT_FULL'.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 99)' (object ID 99).
CHECKDB found 1 allocation errors and 32 consistency errors in database 'master_restore'

Recentemente, nos fundimos com uma nova empresa e eles têm um servidor com 265 bancos de dados (6,6 TB de mdfs). Logo após a fusão, o outro DBA disse que leva 60 minutos para desligar os serviços, o que achei incrivelmente alto. Ontem à noite eles pararam e iniciaram os serviços e com certeza demorou 60 minutos. Eu olhei para o log esta manhã e encontrei as seguintes mensagens de erro no arquivo de log sql enquanto ele estava tentando parar o serviço.

Sei que o SQL leva mais tempo para reiniciar se você tiver muitos VLFs, mas demorou apenas 1 minuto desde o início até a mensagem Recuperação concluída aparecer no log. Alguma ideia se 60 minutos é normal? Ou, se não, o que posso fazer para investigar a aceleração disso. Obrigado!

 Error: 17142, Severity: 14, State: 0.
 SQL Server service has been paused. No new connections will be allowed. To resume the     
 service, use SQL Computer Manager or the Services application in Control Panel.

 Error: 18456, Severity: 14, State: 13.
 Login failed for user ''. Reason: SQL Server service is paused. 
 No new connections can be accepted at this time. [CLIENT: xx.xxx.xx.xx]