Eu quero abstrair uma hierarquia do mundo real em um modelo de banco de dados estrito (o mais estrito possível).

Meu problema é criar uma estrutura com um conjunto de regras isso ou isso, mas não os dois.

Eu tenho dois tipos de entidade,

• Device , deve ser conectável a outro "dispositivo" como filho e como pai.

• Combiner , pode ser conectado a vários "dispositivos" como pais e um como filho.

MAS(!) um dispositivo só pode ser conectado a um deles ao mesmo tempo. Então, como "dispositivo" como apenas um filho e um dos pais.

Eu tento visualizá-lo de alguma forma:

Todas as minhas ideias não são rígidas, mas procuro se existe um padrão para resolver isso sem regras em nível de aplicação. Em um modelo de classe eu resolveria isso com herança. Algumas ideias?

Eu tenho uma tabela particionada e problemas com eliminação de partição.

Configurar

Minha tabela é bastante simples e se parece com isso:

CREATE TABLE [data].[OperationData](
    [DataLoggerID] [bigint] NOT NULL,
    [End] [datetime2](7) NOT NULL
CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED 
(
    [DataLoggerID] ASC,
    [End] ASC
))

A tabela é particionada na [End]coluna por data.

Problema

Eu executo uma consulta simples para encontrar a linha mais recente por [End]coluna.

SELECT TOP(1)
    [OperationData].[DataLoggerID],
    [OperationData].[End]
FROM [data].[OperationData]
WHERE [OperationData].[DataLoggerID] = 30217  
ORDER BY [OperationData].[End] DESC

O problema é que o plano de execução mostra que todas as partições foram pesquisadas e nenhuma eliminação de partição ocorreu.

Pergunta

A eliminação de partição é possível nesta consulta? E quando sim porque não é usado?

Um pouco mais...

Não consigo colar o plano em "Colar o plano" (aparece um erro), então copio-o aqui.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.520" Build="15.0.400.390" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="26.2112" StatementText="SELECT TOP(1)&#xD;&#xA;    [OperationData].[DataLoggerID],&#xD;&#xA;   [OperationData].[End]&#xD;&#xA;FROM [data].[OperationData] AS [OperationData]  &#xD;&#xA;WHERE [OperationData].[DataLoggerID] = 30217  &#xD;&#xA;ORDER BY [OperationData].[End] DESC" StatementType="SELECT" QueryHash="0xCA9C86DFB3F9CA37" QueryPlanHash="0xB953A3686656A27D" RetrievedFromCache="true" StatementSqlHandle="0x090060DDDE8D2D2A050D6AF14FDF6902EDD10000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="7" ParentObjectId="0" StatementParameterizationType="0" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="1024" CachedPlanSize="280" CompileTime="6783" CompileCPU="52" CompileMemory="7848">
            <MissingIndexes>
              <MissingIndexGroup Impact="95.7193">
                <MissingIndex Database="[powerdoo]" Schema="[data]" Table="[OperationData]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[DataLoggerID]" ColumnId="1" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <Warnings>
              <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="16" DesiredMemory="24" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="3104936" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="5872010" EstimatedPagesCached="1101001" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="9384056" />
            <WaitStats>
              <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="805" WaitCount="19" />
              <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="15" WaitCount="8" />
            </WaitStats>
            <QueryTimeStats CpuTime="76" ElapsedTime="846" />
            <RelOp AvgRowSize="23" EstimateCPU="1.00318" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="26.2112">
              <OutputList>
                <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="845" ActualCPUms="76" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="1024" UsedMemoryGrant="16" />
              </RunTimeInformation>
              <TopSort Distinct="false" Rows="1">
                <OrderBy>
                  <OrderByColumn Ascending="false">
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                  </OrderByColumn>
                </OrderBy>
                <RelOp AvgRowSize="23" EstimateCPU="1.22175" EstimateIO="23.975" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15680.7" EstimatedRowsRead="15680.7" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="25.1968" TableCardinality="81308300">
                  <OutputList>
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="49050" ActualRowsRead="49050" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="730" ActualCPUms="64" ActualScans="7672" ActualLogicalReads="2533" ActualPhysicalReads="0" ActualReadAheads="65" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <RunTimePartitionSummary>
                    <PartitionsAccessed PartitionCount="7672">
                      <PartitionRange Start="1" End="7672" />
                    </PartitionsAccessed>
                  </RunTimePartitionSummary>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Index="[PK_OperationData]" Alias="[OperationData]" IndexKind="Clustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <StartRange ScanType="GE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </StartRange>
                          <EndRange ScanType="LE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(7672)">
                                <Const ConstValue="(7672)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </EndRange>
                        </SeekKeys>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(30217)">
                                <Const ConstValue="(30217)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </TopSort>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Eu tenho um problema de E/S com uma tabela grande.

Estatísticas gerais

A mesa tem as seguintes características principais:

• ambiente: Banco de Dados SQL do Azure (camada é P4 Premium (500 DTUs))
• linhas: 2.135.044.521
• 1.275 partições usadas
• índice clusterizado e particionado

Modelo

Esta é a implementação da tabela:

CREATE TABLE [data].[DemoUnitData](
    [UnitID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [Value1] [decimal](18, 2) NULL,
    [Value2] [decimal](18, 2) NULL,
    [Value3] [decimal](18, 2) NULL,
    CONSTRAINT [PK_DemoUnitData] PRIMARY KEY CLUSTERED 
    (
        [UnitID] ASC,
        [Timestamp] ASC
    )
)
GO

ALTER TABLE [data].[DemoUnitData] WITH NOCHECK ADD CONSTRAINT [FK_DemoUnitData_Unit] FOREIGN KEY([UnitID])
REFERENCES [model].[Unit] ([ID])
GO

ALTER TABLE [data].[DemoUnitData] CHECK CONSTRAINT [FK_DemoUnitData_Unit]
GO

O particionamento está relacionado a isso:

CREATE PARTITION SCHEME [DailyPartitionSchema] AS PARTITION [DailyPartitionFunction] ALL TO ([PRIMARY])

CREATE PARTITION FUNCTION [DailyPartitionFunction] (datetime) AS RANGE RIGHT
FOR VALUES (N'2017-07-25T00:00:00.000', N'2017-07-26T00:00:00.000', N'2017-07-27T00:00:00.000', ... )

Qualidade de serviço

Eu acho que os índices e estatísticas são bem mantidos todas as noites por reconstrução/reorganização/atualização incremental.

Estas são as estatísticas de índice atuais das partições de índice mais usadas:

Estas são as propriedades estatísticas atuais das partições mais usadas:

Problema

Eu executo uma consulta simples em alta frequência na tabela.

SELECT [UnitID]
    ,[Timestamp]
    ,[Value1]
    ,[Value2]
    ,[Value3]
FROM [data].[DemoUnitData]
WHERE [UnitID] = 8877 AND [Timestamp] >= '2018-03-01' AND [Timestamp] < '2018-03-13'
OPTION (MAXDOP 1)

O plano de execução fica assim: https://www.brentozar.com/pastetheplan/?id=rJvI_4TtG

Meu problema é que essas consultas produzem uma quantidade extremamente alta de operações de E/S, resultando em um gargalo de PAGEIOLATCH_SHesperas.

Pergunta

Eu li que PAGEIOLATCH_SHas esperas geralmente estão relacionadas a índices não bem otimizados. Você tem alguma recomendação para mim sobre como reduzir as operações de E/S? Talvez adicionando um índice melhor?

Resposta 1 - relacionada ao comentário de @S4V1N

O plano de consulta postado foi de uma consulta que executei no SSMS. Após seu comentário, faço uma pesquisa sobre o histórico do servidor. A consulta real executada do serviço parece um pouco diferente (relacionada ao EntityFramework).

(@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)) 

SELECT 1 AS [C1], [Extent1] 
   .[Timestamp] AS [Timestamp], [Extent1] 
   .[Value1] AS [Value1], [Extent1] 
   .[Value2] AS [Value2], [Extent1] 
   .[Value3] AS [Value3]  
FROM [data].[DemoUnitData] AS [Extent1]  
WHERE ([Extent1].[UnitID] = @p__linq__0)  
AND ([Extent1].[Timestamp] >= @p__linq__1)  
AND ([Extent1].[Timestamp] < @p__linq__2) OPTION (MAXDOP 1) 

Além disso, o plano parece diferente:

https://www.brentozar.com/pastetheplan/?id=H1fhALpKG

ou

https://www.brentozar.com/pastetheplan/?id=S1DFQvpKz

E como você pode ver aqui, nosso desempenho de banco de dados dificilmente é influenciado por essa consulta.

Resposta 2 - relacionada à resposta de @Joe Obbish

Para testar a solução, substituí o Entity Framework por um SqlCommand simples. O resultado foi um incrível aumento de desempenho!

O plano de consulta agora é o mesmo do SSMS e as leituras e gravações lógicas caem para ~8 por execução.

A carga geral de E/S cai para quase 0!

Também explica por que recebo uma grande queda de desempenho depois de alterar o intervalo de partição de mensal para diário. A falta de eliminação de partição resultou em mais partições para verificar.

Eu tenho um grande problema com picos de CPU de 100% devido a um plano de execução ruim usado por uma consulta específica. Passo semanas agora resolver sozinho.

Meu banco de dados

Meu banco de dados de exemplo contém 3 tabelas simplificadas.

[Data logger]

CREATE TABLE [model].[DataLogger](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ProjectID] [bigint] NULL,
CONSTRAINT [PK_DataLogger] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

[Inversor]

CREATE TABLE [model].[Inverter](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SerialNumber] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Inverter] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UK_Inverter] UNIQUE NONCLUSTERED 
(
    [DataLoggerID] ASC,
    [SerialNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [model].[Inverter] WITH CHECK
ADD CONSTRAINT [FK_Inverter_DataLogger]
FOREIGN KEY([DataLoggerID])
REFERENCES [model].[DataLogger] ([ID])

[Dados do Inversor]

CREATE TABLE [data].[InverterData](
    [InverterID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [DayYield] [decimal](18, 2) NULL,
 CONSTRAINT [PK_InverterData] PRIMARY KEY CLUSTERED 
(
    [InverterID] ASC,
    [Timestamp] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

Estatísticas e manutenção

A [InverterData]tabela contém vários milhões de linhas (difere em várias instâncias PaaS) particionadas em lixos mensais.

Todos os indexadores são desfragmentados e todas as estatísticas são recompiladas/reorganizadas conforme necessário em um turno diário/semanal.

Minha consulta

A consulta é gerada pelo Entity Framework e também simples. Mas eu corro 1.000 vezes por minuto e o desempenho é essencial.

SELECT
[Extent1].[InverterID] AS [InverterID],
[Extent1].[DayYield] AS [DayYield]
FROM [data].[InverterDayData] AS [Extent1]
INNER JOIN [model].[Inverter] AS [Extent2] ON [Extent1].[InverterID] = [Extent2].[ID]
INNER JOIN [model].[DataLogger] AS [Extent3] ON [Extent2].[DataLoggerID] = [Extent3].[ID]
WHERE ([Extent3].[ProjectID] = @p__linq__0)
AND ([Extent1].[Date] = @p__linq__1) OPTION (MAXDOP 1)

A MAXDOP 1dica é para outro problema com um plano paralelo lento.

O "bom" plano

Durante os 90% do tempo, o plano usado é extremamente rápido e se parece com isso:

O problema

Ao longo do dia, o plano bom mudou aleatoriamente para um plano ruim e lento.

O plano "ruim" é usado por 10 a 60 minutos e depois alterado de volta para o plano "bom". O plano "ruim" aumenta a CPU para 100% permanente.

Isto é o que parece:

O que eu tentei até agora

Meu primeiro pensamento foi o Hash Matché o bad boy. Então eu modifiquei a consulta com uma nova dica.

...Extent1].[Date] = @p__linq__1) OPTION (MAXDOP 1, LOOP JOIN)

O LOOP JOINdeve forçar a usar Nested Loopo instante de Hash Match.

O resultado é que o plano de 90% se parece com antes. Mas o plano também mudou aleatoriamente para um plano ruim.

O plano "ruim" agora se parece com isso (ordem do loop de tabela alterada):

A CPU também atinge 100% durante o plano "novo ruim".

Solução?

Me vem à mente forçar o "bom" plano. Mas não sei se isso é uma boa ideia.

Dentro do plano há um índice recomendado que inclui todas as colunas. Mas isso dobrará a tabela completa e diminuirá a velocidade das inserções que são muito frequentes.

Por favor me ajude!

Atualização 1 - relacionada ao comentário @James

Aqui estão os dois planos (alguns campos extras mostrados no plano porque são da tabela real):

Bom plano

Plano ruim 1 (Hash Match)

Plano ruim 2 (Loop aninhado)

Atualização 2 - relacionada à resposta do @David Fowler

O plano ruim está entrando em ação no valor do parâmetro aleatório. Então normalmente eu @p__linq__1 ='2016-11-26 00:00:00.0000000' @p__linq__0 =20825o dia inteiro e depois o plano ruim vem no mesmo valor.

Eu conheço o problema de sniffing de parâmetros de procedimentos armazenados e como evitá-los dentro do SP. Você tem uma dica para mim como evitar esse problema para minha consulta?

A criação do índice recomendado incluirá todas as colunas. Isso dobrará a tabela completa e diminuirá a velocidade das inserções, que são muito frequentes. Isso não "parece" certo para construir um índice que simplesmente clona a tabela. Também pretendo dobrar o tamanho dos dados desta grande tabela.

Atualização 3 - relacionada ao comentário de @David Fowler

Também não funcionou e acho que não poderia. Para uma melhor compreensão vou explicar como a consulta é chamada.

Vamos supor que eu tenha 3 entidades na [DataLogger]tabela. Ao longo do dia, eu chamo as mesmas 3 consultas em uma ida e volta:

Consulta básica: ...WHERE ([Extent3].[ProjectID] = @p__linq__0) AND ([Extent1].[Date] = @p__linq__1)

Parâmetro:

1. @p__linq__0 = 1; @p__linq__1 = '2018-01-05 00:00:00.0000000'
2. @p__linq__0 = 2; @p__linq__1 = '2018-01-05 00:00:00.0000000'
3. @p__linq__0 = 3; @p__linq__1 = '2018-01-05 00:00:00.0000000'

O parâmetro @p__linq__1é sempre a mesma data. Mas ele escolhe o plano ruim aleatoriamente em uma consulta que é executada milhares de vezes com um plano bom antes. Com o mesmo parâmetro!

Atualização 4 - relacionada ao comentário @Nic

A manutenção é executada todas as noites e se parece com isso.

Índice

Se um Índice for fragmentado em mais de 5%, ele será reorganizado...

ALTER INDEX [{index}] ON [{table}] REORGANIZE

Se um índice for fragmentado em mais de 30%, ele será reconstruído...

ALTER INDEX [{index}] ON [{table}] REBUILD WITH (ONLINE=ON, MAXDOP=1)

Se o Índice for particionado, ele será verificado quanto à fragmentação e alterado por partição...

ALTER INDEX [{index}] ON [{table}] REBUILD PARTITION = {partitionNr} WITH (ONLINE=ON, MAXDOP=1)

Estatisticas

Todas as estatísticas serão atualizadas se modification_counterfor maior que 0...

UPDATE STATISTICS [{schema}].[{object}] ([{stats}]) WITH FULLSCAN

ou em particionado..

UPDATE STATISTICS [{schema}].[{object}] ([{stats}]) WITH RESAMPLE ON PARTITIONS({partitionNr})

A manutenção inclui todas as estatísticas, também a gerada automaticamente.

Configurar

Tenho uma tabela simples:

CREATE TABLE [dbo].[StringData](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Hour]  AS (dateadd(hour,datediff(hour,(0),[TimeStamp]),(0))) PERSISTED,
    [Date]  AS (CONVERT([date],[TimeStamp],(0))) PERSISTED,
    [DCVoltage] [decimal](18, 2) NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    [DCPower]  AS (([DCVoltage]*[DCCurrent])/(1000)) PERSISTED,
    [IsCompressed] [bit] NULL,
    [TimeStamp15Minutes]  AS (dateadd(minute,(datediff(minute,(0),[TimeStamp])/(15))*(15),(0))),
CONSTRAINT [PK_StringData1] PRIMARY KEY CLUSTERED 
(
    [TimeStamp] DESC,
    [DCStringID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

E eu tenho um procedimento upsert chamado [InsertOrUpdateStringData]

ALTER PROCEDURE [dbo].[InsertOrUpdateStringData]
@DCStringID bigint, @TimeStamp datetime, @DCVoltage decimal(18,2), @DCCurrent decimal(18,2)
AS
/****** avoid parameter spoofing ******/
DECLARE @DCStringID_Value AS bigint; SET @DCStringID_Value = @DCStringID;
DECLARE @TimeStamp_Value AS datetime; SET @TimeStamp_Value = @TimeStamp
DECLARE @DCVoltage_Value AS decimal(18,2); SET @DCVoltage_Value = @DCVoltage
DECLARE @DCCurrent_Value AS decimal(18,2); SET @DCCurrent_Value = @DCCurrent
/************/

MERGE [StringData] AS TARGET
USING (VALUES (@DCStringID_Value, @TimeStamp_Value))
    AS SOURCE ([DCStringID], [TimeStamp])
    ON TARGET.[DCStringID] = SOURCE.[DCStringID] AND TARGET.[TimeStamp] = SOURCE.[TimeStamp]
WHEN MATCHED THEN
    UPDATE
    SET [DCVoltage] = @DCVoltage_Value,
    [DCCurrent] = @DCCurrent_Value
WHEN NOT MATCHED THEN
    INSERT ([DCStringID], [TimeStamp], [DCVoltage], [DCCurrent]) 
    VALUES (@DCStringID_Value, @TimeStamp_Value, @DCVoltage_Value, @DCCurrent_Value);

Usando

Muitos encadeamentos de aplicativos diferentes estão usando o procedimento [InsertOrUpdateStringData] para inserir dados na tabela síncrona.

Os índices da tabela são bem usados ​​e uma única execução é bastante rápida e leva cerca de 31ms.

Problema

Se outra operação de tabela não selecionada for executada (como inserção em massa), eles serão bloqueados por um tempo muito longo.

Infelizmente [sp_WhoIsActive] não está me mostrando se as chamadas de procedimento [InsertOrUpdateStringData] também estão bloqueando umas às outras. Mas parece que há um encadeamento de bloqueio porque é a única explicação para bloquear mais de 10.000 ms, como no meu exemplo.

Pergunta

Parece que meu procedimento upsert está bloqueando a tabela completa e retardando outras operações de inserção.

Há algo que eu possa fazer para otimizar minhas consultas para evitar bloqueios de longa data?

Atualização 1 - relacionada a Nic answere

O bloqueio ocorre em todas as operações não selecionadas, pois escrevi que a inserção em massa é apenas uma amostra.

Veja aqui uma chamada de mesclagem simples de bloqueio de 10 segundos (a sessão de bloqueio 176 é uma chamada [InsertOrUpdateStringData] ):

Eu também tentei @get_locks=1 aqui está o resultado. Talvez te ajude com mais detalhes.

Atualização 2 - relacionada ao Nic answere

Aqui está um exemplo:

sid 52 é bloqueado por sid 119 por mais de 40 segundos!? Mas o sid 119 parece não estar bloqueado. Eu realmente não entendo isso.

Estou com uma dúvida sobre a junção de duas tabelas.

Esquema

CREATE TABLE [dbo].[DCString] (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCDistributionBoxID] [bigint] NOT NULL,
    [CurrentMPP] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCString]
    ADD CONSTRAINT [FK_DCString_DCDistributionBox] FOREIGN KEY([DCDistributionBoxID])
    REFERENCES [dbo].[DCDistributionBox] ([ID])

CREATE TABLE [dbo].[StringData](
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)

A [StringData]tabela tem as seguintes estatísticas de armazenamento:

• Espaço para dados: 26.901,86 MB
• Contagem de linhas: 131.827.749
• Particionado: verdadeiro
• Contagem de partições: 62

Agora quero juntar os dados da [StringData]tabela com os dados da [DCString]tabela.

Algo como:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]
FROM [StringData]
RIGHT OUTER JOIN [StringData] ON [StringData].[DCStringID] = [DCString].[ID]
WHERE [StringData].[ID] = @dcStringID
AND [StringData].[TimeStamp] >= @begin
AND [StringData].[TimeStamp] < @end;

O que eu espero no intervalo de datas pesquisado onde [StringData]existem dados correspondentes na tabela é o seguinte:

ID   | TimeStamp
6658 | 22.02.2016 10:00:00
6658 | 22.02.2016 11:00:00
6658 | 22.02.2016 12:00:00

... e em um intervalo de datas pesquisado em que não [StringData]existem dados correspondentes na tabela é este:

ID   | TimeStamp
6658 | NULL

Pergunta

O que recebo em um intervalo de datas pesquisado em que não [StringData]existem dados correspondentes na tabela é um resultado de 0 linhas. Por quê?

Eu simplesmente quero sempre obter todos os [DCString].[ID]s. O que há de errado com meu JOIN ou eu errei completamente?

Atualização 1 (relacionada à resposta de @Aaron Bertrand):

Já tentei do seu jeito, mas tenho que cancelar o teste da consulta porque depois de 10min ainda está rodando. Ficou assim:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]    
FROM [StringData]    
LEFT JOIN [DCString] 
    ON [StringData].[DCStringID] = [DCString].[ID] 
    AND [StringData].[TimeStamp] >= @begin
    AND [StringData].[TimeStamp] < @end
WHERE [StringData].[ID] = @dcStringID;

Eu tenho um gargalo de desempenho com uma SELECT GROUP BYoperação.

Esquema

Eu tenho uma tabela assim:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,    
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED 
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

e um Indexassim:

CREATE NONCLUSTERED INDEX [TimeStamp_Power-NonClusteredIndex] ON [dbo].[InverterData]
(
    [InverterID] ASC,
    [TimeStamp] ASC
)
INCLUDE 
(   
    [ValueA],
    [ValueB]
)

A [InverterData]tabela possui as seguintes estatísticas de armazenamento:

• Espaço para dados: 26.901,86 MB
• Contagem de linhas: 131.827.749
• Particionado: verdadeiro
• Contagem de partições: 62

Uso

Com meu esquema descrito (um mais algumas tabelas extras que não são importantes para minha pergunta), posso executar consultas super rápidas como esta:

SELECT [TimeStamp], [ValueA], [ValueB]

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160108'   
 AND [InverterData].[TimeStamp] < '20160109'

Tempo de execução : 178ms

Plano de execução:

Problema

Agora quero fazer um SELECT GROUP BYintervalo de [InverterID] e 15 minutos de [TimeStamp].

Alguns pensam assim:

SELECT [InverterID]
 , DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15 * 15, 0) AS [TimeStamp]
 , SUM([ValueA]), SUM([ValueB])

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160107'
 AND [InverterData].[TimeStamp] < '20160108'

GROUP BY
 [InverterID], DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [InverterData].[TimeStamp] ) / 15 * 15, 0)

Tempo de execução : 4637ms

Plano de execução:

tentativas

Acho que pode estar relacionado à Sortoperação necessária aqui:

Tanto quanto sei, é possível evitar isso SORTcriando um indexador correspondente. Mas não sei como fazer isso com meu agrupamento de intervalos de 15 minutos.

Pergunta

Como você pode ver, o tempo de execução do SELECT GROUP BYé muito mais longo. Mas não sei onde e como evitar o gargalo!?

Atualização 1 (relacionada à resposta de @Max Vernon)

Se for possível gostaria de ter uma solução mais rápida onde eu possa alterar o intervalo de forma flexível (por exemplo 10, 15 ou 6o minutos). Portanto, sem colunas calculadas.

Eu tenho um problema com uma quantidade enorme de INSERTs que estão bloqueando minhas operações SELECT.

Esquema

Eu tenho uma tabela assim:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,    
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED 
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

Eu também tenho este pequeno procedimento auxiliar, que me permite inserir ou atualizar (atualizar em caso de conflito) com o comando MERGE:

CREATE PROCEDURE [InsertOrUpdateInverterData]
    @InverterID bigint, @TimeStamp datetime
    , @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
    MERGE [InverterData] AS TARGET
        USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
        AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
        ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
    WHEN MATCHED THEN
        UPDATE
        SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]              
    WHEN NOT MATCHED THEN
        INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB]) 
        VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END

Uso

Agora executei instâncias de serviço em vários servidores que executam atualizações massivas chamando o [InsertOrUpdateInverterData]procedimento rapidamente.

Existe também um site que faz consultas SELECT na [InverterData]tabela.

Problema

Se eu fizer consultas SELECT na [InverterData]tabela, elas serão processadas em intervalos de tempo diferentes, dependendo do uso de INSERT das minhas instâncias de serviço. Se eu pausar todas as instâncias de serviço, o SELECT é extremamente rápido, se a instância executar uma inserção rápida, os SELECTs ficam muito lentos ou até mesmo um cancelamento de tempo limite.

tentativas

Eu fiz alguns SELECTs na [sys.dm_tran_locks]tabela para encontrar processos de bloqueio, como este

SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Este é o resultado:

S = Compartilhado. A sessão de espera recebe acesso compartilhado ao recurso.

Pergunta

Por que os SELECTs estão bloqueados pelo [InsertOrUpdateInverterData]procedimento que está usando apenas comandos MERGE?

Tenho que usar algum tipo de transação com modo de isolamento definido dentro de [InsertOrUpdateInverterData]?

Atualização 1 (relacionada à pergunta de @Paul)

Baseie-se no relatório interno do servidor MS-SQL sobre as [InsertOrUpdateInverterData]seguintes estatísticas:

• Tempo médio de CPU: 0,12ms
• Média de processos de leitura: 5,76 por/s
• Média de processos de gravação: 0,4 por/s

Com base nisso, parece que o comando MERGE está ocupado principalmente com operações de leitura que bloquearão a tabela!(?)

Atualização 2 (relacionada à pergunta de @Paul)

A [InverterData]tabela possui as seguintes estatísticas de armazenamento:

• Espaço para dados: 26.901,86 MB
• Contagem de linhas: 131.827.749
• Particionado: verdadeiro
• Contagem de partições: 62

Aqui está o conjunto de resultados sp_WhoIsActive (quase) completo:

SELECTcomando

• dd hh:mm:ss.mss: 00 00:01:01.930
• id_sessão: 73
• wait_info: (12629ms)LCK_M_S
• CPU: 198
• blocking_session_id: 146
• lê: 99.368
• escreve: 0
• estado: suspenso
• open_tran_count: 0

[InsertOrUpdateInverterData]comando de bloqueio

• dd hh:mm:ss.mss: 00 00:00:00.330
• ID da sessão: 146
• wait_info: NULL
• CPU: 3.972
• blocking_session_id: NULL
• lê: 376,95
• escreve: 126
• estado: dormindo
• open_tran_count: 1

Tenho uma dúvida sobre indexador e planos de execução no T-SQL.

Meu banco de dados é o SQL Server 2008.

Eu tenho um esquema de banco de dados simples de três tabelas:

A InverterDatatabela é muito grande e particionada (28.880.436 linhas).

A Datecoluna é calculada assim:

[Date] AS (ISNULL(CONVERT([date], [TimeStamp]), CONVERT([DATE], '19000101', (112)))) PERSISTED NOT NULL,

Há também um índice para esta coluna:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC)

Selecione a consulta nº 1 :

Agora quero fazer uma seleção simples que inclua todas as três tabelas e a coluna 'Data' em uma cláusula where:

SELECT 
    [TimeStamp], [ACPower], [DCPower]
FROM 
    [InverterData]
JOIN 
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN 
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE 
    [InverterData].[Date] = '05.01.2016'
    AND [DataLogger].[ProjectID] = 20686

Demorou cerca de 19 segundos no banco de dados atual (resultado ~ 80 linhas).

Este é o plano de execução:

Selecione a consulta nº 2 :

No primeiro select detectei que existe uma busca de índice de longa duração para a coluna 'Data'. Portanto, executo uma segunda seleção que inclui apenas a coluna de chave primária 'TimeStamp'.

Esta é a segunda seleção:

SELECT 
    [TimeStamp], [ACPower], [DCPower]
FROM 
    [InverterData]
JOIN 
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN 
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE 
    [TimeStamp] >= '05.01.2016' AND [TimeStamp] < '06.01.2016'
    AND [DataLogger].[ProjectID] = 20686

Demorou apenas cerca de 2 segundos no meu banco de dados atual.

Este é o plano de execução:

Pergunta:

Por que há duas buscas de índice? Incluí todas as colunas usadas do select 1 em um índice. Por que demorou tanto mais?

Atualização 1 :

Se alguém precisar do esquema completo, irei adicioná-lo a um violino SQL e publicá-lo.

Atualização 2 :

Dica de busca de índice