Kahn's questions

Eu tenho um procedimento que recebe parâmetros e faz uma declaração dinâmica com base neles. A instrução sempre retorna um valor numérico. Como faço para retornar esse valor ou passá-lo para a variável?

Por exemplo o seguinte:

DECLARE VAR1_INPUT INT;
DECLARE SQLSTRING VARCHAR(5000);
DECLARE OUTPUTVAR INT;

SET VAR1_INPUT = 5;

SET SQLSTRING = 'select next value into :OUTPUTVAR for MYSCHEMA.MYSEQUENCE_'||CAST(VAR1_INPUT AS VARCHAR(25))||' from sysibm.sysdummy1';

EXECUTE IMMEDIATE SQLSTRING;

Suponho que eu poderia usar um cursor, mas isso será muito executado e precisa ser leve, prefiro evitar cursores. E não fique fixado no SQL dinâmico sem sentido acima, isso é apenas um exemplo. :)

Editar: A pergunta original tinha a seleção de um valor de uma tabela como a pergunta. A resposta correspondeu a isso, então estou marcando-a como a resposta correta. Mais tarde, percebi que as sequências funcionam de maneira diferente, então editei a pergunta. Essa parte eu nunca percebi.

Temos um cenário em que precisamos adicionar várias linhas a tabelas diferentes, obter os IDs de chave primária gerados, criar mais inserções com FKs referindo-se aos IDs gerados anteriormente, etc. No SQL Server, você pode usar apenas a cláusula OUTPUT para armazenar os linhas e os IDs gerados e, em seguida, consulte-os. Mas aparentemente no db2 você não pode usar os resultados FINAL TABLE para nada além de um select.

Então, estamos procurando inserir um monte de dados de uma planilha do Excel na tabela A , depois combinar os IDs gerados no conjunto de dados do Excel e inserir mais linhas na tabela B, C, etc.

Existe uma maneira alternativa de armazenar os registros inseridos em um formato utilizável. De preferência sem algo que exija a compilação de um procedimento, então podemos apenas executar o script uma parte de cada vez? Um exemplo de quase código abaixo, que obviamente não funciona.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP
    (ITEM_IDENTIFIER,
    TABLE1_ID INT) 
ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEMP (ITEM_IDENTIFIER, TABLE1_ID)
SELECT IDENTIFIER, ID
FROM FINAL TABLE 
    (INSERT INTO MYSCHEMA.MYTABLE (IDENTIFIER, ID /*...other columns*/)
    SELECT *
    FROM 
        (SELECT 'asd123' IDENTIFIER /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        UNION
        SELECT 'bsb234' /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        UNION
        SELECT 'dasd654_' /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        /* etc.. */) s
    );

Parece ser bastante difícil encontrar comparações entre tabelas temporais com versão do sistema e as opções mais antigas, como gatilhos de banco de dados e CDC. No momento, não tenho tempo para escrever um teste estendido no SQL Server 2016, então pensei em perguntar sobre isso aqui.

Basicamente, a vantagem típica dos gatilhos é que eles são mais fáceis de gerenciar em ambientes autônomos e em cluster/alwaysOn, são sincronizados em tempo real e têm acesso aos dados da sessão, como o ID do usuário.

O CDC, por outro lado, embora exija um pouco mais de gerenciamento e seja assíncrono, é muito mais leve e, portanto, tem um desempenho muito melhor. Portanto, se houver alguma dúvida de que o gargalo causado pelos gatilhos pode se tornar um problema, o CDC será basicamente a solução superior. Em termos de requisitos de hardware, há um requisito de espaço extra insignificante pelo CDC devido ao uso de logs e tabelas de auditoria do CDC para rastrear as alterações.

A pergunta: como as tabelas temporais se comparam às duas anteriores? Em termos de velocidade, desempenho, uso de espaço de armazenamento. QUANDO devo usar tabelas temporais em vez de gatilhos ou CDC? Quando não devo?

Entendo que algo potencialmente complexo como os requisitos de negócios e as limitações técnicas por trás da auditoria de banco de dados não terá uma resposta fácil, pois depende muito dos requisitos e do escopo do projeto. Mas qualquer coisa para lançar mais luz sobre as questões acima seria apreciada. Obrigado!

(Observação, voltei a isso em 2021 e acrescentei o que havia aprendido como resposta à pergunta original. Você a encontrará abaixo.)

Recentemente, consegui habilitar o CDC em um AG sempre ativo. Embora muitas fontes afirmem que ativar o CDC no alwaysON AG é tão fácil quanto em qualquer servidor autônomo, na verdade não é. Como MS diz aqui :

Você deve criar os trabalhos de [captura e limpeza do CDC] em todos os possíveis destinos de failover antes do failover e marcá-los como desabilitados até que a réplica de disponibilidade em um host se torne a nova réplica primária. Os trabalhos do CDC em execução no antigo banco de dados primário também devem ser desabilitados quando o banco de dados local se tornar um banco de dados secundário. Para desabilitar e habilitar trabalhos, use a opção @enabled de sp_update_job (Transact-SQL). Para obter mais informações sobre como criar trabalhos de CDC, consulte sys.sp_cdc_add_job (Transact-SQL).

Acontece que depois que habilitamos o CDC na réplica primária, absolutamente nada aconteceu na secundária. Portanto, de acordo com o conselho do MS, fiz um failover manual e criei os trabalhos lá, depois fiz outro failover de volta ao primário original.

E, aparentemente, conforme sugerido na citação acima, tão totalmente compatível quanto o CDC deve ser com o alwaysOn AG, ele é totalmente incapaz de determinar se está sendo executado na réplica primária ou secundária.

Então, com o pano de fundo fora do caminho, vamos à minha pergunta : como você garante que os trabalhos do CDC estejam sempre habilitados na réplica primária atual? E sempre desativado (para não produzir erros) nos secundários?

Uma vez que, obviamente, um grande ponto do alwaysOn AG é que você não precisa estar lá manualmente no segundo em que o primário falha por qualquer motivo, porque um secundário vai pegar a folga e manter o sistema online, presumo que seja esperado apenas criar um trabalho em cada uma das réplicas, pesquisando constantemente seus status e alterando os estados dos trabalhos do CDC enquanto faz isso?

Se estiver correto, você tem algum conselho específico sobre como fazer isso, scripts ou guias específicos, etc.? Porque, para o que obviamente deveria ser um dilema bastante comum em todo o mundo, parece estranho que eu tenha que criar manualmente um novo trabalho TSQL em cada réplica apenas para executar o que é essencialmente a funcionalidade básica básica do CDC com alwaysOn AG .

Isso parece improvável, e é por isso que estou perguntando. Ou eu entendi errado alguma coisa? Obrigado!

Temos um login/usuário com DBO para um único banco de dados e agora estamos adicionando uma consulta que fornece algumas informações básicas de monitoramento que exigem permissões que se estendem um pouco para o lado do administrador do sistema.

Basicamente, a consulta precisa ser capaz de executar (somente leitura) os seguintes comandos:

DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
dbo.sysfiles
sys.master_files
tempdb.sys.database_files
tempdb.sys.dm_db_file_space_usage
sys.dm_exec_requests
sys.dm_tran_locks
sys.partitions
sys.indexes
sys.dm_os_waiting_tasks
sys.dm_os_tasks
sys.dm_exec_sessions
sys.dm_exec_sql_text

A maioria dessas coisas envolve permissões como VIEW SERVER STATE, VIEW ANY DEFINITION, etc, todas as quais podem ser usadas para conceder as permissões necessárias sem ir para a função de servidor sysadmin. Mas queremos apenas limitar essas permissões a essa única consulta.

Então, o que estamos procurando são opções. Minha última opção preferida é adicionar as várias permissões ao login/usuário, pois assim ele teria acesso a mais do que precisa (várias sub-permissões da função de servidor sysadmin), então tentei criar um procedimento com o EXECUTE AS cláusula, apenas aparentemente por causa do SQL dinâmico interno (precisamos enviar os resultados de DBCC SQLPERF para uma variável de tabela), o encadeamento de propriedade fica difícil. Mesmo independentemente disso, parece bastante complicado permitir que um procedimento seja executado com credenciais limítrofes de administrador de sistema.

Pesquisei mais sobre esse assunto do que posso entender e ainda não consigo fazer isso funcionar. Basicamente, vocês podem me dizer como fazer isso sem criar falhas de segurança.

Apenas um lote, que executa esta consulta, só pode ser alterado pelo administrador do sistema e pode ser lido/executado por qualquer pessoa com permissão para executá-lo? Obrigado!

EDITAR:

Atualmente estou analisando a possibilidade de criar um proxy de logins e/ou assinar o procedimento com um certificado. Parece uma opção bastante interessante.

Acabei de notar algo peculiar. Estamos usando a disponibilidade AlwaysOn em um de nossos ambientes com uma réplica secundária somente leitura. No entanto, verificando os arquivos .mdf / .ldf para a réplica secundária, mostra um tempo de modificação de vários meses atrás. Da mesma forma, usando sp_helpdb 'DBNAME' na réplica secundária, ele afirma que o espaço é significativamente menor do que no nó primário.

Ainda assim, o banco de dados mostra como sendo sincronizado no explorador de objetos do SSMS e, ao ler os dados no banco de dados, fica claro que os dados mais recentes estão sendo replicados corretamente.

Isso não é um problema, eu simplesmente gostaria de entender o que está acontecendo, pois não consigo encontrar essas informações pesquisando no Google. Por que o uso do espaço parece não ser atualizado corretamente? É uma questão de permissões ou um sintoma do próprio modo somente leitura ou o quê?

Obrigada!

Como diz o tópico, gostaria de saber se vocês têm algum conselho sobre possíveis problemas com a disponibilidade do Always On durante uma atualização massiva?

Precisamos fazer várias mudanças maciças no modelo de dados, remover e adicionar tabelas e mover grandes quantidades de dados (mais de 300 GB), incluindo criar e reconstruir centenas de índices e estatísticas em um procedimento que levará mais de 24 horas em total e de acordo com o cronograma não pode demorar mais de 30 horas antes de colocar o banco de dados novamente online. E não tenho certeza se a sincronização do modo de disponibilidade sempre ativa é a melhor opção para replicar essas alterações para os outros grupos de disponibilidade. Seria melhor apenas fazer backup das alterações depois de concluídas, restaurá-las para os outros grupos e reativar a sincronização a partir daí?

Como eu disse, não tenho experiência em trabalhar com isso, então essa é uma questão em aberto. E peço desculpas por quaisquer mal-entendidos ou equívocos aqui, novamente, já que minha experiência é limitada. Por favor, corrija-me quando apropriado. :)

Obrigado!

Para simplificar:

De acordo com o MSDN (link) entre outras fontes, você pode obter a quantidade de espaço livre atual no tempdb usando o seguinte bit do TSQL:

-- MSDN version
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- The "other" version
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Agora, estamos usando um script agendado que faz o que pode dinamicamente sem exagerar no uso do espaço. Antes de hoje, a versão do MSDN funcionava bem em ambientes que usavam SQL Server 2008 em cluster, mas hoje descobri que um de nossos novos ambientes com SQL Server 2012 e AlwaysOn Availability Groups se comporta de maneira diferente.

Lá, a "versão do MSDN" quase não retorna espaço livre, enquanto a "outra" versão mostra corretamente o espaço livre disponível no tempdb.

Alguém pode me explicar isso? Existe um tempdb compartilhado entre todos os grupos de réplicas AlwaysOn para que, por padrão, não defina especificamente o tempdb de alguma forma como padrão para alguma versão local do tempdb ou o que realmente está acontecendo aqui? Posso confiar que as consultas executadas no banco de dados realmente utilizam tempdb.sys.dm_db_file_usage_space em oposição a tudo o que sys.dm_db_file_usage_space regular está apontando?

Estou tentando descobrir mais sobre isso sozinho, mas precisamos de uma resposta com bastante urgência, então estou perguntando aqui só para ter certeza. Obrigado!

Preciso analisar o XML de um arquivo em tabelas enquanto retenho seus relacionamentos por meio de valores de ID gerados de forma personalizada.

Por exemplo, se eu tivesse o seguinte XML:

<root>
   <construction>
      <constructionName>randomname1</constructionName>
      <project>
         <projectname>another randomname</projectname>
         <businesspartners>
            <partnername>bilbo bagginses</partnername>
         </businesspartners>
         <employees>
            <employee>
               <empname>frodo</empname>
               <empaddress>etc...</empaddress>
            </employee>
         </employees>
      </project>
      <info>
         <randElement></randElement>
      </info>
      <constructionType>houses</constructiontype>
   </construction>
   <construction>
      <...(etc, same as above, times n^10)/>
   </construction>
</root>

A partir disso, preciso gerar dados para as seguintes tabelas:

CONSTRUCTION (CONSTRUCTION_ID INT PRIMARY KEY
   , CONSTRUCTIONNAME VARCHAR..
   , CONSTRUCTIONTYPE VARCHAR.. )

PROJECT (PROJECT_ID INT PRIMARY KEY
   , CONSTRUCTION_ID INT FOREIGN KEY REFERENCES CONSTRUCTION
   , PROJECTNAME VARCHAR.., )


BUSINESSPARTNERS (BUSINESSPARTNERS_ID INT PRIMARY KEY
   , PROJECT_ID INT FOREIGN KEY REFERENCES PROJECT
   , PARTNERNAME VARCHAR..)

etc...

Basicamente, a ideia é construir tabelas com suas referências intactas para representar o XML. As estruturas da tabela já existem e não podem ser alteradas para acomodar este script. Isso é simplesmente uma questão de ser capaz de fazer o mesmo tipo de análise XML e, em seguida, adicionar os dados às tabelas enquanto gera os valores de ID de referência corretos, como fizemos antes com a integração. Só fazendo isso exclusivamente com SQL Server, e sem usar SSIS .

Agora, o cenário real e o arquivo em questão são bastante grandes, então não espero de forma alguma uma resposta completa. Apenas dicas sobre onde começar a procurar. Eu sou bastante inexperiente em lidar com XML.

Por enquanto, minha solução preferida seria apenas importar os dados com OPENROWSETe, em seguida, usar o SQL dinâmico OPENXMLpara analisar o documento nas tabelas, basicamente fazendo um loop dos elementos e seus subelementos, um de cada vez. Mas isso parece muito mais problemático do que alguma outra maneira mais inteligente.

Como os valores de ID são gerados?

Isso é parte da questão. Por enquanto não são. A ideia é que a cada CONSTRUCTIONelemento seria atribuído um IDaumento de 1. Então, todos os filhos de CONSTRUCTIONse refeririam ao mesmo IDatribuído ao pai e assim por diante. Basicamente, trata-se apenas de dividir os dados do XML em várias tabelas, mantendo intacta a integridade referencial.

Eu não teria nenhum problema em usar uma coluna de identidade como chave primária, desde que as relações não se misturassem como resultado. Não sei como fazer isso, então presumi que seria necessário, de alguma forma, definir manualmente a lógica para criar os IDs, em oposição à identidade.

Devido a uma de nossas tabelas crescer fora do intervalo de um INT, algo que não foi previsto quando o DB foi produzido, precisamos alterar o campo PK para um BIGINT.

No entanto, estamos enfrentando problemas com relação ao uso de espaço do tempdb logo na primeira etapa ao manipular essa tabela: tentar descartar a restrição de chave primária nela para que possamos alterá-la. Nenhum de nossos ambientes de teste tem mais espaço do que 50 GB reservados para tempdb, enquanto a tabela ocupa cerca de 120 GB de espaço no momento. A tabela tem apenas 5 colunas e elas contêm apenas outros INTs e alguns varchars curtos.

Algum de vocês tem alguma sugestão melhor, talvez mais leve, no que diz respeito ao tempdb e ao arquivo de log, para fazer as mesmas alterações? A tabela tem apenas algumas colunas, todas contendo poucos dados. Exemplo abaixo, por mais simples que seja.

ALTER TABLE OURTABLE DROP CONSTRAINT OURTABLE_PK

ALTER TABLE OURTABLE ALTER COLUMN OURTABLE_ID BIGINT NOT NULL

ALTER TABLE OURTABLE ADD CONSTRAINT OURTABLE_PK PRIMARY KEY CLUSTERED (OURTABLE_ID)

Obrigado!

Estive pensando em apenas criar uma nova tabela com as mesmas definições e um BIGINT como uma inserção em massa e, em seguida, renomeá-la enquanto descartava a antiga. Mas não tenho certeza se isso será uma solução melhor, já que as restrições teriam que ser colocadas depois de qualquer maneira.

O cenário é bastante simples: Um Job falha na produção, dando um erro "ALTER INDEX falhou porque as seguintes opções SET têm configurações incorretas: 'QUOTED_IDENTIFIER' ...".

A questão é: por que esse problema aparece na produção e não em nenhum de nossos ambientes de teste? Verificando DBCC USEROPTIONS, os resultados são exatamente os mesmos em todos os bancos de dados. Além disso, o script em si não contém aspas. Ele simplesmente reconstrói alguns índices e atualiza algumas estatísticas com fullscan.

Usando exatamente o mesmo trabalho em qualquer outro banco de dados, posso definir QUOTED_IDENTIFIER ON ou OFF, ou simplesmente não configurá-lo e deixá-lo executar nos padrões de conexão. E sempre funcionará independentemente. Mas na produção, parece que deve ser definido como ON ou OFF.

Estou perguntando isso porque, em termos de tempo, não posso testar isso uma vez por dia, preciso saber por que esse erro está acontecendo e como prever qual configuração é necessária e quando. A única diferença visível entre os bancos de dados de teste e de produção é o agrupamento, mas mesmo criando um banco de dados de teste para corresponder ao agrupamento na produção, o trabalho ainda funciona independentemente da configuração QUOTED_IDENTIFIER.

Obrigado!

A questão é relativamente simples. Eu preciso calcular 3 colunas onde os resultados médios são decimais enormes e estou tendo um problema no início com o SQL Server basicamente arredondando os decimais, independentemente de qualquer conversão/conversão.

Por exemplo, vamos fazer uma divisão simples como 1234/1233. Uma calculadora produzirá 1.00081103000811. Mas quando faço isso no SQL Server, obtemos o seguinte:

-- Result: rounded at 1.000811000... with trailing zeroes up until the 37 precision
SELECT CAST(CAST(1234 AS DEC(38,34))/CAST(1233 AS DEC(38,34)) AS DEC(38,37))

-- Result: rounded at 1.000811
SELECT CONVERT(DECIMAL(38,32), 1234)/CONVERT(DECIMAL(38,32),1233)

-- Correct result at 1,00081103000811
-- But this requires the zeroes to be put in manually when you don't
-- even know the precision of the end result
SELECT 1234.0/1233.00000000000000

Por que esse arredondamento automático ocorre? E qual é a melhor maneira de calcular valores decimais insanamente longos quando você não pode ter certeza de quão grande será um número (a parte int ou dec), já que a tabela pode conter vários valores diferentes?

Obrigado!

Apenas uma pergunta rápida. Eu tenho um procedimento armazenado que usa dois parâmetros, um varchar (nome da tabela) e um tipo de tabela (intervalo de valores de ID de chave primária) e os usa para retornar todas as conexões FK de e para a tabela para os IDs fornecidos.

O uso é complicado, já que você realmente precisa declarar o tipo de tabela, selecionar os IDs nele e executar o procedimento da seguinte forma:

DECLARE @IDVALS SearchObjRef_Type

INSERT INTO @IDVALS 
SELECT TOP 10 RAND_ID FROM RANDTABLE 

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE', @IDRANGE = @IDVALS

Isso funciona muito bem, mas eu gostaria de encontrar uma maneira mais confortável, rápida e fácil de usar o procedimento em seleções padrão. Qual seria a melhor maneira, supondo que seja possível, fazer tudo isso em um comando?

Para um exemplo lógico que obviamente não funciona, o que eu gostaria de fazer é uma forma do seguinte:

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE', 
    @IDRANGE = (SELECT TOP 10 RAND_ID FROM RANDTABLE)

Esta pode ser uma pergunta um pouco estranha:

O modelo de dados do nosso projeto tem várias tabelas com várias colunas, qualquer uma das quais pode ser usada para se juntar a uma tabela de configuração. A questão aqui é como obter o nome da coluna que foi usada para se conectar à tabela de configuração em uma junção OR?

Por exemplo, digamos que a consulta tenha esta aparência:

SELECT FIRST.ID, SEC.ID, TRD.ID, FRTH.ID, SETS.ID, SETS.VALUE
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECDONTABLE_ID = FIRST.SECONDTABLE_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRDTABLE_ID = FIRST.THIRDTABLE_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTHTABLE_ID = TRD.FOURTHTABLE_ID
-- etc...
LEFT JOIN SETTINGTABLE SETS 
   ON (SETS.ID = FIRST.ONESETTING_ID OR SETS.ID = FIRST.SECONDSETTING_ID
      OR SETS.ID = FIRST.THIRDSETTING_ID OR SETS.ID = TRD.RANDSETTING_ID
      OR SETS.ID = FRTH.ONESETTING_ID
-- etc...
   )

Agora, esse é um exemplo muito analisado do que realmente está acontecendo. Podemos ter dezenas de tabelas com várias colunas joinin em cada uma delas para o SETTINGTABLE.

Obviamente, simplesmente fazer uma junção separada para cada table.column mostraria a coluna usada. Ou, alternativamente, você pode usar a cláusula CASE no select. Mas qualquer um deles é complicado e requer muita entrada manual. Mesmo apenas usando todas as colunas de ID de junção no select permitiria que você comparasse os IDs, mas como os resultados precisam ser lidos por pessoas não técnicas, isso não ajudaria muito.

Então, existe uma maneira de usar alguns objetos sys ou algo assim para simplesmente retornar o nome da coluna que foi realmente usado para conectar-se a SETTINGTABLE? O design é o que é e não mudará. Então, estou simplesmente tentando encontrar a melhor maneira de trabalhar com o que temos. Isso aceleraria significativamente nosso diagnóstico de alguns de nossos scripts, consultas e dados.

Obrigado!

Recentemente, executamos um script em nosso banco de dados de produção que eliminaria e recriaria dinamicamente centenas de índices como índices filtrados. Embora este script tenha sido executado perfeitamente em todos os outros testes anteriores, agora, depois deste, o cache do plano de consulta do SQL Server está se comportando de maneira estranha.

O planejador de execução revela que estão sendo usados ​​índices errados que retornam milhões de linhas, quando apenas algumas deveriam corresponder. Quando os índices estão corretos, o plano de execução mostra que o SQL Server resolve usar o índice Scan em oposição ao índice Seek, fornecendo resultados muito inferiores aos ideais. Com dicas de tabela como WITH(INDEX(indexname)) ou WITH(FORCESEEK) nos locais apropriados, isso pode ser corrigido. INNER LOOP JOIN também corrige alguns deles.

No entanto, o problema é que, mesmo quando esses novos índices filtrados são descartados e recriados como eram antes, o plano de consulta permanece o mesmo. O cache do plano de consulta foi limpo, o banco de dados foi restaurado para um ambiente diferente, as estatísticas foram atualizadas e, obviamente, os índices foram reconstruídos para que não sejam fragmentados.

Atualmente, esse é um problema crítico que ninguém tem ideia de como consertar. Embora possamos forçar o SQL Server a usar os planos corretos, simplesmente não é uma solução para a infinidade de softwares que teriam que ser atualizados com ele e, obviamente, um banco de dados onde você precisa apontar manualmente como lidar com consultas não é uma opção .

Então, qualquer ajuda seria bem-vinda.

Editar: Conseguimos corrigir uma consulta descartando os índices, recriando-os novamente como filtrados e, em seguida, executando um UPDATE STATISTICS tablename WITH FULLSCAN. Isso corrigiu parte do problema e duas junções estavam funcionando corretamente. Depois disso, tivemos que fazer uma alteração separada em um índice de várias colunas que não estava envolvido no script de índice original, para incluir uma das colunas usadas na junção. Essas duas alterações juntas permitiram que o planejador de consulta resolvesse usar os índices corretos com Seek em vez de Scan.

A teoria agora é que, devido a uma falha anterior, o banco de dados aparentemente foi restaurado excluindo o banco de dados subjacente e criando um novo a partir do backup, em vez de apenas usar REPLACE como antes. Isso teria de alguma forma desconectado os metadados do masterdb, como planos de execução, todo o cache e outros enfeites do banco de dados, resultando em um novo banco de dados massivo sem planos existentes para lidar com consultas. Isso, agrupado com uma atualização de estatísticas aparentemente falha nos índices recém-criados, teria produzido um cenário em que o SQL Server não tinha ideia de como resolver as várias consultas com as quais estava sendo bombardeado.

No entanto, não estou convencido de que isso ainda seja suficiente, já que alguns dos comportamentos, como alterar o índice de várias colunas e o fato de que nenhuma atualização de estatísticas foi necessária em nenhum dos ambientes de teste anteriores nos últimos 2 meses de testes, parece deixar transparecer que algo mais deu errado.

Tivemos várias falhas e restaurações alguns dias atrás e, após elas, o banco de dados do SQL Server tem agido de maneira estranha. Sabemos que houve alguns problemas com os clusters de failover devido aos quais tivemos que inicializar os servidores novamente para finalmente fazer o banco de dados aparentemente funcionar.

Um dos problemas a seguir é que executamos um grande script que eliminava dinamicamente os índices existentes e os recriava com a única diferença de que agora eles eram filtrados com a coluna WHERE NOT NULL. Por algum motivo, no entanto, quando escolho SCRIPT INDEX -> CREATE TO -> NEW QUERY WINDOW do SSMS Object Explorer, ele oferece o script básico de criação de índice em que o índice NÃO é filtrado. Quando nosso cliente com permissões totais faz o mesmo, o script de criação mostra corretamente que está filtrado.

Isso poderia ser um problema de permissão (não encontrei nenhum no Google) ou existe a possibilidade de que, embora o script tenha registrado as alterações corretamente, os nós ou o otimizador de consulta ou o que quer que esteja de alguma forma fora de sincronia?

Da mesma forma, várias consultas que anteriormente funcionavam bem (e ainda funcionam bem em um banco de dados diferente que é uma cópia deste), agora mostram por meio do plano de execução que estão se comportando de maneira diferente. Um deles tem, por exemplo, os seguintes problemas:

1. Por padrão, o planejador de execução mostra que o SQL Server usa o índice errado, produzindo milhões de linhas nos loops aninhados em vez de 2 como deveria.
2. Outro índice está correto, mas produz milhões de loops aninhados onde na cópia deste servidor obtemos apenas 3.
3. Quando a cópia é forçada a usar o mesmo índice errado do banco de dados problemático na seção 1, ela ainda retorna apenas 2 linhas em loops aninhados.

O que poderia ser a causa desses problemas e como começar a diagnosticar o problema? Como dito, os bancos de dados são cópias uns dos outros. A única diferença é que o banco de dados com problema travou onde o banco de dados foi movido para um cluster de failover e, em seguida, retornou ao nó correto novamente. Os índices não estão fragmentados, as estatísticas acabaram de ser atualizadas e parece haver uma carga extrema do planejador de consultas do SQL Server.

Eu realmente apreciaria alguns conselhos de especialistas sobre qual poderia ser a causa e como eu faria para diagnosticar o problema real, obrigado.

Nosso projeto executa um banco de dados muito grande e muito complicado. Então, cerca de um mês atrás, notamos que o espaço usado por colunas indexadas contendo valores nulos estava ficando muito grande. Como resposta a isso, escrevi um script que pesquisaria dinamicamente todos os índices de coluna única contendo mais de 1% de valores nulos e, em seguida, eliminaria e recriaria esses índices como índices filtrados com a condição de que o valor NÃO fosse NULO. Isso eliminaria e recriaria centenas de índices em todo o banco de dados e normalmente liberaria quase 15% do espaço usado por todo o banco de dados.

Agora tenho duas perguntas sobre isso:

A) Quais são as desvantagens de usar índices filtrados dessa maneira? Eu diria que isso apenas melhoraria o desempenho, mas há algum risco de desempenho envolvido?

B) Recebemos erros ( 'não é possível descartar o índice XYZ porque ele não existe ou você não tem permissão' ) ao descartar e recriar os índices, mesmo quando verificados posteriormente, tudo ocorreu exatamente como esperado. Como isso pode acontecer?

Obrigado por qualquer ajuda!

Editar: Em resposta a @Thomas Kejser

Oi e obrigado, mas acontece que isso foi um desastre. Na época não entendíamos várias coisas como:

1. Durante uma consulta, o SQLOS faz planos de índice antes de determinar que não pode usar valores NULL para unir as colunas da tabela. Ou seja, você realmente precisa ter um filtro de cláusula WHERE ajustando o índice para cada índice filtrado usado na consulta, ou o índice não será usado.
2. Eliminar e criar índices e atualizar redundantemente suas estatísticas mais uma vez ainda pode não ser suficiente para produzir os planos atualizados, o que presumimos que aconteceria. Parece que, em alguns casos, apenas uma carga de trabalho alta o suficiente forçará o SQL Server a reavaliar os planos.
3. Existem alguns aspectos exóticos na funcionalidade do planejador de execução que são difíceis de determinar apenas pelo bom senso e pela lógica. Mesmo com milhares de variações geradas por code-behind de diferentes consultas, índices aparentemente inúteis podem ajudar em algumas estatísticas e planos de consulta que acabam sendo usados ​​em consultas críticas.

No final, essas mudanças foram revertidas. Portanto, os índices filtrados são uma ferramenta poderosa, mas você precisa realmente entender exatamente quais dados estão sendo buscados nessas colunas. Onde os índices normais além dos problemas de espaço são bastante fáceis de aplicar, os índices filtrados representam soluções muito personalizadas. Eles certamente não são um substituto para um índice regular, mas sim uma extensão deles nas circunstâncias especiais em que são necessários.

Outra ferramenta que desenvolvi para mim, no que diz respeito a facilitar a criação de várias operações DML em um grande banco de dados de centenas de tabelas, é uma função definida pelo usuário bacana para mostrar facilmente quase todas as informações relevantes sobre uma coluna. A execução da função exibirá o tipo, comprimento, restrições de chave em potencial (FK ou PK), índices em potencial, bem como acionadores em potencial vinculados à coluna.

Código postado na resposta abaixo. Pode haver situações em que a função não funcione, dependendo de algumas configurações do banco de dados ou funcionalidades inexistentes em nosso projeto. Mas deve ser relativamente dinâmico.

Novamente, se você tiver alguma dica ou conselho útil semelhante, não hesite em compartilhar!

Como sou novo e nunca compartilhei, e ainda assim existem alguns scripts úteis de que preciso diariamente, achei que deveria.

Como um novo DBA, algumas operações DML de correção de dados e análises lógicas de negócios simples são muito mais rápidas quando tenho um modelo com o qual posso verificar as restrições em um banco de dados muito grande com centenas de tabelas. O script de Pinal Dave (fonte postada na resposta abaixo) foi uma grande ajuda quando comecei, mas desde então o expandi um pouco para incluir a possibilidade de pesquisar IDs referenciados em tabelas relacionadas também.

Espero que isso seja útil para alguns de vocês!

Esta é mais uma questão geral. Por que você não pode adicionar um índice filtrado no mesmo escopo da transação ao adicionar a nova coluna em primeiro lugar? A solução é obviamente simples, basta fazer duas transações onde você cancela manualmente as alterações feitas na primeira, caso ela retorne um erro.

Mesmo assim, estou interessado no raciocínio técnico por trás desse fenômeno, especialmente considerando que já fiz isso antes. Eu ativei alguma verificação superzelosa interessante no SSMS? Exemplo de código abaixo, e obrigado!

--CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN           
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX 
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN   
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;