dba

Estou no meio da migração de um banco de dados para o Azure SQL Database. Eu estava lendo sobre as diferenças entre o Azure SQL Database e o Azure Managed Instance e notei que as funções CLR NÃO são suportadas no Azure SQL Database.

Isso me surpreendeu, pois já carreguei uma versão de teste do banco de dados no Azure SQL Database e ninguém reclamou de problemas. Dei uma olhada e, com certeza, o assembly CLR está lá e executar consultas que usam as funções CLR funciona muito bem.

Então fui pesquisar na web, e todas as informações que vejo são que o CLR não funciona com o Azure SQL Database. No entanto, aqui estou eu, com um assembly CLR funcionando no Azure SQL Database.

Então agora estou pensando, digo à empresa que temos que mudar para usar a Instância Gerenciada porque esse é o único lugar onde o CLR é suportado? Ou continuo com o Banco de Dados SQL do Azure porque está funcionando bem? Ou esqueci de alguma coisa? A Microsoft reativou o CLR sem avisar ninguém?

Eu tenho um banco de dados que possui colchetes em seu nome. (Não pergunte! :-\)

Ele aparece no SSMS, mas não no Azure porque foi cancelado durante a criação. Não aparece na listagem do banco de dados no Azure. Ele aparece no SSMS, mas não pode ser excluído clicando com o botão direito. Gostaria de eliminá-lo usando SQL, mas não consigo descobrir a sintaxe para incluir os colchetes como caracteres para o nome do banco de dados, em oposição ao caractere de escape/delimitador.

Eu tentei o seguinte sem sucesso:

descartar banco de dados [xxx_2024-07-24]

Msg 911, Nível 16, Estado 1, Linha 1 Banco de dados 'xxx_2024-07-24' não existe. Certifique-se de que o nome foi digitado corretamente.

descartar banco de dados [[xxx_2024-07-24]]

Msg 105, Nível 15, Estado 1, Linha 1 Aspas não fechadas após a sequência de caracteres '[xxx_2024-07-24]'. Msg 102, Nível 15, Estado 1, Linha 1 Sintaxe incorreta próxima a '[xxx_2024-07-24]'.

descartar banco de dados [[[xxx_2024-07-24]]]

Msg 911, Nível 16, Estado 1, Banco de dados da linha 1 '[[xxx_2024-07-24]' não existe. Certifique-se de que o nome foi digitado corretamente.

Alguma ideia sobre a sintaxe correta para eliminar esse banco de dados? [xxx_2024-07-24]

(É mais uma questão de documentação do que de comportamento . Foi migrado do Stack Overflow como foi sugerido lá.)

Ao pesquisar a resposta para outra pergunta que exigia a extração de nós XML na ordem original dos elementos, me deparei com várias respostas ( aqui , aqui e aqui ) que usavam expressões do formato ROW_NUMBER() OVER (ORDER BY xml.node)e afirmavam ou implicavam que os valores de número de linha atribuídos seriam atribuído na ordem do documento XML.

No entanto, não consigo encontrar nenhum lugar que defina o comportamento do ORDER BY xml.node. Embora pareça permitido na OVER()cláusula, a documentação não menciona especificamente os nós XML.

Por exemplo, dado:

DECLARE @xml XML = '<root>
<node>One</node>
<node>Two</node>
<node>Three</node>
<node>Four</node>
</root>'

SELECT
    ROW_NUMBER() OVER(ORDER BY xml.node) AS rn,
    xml.node.value('./text()[1]', 'varchar(255)') AS value
FROM @xml.nodes('*/node') xml(node)
ORDER BY
    ROW_NUMBER() OVER(ORDER BY xml.node)

Os seguintes resultados são retornados:

rn | value
----------
1  | One
2  | Two
3  | Three
4  | Four

Pergunta: Esses resultados são garantidos em algum lugar da documentação? Isso é aceito como comportamento garantido, mas não documentado? Ou este é outro caso como ORDER BY (SELECT NULL)esse que parece funcionar de forma anedótica para pequenos conjuntos de dados de origem aparentemente pré-ordenados, mas pode eventualmente falhar quando ampliado? A razão pela qual estou perguntando é que prefiro não recomendar o uso de uma técnica cujo comportamento e confiabilidade não são suportados pela documentação.

Curiosamente, embora um nó XML seja permitido em um window ORDER BY, ele não é permitido em um SELECT ... ORDER BY. Quando usado em uma cláusula select order-by comum, ORDER BY xml.nodegera o seguinte erro:

Msg 493 Nível 16 Estado 1 Linha 7

A coluna 'node' que foi retornada do método nodes() não pode ser usada diretamente. Ele só pode ser usado com um dos quatro métodos de tipo de dados XML, exist(), nodes(), query() e value(), ou em verificações IS NULL e IS NOT NULL.

A mensagem de erro acima não lista a função de janela OVER(ORDER BY ...)como um uso permitido.

Veja este db<>fiddle .

Eu tenho as seguintes tabelas em meu banco de dados.

Tabela de Sobrenomes

CREATE TABLE [dbo].[LastNames](
    [LastNameID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_LastNames_LastName] ON [dbo].[LastNames]
(
    [LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Tabela de nomes

CREATE TABLE [dbo].[FirstNames](
    [FirstNameID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_FirstNames_FirstName] ON [dbo].[FirstNames]
(
    [FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Tabela PessoasAnon

CREATE TABLE [dbo].[PersonsAnon](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [LastNameID] [int] NOT NULL,
    [FirstNameID] [int] NOT NULL,
    [Info1] [bit] NULL,
    [Info2] [char](1) NULL,
    [Info3] [nchar](50) NULL,
    [AdressID] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_PersonsAnon_PersonID_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [PersonID] ASC,
    [LastNameID] ASC,
    [FirstNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_PersonsAnon_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [LastNameID] ASC,
    [FirstNameID] ASC
)
INCLUDE([PersonID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Geração de dados

Para aqueles que possuem um banco de dados AdventureWorks2014 disponível, usei as seguintes instruções para preencher as tabelas:

INSERT INTO dbo.LastNames (LastName) SELECT DISTINCT app.LastName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.FirstNames (FirstName) SELECT DISTINCT app.FirstName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.PersonsAnon (LastNameID, FirstNameID)
SELECT ln.LastNameID, fn.FirstNameID FROM LastNames ln CROSS APPLY FirstNames fn 

A declaração

A seguinte declaração foi usada para ver que tipo de plano de execução seria criado:

SELECT pa.PersonID,
       fn.FirstName,
       ln.LastName,
       pa.Info2
FROM   PersonsAnon AS pa
       JOIN LastNames AS ln
            ON  pa.LastNameID = ln.LastNameID
       JOIN FirstNames AS fn
            ON  pa.FirstNameID = fn.FirstNameID
WHERE  ln.LastName LIKE 'Pet%'
       AND fn.FirstName LIKE 'John%'
ORDER BY
       ln.LastName,
       fn.FirstName;

O Plano de Execução de Consulta

Colar o plano

Questões

Por que o JOIN é realizado entre Sobrenomes e Nomes mesmo que não haja predicado JOIN? O que está fazendo com que o QO JUNTE as duas tabelas?

Situação Gráfica

Aqui está o que parece:

E os detalhes mostram:

Artigos já consultados

• Por que isso diz que não há predicado de junção?
• O que exatamente significa “No Join Predicate” no SQL Server?
• Devo ficar alarmado com este aviso NO JOIN PREDICATE?
• Por que isso diz que não há predicado de junção?

Sempre pensei que a função CONCAT fosse na verdade um wrapper sobre o + (Concatenação de Strings) com algumas verificações adicionais para facilitar nossa vida.

Não encontrei nenhum detalhe interno sobre como as funções são implementadas. Quanto ao desempenho, parece que há sobrecarga para chamar CONCATquando os dados estão concatenando em um loop (o que parece normal, pois há identificadores adicionais para NULLs).

Há poucos dias, um desenvolvedor modificou algum código de concatenação de strings (de +para CONCAT)porque não gostou da sintaxe e me disse que ficou mais rápido.

Para verificar o caso, usei o seguinte código:

DECLARE @V1 NVARCHAR(MAX)
       ,@V2 NVARCHAR(MAX)
       ,@V3 NVARCHAR(MAX);

DECLARE @R NVARCHAR(MAX);

SELECT  @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000)
       ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000)
       ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000);

onde esta é a variante um:

SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); 
SELECT LEN(@R); -- 1200000017

e esta é a variante dois:

SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}'))
SELECT LEN(@R); -- 1200000017

Para strings menores, não há diferenças. Em algum momento, a CONCATvariante se torna mais rápida:

Eu me pergunto se alguém pode compartilhar alguns detalhes internos ou explicar o comportamento, pois parece que pode haver uma regra de que é melhor concatenar strings grandes usando CONCAT.

Versão:

Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64) 23 de agosto de 2023 14:04:50 Copyright (C) 2022 Microsoft Corporation Standard Edition (64 bits) no Windows Server 2019 Standard 10.0 (Build 17763 : ) (Hipervisor)

O script exato é semelhante ao seguinte:

DECLARE @V1 NVARCHAR(MAX)
       ,@V2 NVARCHAR(MAX)
       ,@V3 NVARCHAR(MAX);

DECLARE @R NVARCHAR(MAX);

SELECT  @V1 = REPLICATE(CAST('V1' AS NVARCHAR(MAX)), 50000000)
       ,@V2 = REPLICATE(CAST('V2' AS NVARCHAR(MAX)), 50000000)
       ,@V3 = REPLICATE(CAST('V3' AS NVARCHAR(MAX)), 50000000);



--SELECT @R = CAST('' AS NVARCHAR(MAX)) + '{some small text}' + ISNULL(@V1, '{}') + ISNULL(@V2, '{}') + ISNULL(@V3, '{}'); -- 00:00:45 -- 00:01:22 -- 00:01:20
--SELECT LEN(@R); -- 300000017

SELECT @R = CONCAT('{some small text}',ISNULL(@V1, '{}'), ISNULL(@V2, '{}'), ISNULL(@V3, '{}')) -- 00:00:11 -- 00:00:16 -- 00:00:10
SELECT LEN(@R); -- 300000017

onde estou alterando o último argumento das funções REPLICATE para gerar strings maiores para a concatenação. Então, estou executando cada variante três vezes.

Alguém pode explicar a situação abaixo, onde dois valores aparentemente iguais não são reduzidos DISTINCT?

A consulta acima éSELECT DISTINCT name FROM master.sys.dm_os_spinlock_stats where name = 'SBS_UCS_DISPATCH';

O equivalente SELECT name FROM master.sys.dm_os_spinlock_stats where name = 'SBS_UCS_DISPATCH' GROUP BY name;também faz o mesmo e a adição HAVING COUNT(1) > 1não produz as linhas.

@@VERSIONé Microsoft SQL Server 2019 (RTM-CU13) (KB5005679) - 15.0.4178.1 (X64) 23 de setembro de 2021 16:47:49 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Licenciamento baseado em núcleo (64 bits) no Windows Server 2016 Padrão 10.0 (Construção 14393: )

Digamos

Você tem um procedimento armazenado que aceita matrizes de data e hora, que são carregadas em uma tabela temporária e usadas para filtrar uma coluna de data e hora em uma tabela.

• Pode haver qualquer número de valores inseridos como datas de início e término.
• Os intervalos de datas podem se sobrepor às vezes , mas não é uma condição com a qual eu contaria regularmente.
• Também é possível fornecer datas com horários.

Qual é a maneira mais eficiente de escrever uma consulta para realizar a filtragem?

configurar

USE StackOverflow2013;

CREATE TABLE
    #d
(
    dfrom datetime,
    dto datetime,
    PRIMARY KEY (dfrom, dto)
)
INSERT
    #d
(
    dfrom,
    dto
)
SELECT
    dfrom = '2013-11-20',
    dto =   '2013-12-05'
UNION ALL
SELECT
    dfrom = '2013-11-27',
    dto =   '2013-12-12'; 

CREATE INDEX
    p
ON dbo.Posts
    (CreationDate)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

consulta

O melhor que consegui foi usar EXISTSassim:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE EXISTS
(
    SELECT
        1/0
    FROM #d AS d
    WHERE p.CreationDate BETWEEN d.dfrom
                             AND d.dto
);

O que resulta em um plano de execução bastante triste:

Nested Loops é o único operador de junção disponível, já que não temos um predicado de igualdade.

O que procuro é uma sintaxe alternativa que produza um tipo diferente de junção.

Obrigado!

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.

Estou trabalhando no SQL Server 2019 .

Tenho uma tabela dbo.AllDates onde tenho todas as datas de 1990 a 2050 . Eu tenho outra tabela dbo.ActualExchangeRates onde tenho taxas de câmbio reais para certas moedas nas datas em que a taxa de câmbio é encontrada em determinada fonte.

Estou tentando escrever uma consulta para obter todas as moedas para todas as datas entre 2010 e 2020 . Se a taxa for encontrada, escreva a taxa, caso contrário, escreva NULL .

Dado este cenário e dado o código abaixo, alguém pode me ajudar a entender por que a consulta SELECT não está gerando nenhum resultado ou até mesmo não consegue ver o plano de execução estimado?

CREATE TABLE dbo.AllDates(Date date)
CREATE TABLE dbo.ActualExchangeRates(Date date, Currency char(3), Rate real)

--Query 1: Not generating any results or estimated plan
SELECT      d.Date, m.Currency, c.Rate
FROM        dbo.AllDates d
INNER JOIN  (
    select
      currency,
      '20100101' as mindate,
      '20201231' as maxdate
    from dbo.ActualExchangeRates
    group by currency
) as m on d.date between m.mindate and m.maxdate
LEFT JOIN   dbo.ActualExchangeRates C ON C.Currency = m.Currency and c.Date = d.Date;

Recebo o seguinte erro após a execução da consulta por 9 minutos em tabelas vazias:

Msg 701, Level 17, State 123, Line 5
Não há memória de sistema insuficiente no pool de recursos 'padrão' para executar esta consulta.

Parece que depende da quantidade de memória disponível para o servidor SQL até atingir o erro. Para mim, parece um bug no mecanismo SQL, pois não há dados na tabela.

Agora, eu sei que a consulta acima pode ser escrita de várias maneiras diferentes e outras maneiras podem gerar resultados, mas minha pergunta é por que o SQL Server simplesmente desliga para sempre nessa consulta, mesmo que ambas as tabelas estejam vazias ?

Eu tenho uma tabela em uma Instância SQL do Azure (12.0.2000.8) e encontrei esse comportamento estranho ao consultá-la.

A coluna é definida como Integer, mas não responde aos filtros "WHERE", também a conversão para varchar retorna resultados estranhos. Isso só acontece em uma tabela (até agora).

O que esta acontecendo aqui? é um bug? Estou fazendo algo errado? Devo descartar a tabela e criá-la novamente?

Mais informações: O problema acontece quando eu consulto a tabela. Se eu usar máquinas e clientes diferentes, tenho o mesmo problema (acontece com o JDBC também).

O agrupamento é SQL_Latin1_General_CP1_CI_AS

Obrigado!

Situação

Ao consultar um banco de dados com uma instrução SELECT com um conjunto definido de colunas, os resultados são recebidos em cerca de 21 segundos.

Se houver um asterisco adicional ( , *) no final da lista de conjunto de colunas definido, a consulta retornará os resultados em 2 segundos.

Planos de Execução de Consultas

Os planos de execução diferem significativamente.

Você pode encontrar o plano de execução de consulta real bom e o plano de execução de consulta real ruim com os links do PasteThePlan.

Instrução contendo , * na lista de colunas (no final)

            SELECT    -- DISTINCT -- 27.04.2020
                'SchuelerKlasse' AS EcoQuery,
                VX_PERSON.PER_MAN_ID, VX_PERSON.PER_ID, VX_PERSON.PER_NAME, VX_PERSON.PER_VORNAME, VX_PERSON.PER_LB_PER_ID, 
                VX_PERSON.PER_GESCHLECHT, VX_PERSON.PER_GEBURTSDATUM, VX_PERSON.PER_TELP, VX_PERSON.PER_MAILP, VX_PERSON.PER_NATP, VX_PERSON.PER_VERSICHERTENNUMMER, VX_PERSON.PER_LAND,
                VX_ADRESSE.ADR_STRASSE, VX_ADRESSE.ADR_PLZ, VX_ADRESSE.ADR_ORT,
                VX_KLASSE.KL_CODE, VX_KLASSE.KL_BEZEICHNUNG,
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS, 
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDETYP, VX_KLASSEABSCHNITTSCHUELER.KAS_ABSCHNITTSNR,
                VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE, VX_KLASSE_ZEITRAUM.KLZ_ZR_NR,
                VX_ZEITRAUM.ZR_BEGINN, VX_ZEITRAUM.ZR_ENDE
                ,'' AS FA_CODE
                ,'' AS FA_BEZ_STP, '' AS FA_BEZ_STP_LANG
                , '' AS EcoOrig_FA_CODE, '' AS EcoOrig_FA_BEZ_STP, '' AS EcoOrig_FA_BEZ_STP_LANG
                , VX_ANGEBOT.ANG_BEGINN
            
 ,* 

            FROM 
                ECOLST.VX_KLASSE_ZEITRAUM, 
                ECOLST.VX_PERSON, 
                ECOLST.VX_KLASSE, 
                ECOLST.VX_KLASSEABSCHNITTSCHUELER, 
                ECOLST.VX_ZEITRAUM, 
                ECOLST.VX_ADRESSE 
                , ECOSYS.T_KLASSE
                , ECOLST.VX_ANGEBOT

            WHERE  
                    VX_KLASSE_ZEITRAUM.klz_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSE_ZEITRAUM.klz_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_ang_id = VX_KLASSE.kl_ang_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_per_id = VX_PERSON.per_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS LIKE 'De%'  -- LIKE 'Definitiv%'
                AND VX_PERSON.per_id = VX_ADRESSE.adr_per_id 
                AND VX_PERSON.per_man_id = VX_KLASSE.kl_man_id
                AND VX_KLASSE.KL_ANG_ID = VX_ANGEBOT.ANG_ID
                AND VX_KLASSE.KL_MAN_ID = 15 
                AND VX_KLASSE.KL_ID = T_KLASSE.KL_ID
                AND T_KLASSE.KL_STATUS_ID = 491   -- d.h. TS_CODE.CODE_UP_BEZEICHNUNG = 'AKTIV'
            

                AND VX_KLASSE.KL_KLASSENTYP_ID IN (742,743,1235,1926,2075,2076,2078,2079,2080,2081,2086,2103,2118,2119,2122,2152,2252,2308,2416)
        

                AND VX_PERSON.PER_NP = 1   -- Natürliche Person
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0        -- TRIM() kann erst ab SQL Server 2017 verwendet werden
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0           -- TRIM() kann erst ab SQL Server 2017 verwendet werden
        
 AND VX_ZEITRAUM.zr_beginn <= CONVERT(DATETIME, '20.05.2023', 104) 
 AND VX_ZEITRAUM.zr_ende   >= CONVERT(DATETIME, '14.02.2023', 104) 
 AND VX_PERSON.per_man_id IN ( 15 ) 

                --AND VX_Person.PER_ID IN  (233777,233779)
        

Questões

A recomendação geral é não usar *ao definir a lista de colunas, mas, no meu caso, adicionar o , *à lista de colunas no final acelera significativamente a consulta. (de 21s até 2s)

Não há recomendações de índices ausentes nos planos de execução reais.

Presumo que tenha a ver com colunas específicas retornadas ao usar o , *in the statement , que possivelmente estão incluídas em índices considerados úteis pelo otimizador de consulta, mas não tenho certeza de como identificar essas colunas.

1. Quais índices eu teria que criar para persuadir o SQL Server a executar a instrução de desempenho ruim que não contém nenhuma , *lista de colunas, para usar um plano semelhante à instrução de desempenho, * que contém o adicional na lista de colunas?

2. Teria que analisar todos os índices usados ​​no bom plano de execução e criar índices reduzidos (omitindo certas colunas) para que o otimizador de consulta considerasse usar um plano de boa execução semelhante para a instrução sem o adicional , *?

Soluções experimentadas de acordo com as sugestões

1. OPTION (MIN_GRANT_PERCENT = 10, MAX_GRANT_PERCENT = 15)

   A aplicação da solução acima forneceu apenas um aumento temporário no desempenho por cerca de 1 hora. Depois disso, a consulta voltou ao plano de execução ruim. Eu não tenho ideia do porquê...

2. Nível de compatibilidade do banco de dados

   Alterar o nível de compatibilidade do banco de dados para 110 (SQL Server 2012) com o seguinte comando resultou em um aumento constante de desempenho para a consulta mencionada sem a adição de ,*na lista de colunas.

   USE [master]
   GO
   ALTER DATABASE [ECOWEBBSP] SET COMPATIBILITY_LEVEL = 110
   GO
   

   O plano de execução de consulta com nível de compatibilidade em 110 mostra que o otimizador de consulta escolheu uma abordagem totalmente diferente ao recuperar os dados e não teve nenhum problema em atribuir a quantidade correta de memória (110 MB).

Questão a seguir

Definir o nível de compatibilidade para 110 é minha única opção?

Comentários Adicionais

A Ominous Function fi_kla_is_abschlussklassemencionada na resposta de Erik é acionada pela coluna VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSEna ECOLST.VX_KLASSE_ZEITRAUMexibição. A tabela subjacente chama a função de valor escalar ao recuperar dados. A própria função retorna 0 ou 1, dependendo se o aluno está em uma turma de graduação (1) ou não (0).

No entanto, a função não parece ter tanto impacto na duração da consulta ao executar com o nível de compatibilidade definido como 110 (SQL Server 2012). Consulte o plano de execução da consulta com nível de compatibilidade em 110 para obter detalhes.

tl; dr - Como afirma o título - Se eu desabilitar um índice não clusterizado em uma tabela, as estatísticas vinculadas para esse índice ainda serão usadas?

Estou ciente de que há muitas perguntas sobre "eliminar ou desabilitar índices" ... mas nenhuma que eu possa encontrar que abranja especificamente as estatísticas.

Eu sei que as estatísticas não são alteradas ou alteradas (pelo menos é o que reuni na documentação do MS). Mas minha pergunta é se as estatísticas ainda são usadas .

Como pano de fundo, estou trabalhando em um grande projeto de ajuste de índice. Envolve adicionar/remover índices em centenas de bancos de dados idênticos, mas com padrões de carga de trabalho variados. Coletivamente, há mais de 2 milhões de índices.

Meu primeiro passo é descartar quaisquer índices "não utilizados". No entanto, em vez de descartá-los, estou pensando em apenas desativá-los para que a definição seja mantida. Isso me permitiria registrar em uma tabela a instância, banco de dados, id do objeto e nome/id do índice de qualquer índice desabilitado. Se o desempenho começar a diminuir depois, o índice poderá ser reativado (recriado).

No entanto, se as estatísticas para o índice desabilitado ainda forem usadas para gerar planos... desativá-las não terá o mesmo impacto no desempenho que eliminá-las. Se for esse o caso, desabilitar o índice não é um teste de impacto de desempenho "verdadeiro" e corro o risco de apresentar problemas de desempenho se os índices desabilitados forem eventualmente descartados.

Minha versão do SQL Server é SQL Server 2019 (RTM-CU18). O código de reprodução a seguir requer que um grupo de arquivos na memória seja criado. Para quem está acompanhando, lembre-se de que um grupo de arquivos na memória não pode ser descartado de um banco de dados depois de criado.

Eu tenho uma tabela simples na memória na qual insiro números inteiros de 1 a 1200:

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem] (
    i [int] NOT NULL,
    CONSTRAINT [PK_InMem]  PRIMARY KEY NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );

INSERT INTO [dbo].[InMem]
SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Eu também tenho o seguinte procedimento armazenado compilado nativamente:

GO

CREATE OR ALTER PROCEDURE p1
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM dbo.[InMem] c1
    CROSS JOIN dbo.[InMem] c2
    CROSS JOIN dbo.[InMem] c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO  

O procedimento retorna uma linha quando executado. Na minha máquina, leva cerca de 32 segundos para ser concluído. Não consigo observar nenhum comportamento incomum em termos de uso de memória durante a execução.

Posso criar um tipo de tabela semelhante:

CREATE TYPE [dbo].[InMemType] AS TABLE(
i [int] NOT NULL,
INDEX [ix_WordBitMap] NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON );

bem como o mesmo procedimento armazenado, mas usando o tipo de tabela:

GO

CREATE OR ALTER PROCEDURE p2 (@t dbo.[InMemType] READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM @t c1
    CROSS JOIN @t c2
    CROSS JOIN @t c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO

O novo procedimento armazenado gera um erro após cerca de um minuto:

Msg 701, Nível 17, Estado 154, Procedimento p2, Linha 6 [Batch Start Line 57] Não há memória de sistema suficiente no pool de recursos 'padrão' para executar esta consulta.

Enquanto o procedimento é executado, posso ver a quantidade de memória usada pelo secretário de memória MEMORYCLERK_XTP aumentar para cerca de 2800 MB para o banco de dados consultando o sys.dm_os_memory_clerksdmv. De acordo com o sys.dm_db_xtp_memory_consumersDMV, quase todo o uso de memória parece ser do consumidor "pool de páginas de 64K":

Para referência, aqui está como executei o novo procedimento armazenado. Ele usa as mesmas 1200 linhas da tabela:

DECLARE @t dbo.[InMemType];

INSERT INTO @t (i)
SELECT i
from [dbo].[InMem];

EXEC p2 @t;

O plano de consulta resultante é um plano de loop aninhado simples sem operadores de bloqueio. Por solicitação, aqui está um plano de consulta estimado para o segundo procedimento armazenado.

Não entendo por que o uso de memória aumenta para mais de 2 GB para essa consulta quando uso um parâmetro com valor de tabela. Eu li vários pedaços de documentação e white papers OLTP na memória e não consigo encontrar nenhuma referência a esse comportamento.

Usando o rastreamento ETW, posso ver que o primeiro procedimento gasta a maior parte do tempo da CPU chamando hkengine!HkCursorHeapGetNexte o segundo procedimento gasta a maior parte do tempo da CPU chamando hkengine!HkCursorRangeGetNext. Também posso obter o código-fonte C para ambos os procedimentos. O primeiro procedimento está aqui e o segundo procedimento, com o problema de memória, está aqui . No entanto, não sei ler o código C, então não sei como investigar mais.

Por que um procedimento armazenado simples compilado nativamente usa mais de 2 GB de memória ao executar loops aninhados em um parâmetro com valor de tabela? O problema também ocorre quando executo a consulta fora de um procedimento armazenado.

Estou executando a configuração do SQL Server 2022 RC1 em um AWS i3.16xlarge com 2 soquetes, 2 nós NUMA, 32 processadores lógicos por nó, 64 processadores lógicos no total.

A configuração está recomendando MAXDOP 8:

Mas se você clicar nesse link para configurar o MAXDOP , as recomendações dizem:

Com base nesse artigo da KB, MAXDOP deve ser 16, não 8. Claro, tecnicamente 8 é menor que 16 - mas também é 2, ou 4, ou 15. De onde vem o 8?

Após a conclusão da instalação do SQL Server e a inicialização do serviço, o log mostra que o SQL Server está implementando automaticamente o Soft-NUMA com 4 nós, cada um com 16 processadores lógicos:

Então, novamente, isso indica que MAXDOP deve ser 16.

Isso é um bug, ou eu perdi algo óbvio? Existe outra regra não escrita em algum lugar que a configuração irá parar no MAXDOP 8?

O SQL Server 2016 adicionou o AT TIME ZONEoperador. Da documentação :

A implementação AT TIME ZONE depende de um mecanismo do Windows para converter valores de data e hora entre fusos horários.

AT TIME ZONEchama o mscorlib.ni!TimeZoneInfo.ConvertTimemétodo de acordo com o rastreamento ETW em uma consulta simples. Jonathan Kehayias tem uma postagem no blog onde ele extrai todas as regras de fuso horário da System.TimeZoneInfoclasse. Só consigo encontrar regras que tenham efeito em 01/01/2004 ou posterior na saída:

Rob Farley menciona que em um post de blog que uma mudança de regra de fuso horário no ano 2000 não parece ser respeitada por AT TIME ZONE:

Ele funciona usando o registro do Windows, que contém todas essas informações, mas, infelizmente, não é perfeito quando se olha para trás no tempo. A Austrália mudou as datas em 2008, e os EUA mudaram suas datas em 2005 – ambos os países economizando a luz do dia durante a maior parte do ano. AT TIME ZONE entende isso. Mas não parece apreciar que na Austrália no ano 2000, graças às Olimpíadas de Sydney, a Austrália começou o horário de verão cerca de dois meses antes.

Eu sinto que há uma grande quantidade de evidências circunstanciais de que o AT TIME ZONEoperador pode retornar resultados imprecisos para datas anteriores ao ano de 2004. No entanto, não consigo encontrar nenhuma documentação que AT TIME ZONEuse a System.TimeZoneInfoclasse, que AT TIME ZONEpossa ser imprecisa para datas mais antigas ou que a System.TimeZoneInfoclasse pode ser impreciso para datas mais antigas.

Existe uma limitação do produto SQL Server que resulta no AT TIME ZONEretorno de resultados imprecisos antes do ano de 2004?

Eu tenho uma tabela que consiste em um ID de registro, um ID de grupo (vinculando 1 ou mais registros em um grupo) e um valor de hash para cada registro.

CREATE TABLE HashTable(
    RecordID VARCHAR(255),
    GroupIdentifier VARCHAR(255),
    Hash VARCHAR (255),
    GroupHashList VARCHAR(4000)
)

(Eu sei que esta não é uma tabela eficiente, mas é apenas uma tabela temporária para os propósitos deste exemplo).

Eu quero gerar um hash para cada grupo, então pensei que a maneira mais simples seria concatenar os hashes de cada registro do grupo. RecordIDs são exclusivos, mas o que esses registros se relacionam não são necessariamente exclusivos, portanto, os hashes podem ser duplicados. O objetivo disso é sinalizar grupos totalmente duplicados, ou seja, um grupo onde todos os registros desse grupo são duplicados de todos os registros de outro grupo. A GUI precisa que todos os membros do grupo tenham o mesmo hash para reconhecê-los como um grupo duplicado.

Estou usando STRING_AGG para concatenar os hashes individuais dos registros no grupo e classificá-los pelo hash para garantir que obtenha a mesma sequência de caracteres para grupos duplicados. Na verdade, não me importo com a ordem dos hashes, desde que seja sempre a mesma. Quando executo como uma consulta SELECT, funciona bem e posso ver strings idênticas para grupos duplicados. Quando eu pego essa mesma consulta SELECT e a coloco em uma consulta UPDATE, a ordenação parece se perder.

SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC) 
FROM HashTable
GROUP BY [GroupIdentifier]

Isso fornece os resultados (para um exemplo de par de grupos duplicados):

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697

Quando coloco esse mesmo código na consulta UPDATE, ele não os classifica corretamente:

UPDATE HashTable
SET GroupHashList = c.HashList
FROM HashTable
INNER JOIN (
    SELECT (STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)) AS [HashList],
        [GroupIdentifier]
    FROM HashTable
    GROUP BY [GroupIdentifier]) c
ON c.[GroupIdentifier] = HashTable.[GroupIdentifier]

Resultados para os mesmos dois grupos que são gravados na tabela:

73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697
73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38

o que estou perdendo?

O que estou recebendo pela primeira vez é

Hash1; Hash2; Hash3
Hash1; Hash2; Hash3

Mas quando está na consulta UPDATE, recebo

Hash1; Hash2; Hash3
Hash1; Hash3; Hash2

A consulta de atualização é classificada por ID de registro, embora eu não saiba se isso é coincidência. ( https://dbfiddle.uk/CPG1-z2l )

Acabei de aprender que os índices podem ser desabilitados em uma tabela. Até mesmo o índice clusterizado pode ser desabilitado. Depois disso, os usuários não podem acessar o índice ou os dados se o índice estiver clusterizado. Quando vamos desabilitar um índice? Só não entendo o caso de uso.

Encontrei novamente um problema com o SQL Server e a instrução MERGE e preciso de alguma confirmação.

Posso reproduzir meu problema constantemente em um banco de dados do Azure (mas não em um SQL Server 2017/2019 local).

Por favor, execute os seguintes passos (passo a passo, não em uma execução de comando)!

1) Script para Schema:

    CREATE TABLE [dbo].[ImpactValueHistory]
    (
        [Rn] BIGINT NOT NULL,

        [ImpactId] UNIQUEIDENTIFIER NOT NULL,
        [ImpactValueTypeId] INT NOT NULL,

        [Date] DATE NOT NULL,
        [Value] DECIMAL(38, 10) NOT NULL,

        [ValidFrom] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
        [ValidTo] DATETIME2 NOT NULL CONSTRAINT [DF_ImpactValueHistory_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

        [ImpactPeriodId] INT NOT NULL,

        [NormalizedValue] DECIMAL(38, 10) NOT NULL,
    )
    GO

    CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_ImpactValueHistory]
        ON [dbo].[ImpactValueHistory];
    GO

    CREATE NONCLUSTERED INDEX [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId]
        ON [dbo].[ImpactValueHistory] ([ValidFrom], [ValidTo], [ImpactId], [ImpactValueTypeId], [Date]);
    GO


    CREATE TABLE [dbo].[ImpactValue]
    (
        [Rn] BIGINT NOT NULL IDENTITY(1,1),

        [ImpactId] UNIQUEIDENTIFIER NOT NULL,
        [ImpactValueTypeId] INT NOT NULL,

        [Date] DATE NOT NULL,
        [Value] DECIMAL(38, 10) NOT NULL,

        [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT [DF_ImpactValue_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
        [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT [DF_ImpactValue_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),

        [ImpactPeriodId] INT NOT NULL,

        [NormalizedValue] DECIMAL(38, 10) NOT NULL,

        PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),

        CONSTRAINT [PK_ImpactValue] PRIMARY KEY NONCLUSTERED ([ImpactId], [ImpactValueTypeId], [Date], [ImpactPeriodId])
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ImpactValueHistory]))
    GO

    CREATE UNIQUE CLUSTERED INDEX [IX_ImpactValue_Id] ON [dbo].[ImpactValue]([Rn])
    GO

    CREATE COLUMNSTORE INDEX [CIX_ImpactValue] ON [dbo].[ImpactValue] ([ImpactId], [ImpactValueTypeId], [Date], [Value], [NormalizedValue])
    GO

2) Script para inserir alguns dados aleatórios

DECLARE @inserted0 TABLE ([Date] DATE, [ImpactId] uniqueidentifier, [ImpactPeriodId] int, [ImpactValueTypeId] int);
MERGE [dbo].[ImpactValue] USING (
SELECT TOP 278 -- <-- this number is critical
        DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY [Name]) - 1, '2000-01-01') AS [Date],
        NEWID() AS [ImpactId], 
        1 AS [ImpactPeriodId], 
        1 AS [ImpactValueTypeId], 
        99 AS [Value], 
        99 AS [NormalizedValue]
    FROM [sys].[all_columns]
) AS i ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue]) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Date], [ImpactId], [ImpactPeriodId], [ImpactValueTypeId], [Value], [NormalizedValue])
VALUES (i.[Date], i.[ImpactId], i.[ImpactPeriodId], i.[ImpactValueTypeId], i.[Value], i.[NormalizedValue])
OUTPUT INSERTED.[Date], INSERTED.[ImpactId], INSERTED.[ImpactPeriodId], INSERTED.[ImpactValueTypeId]
INTO @inserted0;

SELECT * FROM @inserted0

Esta etapa deve retornar como resultado todas as linhas inseridas!

3) Excluir dados da Etapa 2) Esta etapa está preenchendo a tabela de histórico configurada

DELETE [dbo].[ImpactValue]

4) Insira novamente alguns dados aleatórios Você pode usar o script da etapa 2)

Devo observar que as etapas 1) - 4) devem ser executadas separadamente e não em arquivos GO.

Novamente esta etapa deve retornar como resultado todas as linhas inseridas! Mas não! Do meu lado sempre recebo um resultado vazio. Isso pode ser reproduzido em três de nossos bancos de dados de produção :(

A instrução MERGE é gerada pelo EF Core e atualmente estou resolvendo isso definindo um Max Batch Size. Mas esta não poderia ser a solução final.

Deve ter algo a ver com Tabelas Temporais com um índice não clusterizado configurado na tabela temporal.

Veja também:

No passado eu já tropecei neste problema:

• https://stackoverflow.com/questions/70734060/why-does-a-merge-into-a-temporal-table-with-a-nonclustered-index-in-the-history .
• https://github.com/dotnet/efcore/issues/22852

Mas meu problema atual só pode ser reproduzido em um Banco de Dados SQL do Azure e não gera nenhum erro.

Notas laterais interessantes:

1. Se eu desabilitar temporariamente a tabela temporal -> está funcionando
2. Se eu excluir o índice não clusterizado [IX_ImpactValueHistory_ValidFrom_ValidTo_ImpactId_DimensionItemId] -> está funcionando
3. Se eu usar SELECT TOP (@BatchSize) na etapa 2) --> está funcionando
4. Se eu usar apenas OUTPUT em vez de OUTPUT INTO @inserted0 --> está funcionando

Sem o índice COLUMNSTORE na tabela de histórico está funcionando. Ao remover apenas o índice COLUMNSTORE na tabela principal, vejo o mesmo problema.

Plano de execução real para um caso que (a) reproduz o problema com TOP 278e (b) não reproduz TOP (@BatchSize)disponível em https://1drv.ms/u/s!AsOa6e9ukBWQlIRg9_9eySDFp5hvEA?e=KBQBsP . Também adicionei os planos de execução reais para o tamanho do lote de 277. Ambos estão trabalhando com esse tamanho!

Uma das melhores proteções contra ransomware é fazer backup de todos os seus arquivos de banco de dados em um sistema completamente separado. O que temos feito.

Mas um pensamento é que o backup do banco de dados poderia agora conter o ransomware. Isso é possível? Este é um .bak criado nativo do SQL Server de 2016. Ou é impossível que o ransomware se incorpore em um arquivo de backup?

Estou procurando um termo genérico, por exemplo, para uma abstração de banco de dados, que inclui todas as estruturas de dados tabulares, como tabelas de banco de dados, exibições, resultados de consultas tabulares também.

Pelo que entendi, 'entidade' não é o termo adequado, pois corresponderia a uma tabela, mas não a uma exibição ou mesmo a uma consulta. 'Resultado' seria contra-intuitivo em estruturas de dados modificáveis.

Qual seria o termo adequado no ambiente profissional?