Início / dba / Perguntas / 227288
Accepted
Frederik Vanderhaegen
Asked: 2019-01-17 06:10:17 +0800 CST

Classificar derramamentos para tempdb devido a varchar(max)

  • 772

Em um servidor com 32GB estamos executando o SQL Server 2014 SP2 com memória máxima de 25GB temos duas tabelas, aqui você encontra uma estrutura simplificada de ambas as tabelas:

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceId] [int] NULL,
    [typeID] [int] NULL,
    [remark] [varchar](max) NULL,
    CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceUID] [int] NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

com os seguintes índices não agrupados:

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
    [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
    [resourceId] ASC,
    [typeID] ASC
)

O banco de dados está configurado com compatibility level120.

Quando executo essa consulta , há vazamentos para arquivos tempdb. É assim que executo a consulta:

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

Se não selecionar o [remark]campo não ocorre nenhum derramamento. Minha primeira reação foi que os vazamentos ocorreram devido ao baixo número de linhas estimadas no operador de loop aninhado.

Então eu adiciono 5 colunas datetime e 5 integer à tabela de configurações e as adiciono à minha instrução select. Quando executo a consulta, não ocorrem derramamentos.

Por que os derramamentos só acontecem quando [remark]é selecionado? Provavelmente tem algo a ver com o fato de que este é um arquivo varchar(max). O que posso fazer para evitar derramar tempdb?

Adicionar OPTION (RECOMPILE)à consulta não faz diferença.

sql-server performance

3 respostas

  1. Best Answer

    Haverá várias soluções possíveis aqui.

    Você pode ajustar manualmente a concessão de memória, embora eu provavelmente não seguiria esse caminho.

    Você também pode usar um CTE e um TOP para empurrar a classificação para baixo, antes de pegar a coluna de comprimento máximo. Será algo como abaixo.

    WITH CTE AS (
SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID
FROM Resources r
inner join Settings s on resourceid=r.id
where resourceUID=@UID
ORDER BY s.typeID
)
SELECT c.ID, ca.remark
FROM CTE c
CROSS APPLY (SELECT remark FROM dbo.Settings s WHERE s.id = c.ID2) ca(remark)
ORDER BY c.typeID

    Prova de conceito dbfiddle aqui . Dados de amostra ainda seriam apreciados!

    Se você quiser ler uma excelente análise de Paul White, leia aqui.

    • 10

  2. Por que os derramamentos só acontecem quando [observação] é selecionado?

    O derramamento está acontecendo quando você inclui essa coluna porque você não obtém uma concessão de memória grande o suficiente para os dados de cadeia de caracteres grandes que estão sendo classificados.

    Você não obtém uma concessão de memória grande o suficiente porque o número real de linhas é 10 vezes maior que o número estimado de linhas (1.302 reais versus 126 estimados).

    Por que a estimativa está defasada? Por que o SQL Server acha que há apenas uma linha em dbo.Settings com resourceid38?

    Pode ser um problema de estatísticas, que você pode verificar executando DBCC SHOW_STATISTICS('dbo.Settings', 'IX_Test')e vendo as contagens para essa etapa do histograma. Mas o plano de execução parece indicar que as estatísticas estão tão completas e atualizadas quanto poderiam estar.

    Como as estatísticas não estão ajudando, sua melhor aposta é provavelmente uma reescrita de consulta - que Forrest abordou em sua resposta.

    • 7

  3. Para mim, parece que a wherecláusula na consulta está dando o problema e é a causa das estimativas baixas, mesmo que OPTION(RECOMPILE)seja usada.

    Criei alguns dados de teste e, no final, cheguei a duas soluções, armazenando o IDcampo resourcesem uma variável (se for sempre exclusiva) ou em uma tabela temporária, se pudermos ter mais de um ID's.

    Registros de teste básicos

    SET NOCOUNT ON
DECLARE @i int= 1;
WHILE @i <= 10000
BEGIN
INSERT INTO [dbo].[Settings]([resourceId],[typeID],remark)
VALUES(@i,@i,'KEPT THESE VALUES OUT BECAUSE IT WOULD CLUTTER THE EXAMPLES, VALUES OVER 8000 Chars entered here'); -- 23254 character length on each value
INSERT INTO  [dbo].[Resources](resourceUID)
VALUES(@i);
SET @i += 1;
END

    Insira os valores 'Seek', para obter o mesmo conjunto de resultados aproximado do OP (1300 registros)

    INSERT INTO  [dbo].[Settings]([resourceId],[typeID],remark)
VALUES(38,38,'KEPT THESE VALUES OUT BECAUSE IT WOULD CLUTTER THE EXAMPLES, VALUES OVER 8000 Chars entered here')
GO 1300

    Altere as estatísticas de compatibilidade e atualização para corresponder ao OP

    ALTER DATABASE StackOverflow SET COMPATIBILITY_LEVEL = 120;
UPDATE STATISTICS settings WITH FULLSCAN;
UPDATE STATISTICS resources WITH FULLSCAN;

    Consulta original

    exec sp_executesql N'
select r.id
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

    Minhas estimativas são ainda piores , com uma linha estimada, enquanto 1300 são retornadas. E como o OP afirmou, não importa se eu adicionarOPTION(RECOMPILE)

    Uma coisa importante a notar, é que quando nos livramos da cláusula where as estimativas estão 100% corretas, o que é esperado já que estamos usando todos os dados em ambas as tabelas.

    Forcei os índices apenas para garantir que usamos os mesmos da consulta anterior, para provar o ponto

    exec sp_executesql N'
select r.id,remark
FROM Resources r with(index([IX_UID]))
inner join Settings WITH(INDEX([IX_Test])) 
on resourceid=r.id
ORDER BY typeID',
N'@UID int',
@UID=38

    Como esperado, bom estimativas.

    Então, o que poderíamos mudar para obter melhores estimativas, mas ainda buscar em nossos valores?

    SE @UID for único, como no exemplo que o OP deu, poderíamos colocar o single idque foi retornado resourcesem uma variável e procurar nessa variável com um OPTION(RECOMPILE)

    DECLARE @UID int =38 , @RID int;
SELECT @RID=r.id from 
Resources r where resourceUID = @UID;

SELECT @uid, remark 
from Settings 
where resourceId = @uid 
Order by typeID
OPTION(RECOMPILE);

    O que fornece estimativas 100% precisas

    Mas e se houver vários resourceUIDs nos recursos?

    adicione alguns dados de teste

    INSERT INTO Resources(ResourceUID)
VALUES (38);
go 50

    Isso pode ser resolvido com uma tabela temporária

    CREATE TABLE #RID (id int)
DECLARE @UID int =38 
INSERT INTO #RID
SELECT r.id 
from 
Resources r where resourceUID = @UID

SELECT @uid, remark 
from Settings  s
INNER JOIN #RID r
ON r.id =s.resourceId
Order by typeID
OPTION(RECOMPILE)

DROP TABLE #RID

    Novamente com estimativas precisas .

    Isso foi feito com meu próprio conjunto de dados, YMMV.

    Escrito com sp_executesql

    Com uma variável

    exec sp_executesql N'
DECLARE  @RID int;
    SELECT @RID=r.id from 
    Resources r where resourceUID = @UID;

    SELECT @uid, remark 
    from Settings 
    where resourceId = @uid 
    Order by typeID
    OPTION(RECOMPILE);',
N'@UID int',
@UID=38

    Com uma mesa temporária

    exec sp_executesql N'

CREATE TABLE #RID (id int)

INSERT INTO #RID
SELECT r.id 
from 
Resources r where resourceUID = @UID

SELECT @uid, remark 
from Settings  s
INNER JOIN #RID r
ON r.id =s.resourceId
Order by typeID
OPTION(RECOMPILE)

DROP TABLE #RID',
N'@UID int',
@UID=38

    Ainda estimativas 100% corretas no meu teste

    • 4

relate perguntas