crokusek's questions

A consulta a seguir converte uma única string de CSV compactado que representa 13 mil linhas por 2 colunas. A coluna A é um bigint. A coluna B é um inteiro pequeno.

declare
    @dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36';  -- abbreviated, actually 13k rows

  with Input as
  (
      select Value,
             Row = row_number() over (order by (select null)) - 1
      from string_split(@dataCsv, ',') o
  )
  --insert into PubCache.TableName
  select 78064 as CacheId,
         convert(bigint, i.Value) as ObjectId,
         convert(smallint, i2.Value) as BrandId
    from Input i
    inner hash join Input i2    -- hash to encourage string_split() only once per column
      on i2.Row = i.Row + 1
    where i.Row % 2 = 0
    order by i.Row

Plano de Execução: https://www.brentozar.com/pastetheplan/?id=By0hYPmd6

Conforme mostrado no plano, a avaliação de convert() está ocorrendo antes da junção, então às vezes (dependendo do comprimento da entrada), ela falha com

A conversão do valor nvarchar '37645' estourou uma coluna INT2. Use uma coluna inteira maior.

Alterar temporariamente a conversão de smallint para int permite que a consulta seja concluída e a inspeção da saída da coluna BrandId mostra que ela sempre contém apenas o valor '36' para este exemplo.

Existe uma maneira simples de atrasar o convert(smallint, i2.Value) até depois da junção, para que apenas as posições CSV esperadas sejam convertidas?

Sei que existem outras maneiras de compactar um fluxo de string (como usar múltiplas variáveis ​​ou entrelaçar diferentes caracteres divididos, etc.), mas não estou interessado em resolver este exemplo dessa forma para fins desta questão. Obrigado!

Temos uma tabela Ecom.McProductToVendorProductCodeMap que possui um PK de vários campos, conforme mostrado:

Em seguida, uma visualização agrupa essa tabela para calcular uma métrica e é agrupada pelos dois primeiros campos dessa PK:

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

Agora, se selecionarmos na visualização usando esses 2 campos como predicados diretamente, ocorrerá uma busca de índice usando esses 2 campos (19 mil linhas e a dica de ferramenta mostra "Seek Predicate" nos 2 campos):

select * from ECom.McProductToVendorProductMd5SourceView
where ClientAppPrivateLabelId = 101 and BRandId = 3  

No entanto, ao tentar ingressar na mesma visualização usando os mesmos 2 predicados, ele busca apenas ClientAppPrivateLabelId e não BrandId . A dica de junção de loop não ajudou e substituir a junção por uma aplicação cruzada também não ajudou.

select IsNull(convert(smallint, Value), 0) as BrandId 
  into #Brands 
  from string_split('2,3', ',');    
    
select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 inner loop join ECom.McProductToVendorProductMd5SourceView m
    on m.BrandId = b.BrandId
   and m.ClientAppPrivateLabelId = 101;

A visualização é simples, exceto pelo cálculo de janelas:

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

Por que não usa BrandId? A tabela original define BrandId como um smallint não anulável.

Cole o plano: https://www.brentozar.com/pastetheplan/?id=ryZWp86Hp

Atualização nº 1 (05/12/2023)

Converteu a visualização em uma função com valor de tabela (TVF):

alter function ECom.McProductToVendorProductMd5(
   @pBrandId smallint,
   @pClientAppPrivateLabelId smallint
)
returns table as 
return
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  -- Sense any MaterialNumber/VendorProductCode/Status changes
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap m
 where m.BrandId = @pBrandId
   and m.ClientAppPrivateLabelId = @pClientAppPrivateLabelId
 group by ClientAppPrivateLabelId, BrandId

e ajustou a consulta para usá-la via aplicação cruzada:

select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 cross apply ECom.McProductToVendorProductMd5(b.BrandId, @pCaplId) m;

Mesmo problema: https://www.brentozar.com/pastetheplan/?id=SJnRODaBT

Ele está usando uma junção de mesclagem e não buscando no BrandId

A consulta a seguir é executada em aproximadamente 60 bancos de dados em paralelo. Sem dicas, há inúmeros vazamentos e planos não ideais em pelo menos 10% dos BDs.

Usando um banco de dados maior como guia, a consulta foi bloqueada com dicas (~ 75ms em 1 CPU) para reduzir a variação nos tempos de execução, pois 1 plano ruim causa o tempo de execução geral. Nós nos opomos principalmente a permitir que cada banco de dados ajuste seu plano livremente, pois alguns bancos de dados provavelmente pegarão fogo a longo prazo na plataforma de produção. Estamos perfeitamente satisfeitos com um plano quase ideal para bancos de dados maiores que pode ser sub-ótimo para bancos de dados menores.

Alguns (~ 5) dos bancos de dados menores ainda exibem pequenos spills de Nível 1 (consulte o plano) mesmo após adicionar estatísticas com varredura completa. O tempo de execução ainda está ok (125ms), mas gostaria de eliminar o derramamento.

Este é o Sql Server 2019. O recurso de concessão adaptável (2017) deve estar ajustando a concessão devido ao derramamento? Executá-lo repetidamente no SSMS e no plano de visualização parece indicar nenhuma alteração.

select top (@pMax)
           aig.ObjectId,  
           iif((@pA in (1, 2, 3, 4, 5, 6, 9, 11, 12) and ttm.ObjectId is not null) or
               (@pA in (7, 8, 10, 13, 14, 15)), 1.0, 0.0) as Rank
      from oav.value aig               
      inner merge join Pub.CachedObjectHierarchyAttributes coha
        on coha.ObjectId = aig.ObjectId
       and coha.IsActiveForPublisher = 1
       and coha.IsToolItem = 1
      inner merge join Oav.ValueArray v897
        on v897.PropertyId = 897
       and v897.ObjectId = aig.ObjectId
       and v897.[Value] = @pBrandId
      left hash join oav.valuearray ttm      
        on ttm.ObjectId = aig.ObjectId
       and ttm.PropertyId = 11131  
       and ttm.[Value] = @pToolTypeMapId 
     where aig.PropertyId = 2573        
       and aig.[Value] = @pA
     order by ttm.[Value] desc -- to put TTM matches at the top
     option (maxdop 1); -- limit to 1 cpu since it runs across all pubs

As estimativas de linha do índice 3 buscam a correspondência certa dentro de <1% das linhas reais.

No entanto, a estimativa para a primeira mesclagem das 2 buscas mais à direita está um pouco errada e, em seguida, continua causando os vazamentos. Com estimativas perfeitas das 2 etapas anteriores, o que resta para afetar essa estimativa?

Detalhe do derramamento:

Dada uma tabela simples baseada em linhas sem um PK, mas com um índice clusterizado baseado em linhas da seguinte forma:

create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value])

Em seguida, desejo adicionar um índice de armazenamento de colunas segmentado na mesma ordem que o índice clusterizado acima:

create nonclustered columnstore index CS_IX_PropertyValue on dbo.PropertyValue( 
    PropertyId, Value
)
with (drop_existing = on, maxdop = 1); -- maxdop=1 to preserve the order by property 

A dica do MaxDop para preservar a ordem veio de: aqui

Em seguida, a consulta a seguir foi usada para relatar o min/max data_id para a coluna PropertyId e o intervalo completo foi relatado em cada um dos 7 segmentos:

create view [Common].[ColumnStoreSegmentationView]
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose: List ColumnStore table segment min/max of columns.

     Source: https://joyfulcraftsmen.com/blog/cci-how-to-load-data-for-better-columnstore-segment-elimination/
             https://dba.stackexchange.com/a/268329/9415

    Modified    By            Description
    ----------  ----------    -----------------------------------------------------------------------------------------
    2020.06.02  crokusek/inet Initial Version 
  ---------------------------------------------------------------------------------------------------------------------*/
select --top 20000000000
       s.Name as SchemaName, 
       t.Name as TableName,
       i.Name as IndexName,
       c.name as ColumnName,
       c.column_id as ColumnId,
       cs.segment_id as SegmentId,
       cs.min_data_id as MinValue,
       cs.max_data_id as MaxValue
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id
  join sys.partitions as p  
    on p.object_id = t.object_id   
  join sys.indexes as I
    on i.object_id = p.object_id
   and i.index_id = p.index_id
  join sys.index_columns as ic
    on ic.[object_id] = I.[object_id]
   and ic.index_id = I.index_id   
  join sys.columns c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
  join sys.column_store_segments cs
    on cs.hobt_id = p.hobt_id
   and cs.column_id = ic.index_column_id 
 --order by s.Name, t.Name, i.Name, c.Name, cs.Segment_Id
GO

Tentei tornar o índice clusterizado único, o que afetou ligeiramente os intervalos relatados, mas ainda não estava aumentando monotonicamente.

Alguma ideia?

Aqui está um link que realizou a segmentação dessa maneira, mas não vejo diferença.

Versão: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

Temos uma visão otimizada para consultas de item único (200ms sem paralelismo):

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId = 3374700

Também funciona em pequenos conjuntos de ids estáticos (~5).

select * 
    from OptimizedForSingleObjectIdView e2i
   where ObjectId in (3374700, 3374710, 3374720, 3374730, 3374740);

No entanto, se os objetos vierem de uma fonte externa, ele gerará um plano lento. O plano de execução mostra que a ramificação de execução para a parte de exibição está ignorando o predicado em ObjectId, enquanto no caso original ele os usa para realizar buscas de índice.

select v.*
  from 
     (
       select top 1 ObjectId from Objects
        where ObjectId % 10 = 0
        order by ObjectId
     ) o  
  join OptimizedForSingleObjectIdView v -- (also tried inner loop join)
    on v.ObjectId = o.ObjectId;

Não queremos investir em "dual" otimizando a visão para casos não singulares. Em vez disso, a solução que "procuramos" é chamar repetidamente a visão uma vez por objeto sem recorrer a um SP .

Na maioria das vezes, a solução a seguir chama a exibição linha por linha. Porém não desta vez e nem mesmo para apenas 1 objeto:

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0 -- non-trivial predicate
        order by ObjectId
     ) o
   cross apply
    (
      select top 2000000000 *
        from OptimizedForSingleObjectIdView v_
       where ObjectId = o.ObjectId 
       order by v_.SomeField
    ) v;

Ao mesmo tempo, pensei que havia uma alegação de que a aplicação cruzada era garantida para execução de linha por linha quando chama uma UDF, mas isso também falhou:

create function FunctionCallingView(@pObjectId bigint)
returns table
as 
return select *
  from OptimizedForSingleObjectIdView 
 where ObjectId = @pObjectId;             

select v.*
  from
     (
       select top 1 ObjectId 
         from Objects 
        where ObjectId % 10 = 0
        order by ObjectId
     ) o
 cross apply FunctionCallingView(o.ObjectId) v

Adicionar opção (forçar ordem) não ajudou - no entanto, já existem duas dicas de hash na exibição. Removê-los temporariamente não ajudou e retarda o caso único.

Aqui está um trecho do plano estimado para o caso lento baseado em função. A estimativa de 1 linha está correta. Mais à direita (não mostrado) é onde há um predicado de busca que não inclui o resultado 1 superior. Isso parece semelhante a outros casos que temos em que valores de sondagem singulares de buscas de tabela não são usados ​​como predicados de busca em outros lugares.

A documentação da cláusula From começando com o SQL Server 2008 menciona brevemente 3 dicas de junção e seus mecanismos básicos:

• Reduzir
• Replicar
• Redistribuir

No entanto, não parece haver muita informação sobre quando pode ser necessário usá-los.

Parece que eles podem ser usados ​​em conjunto com o hash, loop e merge que já são entendidos para o propósito desta questão.

A seção relevante da documentação:

Para SQL Data Warehouse e Parallel Data Warehouse, essas dicas de junção se aplicam a junções INNER em duas colunas incompatíveis de distribuição. Eles podem melhorar o desempenho da consulta restringindo a quantidade de movimentação de dados que ocorre durante o processamento da consulta. As dicas de junção permitidas para SQL Data Warehouse e Parallel Data Warehouse são as seguintes:

• REDUCE
  Reduz o número de linhas a serem movidas para a tabela no lado direito da junção para tornar compatíveis duas tabelas incompatíveis de distribuição. A dica REDUCE também é chamada de dica de semijunção.

• REPLICATE
  Faz com que os valores na coluna de junção da tabela no lado esquerdo da junção sejam replicados para todos os nós. A tabela à direita é unida à versão replicada dessas colunas.

• REDISTRIBUTE
  Força a distribuição de duas fontes de dados em colunas especificadas na cláusula JOIN. Para uma tabela distribuída, o Parallel Data Warehouse executará uma movimentação aleatória. Para uma tabela replicada, o Parallel Data Warehouse executará uma movimentação de corte. Para entender esses tipos de movimentação, consulte a seção "Operações do Plano de Consulta DMS" no tópico "Entendendo Planos de Consulta" na documentação do produto Parallel Data Warehouse. Essa dica pode melhorar o desempenho quando o plano de consulta estiver usando uma movimentação de transmissão para resolver uma junção incompatível de distribuição.

Corrija-me se estiver enganado, mas pelo que entendi, quando uma consulta está sendo agendada para execução, o mecanismo leva em consideração o número de threads livres disponíveis e ajustará o plano de consulta de acordo.

Por exemplo, se uma máquina estiver altamente carregada de modo que apenas um único thread esteja livre, uma consulta que normalmente é executada usando vários threads em uma máquina descarregada pode ser executada como um único thread.

Existe uma maneira de consultar dinamicamente o número de threads livres disponíveis para que uma consulta crítica específica (que seja executada inaceitavelmente como MaxDop < X) possa ser atrasada (enquanto/espera) para execução até que uma contagem mínima de threads livres esteja disponível?

Atualização: Um ponto de partida baseado na resposta de Kin:

Isso parece seguir um teste de carga simples, mas nunca retorna todo ocioso por algum motivo. Não sei nada sobre esta mesa.

select count(*) as Cpus, 
       sum(IsIdle) + 1 as IdleCpus  -- +1 since current query should be excluded
  from 
     (
        select Cpu_id, 
               min(convert(int, is_idle)) IsIdle
          from sys.dm_os_schedulers
          group by Cpu_id
     ) q
   ;

Atualmente, estou usando esse desastre para localizar deadlocks recentes lendo o system_healthbuffer de anel de eventos estendidos.

select top 2000000000
      XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
      --
      -- Extract the <deadlock>...</deadlock> tag from within the event
      -- Todo: Surely there is a better (xml) way to do this.
      --
      substring(convert(varchar(max), XEvent.query('.')), 
          -- start
          patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),          
          -- end
          patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
          ) AS XdlFile
 from 
    (
      select cast (target_data as xml) as TargetData
        from sys.dm_xe_session_targets st with (nolock)
        join sys.dm_xe_sessions s with (nolock)
          on s.address = st.event_session_address
       where [name] = 'system_health'
         and st.target_name = N'ring_buffer'
    ) as Data
 cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
 order by CreationDateUtc desc

Funciona bem, no entanto, os eventos não parecem durar muito tempo (como 24 horas?) Acho que essa é a parte do "buffer de anel". Agora me deparei com um link que estava lendo o system_health"arquivo" que possui informações semelhantes:

select event_data = CONVERT(XML, event_data) 
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where event_data like '%xml_deadlock%'  

Este arquivo é o mesmo que o buffer de anel, mas permanece por mais tempo? Alguma desvantagem em usar um arquivo? Alguém com alguma habilidade em XML quer converter o script top?

O objetivo é copiar/colar o campo XdlFile em um novo arquivo e lê-lo diretamente no SSMS ou Sql Sentry Plan Explorer usando "File Open".

Links de buffer de anel:

https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks#tabs

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/

https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/

http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/

http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx

O erro de deadlock não está retornando o SQL de deadlock

Arquivo:

https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/

@@versão = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)

Ao consultar tamanhos de arquivo de banco de dados...

1) A maioria, se não todas, essas respostas nem sempre correspondem ao tamanho do arquivo físico mostrado no explorador de arquivos:

https://stackoverflow.com/questions/5945360

A resposta aceita relata:      

contra

2) Esta postagem parece corresponder ao tamanho do arquivo físico mostrado no explorador de arquivos:

Existe uma consulta que reportará o tamanho físico sem depender de dm_os_performance_counters? O primeiro conjunto de consultas está ignorando o espaço livre?

Tamanho real relatado pelo File Explorer:

Por que o Tempdb mostra uma discrepância nos tamanhos de arquivo de dados e de log, conforme mostrado?

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

Usando o Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Dados uma tabela e um índice:

create table [User].[Session] 
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

As linhas reais para cada uma das consultas a seguir são 3,1 milhões, as linhas estimadas são mostradas como comentários.

Quando essas consultas alimentam outra consulta em uma View , o otimizador escolhe uma junção de loop por causa das estimativas de 1 linha. Como melhorar a estimativa neste nível básico para evitar substituir a dica de junção da consulta pai ou recorrer a um SP?

Usar uma data codificada funciona muito bem:

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

Essas consultas equivalentes são compatíveis com visualização, mas todas estimam 1 linha:

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())         

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();          

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc    
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

Tente algumas dicas (mas N/A para visualizar):

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc > dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s    
    on s.CreatedUtc > d.MinCreatedUtc  
option (recompile);

Tente usar Parâmetro/Dicas (mas N/A para visualizar):

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc > @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate
option (optimize for unknown);

As estatísticas estão atualizadas.

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

As últimas linhas do histograma (total de 189 linhas) são mostradas:

Um exemplo dessa pergunta mostra que o SQL Server escolherá uma varredura de índice completa para resolver uma consulta como esta:

select distinct [typeName] from [types]

Onde [typeName] tem um índice ascendente não clusterizado e não exclusivo. Seu exemplo tem 200 milhões de linhas, mas apenas 76 valores únicos. Parece que um plano de busca seria uma escolha melhor com essa densidade (~ 76 pesquisas binárias múltiplas)?

O caso dele poderia ser normalizado mas o motivo da pergunta é que eu realmente quero resolver algo assim:

select TransactionId, max(CreatedUtc) 
from TxLog 
group by TransactionId

Existe um índice em (TransactionId, MaxCreatedUtc).

Reescrever usando uma fonte normalizada (dt) não altera o plano.

select dt.TransactionId, MaxCreatedUtc
 from [Transaction] dt -- distinct transactions
 cross apply
   (
        select Max(CreatedUtc) as MaxCreatedUtc 
          from TxLog tl
         where tl.TransactionId = dt.TransactionId         
   ) ca

Executar apenas a subconsulta CA como uma UDF escalar mostra um plano de 1 busca.

select max(CreatedUtc) as MaxCreatedUtc
 from Pub.TransactionLog 
 where TransactionID = @TxId;

Usar esse UDF escalar na consulta original parece funcionar, mas perde o paralelismo (problema conhecido com UDFs):

select t.typeName, 
       Pub.ufn_TransactionMaxCreatedUtc(t.TransactionId) as MaxCreatedUtc
  from Pub.[Transaction] t

Reescrever usando um TVF Inline o reverte para o plano baseado em varredura.

Da resposta/comentário @ypercube:

select TransactionId, MaxCreatedUtc        
 from Pub.[Transaction]  t
  cross apply
   (
        select top (1) CreatedUtc as MaxCreatedUtc 
        from Pub.TransactionLog l
        where l.TransactionID = t.TransactionId
        order by CreatedUtc desc                     
   ) ca

Plano parece bom. Sem paralelismo, mas inútil desde tão rápido. Terá que tentar isso em um problema maior em algum momento. Obrigado.

Dados dois CTEs recursivos em cascata, autocontidos (sem tabelas reais):

create view NumberSequence_0_100_View
as
with NumberSequence as
(
    select 0 as Number
    union all
    select Number + 1
      from NumberSequence    
     where Number < 100
)
select Number
  from NumberSequence;
go

create view NumberSequence_0_10000_View
as
select top 10001
       v100.Number * 100 + v1.Number as Number
  from Common.NumberSequence_0_100_View v100
 cross join Common.NumberSequence_0_100_View v1
 where v1.Number < 100
   and v100.Number * 100 + v1.Number <= 10000
    -- please resist complaining about "order by in view" for this question
 order by v100.Number * 100 + v1.Number 
go

Em seguida, gere planos estimados/reais para:

select * from NumberSequence_0_10000_View

Estimativa real

Tempo de execução de 23 ms, mas estimando apenas uma linha para a saída final (2 linhas apenas para a primeira exibição).

O problema é que, quando isso é usado como uma subconsulta para unir dados reais (por "DaysAgo", por exemplo), o plano geralmente é um loop aninhado muito lento e geralmente preciso adicionar uma dica de junção/ordem reversa etc.

Existe alguma maneira de melhorar a estimativa, mantendo a abordagem CTE? Já houve uma solicitação de dica "with (AssumeMinRows = N)"? Isso parece ser um ótimo auxiliar de propósito geral para muitos casos (não apenas CTEs).

Está razoavelmente bem documentado que as UDFs escalares forçam um plano serial geral.

Executando funções em paralelo

Dado um grande número de linhas entrando em um ponto no pipeline onde uma UDF deve ser calculada, por que o mecanismo não pode simplesmente distribuí-las entre os processadores? Se não houver estado em uma UDF, a ordem não importa.

Há reclamações sobre UDF ser uma caixa preta que deve usar o cursor. Posso ver que um cursor de usuário não pode ser paralelizado dentro de um SP para os casos em que algum estado é mantido entre as iterações, mas parece que deveria ser paralelizado de outra forma.

Pontos extras para explicar por que o mecanismo força todo o plano a ser serial em vez de apenas o estágio de cálculo do UDF.

O suporte para UDF paralelo é um recurso razoável a ser solicitado?

Um exemplo:

create proc [dbo].[usp_SlowProc]         
as
begin try    
  set xact_abort on;  -- Ensures rollback on some errors that bypass the try/catch mechanism  
  raiserror('Waiting...', 0, 1) with nowait  
  waitfor delay '00:00:10'
  raiserror('Completed successfully.', 0, 1) with nowait  
end try
begin catch
    raiserror('Caught It.', 0, 1) with nowait   
    -- Rollback, log the error.
end catch

Em seguida, execute-o no SSMS/Toad:

exec dbo.usp_SlowProc;    

e, em seguida, cancele-o (usando o SSMS/Toad) antes de concluí-lo. O bloco catch não é executado.

Assumindo a partir deste link que um cancelamento de usuário é o mesmo que o .NET faz em um tempo limite do cliente (o resultado é o mesmo).

Tempo limite de transação do SQL Server

Existe uma estatística personalizada para a coluna CacheId de uma tabela. Após uma atualização de estatísticas durante a noite:

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 26 2014  2:04AM             121482                          121482                          6                               0                               4                               NO                                                              121482                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1666667                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1                               

1) Desempenho de uma junção em relação a um conjunto de dados existente nesta tabela onde CacheId = 41033 funciona bem com boas estimativas (23622 vs real de 20247).

2) Em seguida, é realizada uma inserção com CacheId = 41273 de 20247 linhas.

3) Em seguida, uma junção com esse conjunto de dados recém-inserido mostra uma estimativa ruim de 1 linha, resultando em um plano ruim.

4) Uma atualização manual das estatísticas (que originalmente era com fullscan) mostra um novo histograma:

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 28 2014 10:41AM             141729                          141729                          7                               0                               4                               NO                                                              141729                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1428571                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1                               
41274                           0                               20247                           0                               1                               

5) A execução da mesma consulta de junção novamente para CacheId = 41274 mostra estimativas perfeitas (20247) e bom desempenho.

Q1) Por que matematicamente a estimativa original é tão ruim? Quero dizer, os CacheIds são esparsos, mas não na proporção de 20.000:1.

Q2) À medida que o número de cacheIds aumenta, você esperaria que as estimativas para dados recém-inserido melhorassem naturalmente?

Q3) Existem maneiras (engolir, truques ou outras) para melhorar a estimativa (ou torná-la menos certa de 1 linha) sem ter que atualizar as estatísticas toda vez que um novo conjunto de dados é inserido (por exemplo, adicionando um conjunto de dados falso em um CacheId muito maior = 999999).

Aqui está o número real de linhas para todos os CacheIds na tabela:

CacheId Rows
39968   20247
40058   20247
40062   20247
40066   20247
40069   20247
41033   20247
41274   20247

[Não acho que os QPs sejam necessários para responder a essa pergunta e é um pouco trabalhoso limpá-los. Posso responder a perguntas específicas, se necessário! ]

Há uma declaração no final desta resposta aceita de que, se ocorrer um erro em um gatilho "For Delete", a ação de exclusão será revertida porque faz parte de uma transação implícita.

No entanto, aqui está um exemplo mostrando que as linhas excluídas permanecem excluídas mesmo que um erro seja gerado:

create table T1 ( i1 int  );
go

create trigger T1_ForDelete on T1
for delete
as
raiserror('Raised 16', 16, 1);
raiserror('Raised 18', 18, 1);
raiserror('Raised #2 16', 16, 255);
raiserror('Raised #2 18', 18, 255);
go

insert into T1 (i1) values (1);

set xact_abort on; -- makes no difference

delete from T1;

Saída (mudanças de ordem):

Msg 50000, Level 16, State 1, Procedure T1_ForDelete, Line 4
Raised 16
Msg 50000, Level 16, State 255, Procedure T1_ForDelete, Line 6
Raised #2 16

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure T1_ForDelete, Line 5
Raised 18
Msg 50000, Level 18, State 255, Procedure T1_ForDelete, Line 7
Raised #2 18

Então

select * from T1; -- Returns no records

Este é o comportamento esperado ou existe uma maneira de evitar a exclusão em caso de erro (por exemplo, gravidade/estado diferente)?

Não posso usar um "em vez de excluir" porque "on delete cascade" é empregado. Criar gatilho (MSDN), pesquisar "Em vez de"

Por que ResultFloat = 0 na consulta abaixo?

Estou perdendo um ponto nos documentos cast/convert , sql_variant ?

declare
    @v sql_variant = convert(decimal(28,8), 20.0);

select sql_variant_property(@v, 'BaseType') as BaseType,         -- 'decimal',
       iif(convert(int, 10.0)     < @v, 1, 0) as ResultInt,      -- 1
       iif(convert(decimal, 10.0) < @v, 1, 0) as  ResultDecimal, -- 1
       iif(convert(float, 10.0)   < @v, 1, 0) as  ResultFloat,   -- 0 !
       iif(convert(float, 10.0)   < convert(float, @v), 1, 0) as  ResultFloatFloat,  -- 1              
       iif(convert(float, 10.0)   < convert(decimal(28,8), @v), 1, 0) as  ResultFloatDecimal;   -- 1

SQL Server 2012

Para este caso específico (rastreamento de switches do Load Balancer), desejamos otimizar um Upsert para que

• não apresenta nenhuma condição de corrida,
• causar qualquer violação PK, ou
• adquirir quaisquer bloqueios de tamanho excessivo.

Eu entendo que bloqueios maiores (página) podem ser mais eficientes, mas para fins de questão, o objetivo é mínimo (linha). Existem vários links sobre o assunto upsert/lock, mas as respostas são um tanto inconsistentes (esp. updlock e multi-statements ) e este caso particular envolve uma subconsulta incorporada.

Definição da tabela:

create table [User].[SessionWebServerLog] (
  [SessionId] bigint not null,
  [IsSSL] bit not null default ((0)),
  [LastRequestUtc] datetime2(7) not null default (sysutcdatetime()),
  [WebServerProcessInstanceId] bigint not null,
  [RequestCount] int not null default ((1)),
  [FirstRequestUtc] datetime2(7) not null default (sysutcdatetime()),
  foreign key ([SessionId]) references [User].[Session] ( [SessionId] ) on delete cascade,
  primary key clustered ([SessionId] asc, [IsSSL] asc, [LastRequestUtc] desc, [WebServerProcessInstanceId] asc)
  with ( 
    allow_row_locks = on,
    allow_page_locks = off,  -- Needed else page locks were taken
  )
)

O SP deve inserir apenas se a combinação Session+IsSsl tiver alterado os IDs do servidor desde a solicitação mais recente para essa Session+IsSsl:

create proc [User].[usp_LogSessionWebServerRequest]    
    @pSessionId                   bigint, 
    @pWebServerProcessInstanceId  bigint,    
    @pIsSsl                       bit,        -- True for https, false for http
    @pDebug                       bit = 0     -- debug flag for print statements
as    
begin try        
    set xact_abort on;
    begin transaction;

        update l
           set RequestCount = RequestCount + 1,
               LastRequestUtc = sysutcdatetime()
          from [User].SessionWebServerLog l             
          with (rowlock, xlock, serializable) -- row level, exclusively held, until end of xact
         cross apply
             (
               select top(1) WebServerProcessInstanceId, LastRequestUtc
                 from [User].SessionWebServerLog 
                 with (rowlock, xlock, serializable) -- row level, exclusively held, until end of xact
                   -- PK supports this join:  SessionId, IsSsl, LastRequestUtc (desc), WebServerProcessId                       
                where SessionId = @pSessionId
                  and IsSSL = @pIsSsl
                order by LastRequestUtc desc 
             ) prev -- previous request
         where SessionId = @pSessionId
           and IsSSL = @pIsSsl
           and prev.WebServerProcessInstanceId = @pWebServerProcessInstanceId
           and l.WebServerProcessInstanceId = @pWebServerProcessInstanceId
           and l.LastRequestUtc = prev.LastRequestUtc;

        if (@@rowcount = 0) -- if no update occurred, insert new
        begin
            insert into [user].SessionWebServerLog
                 ( SessionId, WebServerProcessInstanceId, IsSSL )
            values 
                 ( @pSessionId, @pWebServerProcessInstanceId, @pIsSsl );                
        end            

    commit;            
end try
begin catch    
   if (xact_state() = -1 or @@trancount > 0)
    rollback;
   -- log, etc.
end catch

Essa rotina parece funcionar para casos simples, testando usando duas janelas e executando a primeira metade da transação dentro de cada janela e verificando o bloqueio.

Q1: Ele bloqueia quando a atualização não corresponde a nenhuma linha de nenhuma das janelas, mas ainda assim são chaves diferentes. O bloqueio ocorre porque o bloqueio do intervalo de chaves é mantido apenas nas chaves existentes ?

Vitória1:

declare
    @pSessionId                   bigint = 3, -- does not exist in table
    @pWebServerProcessInstanceId  bigint = 100,    
    @pIsSsl                       bit = 0;

 sp_lock 72:

spid  dbid  ObjId      IndId   Type       Resource  Mode      Status
  72    16      0          0     DB                 S          GRANT 
  72    16  388964512      1    KEY (6c2787a590a2)  RangeX-X   GRANT
  72    16  388964512      0    TAB                 IX         GRANT

Vitória2:

 declare
        @pSessionId                   bigint = 4,  -- does not exist in table
        @pWebServerProcessInstanceId  bigint = 100,    
        @pIsSsl                       bit = 0;

    sp_lock 92:

    spid  dbid      ObjId   IndId   Type  Resource         Mode   Status
    92      16          0       0     DB                    S      GRANT
    92      16  388964512       1    KEY  (6c2787a590a2) RangeX-X   WAIT
    92      16  388964512       0    TAB                    IX     GRANT

declare @pSessionId bigint = 4, @pWebServerProcessInstanceId bigint = 100,
@pIsSsl bit = 0;

Q2: Se eu permitir bloqueios de página (o padrão) no PK, por que um bloqueio de página é retirado mesmo que a dica de bloqueio de linha tenha sido especificada.

spid  dbid      ObjId   IndId   Type     Resource      Mode      Status
72      16          0       0      DB                    S       GRANT
72      16  388964512       1     PAG       1:444       IX       GRANT
72      16  388964512       1     KEY (6c2787a590a2)  RangeX-X   GRANT
72      16  388964512       0     TAB                   IX       GRANT

O nível de isolamento da transação é o padrão "leitura confirmada". Eu escolhi não alterar para este específico porque restaurá-lo parece mais confuso (para sucesso e falha e assumir/determinar o padrão) do que apenas usar bloqueios de tabela (imo).

Plano de consulta para o caso zero:

Plano de consulta quando existem várias linhas para WebSession+Ssl com datas diferentes (exatamente uma linha da ramificação para o topo, perfeita, aparentemente usando o PK por data):

Q3: Isso é um exagero - existem outras dicas que atingirão os objetivos? (Por favor, não reorganize a consulta ou tente converter em declaração de mesclagem para fins desta pergunta).

De vez em quando (por exemplo, ~meses), um trabalho por hora do SQL Server Agent começará a relatar um erro 15404 e continuará a fazê-lo até que intervenha.

[298] Erro do SQLServer: 15404, não foi possível obter informações sobre o grupo/usuário do Windows NT 'DOMAIN_NAME\SomeDomainAccount', código de erro 0x6e. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Às vezes, a primeira falha ocorre imediatamente após uma reinicialização manual dos serviços SQL Server Engine e SQL Server Agent. O problema pode ser resolvido reiniciando a máquina.

O proprietário do trabalho é o nome listado na mensagem de erro e é um administrador do SQL Server.

A conta SQL Server Engine Service parece ser uma conta de serviço (acredito que seja a conta de instalação padrão (um ponto melhor que o NetworkService genérico para evitar interferência entre as instâncias do mecanismo/agente):

   NT Service\MSSQL$INSTNAME

Seria uma coisa se o trabalho sempre falhasse, mas como o trabalho é bem-sucedido após uma reinicialização, isso me faz pensar que uma conta de serviço como deveria estar funcionando e que há algum problema de tempo A/D ou possivelmente um bug. Quando a TI é questionada sobre a configuração A/D, a resposta geralmente é "nada mudou".

• Reiniciar o mecanismo e os serviços do agente pode fazer com que o trabalho comece a falhar.
• Uma reinicialização da máquina elimina o problema.
• Uma reinicialização subsequente imediata do mecanismo e do agente não causa mais falha na tarefa.

Link: Como solucionar um erro do SQL Server 8198

Como o otimizador decide se deve ou não usar paralelismo quando o limite de paralelismo é especificado em segundos (em hardware específico , veja abaixo), mas os custos estimados de CPU são supostamente sem unidades? A estimativa é tratada como se fossem segundos ou há uma conversão acontecendo internamente com base em algumas métricas de hardware?

Do MS: limite de custo para opção de paralelismo

O custo refere-se a um tempo decorrido estimado em segundos necessário para executar o plano serial em uma configuração de hardware específica

como-eu-leio-o-custo-da-consulta-e-é-sempre-uma-porcentagem

sql-server-execution-plan-estimated-io-cost-estimated-cpu-cost-no-unit