I am Berke

Évekkel ezelőtt volt egy érdekes felvetése egy "menő" tanácsadó cégnek (nem, nincs név :)), miszerint mindegy, hogy forward vagy backward scan van egy indexen, mert ugyan olyan jó. Ezzel én vitába szálltam, mert nem feltétlen igaz ez, főleg nem ott, ahol számít a teljesítmény. Részletekbe nem feltétlen megyek bele, de a lényeget megmutatom.

Demó környezet

A példához szükségem van egy relatív nagy táblára, amit most a tempdb-ben hozok létre:

USE [tempdb]
GO

DROP TABLE IF EXISTS [dbo].[T1]
GO
CREATE TABLE [dbo].[T1]
(
    [col1] INT IDENTITY PRIMARY KEY NONCLUSTERED,
    [col2] INT,
    [col3] DATETIME
)
GO

CREATE CLUSTERED INDEX [CI_T1] ON [dbo].[T1] ([col3])
GO

Ebbe a táblába létrehozok 5 millió sort (meg egy kicsit).

INSERT INTO [dbo].[T1]
(
    [col2],
    [col3]
)
SELECT
    CHECKSUM(NEWID()),
    DATEADD(ms,CHECKSUM(NEWID()), CURRENT_TIMESTAMP)
FROM
    master.sys.objects o 
CROSS JOIN
    master.sys.objects o1
GO

INSERT INTO [dbo].[T1]
(
    [col2],
    [col3]
)
SELECT TOP 5000000
    CHECKSUM(NEWID()),
        DATEADD(ms,CHECKSUM(NEWID()), CURRENT_TIMESTAMP)
FROM
    [dbo].[T1] a 
CROSS JOIN
    [dbo].[T1] b
GO

Forward scan

Lássunk egy egyszerű lekérdezést a csodálatos T1 táblára:

SELECT *
FROM   
    [dbo].[T1]
WHERE  
    [col3] BETWEEN '20170101' AND '20180101'
AND 
    [col2] BETWEEN 300000000 AND 699999999
ORDER BY [col3] ASC;

Ugye nem is bonyolult. Vajon mi a futási terv?

Nahát, ez egy parallel futási terv. Amit még érdemes megnézni az az Index Seek tulajdonságai:

Látható, hogy itt forward scan van, illetve parallel futási terv van, azaz több CPU-t is tudunk használni. Ez utóbbi az érdekes, és ezen volt a "vita". 

Backward scan

Most nézzük meg ugyan ezt a lekérdezést egy picit másként:

SELECT *
FROM   
    [dbo].[T1]
WHERE  
    [col3] BETWEEN '20170101' AND '20180101'
AND 
    [col2] BETWEEN 300000000 AND 699999999
ORDER BY [col3] DESC;

Amiben különbözik, az az ORDER BY rész. E miatt most másik futási tervünk van:

Azt is észre kell venni, hogy most egy szálon futott a lekérdezés! Nézzük csak meg az Index Seek operátor tulajdonságait:

Nahát, itt backward scan van, illetve nem parallel terv. 

Lássuk tudok e ezen változtatni.

SELECT *
FROM   
    [dbo].[T1]
WHERE  
    [col3] BETWEEN '20170101' AND '20180101'
AND 
    [col2] BETWEEN 300000000 AND 699999999
ORDER BY [col3] DESC
OPTION (MAXDOP 4) 

Sajnos nem, ami látszik is a futási tervben is:

Esetleg más módon? Pl. egy nem dokumentált query hint segítségével? :)

SELECT *
FROM   
    [dbo].[T1]
WHERE  
    [col3] BETWEEN '20170101' AND '20180101'
AND 
    [col2] BETWEEN 300000000 AND 699999999
ORDER BY [col3] DESC
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

Aha, itt már jó lesz ez, de a futási tervben megjelent egy Sort operátor is. A költsége is jóval magasabb lett, mint az eddigi összes lekérdezésnek. Az ENABLE_PARALLEL_PLAN_REFERENCE hint SQL Server 2016 Sp1 CU2-től elérhető, de jelenleg nem dokumentált, azaz mindenki csak saját felelősségre használja. Ennek lehet alternatívája egy table hint, a FORCESCAN, ami esetében ugyan ezt a futási tervet kapom, illetve a költsége is ugyan olyan magas lesz.

Konklúzió

Alapvetően mindegy is, hogy backward vagy forward scan van, de akkor már nem, ha parallel futási tervvel (legalábbis némelyik operátor hasznot tud húzni a párhuzamos futásból) gyorsabban ki tudom szolgálni a lekérdezést. Backward scan esetén nem lehetséges mindenhol a parallel futás egyes operátoroknál, így ez egy szálon fog menni, ahogy a példában is látszik. Szóval a lényeg, hogy erre is érdemes figyelni. Aki kicsit mélyebbre megy a kódban, pár furcsa dolgot is észrevehet, pl az index sorrend és az ORDER BY. 

A query és table hint használatát is csak akkor javaslom, amikor a "hagyományos" optimalizálással már nem érünk célt, ez legyen az utolsó, amit bevetünk a lekérdezések "gyorsításához"

Akit mélyebben érdekel a téma, ajánlom a windbg használatát és ott meg lehet nézni a call stack-et is :)