dba

Pergunta : Por que SUMo retorno é com 4 casas decimais (como esperado), mas o Avgretorno é com 6 casas decimais?

CREATE TABLE #t(Col DECIMAL(19,4))
INSERT #t VALUES (123456.1200),(654321.3400)
SELECT SUM(Col), AVG(Col) FROM #t

Observações : Observe que a questão NÃO é sobre como podemos exibir AverageAmountcom 4 decimais, mas por que AverageAmountestá retornando com 6 casas decimais. Isso é um bug ou há algum motivo válido por trás disso? Estou usando a versão mais recente do SQL Server 2022 no Windows 11

Configurar

-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

Consulta de teste

Exibir 9% das linhas na ordem da chave primária:

SELECT TOP (9e) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

db<>demonstração de violino

Resultados

Plano de execução

Pergunta

Por que o SQL Server classifica a coluna quando o índice clusterizado fornece exatamente essa ordem?

Mais linhas

Se eu aumentar o número de linhas na tabela, obtenho um Eager Spool em vez de uma Sort e o índice é escaneado em ordem:

-- Create a heap table of numbers from 1 to 1,000
SELECT TOP (1000)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

-- 0.9% now
SELECT TOP (9e-1) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

Pegue o seguinte código:

pragma foreign_keys = ON;

create table people(people_id integer primary key, name text not null);
insert into people (name) values ("Mom"), ("Jack the Ripper");

create table family_member(people_id integer primary key references people(people_id));

insert into family_member values ((select people_id from people where name = "Mom"));
insert into family_member values ((select people_id from people where name = "Dad"));  -- silent error here

select name from family_member inner join people using (people_id);
-- Jack the Ripper is part of the family

Como isso é legítimo no sqlite? Nem mesmo um aviso é gerado. Isso é por boas razões, por razões de legado, há algo na documentação sobre isso que eu não encontrei? Para mim, isso é preocupante e acredito que nenhum outro SQL DB se comporte dessa forma.

Existe algum significado para o SQL Server ter BITdatatype em vez de BOOLEAN? É apenas uma maneira estranha de chamar a mesma coisa, ou existe alguma diferença conceitual sutil?

A documentação sobre BITo tipo de dados menciona:

O tipo de dados bit pode ser usado para armazenar valores booleanos.

Isso parece sugerir que, na verdade, elas não são a mesma coisa.

Estou ciente de que [a coisa que a MS chama de boolean] e [a coisa que a MS chama de bit] são diferentes, MAS do ponto de vista do programador, bité a melhor aproximação do booleanconceito platônico que a linguagem T-SQL oferece. E eu luto para acreditar que seja apenas uma casualidade e não uma intenção. O tipo de dado numérico que aceita apenas 0 e 1 parece pouco prático para outros usos.

Observei (e reproduzi) o seguinte cenário com o SQL Server 2022.

O padrão em uso

• o código é executado via sp_executesql (nenhum procedimento armazenado está envolvido)
• A primeira consulta seleciona dados em uma tabela temporária
• Uma instrução DDL cria então um índice clusterizado na tabela temporária. A tabela temporária definitivamente NÃO é armazenável em cache-- primeiro de tudo, isso não é um módulo (sproc ou função), mas também estamos criando um índice depois que a tabela temporária é preenchida. Então eu não esperaria que estatísticas deixadas para trás em um objeto temporário em cache estivessem envolvidas aqui.
• Uma consulta seleciona dados da tabela temporária. Esta consulta obtém otimização COMPLETA a cada vez (não é um plano TRIVIAL)

Este lote pode ser executado tanto para conjuntos de dados pequenos quanto maiores, de modo que a tabela temporária pode ter apenas uma linha ou milhares de linhas.

Esse comportamento normalmente ocorre em um secundário legível. Não há armazenamento de consulta gravável e nenhum plano automático forçando como um fator.

Verifiquei que posso reproduzir o comportamento também na réplica primária. (A correção automática do plano foi instruída a ignorar a consulta e confirmei que não há imposição de plano na primária quando reproduzida.)

Script de reprodução

• Script de configuração - Eu executei isso no SQL Server 2022 CU15. Isso desativa o armazenamento de consultas e usa o nível de compatibilidade 130.
• Consulta de reprodução - Tenho executado isso por meio do SQL Query Stress para poder executá-lo facilmente e simultaneamente em um ou mais threads
• Plan Generation Num e tabelas temporárias - Uma consulta muito simples para observar o plan_generation_num em sys query stats ("Um número de sequência que pode ser usado para distinguir entre instâncias de planos após uma recompilação.") e a lista atual de tabelas temporárias

O que normalmente acontece - e o comportamento que espero

Normalmente, alterar grandes quantidades de linhas na tabela temporária entre execuções de consultas causa automaticamente recompilações, e vejo que a consulta que seleciona os dados da tabela temporária tem uma estimativa de linha correspondente às linhas na tabela temporária.

Quando isso funciona como esperado, o desempenho é bom.

Com a consulta de reprodução : se eu limpar o cache do plano e executar a consulta de reprodução 40 iterações em um único thread no SQL Query Stress, o plan_generation_number acaba sendo 82. Ao amostrar planos de consulta com sp_WhoIsActive, as estimativas de linha que consultam a tabela temporária correspondem ao número de linhas na tabela temporária, conforme esperado.

O que às vezes acontece -- e parece um bug para mim

Em raras ocasiões, vejo que um plano está em uso onde há um plano de estimativa de 1 linha para a tabela temporária, mas uma quantidade muito grande de linhas está realmente na tabela temporária. MUITAS linhas foram alteradas, mas não recompilou automaticamente:

Isso leva a um desempenho muito lento porque o plano de estimativa baixa decide usar um loop aninhado sem pré-busca, o que o torna um queimador de CPU.

Com a consulta de reprodução : Se eu limpar o cache do plano e executar a consulta de reprodução 20 iterações em 2 threads no SQL Query Stress, o plan_generation_number acaba sendo menor que 82 — varia conforme a execução, mas pode ser 72 ou 59, indicando menos recompilações. Enquanto isso está em execução, também posso amostrar ocasiões com sp_WhoIsActive em que há uma única contagem de linhas estimada, mas muito mais linhas na tabela temporária. Captura de tela:

Só consigo reproduzir isso ao executar código de reprodução em várias sessões simultâneas

Não consegui reproduzir esse comportamento com uma única sessão no SQL Server. A única maneira de reproduzir isso é configurar um bloco de código que:

• Executa pelo menos 1 iteração da instrução sp_executesql que tem 1 linha na tabela temporária
• Em seguida, executa 1 iteração da instrução sp_executesql que tem muito mais linhas na tabela temporária

Se eu executar isso em uma única sessão, não consegui reproduzir os problemas. Mas se eu executar isso simultaneamente em quatro ou cinco sessões, ocasionalmente poderei ter o problema "QUE NÃO RECOMPILA COMO DEVERIA" aparecendo. (Observação: usando o SQL Query Stress, posso reproduzir isso com apenas 2 sessões/iterações.)

Isso parece um bug para mim, estou curioso para saber se mais alguém viu. O comportamento de recompilação e estatísticas com tabelas temporárias é super complexo, então pode haver alguma nuance que estou perdendo com a forma como isso funciona com tabelas temporárias não armazenáveis ​​em cache.

PS: Eu acho que tabelas temporárias armazenáveis ​​em cache são geralmente melhores. Só estou tentando descobrir por que esse comportamento aconteceria em um cenário de tabela temporária não armazenável em cache neste momento.

Soluções alternativas

Após adicionar um option (recompile)à consulta, não consigo mais reproduzir a reutilização do plano de 1 linha consultando a tabela temporária. Isso é suficiente, mas estou intrigado sobre o porquê de ser necessário.

Temos um SQL Server 2019 CU18 onde descobrimos um problema estranho com o querystore. Normalmente, o tamanho médio do logbackup por hora é de 40 MB, mas assim que habilitamos o querystore, o tamanho médio do logbackup é de 2,5 GB.

Há (de acordo com o querystore) 140.000 consultas executadas/hora. Isso é cerca de 40 execuções/segundo.

Esta é a configuração do nosso querystore:

ALTER DATABASE [db_name]
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE
        ,CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 45)
        ,DATA_FLUSH_INTERVAL_SECONDS = 900
        ,MAX_STORAGE_SIZE_MB = 2048
        ,INTERVAL_LENGTH_MINUTES = 30
        ,SIZE_BASED_CLEANUP_MODE = AUTO
        ,QUERY_CAPTURE_MODE = AUTO
);

Quando abro um arquivo de logbackup tão grande, fn_dump_dblogvejo que várias transações acontecem no mesmo segundo. Todas as transações têm o nome 'SwapPage'.

A unidade de alocação aponta para plan_persist_runtime_stats.

Após um comentário de Paul White, configurei um Evento Estendido para capturar query_store_index_rebuild_startede query_store_index_rebuild_finished. Para minha surpresa, o querystore estava fazendo reconstruções de índice. Estes são os resultados deste rastreamento:

Parece que a reconstrução do índice foi iniciada em torno de 874 MB, o tamanho máximo do querystore está definido como 2048.

Também incluí o stacktrace do query_store_index_rebuild_startedevento no Evento Estendido.

sqllang!XeSqlPkg::CollectClientHostnameActionInvoke sqllang!XeSqlPkg::CollectDatabaseIdActionInvoke sqllang!XeSqlPkg::CollectDatabaseNameActionInvoke sqllang!XeSqlPkg
::CollectNtUsernameActionInvoke sqllang!XeSqlPkg::CollectSessionIdActionInvoke sqllang!XeSqlPkg::CollectTSqlStack<XE_ActionForwarder> sqllang!XeSqlPkg::CollectTSqlStackActionInvoke qds!XeQdsPkg::query_store_index_rebuild_started::Publicar
qds!CDBQDS::ReclaimFreePages
qds!CDBQDS::DoSizeRetention
qds!CDBQDS::ProcessQdsBackgroundTask
qds!CQDSManager::AcquireGenericQdsDbAndProcess<<lambda_e51628d7833f66b5a045fa5bf2d27953>>
qds!CDBQDS::ProcessQdsBackgroundTask
sqldk!SOS_Task::Param::Executar
sqldk!SOS_Scheduler:: Executar tarefa sqldk
!SOS_Scheduler::ProcessTasks
sqldk!SchedulerManager::WorkerEntryPoint
sqldk!SystemThreadDispatcher::ProcessWorker
sqldk!SchedulerManager::ThreadEntryPoint
KERNEL32+0x17AC4
ntdll+0x5A8C1

Eu esperava descobrir o que está acionando a reconstrução do índice, mas não tive sorte.

Após algumas dicas do Zikato, adicionei alguns eventos extras relacionados ao querystore ao meu trace. Isso mostra que a reconstrução do índice só é acionada se um query_store_size_retention_cleanup_startedevento tiver ocorrido.

Sem reconstrução:

Reconstruir: Toda vez que a limpeza é executada, 0 KB é excluído, mas aparentemente uma reconstrução é necessária. O que me confunde é a aparência do evento de limpeza. Pensei que isso só seria acionado quando o querystore atingisse 90% do tamanho máximo de armazenamento. Aumentar o tamanho máximo do querystore não faz diferença.

Alguém passou pelo mesmo problema ou pode explicar o que está acontecendo? Outros bancos de dados na instância não têm esse problema.

Preciso usar STRING_SPLIT com ordenação habilitada:

Atualizamos todos os nossos servidores para o SQL Server 2022 e alteramos o nível de compatibilidade para 160, mas o código a seguir não está funcionando:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

O erro é:

Msg 8748, Nível 16, Estado 1, Linha 1 O argumento enable_ordinal para STRING_SPLIT suporta apenas valores constantes (não variáveis ​​ou colunas).

O ambiente de produção é:

Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) 
    Nov 14 2023 18:33:19 
    Copyright (C) 2022 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

A versão do SSMS é:

Testamos o código na CU mais recente:

A única coisa que encontrei foi esta resposta apontando que:

O problema é que o SSMS tem IntelliSense/dicas de ferramentas codificadas sem lógica condicional com base na versão, e o código está à frente do mecanismo. Atualmente, a funcionalidade está disponível apenas no Banco de Dados SQL do Azure, Instância Gerenciada e Synapse.

Ainda assim, não tenho certeza de onde está o problema: na documentação, no mecanismo, no SSMS ou em algo que não estou fazendo direito.

Observação: o problema é o mesmo no Azure Data Studio.

(É 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 .

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 )