Martin Smith's questions

Para os dados de exemplo...

/*Quick and dirty generation of some rows of data*/
SELECT value as [orderid], 
       1 as [custid], 
       1 as [empid], 
       1 as [shipperid], 
       getdate() as [orderdate], 
       'abcdefgh' as [filler]
INTO dbo.Orders
FROM generate_series(1,10000000)

CREATE CLUSTERED INDEX [idx_cl_od] ON [dbo].[Orders]
(
    [orderdate] ASC
)

UPDATE STATISTICS dbo.Orders WITH FULLSCAN

E a seguinte consulta

SELECT [orderid], [custid], [empid], [shipperid], [orderdate], [filler]
FROM dbo.Orders
WHERE orderid <=7601715 AND 1=1 /*Prevent simple parameterisation*/

Então, na minha máquina de desenvolvimento (SQL Server 2022, DOP de 4), o custo de E/S da varredura de índice clusterizado é 46.8853independente do plano serial ou paralelo. E o custo de CPU da varredura está 11.0002no plano serial e 2.75004no paralelo. Então, eu esperava que o ponto de inflexão entre os planos fosse quando o operador de paralelismo excedesse 8.25016(um limite atingido quando as linhas estimadas que entram nele são em torno de 4,5 milhões). Na realidade, no ponto em que isso realmente ocorre, o custo para o operador de fluxos de coleta é 13.0501(em torno de 3 milhões de linhas a mais do que eu esperava).

Se o SQL Server não estiver usando o custo geral do plano como ponto de inflexão, qual é a lógica real?

( XML para planos estimados em pastetheplan )

Recentemente, me deparei com um problema em que tSQLtos testes demoravam muito para serem executados.

O procedimento em teste estava fazendo uma junção de 38 tabelas (!) (com 37 tabelas falsificadas e um parâmetro com valor de tabela).

Apenas duas das tabelas falsificadas e o TVP tiveram alguma linha inserida

Os tempos de compilação eram extremamente lentos.

Sinalizador de rastreamento 8675 mostrado

End of simplification, time: 0.002 net: 0.002 total: 0 net: 0.002
end exploration, tasks: 549 no total cost time: 0.013 net: 0.013 total: 0 net: 0.015
end search(0),  cost: 13372.9 tasks: 3517 time: 0.012 net: 0.012 total: 0 net: 0.028
end exploration, tasks: 3983 Cost = 13372.9 time: 0 net: 0 total: 0 net: 0.028
end search(1),  cost: 6706.79 tasks: 10187 time: 0.024 net: 0.024 total: 0 net: 0.052
end exploration, tasks: 10188 Cost = 6706.79 time: 0 net: 0 total: 0 net: 0.052
end search(1),  cost: 6706.79 tasks: 61768 time: 0.165 net: 0.165 total: 0 net: 0.218
*** Optimizer time out abort at task 614400 ***
end search(2),  cost: 6706.79 tasks: 614400 time: 12.539 net: 12.539 total: 12 net: 12.758
*** Optimizer time out abort at task 614400 ***
End of post optimization rewrite, time: 0.001 net: 0.001 total: 12 net: 12.759
End of query plan compilation, time: 0.003 net: 0.003 total: 12 net: 12.762
SQL Server parse and compile time: 
   CPU time = 12735 ms, elapsed time = 12770 ms.

Parece que as linhas estimadas crescem exponencialmente para cada junção entre tabelas vazias até que, no final, a contagem estimada de linhas foi de 135.601.000 e a consulta teve um custo estimado enorme justificando um tempo de compilação mais longo.

Havia uma tabela específica envolvida em muitas dessas junções e a inserção de uma única linha nessa tabela foi suficiente para parar essa explosão nas junções em que a tabela estava envolvida (a saída do estimador de cardinalidade indica que agora está usando o histograma de estatísticas dessa tabela )

O comportamento original parece estranho para mim. O SQL Server sabe que a tabela à qual está se juntando está vazia e o white paper do plano de cache indica que a inserção de qualquer linha em uma tabela vazia fará com que o limite de recompilação seja atingido, então há algum bom motivo para isso?

Repro mostrando o crescimento estimado da contagem de linhas (embora sem longos tempos de compilação)

CREATE TABLE T1(C1 INT);

INSERT INTO T1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE T2(C1 INT, C2 VARCHAR(MAX));

SELECT *
FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1
        LEFT OUTER JOIN T2 T3 ON T3.C1 = T2.C1
        LEFT OUTER JOIN T2 T4 ON T4.C1 = T2.C1
        LEFT OUTER JOIN T2 T5 ON T5.C1 = T2.C1
        LEFT OUTER JOIN T2 T6 ON T6.C1 = T2.C1
        LEFT OUTER JOIN T2 T7 ON T7.C1 = T2.C1
        LEFT OUTER JOIN T2 T8 ON T8.C1 = T2.C1
        LEFT OUTER JOIN T2 T9 ON T9.C1 = T2.C1
        LEFT OUTER JOIN T2 T10 ON T10.C1 = T2.C1
        LEFT OUTER JOIN T2 T11 ON T11.C1 = T2.C1
        LEFT OUTER JOIN T2 T12 ON T12.C1 = T2.C1
        LEFT OUTER JOIN T2 T13 ON T13.C1 = T2.C1
        LEFT OUTER JOIN T2 T14 ON T14.C1 = T2.C1
        LEFT OUTER JOIN T2 T15 ON T15.C1 = T2.C1
        LEFT OUTER JOIN T2 T16 ON T16.C1 = T2.C1
        LEFT OUTER JOIN T2 T17 ON T17.C1 = T2.C1
        LEFT OUTER JOIN T2 T18 ON T18.C1 = T2.C1
        LEFT OUTER JOIN T2 T19 ON T19.C1 = T2.C1
        LEFT OUTER JOIN T2 T20 ON T20.C1 = T2.C1
        LEFT OUTER JOIN T2 T21 ON T21.C1 = T2.C1
        LEFT OUTER JOIN T2 T22 ON T22.C1 = T2.C1
        LEFT OUTER JOIN T2 T23 ON T23.C1 = T2.C1
        LEFT OUTER JOIN T2 T24 ON T24.C1 = T2.C1
        LEFT OUTER JOIN T2 T25 ON T25.C1 = T2.C1
        LEFT OUTER JOIN T2 T26 ON T26.C1 = T2.C1
        LEFT OUTER JOIN T2 T27 ON T27.C1 = T2.C1
        LEFT OUTER JOIN T2 T28 ON T28.C1 = T2.C1
        LEFT OUTER JOIN T2 T29 ON T29.C1 = T2.C1
        LEFT OUTER JOIN T2 T30 ON T30.C1 = T2.C1
        LEFT OUTER JOIN T2 T31 ON T31.C1 = T2.C1
        LEFT OUTER JOIN T2 T32 ON T32.C1 = T2.C1
        LEFT OUTER JOIN T2 T33 ON T33.C1 = T2.C1
        LEFT OUTER JOIN T2 T34 ON T34.C1 = T2.C1
        LEFT OUTER JOIN T2 T35 ON T35.C1 = T2.C1
        LEFT OUTER JOIN T2 T36 ON T36.C1 = T2.C1
        LEFT OUTER JOIN T2 T37 ON T37.C1 = T2.C1
        LEFT OUTER JOIN T2 T38 ON T38.C1 = T2.C1
        LEFT OUTER JOIN T2 T39 ON T39.C1 = T2.C1

Eu tenho um arquivo de dados de exemplo com o seguinte conteúdo e salvo com codificação UTF8.

oab~opqr
öab~öpqr
öab~öpqr

O formato deste arquivo é de largura fixa com as colunas 1 a 3 sendo cada uma alocada com 1 caractere e a coluna 4 reservada com 5 caracteres.

Eu criei um arquivo de formato XML como abaixo

<?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharFixed" ID="Col1" LENGTH="1"/>       
      <FIELD xsi:type="CharFixed" ID="Col2" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col3" LENGTH="1"/> 
      <FIELD xsi:type="CharFixed" ID="Col4" LENGTH="5"/> 
      <FIELD xsi:type="CharTerm" ID="LINE_BREAK" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="Col1" NAME="Col1" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col2" NAME="Col2" xsi:type="SQLNVARCHAR"/> 
      <COLUMN SOURCE="Col3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
      <COLUMN SOURCE="Col4" NAME="Col4" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

Lamentavelmente executando o seguinte SQL ...

SELECT *
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X

Produz os seguintes resultados

Col1 Col2 Col3 Col4
---- ---- ---- -----
o    a    b    ~opqr
�    �    a    b~öp
�    �    a    b~öp

do qual concluo que LENGTHestá contando bytes em vez de caracteres.

Existe alguma maneira de fazer isso funcionar corretamente para larguras de caracteres fixas com codificação UTF8?

(O ambiente de destino é a leitura do Banco de Dados SQL do Azure do armazenamento de BLOBs)

NB: Foi sugerido nos comentários que adicionar COLLATION="LATIN1_GENERAL_100_CI_AS_SC_UTF8"os FIELDelementos pode ajudar, mas os resultados permanecem inalterados com isso.

Configurar

DROP TABLE IF EXISTS #EmptyTable, #BigTable

CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);

INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM   sys.all_objects o1,
       sys.all_objects o2,
       sys.all_objects o3;

Consulta

WITH agg
     AS (SELECT DISTINCT a
         FROM   #BigTable)
SELECT *
FROM   #EmptyTable E
       INNER HASH JOIN agg B
                    ON B.A = E.A;

Plano de execução

Problema

Esta é uma reprodução simplificada de um fenômeno que eu não havia notado antes de hoje. Minha expectativa para uma junção de hash interna seria que, se a entrada de compilação estiver vazia, o lado do teste não deve ser executado, pois a junção não pode retornar nenhuma linha. O exemplo acima contradiz isso e lê as 10 milhões de linhas da tabela. Isso adiciona 2,196 segundos ao tempo de execução da consulta (99,9%).

Observações Adicionais

1. Com OPTION (MAXDOP 1)o plano de execução não lê nenhuma linha de #BigTable. O ActualExecutionsé 0para todos os operadores dentro da junção de hash.
2. Para a consulta SELECT * FROM #EmptyTable E INNER HASH JOIN #BigTable B ON B.A = E.A- recebo um plano paralelo, o operador de varredura no interior da junção de hash tem ActualExecutionsDOP, mas ainda nenhuma linha é lida. Este plano não tem operador de repartição de fluxos (ou agregado)

Pergunta

O que está acontecendo aqui? Por que o plano original apresenta o problema e os outros casos não?

O abaixo é uma versão simplificada de algo que encontrei na produção (onde o plano ficou catastroficamente pior em um dia em que um número excepcionalmente alto de lotes foi processado).

A reprodução foi testada em 2014 e 2016 com o novo estimador de cardinalidade.

CREATE TABLE T1 (FromDate  DATE, ToDate DATE, SomeId INT, BatchNumber INT);

INSERT INTO T1
SELECT TOP 1000 FromDate = '2017-01-01',
                ToDate = '2017-01-01',
                SomeId = ROW_NUMBER() OVER (ORDER BY @@SPID) -1,
                BatchNumber = 1
FROM   master..spt_values v1

CREATE TABLE T2 (SomeDateTime DATETIME, SomeId INT, INDEX IX(SomeDateTime));

INSERT INTO T2
SELECT TOP 1000000 '2017-01-01',
                   ROW_NUMBER() OVER (ORDER BY @@SPID) %1000
FROM   master..spt_values v1,
       master..spt_values v2

T1contém 1.000 linhas.

Os FromDate, ToDate, e BatchNumbersão idênticos em todos eles. O único valor que difere é SomeIdcom valores entre 0e999

+------------+------------+--------+-----------+
|  FromDate  |   ToDate   | SomeId | BatchNumber |
+------------+------------+--------+-----------+
| 2017-01-01 | 2017-01-01 |      0 |         1 |
| 2017-01-01 | 2017-01-01 |      1 |         1 |
....
| 2017-01-01 | 2017-01-01 |    998 |         1 |
| 2017-01-01 | 2017-01-01 |    999 |         1 |
+------------+------------+--------+-----------+

T2contém 1 milhão de linhas

mas apenas 1.000 distintos. Cada um repetido 1.000 vezes como abaixo.

+-------------------------+--------+-------+
|      SomeDateTime       | SomeId | Count |
+-------------------------+--------+-------+
| 2017-01-01 00:00:00.000 |      0 |  1000 |
| 2017-01-01 00:00:00.000 |      1 |  1000 |
...
| 2017-01-01 00:00:00.000 |    998 |  1000 |
| 2017-01-01 00:00:00.000 |    999 |  1000 |
+-------------------------+--------+-------+

Executando o seguinte

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

Demora cerca de 7 segundos na minha máquina. As linhas reais e estimadas são perfeitas para todos os operadores no plano.

Agora adicione 3.000 lotes adicionais a T1 (com números de lote de 2 a 3001). Cada um deles clona as mil linhas existentes para o lote número 1

INSERT INTO T1
SELECT T1.FromDate,
       T1.ToDate,
       T1.SomeId,
       Nums.NewBatchNumber
FROM   T1
       CROSS JOIN (SELECT TOP (3000) 1 + ROW_NUMBER() OVER (ORDER BY @@SPID) AS NewBatchNumber
                   FROM   master..spt_values v1, master..spt_values v2) Nums 

e atualize as estatísticas para dar sorte

 UPDATE STATISTICS T1 WITH FULLSCAN

E execute a consulta original novamente.

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

Deixei correr por um minuto antes de matá-lo. Naquela época, ele havia gerado 40.380 linhas, então acho que levaria 25 minutos para produzir o milhão completo.

A única coisa que mudou é que adicionei algumas linhas adicionais que não correspondiam ao T1.BatchNumber = 1predicado.

No entanto, o plano agora mudou. Em vez disso, ele usa loops aninhados e, embora o número de linhas que saem t1ainda seja estimado corretamente em 1.000 (①), a estimativa do número de linhas unidas agora caiu de 1 milhão para mil (②).

Então a pergunta é...

Por que adicionar linhas adicionais de BatchNumber <> 1alguma forma afeta as estimativas para linhas unidas quando BatchNumber = 1?

Certamente parece contra-intuitivo que adicionar linhas a uma tabela acabe reduzindo o número estimado de linhas da consulta como um todo.

Nas consultas abaixo, estima-se que ambos os planos de execução executem 1.000 buscas em um índice exclusivo.

As buscas são conduzidas por uma varredura ordenada na mesma tabela de origem, portanto, aparentemente, devem acabar buscando os mesmos valores na mesma ordem.

Ambos os loops aninhados têm<NestedLoops Optimized="false" WithOrderedPrefetch="true">

Alguém sabe por que essa tarefa custa 0,172434 no primeiro plano, mas 3,01702 no segundo?

(O motivo da pergunta é que a primeira consulta foi sugerida para mim como uma otimização devido ao aparente custo de plano muito menor. Na verdade, parece-me que funciona mais, mas estou apenas tentando explicar a discrepância. .)

Configurar

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL); 

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,  
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

Consulta 1 _{link "Colar o plano"}

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Consulta 2 _{link "Colar o plano"}

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol; 

Consulta 1

Consulta 2

O acima foi testado no SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

@Joe Obbish aponta nos comentários que uma reprodução mais simples seria

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

vs

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN (SELECT * FROM Target) AS T 
    ON T.KeyCol = S.KeyCol;

Para a tabela de preparação de 1.000 linhas, ambos os itens acima ainda têm a mesma forma de plano com loops aninhados e o plano sem a tabela derivada parecendo mais barato, mas para uma tabela de preparação de 10.000 linhas e a mesma tabela de destino acima, a diferença nos custos altera o plano forma (com uma varredura completa e junção de mesclagem parecendo relativamente mais atraente do que buscas caras) mostrando essa discrepância de custo pode ter outras implicações além de apenas dificultar a comparação de planos.

Percebi uma operação de estatísticas de atualização automática relativamente longa (20 min+) em uma compilação diária de datawarehouse. A tabela envolvida é

CREATE TABLE [dbo].[factWebAnalytics](
    [WebAnalyticsId] [bigint] IDENTITY(1,1) NOT NULL,
    [MarketKey] [int] NOT NULL CONSTRAINT [DF_factWebAnalytics_MarketKey]  DEFAULT ((-1)),
    /*Other columns removed*/
 CONSTRAINT [PK_factWebAnalytics] PRIMARY KEY CLUSTERED 
(
    [MarketKey] ASC,
    [WebAnalyticsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MarketKeyPS]([MarketKey])
) ON [MarketKeyPS]([MarketKey])

Isso está sendo executado no Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64), portanto, os índices columnstore graváveis ​​não estão disponíveis.

A tabela contém dados para duas chaves de mercado distintas. A compilação alterna a partição de um MarketKey específico para uma tabela de preparação, desativa o índice columnstore, executa as gravações necessárias, reconstrói o columnstore e o alterna novamente.

O plano de execução para as estatísticas de atualização mostra que ele extrai todas as linhas da tabela, as classifica, obtém o número estimado de linhas totalmente errado e se espalha para o tempdbnível de derramamento 2.

Corrida

SELECT [s].[name] AS "Statistic",
       [sp].*
FROM   [sys].[stats] AS [s]
       OUTER APPLY sys.dm_db_stats_properties ([s].[object_id], [s].[stats_id]) AS [sp]
WHERE  [s].[object_id] = OBJECT_ID(N'[dbo].[factWebAnalytics]'); 

shows

Se eu tentar explicitamente reduzir o tamanho da amostra das estatísticas desse índice para o usado pelos outros com

UPDATE STATISTICS [dbo].[factWebAnalytics] [PK_factWebAnalytics] WITH SAMPLE 897667 ROWS

A consulta é executada por mais de 20 minutos e o plano de execução mostra que está processando todas as linhas, não a amostra de 897.667 solicitada.

As estatísticas geradas no final de tudo isso não são muito interessantes e definitivamente não parecem justificar o tempo gasto em uma varredura completa.

Statistics for INDEX 'PK_factWebAnalytics'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_factWebAnalytics             Jan 22 2016 11:31AM             420072086                       420072086                       2                               0                               12                              NO                                                              420072086                       

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.5                             4                               MarketKey                       
2.380544E-09                    12                              MarketKey, WebAnalyticsId       

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               0                               3.441652E+08                    0                               1                               
2                               0                               7.590685E+07                    0                               1                               

Alguma ideia de por que estou encontrando esse comportamento e quais etapas posso seguir além de usá NORECOMPUTE-las?

Um script de reprodução está aqui . Ele simplesmente cria uma tabela com um PK clusterizado e um índice columnstore e tenta atualizar as estatísticas do PK com um tamanho de amostra baixo. Isso não usa particionamento - mostrando que o aspecto de particionamento não é necessário. No entanto, o uso do particionamento descrito acima torna as coisas piores, pois remover a partição e, em seguida, recuperá-la (mesmo sem nenhuma outra alteração) aumentará o modify_counter pelo dobro do número de linhas na partição, garantindo assim praticamente que as estatísticas serão considerado obsoleto e atualizado automaticamente.

Tentei adicionar um índice não clusterizado à tabela, conforme indicado em KB2986627 (ambos filtrados sem linhas e, quando isso falhou, um NCI não filtrado também sem efeito).

A reprodução não mostrou o comportamento problemático na compilação 11.0.6020.0 e, após a atualização para o SP3, o problema foi corrigido.

O código de exemplo neste item de conexão

Mostra um bug onde

SELECT COUNT(*)
FROM   dbo.my_splitter_1('2') L1
       INNER JOIN dbo.my_splitter_1('') L2
         ON L1.csv_item = L2.csv_item

Retorna os resultados corretos. Mas o seguinte retorna resultados incorretos (em 2014 usando o novo Estimador de Cardinalidade)

SELECT
    (SELECT COUNT(*)
    FROM dbo.my_splitter_1('2') L1
     INNER JOIN dbo.my_splitter_1('') L2
        ON L1.csv_item = L2.csv_item)

Como ele carrega incorretamente os resultados para L2 em um spool de subexpressão comum, reproduz o resultado disso para o resultado L1.

Fiquei curioso para saber por que a diferença de comportamento entre as duas consultas. O Trace Flag 8675 mostra que o que funciona entra search(0) - transaction processinge o que falha entra search(1) - quick plan.

Portanto, presumo que a disponibilidade de regras de transformação adicionais esteja por trás da diferença de comportamento (desativar BuildGbApply ou GenGbApplySimple parece corrigi-lo, por exemplo).

Mas por que os dois planos para essas consultas tão semelhantes encontram diferentes fases de otimização? Pelo que li search (0)requer pelo menos três tabelas e essa condição certamente não é atendida no primeiro exemplo.

Me deparei com esse problema recentemente e não consegui encontrar nenhuma discussão sobre ele online.

A consulta abaixo

DECLARE @S VARCHAR(1) = '';

WITH T
     AS (SELECT name + @S AS name2,
                *
         FROM   master..spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

Sempre obtém um plano de loops aninhados

A tentativa de forçar o problema com INNER HASH JOINou INNER MERGE JOINdicas produz o seguinte erro.

O processador de consulta 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.

Encontrei uma solução alternativa que permite o uso de junções de hash ou mesclagem - envolvendo a variável em um agregado. O plano gerado tem um custo significativamente menor (19,2025 vs 0,261987)

DECLARE @S2 VARCHAR(1) = '';

WITH T
     AS (SELECT name + (SELECT MAX(@S2)) AS name2,
                *
         FROM   spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2; 

Qual é a razão para este comportamento? e existe uma solução alternativa melhor do que a que encontrei? (que talvez não exija as ramificações extras do plano de execução)

Para o seguinte esquema e dados de exemplo

CREATE TABLE T
  (
     A INT NULL,
     B INT NOT NULL IDENTITY,
     C CHAR(8000) NULL,
     UNIQUE CLUSTERED (A, B)
  )

INSERT INTO T
            (A)
SELECT NULLIF(( ( ROW_NUMBER() OVER (ORDER BY @@SPID) - 1 ) / 1003 ), 0)
FROM   master..spt_values 

Um aplicativo está processando as linhas desta tabela na ordem do índice clusterizado em blocos de 1.000 linhas.

As primeiras 1.000 linhas são recuperadas da consulta a seguir.

SELECT TOP 1000 *
FROM   T
ORDER  BY A, B 

A linha final desse conjunto está abaixo

+------+------+
|  A   |  B   |
+------+------+
| NULL | 1000 |
+------+------+

Existe alguma maneira de escrever uma consulta que apenas busque essa chave de índice composto e a siga para recuperar o próximo bloco de 1.000 linhas?

/*Pseudo Syntax*/
SELECT TOP 1000 *
FROM   T
WHERE (A, B) is_ordered_after (@A, @B)
ORDER  BY A, B 

O menor número de leituras que consegui até agora é 1020, mas a consulta parece muito complicada. Existe uma maneira mais simples de eficiência igual ou melhor? Talvez aquele que consegue fazer tudo em uma busca de alcance?

DECLARE @A INT = NULL, @B INT = 1000

;WITH UnProcessed
     AS (SELECT *
         FROM   T
         WHERE  ( EXISTS(SELECT A
                         INTERSECT
                         SELECT @A)
                  AND B > @B )
         UNION ALL
         SELECT *
         FROM   T
         WHERE @A IS NULL AND A IS NOT NULL
         UNION ALL
         SELECT *
         FROM   T
         WHERE A > @A        
         )
SELECT TOP 1000 *
FROM   UnProcessed
ORDER  BY A,
          B 

FWIW: Se a coluna Afor feita NOT NULLe um valor sentinela -1for usado, o plano de execução equivalente certamente parecerá mais simples

Mas o único operador de busca no plano ainda realiza duas buscas em vez de reduzi-lo em um único intervalo contíguo e as leituras lógicas são praticamente as mesmas, então estou suspeitando que talvez isso seja tão bom quanto será.

Dados os seguintes componentes

DECLARE @D DATE = '2013-10-13'
DECLARE @T TIME(7) = '23:59:59.9999999'

Qual a melhor forma de combiná-los para produzir um DATETIME2(7)resultado com valor '2013-10-13 23:59:59.9999999'?

Algumas coisas que não funcionam estão listadas abaixo.

SELECT @D + @T 

A data do tipo de dados do operando é inválida para o operador add.

SELECT CAST(@D AS DATETIME2(7)) + @T 

O tipo de dados do operando datetime2 é inválido para o operador add.

SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)

A função dateiff resultou em um estouro. O número de partes de data que separam duas instâncias de data/hora é muito grande. Tente usar datediff com um datepart menos preciso.

* O estouro pode ser evitado no Banco de Dados SQL do Azure e no SQL Server 2016, usando DATEDIFF_BIG.

SELECT CAST(@D AS DATETIME) + @T 

Os tipos de dados datetime e time são incompatíveis no operador add.

SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)

Retorna um resultado, mas perde a precisão2013-10-13 23:59:59.997

Me deparei com esse quebra-cabeça nos comentários aqui

CREATE TABLE r (b INT);

SELECT 1 FROM r HAVING 1=1;

SQL Server e PostgreSQL retornam 1 linha.

MySQL e Oracle retornam zero linhas.

Qual é correto? Ou ambos são igualmente válidos?

Executando a consulta daqui para retirar os eventos de impasse da sessão padrão de eventos estendidos

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

leva cerca de 20 minutos para ser concluído na minha máquina. As estatísticas relatadas são

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0, 
         lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

 SQL Server Execution Times:
   CPU time = 1241269 ms,  elapsed time = 1244082 ms.

Plano Lento XML

Se eu remover a WHEREcláusula, ela será concluída em menos de um segundo, retornando 3.782 linhas.

Da mesma forma, se eu adicionar OPTION (MAXDOP 1)à consulta original que acelera as coisas também, as estatísticas agora mostram muito menos leituras de lob.

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
                lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

 SQL Server Execution Times:
   CPU time = 639 ms,  elapsed time = 693 ms.

XML do plano mais rápido

Então minha pergunta é

Alguém pode explicar o que está acontecendo? Por que o plano original é tão catastroficamente pior e existe alguma maneira confiável de evitar o problema?

Adição:

Também descobri que alterar a consulta para INNER HASH JOINmelhorar as coisas até certo ponto (mas ainda leva> 3 minutos), pois os resultados do DMV são tão pequenos que duvido que o próprio tipo Join seja responsável e presumo que algo mais deva ter mudado. Estatísticas para isso

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0, 
          lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

 SQL Server Execution Times:
   CPU time = 200914 ms,  elapsed time = 203614 ms.

(E planejar)

Depois de preencher o buffer de anel de eventos estendidos ( DATALENGTHdos XMLquais eram 4.880.045 bytes e continha 1.448 eventos.) e testar uma versão reduzida da consulta original com e sem a MAXDOPdica.

SELECT COUNT(*)
FROM   (SELECT CAST (target_data AS XML) AS TargetData
        FROM   sys.dm_xe_session_targets st
               JOIN sys.dm_xe_sessions s
                 ON s.address = st.event_session_address
        WHERE  [name] = 'system_health') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID 

Deu os seguintes resultados

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

Há uma clara diferença nas alocações de tempdb com a mais rápida mostrando que as 616páginas foram alocadas e desalocadas. Esta é a mesma quantidade de páginas usadas quando o XML também é colocado em uma variável.

Para o plano lento, essas contagens de alocação de página chegam a milhões. A pesquisa dm_db_task_space_usageenquanto a consulta está em execução mostra que parece estar constantemente alocando e desalocando páginas tempdbentre 1.800 e 3.000 páginas alocadas a qualquer momento.

Este é um problema com o qual me deparo periodicamente e ainda não encontrei uma boa solução.

Supondo a seguinte estrutura de tabela

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

e o requisito é determinar se uma das colunas anuláveis B​​ou Crealmente contém algum NULLvalor (e, em caso afirmativo, qual(is)).

Suponha também que a tabela contém milhões de linhas (e que não há estatísticas de coluna disponíveis que possam ser espiadas, pois estou interessado em uma solução mais genérica para essa classe de consultas).

Posso pensar em algumas maneiras de abordar isso, mas todas têm pontos fracos.

Duas EXISTSdeclarações separadas. Isso teria a vantagem de permitir que as consultas parem de varrer antecipadamente assim que um NULLfor encontrado. Mas se ambas as colunas de fato não contiverem nenhum NULLs, resultarão em duas varreduras completas.

Consulta agregada única

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

Isso pode processar as duas colunas ao mesmo tempo, portanto, o pior caso é uma varredura completa. A desvantagem é que, mesmo que encontre um NULLem ambas as colunas muito cedo, a consulta ainda acabará varrendo todo o restante da tabela.

Variáveis ​​do usuário

Eu posso pensar em uma terceira maneira de fazer isso

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

mas isso não é adequado para código de produção, pois o comportamento correto para uma consulta de concatenação agregada é indefinido. e encerrar a varredura lançando um erro é uma solução horrível de qualquer maneira.

Existe outra opção que combine os pontos fortes das abordagens acima?

Editar

Apenas para atualizar isso com os resultados que recebo em termos de leituras das respostas enviadas até agora (usando os dados de teste do @ypercube)

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

Para a resposta de @Thomas, mudei TOP 3para TOP 2potencialmente permitir que ela saísse mais cedo. Eu tenho um plano paralelo por padrão para essa resposta, então também tentei com uma MAXDOP 1dica para tornar o número de leituras mais comparável aos outros planos. Fiquei um pouco surpreso com os resultados, pois no meu teste anterior eu havia visto esse curto-circuito na consulta sem ler a tabela inteira.

O plano para meus dados de teste que curtos-circuitos está abaixo

O plano para os dados do ypercube é

Portanto, ele adiciona um operador de classificação de bloqueio ao plano. Eu também tentei com a HASH GROUPdica, mas isso ainda acaba lendo todas as linhas

Portanto, a chave parece ser obter um hash match (flow distinct)operador para permitir que esse plano entre em curto-circuito, pois as outras alternativas bloquearão e consumirão todas as linhas de qualquer maneira. Não acho que haja uma dica para forçar isso especificamente, mas aparentemente "em geral, o otimizador escolhe um Flow Distinct onde determina que são necessárias menos linhas de saída do que valores distintos no conjunto de entrada". .

Os dados do @ypercube têm apenas 1 linha em cada coluna com NULLvalores (cardinalidade da tabela = 30300) e as linhas estimadas que entram e saem do operador são ambas 1. Ao tornar o predicado um pouco mais opaco para o otimizador, gerou um plano com o operador Flow Distinct.

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT 

Editar 2

Um último ajuste que me ocorreu é que a consulta acima ainda pode acabar processando mais linhas do que o necessário caso a primeira linha que encontrar com a NULLtenha NULLs na coluna Be no C. Ele continuará digitalizando em vez de sair imediatamente. Uma maneira de evitar isso seria desarticular as linhas à medida que são digitalizadas. Então, minha correção final para a resposta de Thomas Kejser está abaixo

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

Provavelmente seria melhor que o predicado fosse, WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULLmas contra os dados de teste anteriores que não me dê um plano com um Flow Distinct, enquanto o NullExists IS NOT NULLoutro faz (plano abaixo).

Este White Paper de 2007 compara o desempenho de instruções individuais de seleção/inserção/exclusão/atualização e seleção de intervalo em uma tabela organizada como um índice clusterizado versus uma tabela organizada como um heap com um índice não clusterizado nas mesmas colunas de chave que o CI tabela.

Geralmente, a opção de índice agrupado teve melhor desempenho nos testes, pois há apenas uma estrutura para manter e porque não há necessidade de pesquisas de favoritos.

Um caso potencialmente interessante não coberto pelo artigo seria uma comparação entre um índice não clusterizado em um heap versus um índice não clusterizado em um índice clusterizado. Nesse caso, eu esperava que o heap pudesse ter um desempenho ainda melhor, pois uma vez no nível de folha NCI, o SQL Server tem um RID para seguir diretamente, em vez de precisar percorrer o índice clusterizado.

Alguém está ciente de testes formais semelhantes que foram realizados nesta área e, em caso afirmativo, quais foram os resultados?

De acordo com este artigo (bastante antigo), o princípio por trás das sequências de escape ODBC é que

O driver ODBC lê a sequência de escape e a traduz na sintaxe específica do DBMS antes de enviar a consulta ao banco de dados

No entanto, se eu tentar o seguinte

servidor SQL

CREATE PROCEDURE foo_bar
AS
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

meu SQL

DELIMITER $$

CREATE PROCEDURE foo_bar()
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

As sequências de escape permanecem não traduzidas na definição do objeto e chamar as rotinas funciona bem. Isso indica para mim que os próprios RDBMSs entendem nativamente a sintaxe, em vez de depender da tradução, portanto funcionarão independentemente do método de conexão. Esse entendimento está correto e, em caso afirmativo, também se aplica ao Oracle?