Paul White提出的问题 -dba

Paul White

Asked: 2025-02-18 16:34:13 +0800 CST

Classificação desnecessária com TOP PERCENT?

21

Configurar

-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

Consulta de teste

Exibir 9% das linhas na ordem da chave primária:

SELECT TOP (9e) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

db<>demonstração de violino

Resultados

Plano de execução

Pergunta

Por que o SQL Server classifica a coluna quando o índice clusterizado fornece exatamente essa ordem?

Mais linhas

Se eu aumentar o número de linhas na tabela, obtenho um Eager Spool em vez de uma Sort e o índice é escaneado em ordem:

-- Create a heap table of numbers from 1 to 1,000
SELECT TOP (1000)
    i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;

-- Add a clustered primary key
ALTER TABLE #T
    ADD PRIMARY KEY CLUSTERED (i);

-- 0.9% now
SELECT TOP (9e-1) PERCENT 
    i 
FROM #T 
ORDER BY 
    i ASC;

DROP TABLE #T;

Paul White

Asked: 2024-05-22 23:24:21 +0800 CST

Cálculo interno da porcentagem de estatísticas amostradas

11

Quando o SQL Server cria estatísticas de amostra , como a porcentagem de amostragem é calculada?

Por exemplo, atualizando estatísticas na coluna PostId da tabela dbo.Votes no banco de dados Stack Overflow 2013:

UPDATE STATISTICS dbo.Votes
(
    _WA_Sys_00000002_0AD2A005
) 
WITH MAXDOP = 1;

O SQL Server cria uma instrução interna:

SELECT 
    StatMan
    (
        [SC0], 
        [SB0000]
    ) 
    FROM 
    (
        SELECT TOP 100 PERCENT 
            [SC0], 
            step_direction([SC0]) over (order by NULL) AS [SB0000]  
        FROM 
        (
            SELECT [PostId] AS [SC0] 
            FROM [dbo].[Votes] 
                TABLESAMPLE SYSTEM (9.234204e-01 PERCENT) 
                WITH (READUNCOMMITTED) 
        ) AS _MS_UPDSTATS_TBL_HELPER 
        ORDER BY 
            [SC0], 
            [SB0000] 
    ) AS _MS_UPDSTATS_TBL  
OPTION (MAXDOP 1)

De onde vem 9.234204e-01 PERCENT?

Paul White

Asked: 2022-11-04 14:06:14 +0800 CST

Por que as estatísticas são mais lentas para construir no columnstore clusterizado?

13

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

Asked: 2022-08-04 12:39:11 +0800 CST

Variáveis de LOB do SQL Server e uso de memória

9

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?

Paul White

Asked: 2020-08-30 06:55:14 +0800 CST

Evitar esperas de THREADPOOL devido ao corte de thread de trabalho ocioso

12

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 THREADPOOLesperas 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?

Paul White

Asked: 2019-08-30 14:54:29 +0800 CST

Planos forçados em secundários legíveis

17

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 Planou PlanGuideNamee PlanGuideDBpropriedades no plano de execução do secundário.

Paul White

Asked: 2019-07-18 12:47:55 +0800 CST

Resgate agregado de hash

10

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)?

Paul White

Asked: 2019-06-06 06:08:22 +0800 CST

CROSS APPLY produz junção externa

18

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)

Paul White

Asked: 2019-02-26 02:22:55 +0800 CST

INSERT...SELECT de linha única muito mais lento que SELECT separado

18

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 SELECTinstruçã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 FORclá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 INSERTdeclaração é muito mais lenta?

Paul White

Asked: 2019-01-29 04:15:34 +0800 CST

Quando o SQL Server avisa sobre uma concessão de memória excessiva?

22

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>

Paul White

Asked: 2018-09-02 06:22:32 +0800 CST

Por que a eliminação de ingresso não funciona com sys.query_store_plan?

10

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_idcoluna 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_idnão pode duplicar linhas da tabela temporária
A LEFT JOINé usado, portanto, nenhuma linha de Tpode ser eliminada.

Plano de execução

Por que isso acontece e o que pode ser feito para obter a eliminação de junção aqui?

Paul White

Asked: 2018-07-14 09:02:49 +0800 CST

O pooling leve desabilita os recursos CLR integrados?

7

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 geometrye geography(embora hierarchyidmencionado 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 FORMATfunção :

FORMAT depende da presença do .NET Framework Common Language Runtime (CLR).

A execução do SQL Server no modo fibra desabilita a FORMATfunção e/ou o uso dos tipos CLR?

Paul White

Asked: 2018-07-04 01:00:56 +0800 CST

Custo do Operador de Busca de Índice

10

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?

Paul White

Asked: 2018-05-03 19:52:43 +0800 CST

Grau de paralelismo e uso do agendador

4

É 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 xagendadores distintos? Mesmo que o plano de execução tenha muitas zonas paralelas?

Paul White

Asked: 2018-03-28 07:19:01 +0800 CST

O que é o atributo do plano StatementParameterizationType?

8

Observei que os planos de execução às vezes incluem um StatementParameterizationTypeatributo.

O que é isso, o que significa e quando aparece?

Paul White

Asked: 2017-09-11 12:12:10 +0800 CST

Atualizações de índice exclusivo e contadores de modificação de linha de estatísticas

14

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 pkvalor 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 pkestatísticas mostram 2 modificações, mas c1mostram c25?

Paul White

Asked: 2017-01-07 00:30:16 +0800 CST

Armazenamento de índice não clusterizado em columnstore clusterizado

20

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?

Paul White

Asked: 2016-12-29 05:20:49 +0800 CST

O SQL Server armazena em cache o resultado de uma função com valor de tabela de várias instruções?

24

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?

Paul White

Asked: 2016-06-04 22:35:05 +0800 CST

Existe algum benefício em SCHEMABINDING uma função além da proteção de Halloween?

59

É bem conhecido que SCHEMABINDINGuma 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 SCHEMABINDINGopçã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 SCHEMABINDINGuma função, mesmo que ela não acesse dados?

Paul White

Asked: 2016-05-09 06:07:58 +0800 CST

Leituras lógicas diferentes ao acessar os mesmos dados LOB

26

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:

SELECTUma declaração simples
Atribuindo o xml a uma variável
Usando SELECT INTOpara 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?

Paul White's questions

Configurar

Consulta de teste

Resultados

Plano de execução

Pergunta

Mais linhas

SSMS

Explorador de planos

xml do plano de exibição

Perguntas

Configurar

testes

Resultados

Perguntas