Sortuj wycieki do tempdb z powodu varchar (maks.)

Na serwerze z 32 GB działamy SQL Server 2014 SP2 z maksymalną pamięcią 25 GB mamy dwie tabele, tutaj znajdziesz uproszczoną strukturę obu tabel:

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

z następującymi indeksami nieklastrowanymi:

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

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

Baza danych jest skonfigurowana na compatibility level120.

Kiedy uruchamiam to zapytanie, pojawiają się wycieki tempdb. Oto jak wykonuję zapytanie:

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

Jeśli nie wybierzesz [remark]pola, nie nastąpi wyciek. Moja pierwsza reakcja była taka, że ​​wycieki wystąpiły z powodu niskiej liczby szacowanych wierszy operatora pętli zagnieżdżonej.

Tak więc dodaję 5 kolumn datetime i 5 liczb całkowitych do tabeli ustawień i dodaję je do mojej instrukcji select. Kiedy wykonuję zapytanie, nie dochodzi do wycieków.

Dlaczego wycieki zdarzają się tylko po [remark]wybraniu? Prawdopodobnie ma to coś wspólnego z faktem, że jest to varchar(max). Co mogę zrobić, aby uniknąć rozlania tempdb?

Dodanie OPTION (RECOMPILE)do zapytania nie ma znaczenia.

Będzie kilka możliwych obejść tego problemu.

Możesz ręcznie dostosować przyznanie pamięci, choć prawdopodobnie nie wybrałbym tej trasy.

Możesz także użyć CTE i TOP, aby przesunąć sortowanie niżej, zanim przejmiesz kolumnę o maksymalnej długości. Będzie to wyglądać jak poniżej.

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

Proof-of-concept dbfiddle tutaj . Przykładowe dane nadal będą mile widziane!

Jeśli chcesz przeczytać doskonałą analizę Paula White'a, przeczytaj tutaj.

Dlaczego wycieki zdarzają się tylko po wybraniu [uwaga]?

Wyciek ma miejsce, gdy dodasz tę kolumnę, ponieważ nie dostaniesz wystarczającej wielkości pamięci na sortowanie danych o dużych ciągach.

Nie dostajesz dostatecznie dużego przydziału pamięci, ponieważ faktyczna liczba wierszy jest 10 razy większa niż szacowana liczba wierszy (1302 rzeczywistych vs 126 oszacowanych).

Dlaczego wycena jest wyłączona? Dlaczego SQL Server uważa, że ​​w dbo jest tylko jeden wiersz. Ustawienia z liczbą resourceid38?

Może to być problem ze statystykami, który można sprawdzić, uruchamiając DBCC SHOW_STATISTICS('dbo.Settings', 'IX_Test')i wyświetlając liczby dla tego kroku histogramu. Ale plan wykonania wydaje się wskazywać, że statystyki są tak kompletne i aktualne, jak to tylko możliwe.

Ponieważ statystyki nie pomagają, najlepszym rozwiązaniem jest prawdopodobnie przepisanie zapytania - które Forrest ujął w swojej odpowiedzi.

Wydaje mi się, że whereklauzula w zapytaniu podaje problem i jest przyczyną niskich oszacowań, nawet jeśli OPTION(RECOMPILE)jest używana.

Stworzyłem trochę danych testowych, a na koniec wymyśliłem dwa rozwiązania, przechowując IDpole z resourceszmiennej (jeśli zawsze jest unikalna) lub tabeli tymczasowej, jeśli możemy mieć więcej niż jedną ID.

Podstawowe zapisy testów

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

Wstaw wartości „Szukaj”, aby uzyskać ten sam przybliżony zestaw wyników, co OP (1300 rekordów)

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

Zmień statystyki kompatybilności i aktualizacji, aby dopasować OP

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

Oryginalne zapytanie

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

Moje szacunki są jeszcze gorsze , z jednym szacowanym wierszem, a zwracanych jest 1300. I jak stwierdził OP, nie ma znaczenia, czy dodamOPTION(RECOMPILE)

Ważną rzeczą do zapamiętania jest to, że kiedy pozbyjemy się klauzuli where, szacunki są w 100% poprawne, co jest oczekiwane, ponieważ używamy wszystkich danych w obu tabelach.

Zmusiłem indeksy tylko po to, aby upewnić się, że używamy tych samych, co w poprzednim zapytaniu, aby to udowodnić

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

Zgodnie z oczekiwaniami dobre szacunki.

Co więc możemy zmienić, aby uzyskać lepsze szacunki, ale nadal szukać naszych wartości?

JEŻELI @UID jest unikalny, tak jak w przykładzie podanym przez OP, możemy umieścić singiel, idktóry został zwrócony resourcesw zmiennej, a następnie wyszukać tę zmienną z OPCJĄ (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);

Co daje 100% dokładne szacunki

Ale co, jeśli w zasobach znajduje się wiele zasobów UID?

dodaj trochę danych testowych

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

Można to rozwiązać za pomocą tabeli tymczasowej

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

Ponownie z dokładnymi szacunkami .

Dokonano tego z moim własnym zestawem danych, YMMV.

Napisane za pomocą sp_executesql

Ze zmienną

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

Ze stołem tymczasowym

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

Wciąż 100% poprawne szacunki w moim teście