J.D.'s questions

Não consigo fazer com que o SQL Server utilize um índice espacial básico em um GEOGRAPHYobjeto, mesmo para as consultas mais simples.

Aqui está uma reprodução do dbfiddle.uk que demonstra como criar uma tabela simples com uma GEOGRAPHYcoluna chamada Coordinates, adicionar 1 linha com um polígono a essa tabela e, em seguida, criar um índice espacial na Coordinatescoluna.

A consulta parece que deveria ser SARGable, mas ainda recebo uma varredura de índice clusterizado, em vez de uma busca de índice não clusterizado no índice espacial:

Se eu tentar forçar a dica de índice, recebo o erro clássico:

Msg 8635, Nível 16, Estado 4, Linha 15

O processador de consulta não pôde produzir um plano de consulta para uma consulta com uma dica de índice espacial. Motivo: Índices espaciais não suportam o comparador fornecido no predicado. Tente remover as dicas de índice ou remover SET FORCEPLAN.

Portanto, não é que o SQL Server pense que a verificação do índice clusterizado seja mais eficiente, mas sim que ele não consegue sequer criar um plano de consulta para o índice espacial.

De acordo com a documentação da Microsoft sobre Índices Espaciais , a STDistance()função deve ser aplicável em um predicado para sargabilidade de índices espaciais:

Os índices espaciais suportam as seguintes formas de predicado:

• geografia1.STDistance(geografia2) <= número

Estou sendo burro?

Olá pessoas mais espertas que eu! Eu criei uma espécie de sistema de tabela de fila, mas parece simples demais para estar protegido contra condições de corrida. Estou faltando alguma coisa ou a seguinte condição de corrida é segura?

O Esquema

Eu tenho uma mesa, vamos chamá-la ProductQueue:

CREATE TABLE dbo.ProductQueue
(
    SerialId BIGINT PRIMARY KEY,
    QueuedDateTime DATETIME NOT NULL -- Only using this for reference, no functionality is tied to it
);

Eu tenho um procedimento para adicionar à fila chamado AddToProductQueue:

CREATE PROCEDURE dbo.AddToProductQueue (@SerialId BIGINT)
AS
BEGIN
    INSERT INTO dbo.ProductQueue (SerialId, QueuedDateTime)
    OUTPUT Inserted.SerialId
    SELECT @SerialId, GETDATE();
END

Também tenho um procedimento para remover da fila chamado RemoveFromProductQueue:

CREATE PROCEDURE dbo.RemoveFromProductQueue (@SerialId BIGINT)
AS
BEGIN
    DELETE FROM dbo.ProductQueue
    OUTPUT Deleted.SerialId
    WHERE SerialId = @SerialId;
END

Observe SerialIdque é globalmente exclusivo para um Productbanco de dados/sistema de origem. Ou seja, duas instâncias de a Productnunca podem ter o mesmo SerialId. Essa é a extensão do lado do banco de dados.

O fluxo de trabalho

• Eu tenho um processo de inscrição que é executado de hora em hora.
• Esse processo obtém uma lista de variáveis SerialIds​​do sistema de origem.
• Ele chama iterativamente o AddToProductQueueprocedimento em cada um SerialIdde sua lista.
• Se o procedimento tentar inserir um SerialIdque já existe na ProductQueuetabela, ele gerará um erro de violação de chave primária e o processo do aplicativo capturará esse erro e o ignorará SerialId.
• Caso contrário, o procedimento adiciona isso SerialIdà ProductQueuetabela com êxito e o retorna ao processo de aplicativo.
• O processo de inscrição então adiciona o que foi enfileirado com sucesso SerialIdem uma lista separada.
• Depois que o processo de aplicativo termina de iterar sua lista de todos os candidatos SerialIdsa serem enfileirados, ele itera sua nova lista de enfileirados com sucesso SerialIdse realiza trabalho externo neles, em um thread separado por SerialId. (Este trabalho não está relacionado ao banco de dados.)
• Finalmente, à medida que cada thread termina seu trabalho externo, a última etapa desse thread assíncrono é removê-lo SerialIdda ProductQueuetabela chamando o RemoveFromProductQueueprocedimento. (Observe que um novo objeto de contexto de banco de dados é instanciado e uma nova conexão é criada para cada chamada assíncrona para esse procedimento, para que ele seja thread-safe no lado do aplicativo.)

Informações adicionais

• Não há índices na ProductQueuetabela e ela nunca terá mais de 1.000 linhas ao mesmo tempo. (Na verdade, na maioria das vezes terá literalmente apenas algumas linhas.)
• O mesmo SerialIdpode se tornar candidato novamente para ser adicionado novamente à tabela de filas em uma futura execução do processo de aplicação.
• Não há proteções que impeçam a execução simultânea de uma segunda instância do processo de aplicativo, seja por acidente ou se a primeira instância demorou mais de 1 hora para ser executada, etc. (Esta é a parte simultânea com a qual estou mais preocupado.)
• O nível de isolamento da transação do banco de dados (e da conexão que está sendo feita) onde residem a tabela de filas e os procedimentos é o nível de isolamento padrão de Read Committed.

Problemas potenciais

• A instância em execução do processo do aplicativo trava de forma não tratada, deixando- SerialIdsa presa na tabela de filas. Isso é aceitável para as necessidades do negócio e planejamos ter relatórios de exceção para nos ajudar a remediar manualmente esse caso.
• O processo do aplicativo é executado várias vezes simultaneamente e captura algumas das mesmas SerialIdsentre as instâncias em suas listas de origem iniciais. Ainda não consigo pensar em nenhuma ramificação negativa deste caso, uma vez que o procedimento de enfileiramento é atômico, e a lista real em SerialIdsque o processo de aplicação funcionará deve ser independente devido a esse procedimento de enfileiramento atômico. Não nos importamos qual instância do processo de aplicativo realmente processa cada uma SerialId, desde que a mesma SerialIdnão seja processada simultaneamente por ambas as instâncias do processo.

Eu tenho a seguinte EventTimestabela:

DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
  EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
  EventId INT NOT NULL,
  StartTime TIME NOT NULL,
  EndTime TIME NOT NULL
);

Com os seguintes dados:

-- Event 1
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (1, '04:00:00', '14:00:00'), 
  (1, '06:00:00', '11:00:00'), 
  (1, '09:00:00', '12:00:00'), 
  (1, '13:00:00', '14:00:00'), -- Gap between this row and the next row
  (1, '02:30:00', '04:00:00'); -- Notice the half-hour on this one

-- Event 2
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (2, '00:00:00', '06:00:00'), -- Gap between this row and the next row
  (2, '09:00:00', '13:00:00'), 
  (2, '11:00:00', '15:00:00');

Perceber:

• O mesmo Eventpode ter dois intervalos de tempo que se sobrepõem. Por exemplo, o mesmo Eventdas 4h às 14h e também das 6h às 11h.
• Também pode haver lacunas entre os dois intervalos de tempo. Por exemplo, o mesmo Eventdas 13h às 14h e também das 14h30 às 16h.

Objetivo final:

Estou tentando calcular o valor TotalTimede um determinado Eventignorando o tempo de sobreposição duplicado. Por exemplo, para o conjunto de intervalos das 9h00 às 13h00 e das 11h00 às 15h00, o intervalo TotalTimedeverá ser de 6 horas (9h00 às 15h00). Por outro lado, também não quero contar o tempo nos intervalos entre dois intervalos de tempo. Portanto, para o conjunto de intervalos de 13h às 14h e de 14h30 às 16h, TotalTimedeve ser de 2,5 horas. (Observe que estes são apenas subconjuntos do exemplo completo acima, e o resultado final deve ser a soma de todos esses tempos únicos por Event.)

Nunca TotalTimedeve exceder 24 horas, esses tempos são todos dentro de um único dia (assim como o TIMEtipo de dados).

Resultados finais esperados para os exemplos fornecidos nos scripts acima:

dbfiddle.uk para referência.

Outras informações:

• Se for mais fácil trabalhar com datas e horas, sinta-se à vontade para alterar os tipos de dados de TIMEpara DATETIME. Posso converter os resultados de volta, sem problemas.

• Acredito que algum tipo de recursão seja necessária para resolver isso. Sinto que estou bem perto de uma solução, mas ainda não cheguei lá.

Em um caso como 2h30 - 4h e 4h - 14h, esperaria que o tempo total fosse de 11,5 horas.

Existe alguma maneira de saber de dentro de um gatilho, quando ele é acionado, que está sendo acionado como resultado de uma ação DML que ocorreu em outro gatilho específico ?

Alguma chance de qualquer informação sobre a pilha de chamadas ser exposta na EVENTDATA()função? Ou outra função? Eu preferiria não ter que destruir XML. ?

O ideal é obter o nome do gatilho original que executou o DML que causou o disparo do segundo gatilho, dentro do escopo desse segundo gatilho. Mas também estou aberto a maneiras semelhantes de identificar a fonte.

Tenho total controle sobre o código de ambas as triggers em questão.

Quando as consultas DML e DDL são executadas em uma Tabela Temporal , essas alterações são propagadas adequadamente para a tabela de Histórico correlacionada. Por exemplo, uma UPDATEtabela temporal resulta em um novo registro a ser INSERTinserido na tabela de histórico para refletir essa mudança.

Isso é INSERTpara a tabela de histórico, bem como outras alterações propagadas, registradas no log de transações?

Isso significa que habilitar tabelas temporais efetivamente dobrará a quantidade de dados que passa pelo log de transações (pelo menos para um banco de dados com o modelo de recuperação definido como completo)?

Recentemente, atualizamos um de nossos servidores de produção do SQL Server 2016 para o SQL Server 2019 (CU 15). Essa foi uma ótima oportunidade para habilitar o Query Store em nosso banco de dados de aplicativos principal. Ele está em execução há alguns dias e é isso que o Relatório geral de consumo de recursos está mostrando:

Na captura de tela, selecionei alguns números que pareciam insanos (para o primeiro dia em que o Query Store foi ativado) e os normalizei para unidades de medida mais fáceis de serem discutidas. Coisas como ~183 TB de dados consumidos por leituras lógicas ou ~5 TB de dados consumidos pela memória, em um único dia, parecem quase impossíveis neste servidor.

Esse banco de dados é o John Smith dos bancos de dados, com apenas 100 GB de tamanho para o arquivo de dados e 200 GB para o arquivo de log. No máximo, talvez 100 usuários diferentes se conectam a ele ao longo do dia, e não há uma tonelada de transações sendo criadas em um único dia. O próprio servidor possui apenas 32 GB de memória provisionada para ele. Para que 5 TB de memória sejam consumidos, a memória alocada precisaria ser preenchida mais de 150 vezes ao longo do dia.

A única outra informação potencialmente relevante que posso pensar em adicionar é que, após a atualização, definimos imediatamente o "Nível de compatibilidade" desse banco de dados para 150 (SQL Server 2019) e deixamos a configuração "Estimativa de cardinalidade herdada" desativada. Eu sei que não é o ideal e é melhor deixar a poeira baixar enquanto coletamos as métricas básicas, mas parte do motivo da atualização foi corrigir alguns problemas urgentes de desempenho para os quais essa combinação de configurações funcionou melhor em nossos testes (e ainda parece estar funcionando muito bem).

Alguns dos problemas de desempenho anteriores que estávamos tendo eram devido a estimativas de cardinalidade insanas, que se o Query Store estivesse usando os pontos de dados estimados , então eu poderia ver os números deste relatório sendo correlativos, mas eu teria que imaginar que o relatório é usando pontos de dados reais ? Embora fosse interessante se isso fosse outro sinal de algo fundamentalmente errado com a forma como meu servidor / banco de dados de produção está configurado em minha conquista contínua para eliminar problemas de estimativa de cardinalidade.

Estou lendo esses números errado, o Query Store está bugando ou meu servidor está travando?

O que eu fiz:

Recentemente testei fazer uma atualização in-loco no ServerA, um servidor de desenvolvimento que estava executando o SQL Server 2016 (SP 2 CU17) Standard Edition para o SQL Server 2019 Standard Edition. (Eu sei que esta não é a maneira preferida de fazer uma atualização, mas novamente apenas um teste em um servidor de desenvolvimento, então não há mal nenhum.)

Durante o Assistente de instalação, uma das etapas ficou suspensa por muito tempo, então meu colega de trabalho clicou no botão Avançar para pular a etapa. Não consigo lembrar qual etapa foi, mas acredito que foi a etapa Atualizações do produto ou Instalar arquivos de instalação. Lembro-me que em seguida disse que havia ignorado alguns tipos diferentes de downloads. O resto da instalação correu bem, e terminou com sucesso .

Consegui iniciar a instância, fazer login e acessar nosso banco de dados. Em seguida, aumentei o nível de compatibilidade do banco de dados para 150 (nível de compatibilidade do SQL Server 2019). Executei algumas consultas para testes de desempenho e, finalmente, decidi ativar o Estimador de cardinalidade herdado. Tudo parecia estar funcionando até agora.

O que aconteceu foi:

Então notei algo interessante no ServerB, outro servidor de desenvolvimento que já estava executando o SQL Server 2019 e possui uma configuração de servidor vinculado apontando para o ServerA. Tudo estava funcionando bem em ServerB, exceto quaisquer consultas que referenciassem uma exibição no servidor vinculado para ServerAonde essa exibição usava uma função escalar vinculada ao esquema dentro dela. Eu estava recebendo erro The EXECUTE permission was denied on the object 'MyFunction', database 'Database1OnServerA', schema 'dbo'. Se eu voltasse ServerAe alterasse a função com a linha WITH SCHEMABINDINGcomentada, ServerBpoderia selecionar na exibição que referencia essa função novamente.

Informação adicional:

A conta usada no objeto de servidor vinculado é um logon do SQL Server ServerAcom apenas a db_datareaderfunção mapeada para ela (além da Database1função de banco de dados, é claro). Não há permissões granulares adicionais definidas nele e também é atribuída à função de servidor.ServerAPublicPublic

Curiosamente, uma solução alternativa para o meu problema foi conceder a permissão EXECUTEou especificamente para a conta do servidor vinculado. MAS eu não precisei conceder a permissão antes da atualização para o SQL Server 2019, e meus servidores de produção (que espelham meus servidores de desenvolvimento antes desta atualização de teste) atualmente também não fornecem a permissão para a conta do servidor vinculado.Database1ServerAMyFunctionEXECUTEServerAEXECUTE

Porta Número 1, 2 ou 3:

1. Algo relacionado à segurança mudou do SQL Server 2016 para o SQL Server 2019 que eu não percebi ou...
2. Isso soa como um bug que encontrei ou ...
3. Você acha que eu errei minha atualização no local ServerA?

Alguma outra ideia sobre por que ServerBestá recebendo um erro de permissão na EXECUTEpermissão apenas para funções vinculadas ao esquema acessadas em um servidor vinculado para ServerA, após a atualização ServerAdo SQL Server 2016 para o SQL Server 2019, simplesmente?

Eu tenho a seguinte consulta (estupidamente simplificada) que aproveita dois CTEs que se referem à mesma tabela e os une:

WITH CTE1 AS
(
    SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
    FROM PhoneNumbersTable
    GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
    SELECT CTE1.PhoneNumberCleaned
    FROM CTE1
    INNER HASH JOIN PhoneNumbersTable
        ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
    WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2

Observe o HASH JOINque acontece dentro CTE2. Isso tudo funciona bem e bem até agora.

Se eu adicionar a seguinte WHEREcláusula à consulta final SELECT, minha consulta inteira agora se torna:

WITH CTE1 AS
(
    SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
    FROM PhoneNumbersTable
    GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
    SELECT CTE1.PhoneNumberCleaned
    FROM CTE1
    INNER HASH JOIN PhoneNumbersTable
        ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
    WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2
WHERE PhoneNumberCleaned = 'SomePhoneNumberInTheResultSet' -- E.g. 7183998888

Então recebo o erro clássico:

Msg 8622, Level 16, State 1, Line 50 O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.

Isso só acontece se o valor que eu uso na minha WHEREcláusula realmente existir no conjunto de resultados. Se eu escolher qualquer valor que não exista, não recebo o erro acima.

Agora, obviamente, meu exemplo é meio estúpido para o que está acontecendo, e posso reescrevê-lo de algumas maneiras diferentes para provavelmente corrigi-lo, mas estou mais curioso para saber por que isso acontece? Se o SQL Server Engine é capaz de produzir um plano de consulta que retorna todos os registros, por que ele não consegue adicionar um operador de filtro adicional no final desse plano de consulta para o valor escalar que estou filtrando na minha WHEREcláusula?

Aqui está o código da caixa preta da minha funçãodbo.RemoveNonNumericCharacters (observe que não escrevi isso):

CREATE FUNCTION [dbo].[RemoveNonNumericCharacters] (@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Observe também que a coluna PhoneNumberno PhoneNumbersTableé do tipo VARCHAR(20).

Configurei um plano de manutenção para fazer um backup completo diário de todos os bancos de dados no meu servidor. Também configurei um segundo plano de manutenção para fazer backups do log de transações a cada 5 minutos. A tarefa de backups do log de transações é interrompida e reiniciada entre 23h e 1h, para permitir que backups em nível de servidor sejam feitos (aconselhamento da equipe de infraestrutura, talvez para serem revisitados). Não acredito que isso deva ser um problema, pois tenho outro servidor seguindo o mesmo padrão e não tenho problemas com seus backups.

Estou tentando verificar se meus backups estão funcionando corretamente, mas sempre que tento restaurar o backup completo com qualquer um dos backups do log de transações que vieram depois dele, recebo imediatamente o seguinte erro:

Estou tentando restaurar os backups em um servidor separado (mesma versão do SQL Server) cujo objetivo principal agora é testar a integridade do backup do banco de dados. O banco de dados que estou tentando restaurar atualmente não existe nesse servidor.

Se for de alguma relevância, estas são as opções de restauração que estou usando:

Seguindo esta resposta interessante de Aaron Bertrand a uma pergunta semelhante, agora estou investigando algumas coisas com o RESTORE HEADERONLY FROM DISKcomando. Em primeiro lugar, estes são os resultados do meu arquivo de backup completo:

Apenas um banco de dados/arquivo armazenado nesse backup completo.

Além disso, se eu executar RESTORE HEADERONLY FROM DISKmeu primeiro arquivo de backup do log de transações após o último backup completo, para o próprio arquivo de backup completo e o último arquivo de backup do log de transações antes do último backup completo, observo os seguintes LSNs:

Eu pensei que DatabaseBackupLSNtodos os backups deveriam corresponder ao FirstLSNúltimo backup completo anterior a esses backups. É estranho para mim que todos eles pareçam apontar para um backup completo mais antigo.

Quando tento gerar os scripts da restauração que o SSMS está tentando fazer, estranhamente recebo o mesmo erro como uma dica de ferramenta e não me dá os scripts:

Após uma inspeção mais detalhada, quando seleciono todos os meus backups, incluindo o completo, o SSMS remove automaticamente o backup completo e carrega apenas os backups do log de transações para restauração. Eu realmente acho que há uma espécie de cadeia de backup quebrada que está confundindo o SSMS:

Se eu selecionar apenas meu backup completo para restaurar, ele me permitirá clicar no botão de script e este é o script apenas para o backup completo:

USE [master]
RESTORE DATABASE [MyDatabase] FROM  DISK = N'P:\MyDatabase\MyDatabase_backup_2021_09_16_223001_3540051.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

Além disso, meu plano de manutenção não está definido para "backups somente cópia":

Para referência, aqui está o script T-SQL para um dos bancos de dados no plano de manutenção de backup completo:

BACKUP DATABASE [master] TO  DISK = N'\\OurDomain\Shares\SQL Backups\Full Backups\master\master_backup_2021_09_19_004931_6111907.bak' WITH  RETAINDAYS = 14, NOFORMAT, NOINIT,  NAME = N'master_backup_2021_09_19_004931_6111907', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

Se eu fizer o script manualmente de uma restauração do meu backup completo e do backup do log de transações mais próximo que ocorreu após esse backup completo, ele restaurará o backup completo e deixará o banco de dados, NORECOVERYmas o backup do log de transações falhará com o seguinte erro:

Estes são os scripts manuais que escrevi:

USE [master]

RESTORE DATABASE [MyDatabase] 
FROM DISK = N'P:\MyDatabase\MyDatabase_backup_2021_09_16_223001_3540051.bak'
WITH NORECOVERY;

RESTORE LOG [MyDatabase]
FROM DISK = N'P:\MyDatabase\MyDatabase_backup_2021_09_16_223001_4008446.trn'
WITH RECOVERY;

Estranhamente, se eu tentar restaurar o backup do log de transações mais próximo que ocorreu antes do backup completo, recebo a mesma mensagem de erro exata referenciando exatamente os mesmos LSNs. Então é como se meus backups de log de transações não estivessem seguindo a mesma cadeia de backup que meu backup completo?

A única outra informação relevante que consigo pensar é que esses planos de manutenção de backup foram configurados originalmente em uma instância do SQL Server 2016. O servidor dessa instância travou e criamos um novo servidor com uma instância do SQL Server 2019. Eu restaurei todos os bancos de dados de usuários e sistemas de backups (ironicamente) para a nova instância do SQL Server 2019. (Os bancos de dados do sistema que tive que restaurar primeiro em uma instância de 2016 e fazer uma atualização in-loco antes de permitir restaurá-los no novo servidor de 2019.) O único banco de dados que não consegui restaurar para o novo servidor com êxito foi o masterbase de dados. Todo o resto parece estar funcionando bem até agora.

Eu preciso dividir uma string delimitada por vírgulas, manipulá-la e concatená-la novamente em uma única string mantendo a ordem original dos dados (se possível).

Por exemplo, pegue uma lista de definição de coluna de uma CREATE TABLEinstrução (como uma string) como 'BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)'. Eu gostaria de dividir a lista delimitada por vírgulas em um conjunto de resultados, como usar a função interna do SQL Server STRING_SPLIT()assim: SELECT TRIM([Value]) AS CoolDataPeople FROM STRING_SPLIT('BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)', ',').

Sem especificar uma ORDER BYcláusula, isso repetidamente produz (por coincidência?) os seguintes resultados que parecem ser ordenados pela mesma ordenação que estão na string:

Depois de ter o resultado definido acima, quero aplicar algumas manipulações de string adicionais a cada linha (como anexar algum texto constante) e, em seguida, concatenar cada linha de volta com uma função como STRING_AGG()(adeus dias de STUFF ... FOR XML PATH:) na mesma ordem que o corda original. Então, um exemplo do meu resultado final poderia ser 'BrentOzarColumn INTEGER SQLROX, PaulWhiteColumn DATETIME SQLROX, ErikDarlingColumn VARCHAR(100) SQLROX'.

Em última análise, minha pergunta é: Os resultados da STRING_SPLIT()função são retornados em uma ordem determinística? Eu sei que sem uma ORDER BYcláusula, a ordenação não é garantida ao selecionar um conjunto de dados como a Tableou View, mas queria saber se há uma diferença com as funções?

Enquanto digito isso, tenho um palpite de que a resposta é não, a ordenação não é determinística, portanto, não tenho garantia da ordem dos resultados. Além disso, estou apostando que há um possível não determinismo adicional adicionado para cada função que executo em cima dos resultados, especialmente quando os combino novamente com STRING_AGG(). (Independentemente da resposta, agradeço sua ajuda e todos vocês são pessoas legais com dados. ;)

Eu tenho uma configuração de publicação de replicação de instantâneo no servidor 1. O servidor 2 assina a publicação no servidor 1. Os dados estão sendo replicados com êxito.

O trabalho do Agente Distribuidor e do Agente para o Assinante do Servidor 2 é executado no Servidor 1 (onde reside o banco de dados do Distribuidor).

Criei um procedimento armazenado no Servidor 2 para descartar (pré-replicação) e criar (pós-replicação) entidades vinculadas ao esquema no banco de dados de assinantes do Servidor 2. (Eu fiz isso com sucesso no passado porque a Snapshot Replication pode parar se houver entidades no banco de dados do assinante que estejam vinculadas ao esquema às entidades replicadas.)

Eu também criei um script que executa este procedimento armazenado do Servidor 1 por meio de um servidor vinculado ao Servidor 2. (Verifiquei se o servidor vinculado está configurado corretamente e posso consultar dados e executar manualmente o procedimento armazenado por meio do servidor vinculado do Servidor 1 para o servidor 2.) Adicionei este script às propriedades "Snapshot" da publicação de instantâneo:

Inicialmente, recebi um erro no trabalho do Snapshot Agent porque meu Snapshot Agent não tinha acesso de compartilhamento de arquivos ao caminho nos scripts, mas adicionei essa conta e, em seguida, o erro desapareceu e o trabalho do Snapshot foi concluído com êxito agora.

Mas as entidades no script SQL não são descartadas ou criadas e não vejo erros em nenhum lugar. Eu também tentei mover o arquivo de script antes e depois para um compartilhamento de pasta local para o Servidor 1 e referenciá-lo com um caminho UNC (em oposição ao caminho de rede na captura de tela) e ainda não houve alteração no comportamento.

Atualizei o script TEST.sql com um teste bem simples com o seguinte código:

INSERT INTO DatabaseA.dbo.Table1 (Field1) -- DatabaseA is on Server1, so it should insert to the local table
SELECT 'Test' AS Field1

Quando executo novamente o Snapshot Agent, ele ainda é executado com êxito, mas a tabela não é inserida (no Servidor 1).

Por que meus pré e pós-scripts não estão sendo executados, mas também não estou recebendo nenhum erro?

(Também tenho outra publicação para replicação transacional no servidor 1 que está sincronizando dados perfeitamente com o servidor 2 no mesmo banco de dados que a replicação de instantâneo. Publicação Transacional no Servidor 1, ainda não recebo erros e o SQL não é executado.)

Edit: Eu também tentei mover o arquivo de script para uma pasta local do Servidor 1, dando ao Snapshot Agent acesso de leitura a essa pasta e arquivo de script e re-executando o Snapshot Agent, e ainda nada. Sem execução do script e sem erros.

MAS, curiosamente, se eu modificar manualmente o próprio trabalho do Subscription Agent e adicionar uma etapa para executar a mesma consulta SQL exata, ele funcionará.

Também notei que o trabalho do SQL Agent é executado em uma conta SQL diferente "sqlmin" do que eu esperava para determinadas etapas (novas etapas que criei ou a primeira etapa do trabalho chamada "mensagem de inicialização do Snapshot Agent" - acho que porque essas etapas não são t do tipo "REPL-Distribuidor"). Mas as outras etapas pré-existentes executam como o SQL Agent que designei, que é "DBReplication". A conta "sqlmin" que conheço tem permissões SQL mínimas no Servidor 1 e no Servidor 2, pode ser por isso que os scripts de pré e pós-replicação não estão sendo executados (mas também não estou recebendo um erro em nenhum lugar) quando eu definir os scripts nos campos pré e pós das propriedades Snapshot Publication - Snapshot?

Também detalhes adicionais sobre a conta do distribuidor (que também é minha conta do Snapshot Agent): O distribuidor é uma conta do Windows AD que possui um LOGIN SQL no Servidor 1 e no Servidor 2 e com a função SQL de db_owner na origem e no destino replicados bancos de dados, bem como db_owner no banco de dados de distribuição (que reside no Servidor 1).

A história de fundo:

Tenho uma tabela com a seguinte estrutura:

CREATE TABLE WideTable1
(
    BoringColumn1,
    BoringColumn2,
    CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    PagerPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    IsActive YN(CHAR(1)), -- User-defined types, see below
    BoringColumn3,
    ...
    BoringColumn350
)

A consulta original que consumia essa tabela estava executando algumas funções SQL em CellPhoneNumberColumn e PagerPhoneNumber e, posteriormente, usando a saída dessas funções como predicados em outra consulta. NULLIF(LTRIM(ISNULL(CellPhoneNumberColumn, PagerPhoneNumberColumn)))foi um exemplo disso.

As colunas também são todos os tipos definidos pelo usuário, neste caso CellPhoneNumberColumn e PagerPhoneNumberColumn são definidos como UDT Phone(VARCHAR(20)), portanto, seu tipo de dados subjacente é VARCHAR(20). E IsActive é definido como YN(CHAR(1)) então é na verdade um CHAR(1).

Além disso, para complicar ainda mais as coisas, o agrupamento desta tabela/banco de dados é Latin1_General_BIN.

Para encurtar a história, a consulta de consumo original estava enfrentando problemas de estimativa de cardinalidade. Na tentativa de aliviar o problema, criei uma visualização de índice nas colunas acima e funções SQL sendo aplicadas a elas com a seguinte definição (*observe que não criei essa lógica original, apenas tentando corrigir o desempenho dela):

CREATE VIEW PhoneNumbersNormalized WITH SCHEMABINDING AS

SELECT 
    NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20)))) AS Cell,
    SUM(CASE WHEN CAST(IsActive AS CHAR(1)) = 'Y'THEN 1 ELSE 0 END) AS IsActive 
FROM dbo.WideTable1
GROUP BY NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20)))) 

Também criei os seguintes índices na view indexada PhoneNumbersNormalized:

CREATE UNIQUE CLUSTERED INDEX IXV_PhoneNumbersNormalized_Cell ON dbo.PhoneNumbersNormalized(Cell)
CREATE NONCLUSTERED INDEX IXV_NC_PhoneNumbersNormalized_Cell_IsActive ON  dbo.PhoneNumbersNormalized(Cell, IsActive)

O problema: quando seleciono na exibição indexada PhoneNumbersNormalized com o Plano de Execução Real incluído, notei que o plano de execução menciona especificamente a tabela subjacente original WideTable1 como de onde os dados estão vindo.

Além disso, se eu selecionar na exibição indexada PhoneNumbersNormalized com uma dica indexada no índice não clusterizado que criei acima IXV_NC_PhoneNumbersNormalized_PhoneNumber_IsActive, o plano de execução não mostra nenhuma menção a esse índice não clusterizado sendo usado, em vez disso, ele diz que uma verificação de índice clusterizado é o que está fazendo (observe que ofusquei o original nome da tabela, na verdade não é chamado WideTable1 no meu servidor):

Plano de execução colado: https://www.brentozar.com/pastetheplan/?id=HJytxh2UP

Por que o plano de execução está sempre mostrando a tabela subjacente original em vez da exibição indexada e sempre usando o índice clusterizado na tabela subjacente, mesmo quando minha consulta de seleção na exibição indexada usa uma dica de índice para forçar o uso do índice não clusterizado ?

Eu tenho um banco de dados (de um aplicativo de fornecedor) que por algum motivo desconhecido tem um UDDT em quase todas as colunas, mesmo quando o UDDT é logicamente o mesmo que o tipo subjacente que ele implementa. Por exemplo, uma coluna neste banco de dados pode ser de UDDT bTinyInt(TinyInt).

Duas tabelas neste banco de dados têm colunas que compartilham exatamente o mesmo UDDT. Por exemplo TableA.Field1, é do tipo bVarchar(VARCHAR(10))e TableB.Field2também é bVarchar(VARCHAR(10)).

Ao unir essas duas tabelas pelos campos mencionados, observo um aviso no plano de execução informando um problema de cardinalidade porque esses campos estão sendo convertidos implicitamente para VARCHAR(MAX).

É sempre o caso de UDDTs serem convertidos implicitamente quando usados ​​como predicados, mesmo ao comparar o mesmo UDDT?

Também não tenho certeza se isso importa também, mas o banco de dados ao qual essas duas tabelas pertencem é de agrupamento Latin1_General_BIN.

Eu tenho um banco de dados com um agrupamento com distinção entre maiúsculas e minúsculas porque os dados dentro dele devem ser tratados como diferenciando maiúsculas de minúsculas no contexto de como são usados.

As entidades e seus nomes de coluna nesse mesmo banco de dados não precisam diferenciar maiúsculas de minúsculas e preferimos que não sejam para fins de consulta.

É possível manter os próprios dados com distinção entre maiúsculas e minúsculas, mas permitir que as entidades não façam distinção entre maiúsculas e minúsculas no mesmo banco de dados?

Eu tenho uma tabela bastante grande (500 milhões de linhas, 30 colunas de largura, cerca de 130 GB de dados no total). Uma das colunas dessa tabela é um tipo de dados DateTime e preciso selecionar repetidamente os registros dessa tabela em um determinado intervalo de datas. Parece um bom candidato para um índice filtrado para mim.

Este é o meu índice filtrado:

CREATE NONCLUSTERED INDEX IX_Filtered_Table1_DateField1 
ON Table1 (DateField1, PrimaryKeyField) 
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

Esta é a minha consulta:

DECLARE @MinPrimaryKeyId BIGINT = 2854868995

SELECT TOP 500000 PrimaryKeyField
INTO #Results
FROM Table1 WITH(INDEX(IX_Filtered_Table1_DateField1))
WHERE DateField1 >= '2/24/20'
and DateField1 < '4/14/20'
and PrimaryKeyField > @MinPrimaryKeyId
ORDER BY PrimaryKeyField ASC

Ao verificar o plano de execução, percebo que ele está usando meu índice filtrado, mas o padrão é uma operação de verificação de índice em vez de uma busca de índice.

Se eu tentar usar a dica de consulta FORCESEEK (além da minha dica de índice), recebo o erro clássico:

O processador de consulta não pôde produzir um plano de consulta devido às dicas definidas nesta consulta

Por que o processador de consultas não poderia gerar um plano de execução usando uma operação Seek nesse índice quando pode fazer uma operação Scan com ele? (Se eu trocar minha dica de consulta FORCESEEK por um FORCESCAN, ela funcionará, o que eu sei que não é diferente de não usar uma dica aqui.)

Estou fazendo alguns testes de indexação columnstore em uma única tabela que tem cerca de 500 milhões de linhas. Os ganhos de desempenho em consultas agregadas foram incríveis (uma consulta que antes levava cerca de 2 minutos para ser executada agora é executada em 0 segundos para agregar a tabela inteira).

Mas também notei que outra consulta de teste que aproveita a busca em um índice rowstore existente na mesma tabela agora está sendo 4x mais lenta do que antes antes de criar o índice columnstore. Posso demonstrar repetidamente ao descartar o índice columnstore que a consulta rowstore é executada em 5 segundos e, adicionando novamente no índice columnstore, a consulta rowstore é executada em 20 segundos.

Estou de olho no plano de execução real para a consulta de índice rowstore, e é quase exatamente o mesmo em ambos os casos, independentemente de o índice columnstore existir. (Ele usa o índice rowstore em ambos os casos.)

A consulta de teste do rowstore é:

SELECT *
INTO #TEMP
FROM Table1 WITH (FORCESEEK)
WHERE IntField1 = 571
    AND DateField1 >= '6/01/2020'

O índice rowstore usado nesta consulta é:CREATE NONCLUSTERED INDEX IX_Table1_1 ON Table1 (IntField1, DateField1) INCLUDE (IntField2)

A consulta de teste columnstore é:

SELECT COUNT(DISTINCT IntField2) AS IntField2_UniqueCount, COUNT(1) AS RowCount
FROM Table1
WHERE IntField1 = 571 -- Some other test columnstore queries also don't use any WHERE predicates on this table
    AND DateField1 >= '1/1/2019' 

O índice columnstore é:CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table1_2 ON Table1 (IntField2, IntField1, DateField1)

Aqui está o plano de execução para a consulta de índice rowstore antes de criar o índice columnstore:

Aqui está o plano de execução para a consulta de índice rowstore depois de criar o índice columnstore:

As únicas diferenças que noto entre os dois planos é que o aviso da operação Sort desaparece após a criação do índice columnstore e os operadores Key Lookup e Table Insert (#TEMP) demoram significativamente mais.

Aqui estão as informações da operação Sort com o aviso (antes de criar o índice columnstore):

Aqui estão as informações da operação Sort sem o aviso (depois de criar o índice columnstore):

Eu teria pensado que uma consulta de leitura que está aproveitando especificamente o mesmo índice de armazenamento de linhas e plano de execução em ambos os casos deveria ter aproximadamente o mesmo desempenho em cada execução, independentemente de quais outros índices existem nessa tabela. O que dá aqui?

Edit: Aqui estão as estatísticas de TIME e IO antes de criar o índice:

Aqui estão as estatísticas depois de criar o índice columnstore:

Meu servidor está executando o SQL Server 2016. O ambiente é de carga de trabalho bastante alta com muitas transações de gravação e leitura dos dados ao longo do dia. Eu tenho um palpite de que o servidor não está provisionado com memória suficiente e gostaria de investigar e ver se é assim. Quais são as melhores maneiras de determinar se e quanta contenção está sendo gerada pela quantidade de memória disponível no servidor?

Dei uma olhada no DMV sys.dm_os_wait_stats e, quando classificado por wait_tasks_count desc, os dois principais tipos de espera são "MEMORY_ALLOCATION_EXT" e "RESERVED_MEMORY_ALLOCATION_EXT" por uma ordem de magnitude maior do que qualquer outra contagem de tarefas do tipo de espera. Existem outros lugares que eu possa verificar também quanto à pressão ou contenção da memória?

Edit: O tamanho total de todos os bancos de dados neste servidor é de 3 TB, o banco de dados principal com a maioria das transações é de 2 TB e a quantidade total de RAM no servidor é de 32 GB .

Edit 2: Aqui estão os resultados do contador Lazy writes / second perfmon ao longo de um dia:

Meu entendimento de como os índices rowstore regulares são armazenados está em um tipo de estrutura de dados b-tree, mas eu queria saber, devido à natureza diferente dos índices columnstore, que tipo de estrutura de dados subjacente é usada?

Se eu fizer backup regularmente de todo o meu servidor onde reside uma instância SQL e banco de dados e restaurá-lo em uma máquina diferente, posso usar os backups de banco de dados que ocorreram (no servidor original) após a geração do snapshot completo do servidor, para trazer o novo servidor restaurado atualizado?

Quais tipos de backups de banco de dados eu precisaria estar disponível para poder fazer isso? Suponho que se eu tiver um backup completo do banco de dados atual, isso seria suficiente, mas se meu último backup completo fosse tão antigo (ou até mais antigo) que o instantâneo do servidor, eu poderia usar esse backup completo + qualquer diferencial , log de transações, etc backups que vieram após esse backup completo para atualizar o instantâneo do servidor no servidor recém-restaurado?

Exemplo (para simplificar, vamos supor que cada um desses backups seja concluído instantaneamente):

O servidor 1 tem um instantâneo de ponto no tempo completo do servidor tirado às 17h30 todos os dias.

O banco de dados A no servidor 1 passa por backups completos todas as noites às 23h30 todos os dias.

O banco de dados A também tem backups diferenciais de rotina feitos a cada hora durante todo o dia.

O banco de dados A também tem backups de log de transações feitos a cada 15 minutos durante todo o dia.

Cenário 1:

O servidor 1 falha às 23h50.

O servidor 2 é restaurado a partir do backup de instantâneo de ponto no tempo completo do servidor 1 a partir das 17h30 do dia anterior.

Podemos usar o backup completo (do servidor 1) do banco de dados A das 23h30 (20 minutos antes da falha) para restaurar o banco de dados como estava às 23h30 e, em seguida, usar o backup do log de transações das 23h45 trazer o banco de dados para o mais recente possível (no Servidor 2)?

Cenário 2:

O servidor 1 falha às 23h20.

O servidor 2 é restaurado a partir do backup de instantâneo de ponto no tempo completo do servidor 1 a partir das 17h30 do dia anterior.

Podemos usar o backup completo do banco de dados A da noite anterior às 23h30 (já que não conseguimos fazer um hoje ainda antes da falha) e, em seguida, todos os backups diferenciais de hora em hora entre 23h30 da noite anterior até 23:00 da noite e, em seguida, o backup do log de transações das 11:15 para trazer o banco de dados o mais recente possível (no Servidor 2)?

Existe uma maneira mais eficiente de restaurar em qualquer cenário acima e/ou uma maneira melhor de processar os tipos e a frequência dos backups de banco de dados?

Posso ter várias réplicas RW de um banco de dados AlwaysOn Availability Group em diferentes servidores? (Ou seja, replicação bidirecional para que vários servidores possam gravar no mesmo banco de dados.)