A criação de estatísticas em uma tabela columnstore clusterizada sempre parece ler a tabela inteira, mesmo que eu peça uma pequena amostra. Por que é isso?
Paul White's questions
Quando uso uma variável de um tipo de dados de objeto grande (LOB) no SQL Server, a coisa toda é mantida na memória o tempo todo? Mesmo que seja 2GB de tamanho?
Depois de ler Unusual THREADPOOL Waits de Josh Darnell, um usuário do Twitter mencionou que há um sinalizador de rastreamento não documentado para evitar o corte de trabalhadores ociosos :
A ideia é que, uma vez que o SQL Server tenha criado threads suficientes para atender a carga de trabalho de pico, ele não deve aparar os threads de trabalho (liberando-os para o sistema operacional) após 15 minutos ou mais de não serem necessários.
Os threads de trabalho ociosos continuarão a usar recursos (por exemplo, memória), mas não haverá a explosão de THREADPOOL
esperas quando mais trabalhos forem necessários de repente. Aparentemente, isso pode ser útil ao usar grupos de disponibilidade AlwaysOn.
O que é esse sinalizador de rastreamento não documentado e como ele funciona?
Se um plano for forçado no primário em um Grupo de Disponibilidade, ele será aplicado a consultas executadas em um secundário?
Estou procurando respostas que cubram as duas possibilidades de forçar o plano:
Eu li o seguinte que sugere que os planos forçados de QS não são transferidos, mas não consigo encontrar nada oficial na documentação ou nada sobre guias de planos.
- Repositório de consultas e grupos de disponibilidade por Erin Stellato
- Comportamento do plano forçado do armazenamento de dados de consulta no secundário legível AlwaysOn por Vikas Rana
Evidência conclusiva de forçamento seria a presença de Use Plan
ou PlanGuideName
e PlanGuideDB
propriedades no plano de execução do secundário.
Uma pergunta que surgiu em uma discussão no chat:
Eu sei que a junção de hash muda internamente para uma espécie de loops aninhados.
O que o SQL Server faz para um resgate agregado de hash (se puder acontecer)?
Em resposta à contagem de SQL distinta na partição , Erik Darling postou este código para contornar a falta de COUNT(DISTINCT) OVER ()
:
SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
FROM #MyTable AS mt2
WHERE mt2.Col_A = mt.Col_A
-- GROUP BY mt2.Col_A
) AS ca;
A consulta usa CROSS APPLY
(não OUTER APPLY
), então por que há uma junção externa no plano de execução em vez de uma junção interna ?
Além disso, por que descomentar a cláusula group by resulta em uma junção interna?
Eu não acho que os dados sejam importantes, mas copiando os dados fornecidos por kevinwhat na outra pergunta:
create table #MyTable (
Col_A varchar(5),
Col_B int
)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)
Dada a seguinte tabela de heap com 400 linhas numeradas de 1 a 400:
DROP TABLE IF EXISTS dbo.N;
GO
SELECT
SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
SV.[type] = N'P'
AND SV.number BETWEEN 1 AND 400;
e as seguintes configurações:
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
A SELECT
instrução a seguir é concluída em cerca de 6 segundos ( demo , plan ):
DECLARE @n integer = 400;
SELECT
c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
N.number <= @n
AND N2.number <= @n
AND N3.number <= @n
OPTION
(OPTIMIZE FOR (@n = 1));
Observação: @A OPTIMIZE FOR
cláusula é apenas para produzir uma reprodução de tamanho sensato que capture os detalhes essenciais do problema real, incluindo uma estimativa incorreta de cardinalidade que pode surgir por vários motivos.
Quando a saída de linha única é gravada em uma tabela, leva 19 segundos ( demo , plan ):
DECLARE @T table (c bigint NOT NULL);
DECLARE @n integer = 400;
INSERT @T
(c)
SELECT
c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
N.number <= @n
AND N2.number <= @n
AND N3.number <= @n
OPTION
(OPTIMIZE FOR (@n = 1));
Os planos de execução parecem idênticos, exceto pela inserção de uma linha.
Todo o tempo extra parece ser consumido pelo uso da CPU.
Por que a INSERT
declaração é muito mais lenta?
Quais são as condições que produzem um aviso de plano de execução de "Concessão Excessiva" ?
A concessão de memória de consulta detectou "ExcessiveGrant", o que pode afetar a confiabilidade. Tamanho da concessão: inicial 5128 KB, final 5128 KB, usado 16 KB.
SSMS
Explorador de planos
xml do plano de exibição
<Warnings>
<MemoryGrantWarning GrantWarningKind="Excessive Grant"
RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" />
</Warnings>
Veja a seguir uma simplificação de um problema de desempenho encontrado com o Query Store:
CREATE TABLE #tears
(
plan_id bigint NOT NULL
);
INSERT #tears (plan_id)
VALUES (1);
SELECT
T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
ON QSP.plan_id = T.plan_id;
A plan_id
coluna está documentada como sendo a chave primária de sys.query_store_plan
, mas o plano de execução não usa a eliminação de junção como seria esperado:
- Nenhum atributo está sendo projetado do DMV.
- A chave primária DMV
plan_id
não pode duplicar linhas da tabela temporária - A
LEFT JOIN
é usado, portanto, nenhuma linha deT
pode ser eliminada.
Por que isso acontece e o que pode ser feito para obter a eliminação de junção aqui?
A execução do SQL Server no modo de fibra ( pooling leve ) desabilita o uso do SQL CLR:
A execução do Common Language Runtime (CLR) não tem suporte em pooling leve. Desative uma das duas opções: "clr ativado" ou "pooling leve". Os recursos que dependem do CLR e que não funcionam corretamente no modo de fibra incluem o tipo de dados de hierarquia, replicação e gerenciamento baseado em políticas.
Por outro lado, desabilitar o SQL CLR sozinho (sem habilitar o pooling leve) não desabilita os tipos CLR internos como geometry
e geography
(embora hierarchyid
mencionado acima), conforme mostrado em Como o tipo "HierarchyID" pode funcionar quando "CLR" está desabilitado ?
Agora, alguns novos recursos de linguagem dependem do CLR, por exemplo, a FORMAT
função :
FORMAT depende da presença do .NET Framework Common Language Runtime (CLR).
A execução do SQL Server no modo fibra desabilita a FORMAT
função e/ou o uso dos tipos CLR?
Para a consulta de banco de dados de exemplo AdventureWorks abaixo:
SELECT
P.ProductID,
CA.TransactionID
FROM Production.Product AS P
CROSS APPLY
(
SELECT TOP (1)
TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE
TH.ProductID = P.ProductID
ORDER BY
TH.TransactionID DESC
) AS CA;
O plano de execução mostra um Custo Estimado do Operador de 0,0850383 (93%) para a Busca do Índice :
O custo é independente do modelo de estimativa de cardinalidade em uso.
Não é uma simples adição do Custo Estimado da CPU e do Custo Estimado de E/S . Tampouco é o custo de uma execução do Index Seek multiplicado pelo Número Estimado de Execuções .
Como se chegou a esse número de custo?
É possível que uma consulta do SQL Server em execução com um grau efetivo de paralelismo x
tenha trabalhadores paralelos atribuídos a mais de x
agendadores distintos? Mesmo que o plano de execução tenha muitas zonas paralelas?
Dada a tabela a seguir, índice clusterizado exclusivo e estatísticas:
CREATE TABLE dbo.Banana
(
pk integer NOT NULL,
c1 char(1) NOT NULL,
c2 char(1) NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk);
CREATE STATISTICS c1 ON dbo.Banana (c1);
CREATE STATISTICS c2 ON dbo.Banana (c2);
INSERT dbo.Banana
(pk, c1, c2)
VALUES
(1, 'A', 'W'),
(2, 'B', 'X'),
(3, 'C', 'Y'),
(4, 'D', 'Z');
-- Populate statistics
UPDATE STATISTICS dbo.Banana;
Os contadores de modificação de linha de estatísticas obviamente mostram zero antes de qualquer atualização:
-- Show statistics modification counters
SELECT
stats_name = S.[name],
DDSP.stats_id,
DDSP.[rows],
DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP
WHERE
S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');
Incrementando cada pk
valor de coluna em um para cada linha:
-- Increment pk in every row
UPDATE dbo.Banana
SET pk += 1;
Usa o plano de execução:
Ele produz os seguintes contadores de modificação de estatísticas:
Perguntas
- O que os operadores Dividir, Classificar e Recolher fazem?
- Por que as
pk
estatísticas mostram 2 modificações, masc1
mostramc2
5?
No SQL Server, um índice não clusterizado não exclusivo em uma tabela rowstore incorpora o marcador do objeto base (RID ou chave de clustering) em todos os níveis da estrutura do índice não clusterizado. O marcador é armazenado como parte da chave de índice não clusterizado em todos os níveis de índice.
Por outro lado, se o índice não clusterizado for exclusivo , o marcador estará presente apenas no nível folha do índice - não como parte da chave (o marcador está presente como uma ou mais colunas incluídas, na verdade).
No SQL Server 2016, é possível criar um índice b-tree não clusterizado em uma tabela orientada a colunas (uma que tenha um índice columnstore clusterizado).
- Qual é o 'marcador' usado para um índice de árvore b não clusterizado em uma tabela columnstore clusterizada?
- As diferenças entre índices não clusterizados exclusivos e não exclusivos descritos acima ainda se aplicam?
Uma função com valor de tabela com várias instruções retorna seu resultado em uma variável de tabela.
Esses resultados são reutilizados ou a função é sempre totalmente avaliada toda vez que é chamada?
É bem conhecido que SCHEMABINDING
uma função pode evitar um spool desnecessário nos planos de atualização:
Se você estiver usando UDFs T-SQL simples que não tocam em nenhuma tabela (ou seja, não acessam dados), certifique-se de especificar a
SCHEMABINDING
opção durante a criação das UDFs. Isso tornará os UDFs vinculados ao esquema e garantirá que o otimizador de consulta não gere nenhum operador de spool desnecessário para planos de consulta envolvendo esses UDFs.
Existem outras vantagens de SCHEMABINDING
uma função, mesmo que ela não acesse dados?
Aqui estão três testes simples que leem os mesmos dados, mas relatam leituras lógicas muito diferentes:
Configurar
O script a seguir cria uma tabela de teste com 100 linhas idênticas, cada uma contendo uma coluna xml com dados suficientes para garantir que ela seja armazenada fora da linha. No meu banco de dados de teste, o comprimento do xml gerado é de 20.204 bytes para cada linha.
-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
ID integer IDENTITY PRIMARY KEY,
X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;
SET @X =
(
SELECT TOP (100) *
FROM sys.columns AS C
FOR XML
PATH ('row'),
ROOT ('root'),
TYPE
);
INSERT dbo.XMLTest
(X)
SELECT TOP (100)
@X
FROM sys.columns AS C;
-- Flush dirty buffers
CHECKPOINT;
testes
Os três testes a seguir leem a coluna xml com:
SELECT
Uma declaração simples- Atribuindo o xml a uma variável
- Usando
SELECT INTO
para criar uma tabela temporária
-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @X xml;
SELECT
@X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='
IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
DROP TABLE #T;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT
XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;
Resultados
A saída é:
=== SELEÇÃO Simples ==== Tabela 'XMLTest'. Contagem de varredura 1, leituras lógicas 3, leituras físicas 1, leituras antecipadas 0, lob lógico lê 795, lob físico lê 37, lob read-ahead lê 796. === Atribuir a uma variável ==== Tabela 'XMLTest'. Contagem de varredura 1, leituras lógicas 3, leituras físicas 1, leituras antecipadas 0, lob leituras lógicas 0, lob leituras físicas 0, leitura antecipada de lob 0. === SELECIONE EM ==== Tabela 'XMLTest'. Contagem de varredura 1, leituras lógicas 3, leituras físicas 1, leituras antecipadas 0, lob lógico lê 300, lob físico lê 37, lob read-ahead lê 400.
Perguntas
- Por que as leituras de LOB são tão diferentes?
- Certamente os mesmos dados exatos foram lidos em cada teste?
Preciso calcular uma soma contínua em um intervalo de datas. Para ilustrar, usando o banco de dados de exemplo AdventureWorks , a seguinte sintaxe hipotética faria exatamente o que eu preciso:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 = SUM(TH.ActualCost) OVER (
PARTITION BY TH.ProductID
ORDER BY TH.TransactionDate
RANGE BETWEEN
INTERVAL 45 DAY PRECEDING
AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
Infelizmente, a RANGE
extensão do quadro da janela não permite atualmente um intervalo no SQL Server.
Eu sei que posso escrever uma solução usando uma subconsulta e uma agregação regular (não janela):
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 =
(
SELECT SUM(TH2.ActualCost)
FROM Production.TransactionHistory AS TH2
WHERE
TH2.ProductID = TH.ProductID
AND TH2.TransactionDate <= TH.TransactionDate
AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
Dado o seguinte índice:
CREATE UNIQUE INDEX i
ON Production.TransactionHistory
(ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
(ActualCost);
O plano de execução é:
Embora não seja terrivelmente ineficiente, parece que deve ser possível expressar essa consulta usando apenas funções analíticas e agregadas de janela com suporte no SQL Server 2012, 2014 ou 2016 (até agora).
Para maior clareza, estou procurando uma solução que execute uma única passagem pelos dados.
Em T-SQL, isso provavelmente significa que a OVER
cláusula fará o trabalho e o plano de execução apresentará Spools de Janelas e Agregados de Janelas. Todos os elementos de linguagem que usam a OVER
cláusula são um jogo justo. Uma solução SQLCLR é aceitável, desde que seja garantida a produção de resultados corretos.
Para soluções T-SQL, quanto menos Hashes, Classificações e Spools/Agregados de Janelas no plano de execução, melhor. Sinta-se à vontade para adicionar índices, mas estruturas separadas não são permitidas (portanto, nenhuma tabela pré-computada é mantida em sincronia com gatilhos, por exemplo). São permitidas tabelas de referência (tabelas de números, datas, etc.)
Idealmente, as soluções produzirão exatamente os mesmos resultados na mesma ordem que a versão da subconsulta acima, mas qualquer coisa comprovadamente correta também é aceitável. O desempenho é sempre uma consideração, portanto, as soluções devem ser pelo menos razoavelmente eficientes.
Sala de bate-papo dedicada: Criei uma sala de bate-papo pública para discussões relacionadas a esta pergunta e suas respostas. Qualquer usuário com pelo menos 20 pontos de reputação pode participar diretamente. Por favor, ping me em um comentário abaixo se você tem menos de 20 representantes e gostaria de participar.
Muitas vezes preciso selecionar um número de linhas de cada grupo em um conjunto de resultados.
Por exemplo, talvez eu queira listar os 'n' valores de pedidos recentes mais altos ou mais baixos por cliente.
Em casos mais complexos, o número de linhas a serem listadas pode variar por grupo (definido por um atributo do registro de agrupamento/pai). Esta parte é definitivamente opcional/para crédito extra e não pretende dissuadir as pessoas de responder.
Quais são as principais opções para resolver esses tipos de problemas no SQL Server 2005 e posterior? Quais são as principais vantagens e desvantagens de cada método?
Exemplos de AdventureWorks (para maior clareza, opcional)
- Liste as cinco datas e IDs de transações mais recentes da
TransactionHistory
tabela, para cada produto que começa com uma letra de M a R inclusive. - Mesmo novamente, mas com
n
linhas de histórico por produto, onden
é cinco vezes oDaysToManufacture
atributo Product. - O mesmo, para o caso especial em que é necessária exatamente uma linha de histórico por produto (a única entrada mais recente de
TransactionDate
, desempate emTransactionID
.