LCJ's questions

Eu tenho uma tabela no SQL Server 2016, conforme mostrado abaixo. Os registros são inseridos na tabela a cada trimestre, para todos os parceiros.

Restrição necessária: deve permitir apenas um BatchID para um partnerID por um trimestre de um ano, para um Distribution_Type. No entanto, é permitido ter vários registros com o mesmo BatchID para o parceiro em um determinado trimestre do ano, para um tipo de distribuição.

Como criar tal restrição na tabela? Se puder ser feito sem gatilho, seria ótimo.

Mesa

CREATE TABLE [dbo].[Distribution]
(
    [Distribtution_Result_ID] [int] IDENTITY(1,1) NOT NULL,
    [PartnerID] [int] NOT NULL,
    [Distribution_Year] [int] NOT NULL,
    [Distribution_Quarter] [int] NOT NULL,
    [Distribution_Amount] [decimal](16, 4) NULL,
    [Distribution_Type] [varchar](100) NULL,
    [Batch_ID] [int] NOT NULL
)

A definição de Query_hash de sys.dm_exec_query_stats é:

query_hash: valor de hash binário calculado na consulta e usado para identificar consultas com lógica semelhante. Você pode usar o hash de consulta para determinar o uso de recursos agregados para consultas que diferem apenas por valores literais.

Mas quando procuro um query_hash específico (como mostrado abaixo), estou recebendo vários textos de consulta que diferem muito na lógica e size_in_bytes(para o plano).

Observação: estou usando o SQL Server 2016

DECLARE @QueryHashTest BINARY(8)
SET @QueryHashTest = CONVERT(BINARY(8), 'Ð…U¹üŒv¿')

SELECT  
        QCP.objtype
        ,qStat.query_hash,
        CONVERT(VARCHAR(100),  qStat.query_Hash) AS VARCHAR_query_hash
        ,sText.text AS QueryText
        ,QCP.size_in_bytes
        ,qStat.creation_time
        ,qp.query_plan  
FROM ( 
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Proc'
AND qStat.query_hash= @QueryHashTest 
AND (size_in_bytes >= 2179072 OR size_in_bytes <= 262144)
ORDER BY size_in_bytes DESC

Resultado

Por que as consultas não relacionadas mostram o mesmo query_hash? É um bug ou há algo inesperado acontecendo no banco de dados?

Eu tenho uma consulta enorme no SQL Server 2016 – quando executo a consulta para um subconjunto de registros, ela é concluída em 15 minutos. Quando eu extrapolo para o número total de registros, ele deve ser concluído em 1 hora. Mas não está completo mesmo após 3 horas. Quando verifiquei master.sys.sysprocesses, há uma espera CXPACKET com 10293331 waittime

SELECT waittime, waittype, lastwaittype,  open_tran, cpu, physical_io, [sid], loginame, [sql_handle]      
FROM master.sys.sysprocesses WHERE SPID=58

Nota: eu fiz exec sp_Who2para obter o SPID = 58 específico

De Como usar sp_WhoIsActive para encontrar consultas lentas do SQL Server

@get_task_info = 2 – se uma consulta for paralela e você estiver solucionando problemas de esperas do CXPACKET, você poderá descobrir o que cada tarefa na consulta está aguardando.

Executei sp_WhoIsActive com @get_task_info = 2.

O wait_infodá o seguinte:

(5x: 68/168850/635541ms)CXPACKET:15, (3x: 3/16/32ms)CXPACKET:7, (1x: 10207374ms)CXPACKET:1

Como determinar o que cada tarefa está esperando a partir dessas informações?

ATUALIZAR

Esta questão é mais sobre encontrar uma "maneira" de descobrir os recursos em espera (usando sp_WhoIsActive ou similar) do que realmente corrigir esse problema de desempenho atual adicionando MAXDOP ou analisando o plano de execução.

Eu tenho a seguir duas abordagens de consulta. A Abordagem 1 funciona em 20 segundos. A Abordagem 2 não foi concluída mesmo após o expediente.

Abordagem 1

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

Abordagem 2

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$'; 

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

Observação: as duas acima são consultas simplificadas. As consultas reais podem ser vistas no plano de execução abaixo.

Planos de Execução

Plano de Execução Real – Abordagem 1 https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

Plano Estimado - Abordagem 2: https://www.brentozar.com/pastetheplan/?id=SJddO3en7

Nota: O plano do Approach 2 usa Nested Loopjoin em vez de Hash Matchjoin

Pergunta

Eu sei que isso pode ser corrigido adicionando OPTION (RECOMPILE). Se bem entendi, a lentidão está acontecendo, pois o SQL Server está criando um plano para acomodar a NULLpossibilidade da variável.

No meu caso, a variável será Non-Nullantes da execução da consulta (garantida)

1. No SQL Server 2016, quais são as possibilidades de informar ao mecanismo SQL que a variável será não nula e preparar um plano apenas para tal condição, sem usar OPTION (RECOMPILE)?

2. A lentidão é chamada de sniffing de parâmetros? (Não há parâmetro aqui. É uma variável local em uma consulta autônoma)

Nota: estou tentando ver opções para fazer a consulta rodar mais rápido sem usar RECOMPILE.

Referências :

1. Consulta do SQL Server: Rápida com Literal, mas Lenta com Variável
2. Melhorando o desempenho da consulta com OPTION (RECOMPILE), Constant Folding e evitando problemas de Parameter Sniffing
3. Sniffing de Parâmetros vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
4. Variáveis ​​locais versus procedimentos armazenados parametrizados no SQL Server

Tenho as seguintes consultas para selecionar o pedido mais recente de cada usuário. Existe alguma vantagem de performance na consulta utilizando CTE? Para minhas tabelas atuais, ambas estão fornecendo o mesmo plano e tempo de execução.

Obs: Estou usando o SQL Server 2012

DECLARE  @Person TABLE (PersonId INT, NameFirst VARCHAR(100), NameLast VARCHAR(100))
DECLARE @Order TABLE (OrderId INT, PersonId INT, OrderDateTime DATETIME)

--Query1
SELECT A.PersonID,A.NameFirst,A.NameLast, A.OrderID AS LastOrderID,A.OrderDateTime AS LastOrderDateTime
FROM
(
    SELECT P.PersonID,P.NameFirst,P.NameLast,O.OrderID,O.OrderDateTime, 
           ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.PersonID,O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)A
WHERE RowNumber = 1
ORDER BY PersonID

--Query2
;WITH CTE AS
(
    SELECT  P.PersonID,P.NameFirst,NameLast,O.OrderID AS LastOrderID,O.OrderDateTime AS LastOrderDateTime,
            ROW_NUMBER() OVER(PARTITION BY O.PersonID ORDER BY O.OrderDateTime DESC) AS RowNumber
    FROM @Person P
    LEFT OUTER JOIN @Order O
        ON P.PersonId = O.PersonId
)
SELECT A.PersonID,A.NameFirst,A.NameLast, A.LastOrderID, A.LastOrderDateTime
FROM CTE A
WHERE RowNumber = 1
ORDER BY PersonID

Estou tentando descobrir os Planos de Execução que tem Probe Residual.

Precisa saber o seguinte

1. Qual operador físico e lógico tem esseProbe Residual
2. Qual é o % de custo desse operador na consulta
3. Plano de Execução Associado
4. Texto da consulta

A seguir está uma tentativa que fiz - mas estou preso por obter outros detalhes. Como obter esses detalhes?

Obs: Estou usando o SQL Server 2012

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            ]')

Um exemplo de resíduo de sonda

Referências de blogs/artigos de Grant Fritchey e Rob Farley abaixo

1. Investigar resíduo em correspondência de hash - lar do assustador DBA
2. Probe Residual quando você tem um Hash Match – um custo oculto nos planos de execução

Eu tenho uma tabela listada abaixo no SQL Server 2012. Há um índice clusterizado em RequisitionID – mas esta coluna não é exclusiva. Pode haver muitos ProductID para um RequisitionID.

CREATE TABLE [dbo].[RequisitionProducts](
    [RequisitionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [Qty] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [UnitAmount] [decimal](10, 2) NOT NULL,
 CONSTRAINT [pk_RequisitionProducts] PRIMARY KEY NONCLUSTERED 
    (
        [RequisitionID] ASC,
        [ProductID] ASC
    ) 
)

CREATE CLUSTERED INDEX [cidx_RequistionProducts] ON [dbo].[RequisitionProducts]
(
    [RequisitionID] ASC
) 
GO

Pesquisei bastante e descobri que o Clustered Index pode ser não exclusivo - mas apenas em um cenário limitado. O único cenário mencionado apropriado é quando há uma pesquisa de intervalo . No meu caso, quase todas as consultas serão baseadas apenas em RequisitionID - e não há necessidade de pesquisa de intervalo.

Devo adicionar ProductID também para tornar o índice clusterizado exclusivo? Quais são os prós e contras?

Eu tenho uma tabela existente chamada “Pedidos” em nosso sistema. OrderID é a chave primária nesta tabela – é um índice clusterizado. Eu tenho uma nova tabela projetada conforme mostrado abaixo para “OrderCompanyDetails”. Tem um 1-to-1 relacionamento com a tabela Pedidos. Na nova tabela, OrderID é mantido como a chave primária agrupada.

Os dados são inseridos na nova tabela somente quando o pedido é aprovado. Portanto, o OrderID sendo inserido na nova tabela pode não estar em sequência. O OrderID 10 pode ser inserido antes do OrderID 5, dependendo de qual pedido for aprovado primeiro.

Ter o índice clusterizado em OrderID ajuda minhas consultas. Mas o índice clusterizado está em uma coluna que obtém dados em uma sequência aleatória. É um design de índice ruim? Se sim, devo adicionar uma nova coluna de identidade sem sentido com o nome OrderCompanyDetailID e torná-la um índice clusterizado?

CREATE TABLE [dbo].[Orders]
(
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderType] [char](3) NOT NULL,
    [StatusCD] [char](10) NOT NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF__Orders__CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC
    )
)

CREATE TABLE [dbo].[OrderCompanyDetails](
    [OrderID] [int] NOT NULL,
    [POCompanyCD] [char](4) NULL,
    [VendorNo] [varchar](9) NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF_OrderCompanyDetails_CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_OrderCompanyDetails] PRIMARY KEY CLUSTERED 
    (
        [OrderID] ASC
    )
)

ATUALIZAR

Leia recentemente o artigo Chave de agrupamento cada vez maior – o debate sobre o índice agrupado……….novamente! . Tem um comentário nisso

Lembre-se – estreito, estático, único, sempre crescente – e geralmente uma chave substituta em vez de uma chave natural.

Eu segui a versão simplificada da minha consulta real, no SQL Server 2012. Ele tem um operador escalar no predicado de busca ao selecionar dados da tabela de contêineres.

Qual é o propósito do operador escalar neste predicado de busca?

CREATE TABLE #EligibleOrders (OrderID INT PRIMARY KEY,
                             StatusCD CHAR(3),
                              CreatedOnDate DATETIME
                              )
--insert logic into #EligibleOrders

--Final Query
SELECT T2.OrderID ,olic.LineItemID,
        SUM(c.quantity) AS ShippedQty,
        COUNT(DISTINCT c.ContainerID) AS ShippedCases
FROM #EligibleOrders T2
INNER JOIN dbo.OrderLineItemContainers (NOLOCK) AS olic 
    ON  olic.OrderID = T2.OrderID
INNER JOIN dbo.Containers (NOLOCK) AS c
    ON olic.Containerid = c.Containerid
GROUP BY T2.OrderID ,olic.LineitemID 
OPTION (MAXDOP 1)

Plano de execução

Procurar Predicado

Eu tenho um banco de dados SQL Server 2012. Percebi valor Reason for early termination of statement optimizationpara algumas consultas e todas deram Good Enough Plan Found. Agora minhas perguntas são:

1. Quais são todos os tipos possíveis de “Razão para término antecipado da otimização de instrução”. Eu procurei por isso no msdn, mas não obtive uma lista completa de valores.

2. Existe um DMV ou evento estendido para listar todas as consultas para as quais a otimização foi encerrada devido a outros motivos que não o Plano Bom o Suficiente Encontrado? Referi-me a seguir dois artigos que não listam a lista completa de possibilidades. [Além disso, eles me dão resultados diferentes no meu banco de dados].

   • Descoberta: tempo limite de compilação da consulta
   • Identificando planos de consulta que não são bons o suficiente

Depois de ler os blogs a seguir, entendo que as hash matchcausas agregadas blocking. Usando índices apropriados, pode ser feito como stream aggregate.

1. Operadores agregados bloqueadores/não bloqueadores
2. A natureza bloqueadora dos agregados - Rob Farley
3. Hash Aggregate-Craig Freedman

Eu tenho um banco de dados com mais de 200 tabelas que foram criadas anos atrás. Estou tentando encontrar todas as consultas com group by que estão trabalhando atualmente com hash matcho operador agregado. Uma possibilidade que encontrei é usar dmv como abaixo. Mas não sei como filtrá-lo para listar apenas consultas com hash matchoperadores agregados. Como conseguir isso? Além disso, em um nível geral, quais são as outras opções para obter essas informações além de seguir o dmv?

SELECT cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.TEXT LIKE '%GROUP%'

Eu criei um índice filtrado (no SQL Server 2012) e o comparei com um índice não filtrado – pude ver melhorias promissoras conforme mostrado abaixo. Agora preciso alterar a condição do filtro para usar duas colunas na tabela. A condição necessária é WHERE InboundQuantity - OutboundQuantity <> 0ou InboundQuantity <> OutboundQuantity

Mas quando aplico este filtro, recebo uma mensagem de erro

“Cláusula WHERE incorreta para índice filtrado 'IX_WO_PlantCD_FilterQtyNotEqual'”.

Eu sei que existem limitações com índice filtrado. No entanto, existe uma maneira de obter essa melhoria com duas condições de coluna, usando índice filtrado ou algo semelhante?

Consulta

--Normal Index
CREATE NONCLUSTERED INDEX IX_WO_NormalPlantCD
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)

--Filtered Index
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)
WHERE InboundQuantity <> 0


--Query 1
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'

--Query2 (suing same where condition as filtered index)
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'
AND InboundQuantity<>0

Plano

Eu tenho uma consulta baseada em duas tabelas recém-criadas em SQL Server 2005. Eu criei um índice nessas tabelas. Mas quando escrevo uma consulta com JOINcondição, o número de linhas reais retornadas é muito alto, enquanto o número estimado de linhas é menor. E o plano de consulta usa um loop aninhado. [O diagrama do plano de consulta é fornecido abaixo.] Como essas são novas tabelas, acho que as estatísticas obsoletas usuais não são o motivo aqui.

Posso reescrever esta consulta usando NOT EXISTS(conforme mostrado na Consulta 2) e o número real de linhas é reduzido. Mas tenho outros requisitos para buscar detalhes da tabela LWTest usando INNER JOIN- o alto número de linhas reais é um problema.

Portanto, alguma pista de por que o número real de linhas é tão alto, embora haja índice e estatísticas? O que pode ser feito para derrubá-lo?

Nota: Número de linhas em TransmittedManifests –904. Número de linhas no LWTest -- 829785

ATUALIZAR

Observação2: Compatibility_Level é 90. O tempo decorrido para a Consulta 1 é 64 ms. O tempo decorrido da consulta 2 é de 6 ms.

Nota3: Tentei OPTION (RECOMPILE), reconstruir índice e ATUALIZAR ESTATÍSTICAS COM FULLSCAN nestas duas tabelas. Mas o número real de linhas ainda é alto.

Agradecimentos a Martin Smith pelos detalhes da diferença na contagem por execução (estimativa) e contagem total (real). As linhas reais são contadas em todas as execuções, as estimadas são contadas por execução. Linhas estimadas, linhas reais e contagem de execuções .

Para a Consulta 1, o ActualExecutions="904" para a tabela LWTest.

Consulta

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS IO ON 
PRINT 'BEGIN -----------------------------------------'

--Query 1
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
LEFT OUTER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest
WHERE LW.Manifest IS NULL

PRINT 'QUERY 1 COMPLETED -----------------------------------------'



--Query 2
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
WHERE NOT EXISTS (SELECT Manifest FROM dbo.LWTest L
                  WHERE L.Manifest = T.Manifest
                 )

PRINT 'QUERY 2 COMPLETED -----------------------------------------'


--Query 3
SELECT  T.[Manifest],LineItemID,InvoicedQuantity
FROM dbo.TransmittedManifests T
INNER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest

Esquema de tabela

Plano de consulta 1

Plano de consulta 2

Eu sou um desenvolvedor .Net. Eu tenho uma atualização de banco de dados chegando (do SQL Server 2005 para o SQL Server 2012). A equipe do banco de dados cuidará da migração. Mas eu vi postagens em fóruns dizendo que após a migração, as consultas estão demorando muito para serem executadas, embora a quantidade de dados e o número de usuários sejam quase os mesmos. E um dos principais motivos é a mudança execution planentre esses servidores. Referência: Problema de desempenho após a migração do SQL Server 2005 para 2012

Portanto, estou considerando a possibilidade de armazenar os planos de execução reais do meu próprio servidor de produção atual. No Oracle, pude encontrar um recurso para linha de base: -

1. SQL Plan Management (Parte 1 de 4) Criando linhas de base do plano SQL
2. Usando o SQL Plan Management

No SQL Server, qual é a prática usual para conseguir isso?

Nota: eu me referi a criar uma linha de base para SQL Server , mas não diz nada sobre linha de baseexecution plan

Eu tenho uma Employeetabela que tem um milhão de registros. Eu tenho o seguinte SQL para paginar dados em um aplicativo da web. Está funcionando bem. No entanto, o que vejo como um problema é - a tabela derivada tblEmployeeseleciona todos os registros na Employeetabela (para criar os MyRowNumbervalores).

Eu acho que isso causa a seleção de todos os registros na Employeetabela.

Será que realmente funciona assim? Ou o SQL Server é otimizado para selecionar apenas os 5 registros da Employeetabela original também?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize