Over het algemeen zorgt het toevoegen van een clustered index aan tabel ervoor dat query’s op deze tabel een betere performance hebben. Er zijn echter situaties waarbij het toevoegen van een nonclustered index een nog beter resultaat in performance geeft. Om te begrijpen waarom een nonclustered index soms sneller is, is het nodig te begrijpen hoe SQL Server fysiek werkt. Een clustered index is een index op een volledig record. Alle velden van een record zitten in de clustered index. Bij een nonclustered index is dat niet het geval. In deze index zit een selectie van velden. SQL Server slaat data op in zogenaamde 8K pagina’s (pagina’s van 8 kilobyte). Omdat bij een nonclustered index een selectie van velden wordt gebruikt, passen er veel meer rijen op een 8K pagina en hoeft SQL Server bij gebruik van een nonclustered index minder pagina’s te lezen (dus minder i/o). Hierdoor kan het gebruik van een nonclustered index voordeliger zijn dan het gebruik van een clustered index. In onderstaande reproductie wordt zichtbaar dat een nonclustered index voordeliger blijkt dan een custered index, waardoor de nonclustered index sneller is.
Downloads
Het volledige script is via deze link te downloaden.
We beginnen met het opzetten van een database en het aanmaken en vullen van tabellen
/*
Auteur : Tim Wiseman
Datum : 30 September 2013
Bron : https://www.mssqltips.com/sqlservertip/3041/when-sql-server-nonclustered-indexes-are-faster-than-clustered-indexes/
Bewerkt door : Mickel Reemer
Bewerkingsdatum : 24 juli 2016
Bewerkingsreden : Vertaling en verduidelijking
Testomgeving : SQL Server 2016, Developer Edition
Doel van het script
Dit script maakt de verschillen in performance met diverse soorten tabellen en
diverse indexen inzichtelijk. Het script maakt duidelijk dat clustered indexen
niet altijd sneller dan nonclustered indexen zijn.
Doorlooptijden
Aanmaken van de vier tabellen : 11m22s
*/
-- Maak een testdatabase aan; de gebruikte naam voor deze testdatabase is Demolition
USE Demolition
GO
-- We starten met het aanmaken van een tabel zonder indexen als referentie
if OBJECT_ID('noIndex', 'U') is not NULL
drop table dbo.noIndex
CREATE TABLE dbo.noIndex(
n int NOT NULL, -- Wordt onderdeel van de index
singleChar char(1) NOT NULL, -- Wordt onderdeel van de index
stringData char(2000) NOT NULL,
bigIntData bigint NOT NULL,
decimalData decimal(18, 0)NOT NULL
)
GO
-- De tabel met 1 miljoen records vullen
INSERT INTO dbo.noIndex(n, singleChar, stringData, bigIntData, decimalData)
SELECT TOP 1000000
ROW_NUMBER() OVER (ORDER BY s1.name) AS n,
CHAR((ROW_NUMBER() OVER (ORDER BY s1.name) % 89) + 33) AS singleChar,
REPLICATE(CHAR((ROW_NUMBER() OVER (ORDER BY s1.name) % 89) + 33), 2000) AS stringData,
ROW_NUMBER() OVER (ORDER BY s1.name) * 1000000000 AS bigIntData,
ROW_NUMBER() OVER (ORDER BY s1.name) *1.1 AS decimalData
FROM master.dbo.syscolumns s1,
master.dbo.syscolumns s2
GO
-- Vervolgens wordt er een tabel met een primary key aangemaakt.
-- De primary key zorgt voor een clustered index op de kolommen 'n' en 'singleChar'
IF OBJECT_ID('pkIndex', 'U') is not NULL
DROP TABLE dbo.pkIndex
CREATE TABLE dbo.pkIndex(
n int NOT NULL,
singleChar char(1) NOT NULL,
stringData char(2000) NOT NULL,
bigIntData bigint NOT NULL,
decimalData decimal(18, 0) NOT NULL,
CONSTRAINT PK_pkIndextable PRIMARY KEY CLUSTERED (n, singleChar)
)
GO
-- Ook deze tabel vullen we met data
INSERT INTO
dbo.pkIndex(n, singleChar, stringData, bigIntData, decimalData)
SELECT
n, singleChar, stringData, bigIntData, decimalData
FROM
dbo.noIndex
GO
-- De volgende tabel die wordt aangemaakt is een tabel vooor het testen
-- van de nonclustered index
IF OBJECT_ID('nonclusteredIdx', 'U') IS NOT NULL
DROP TABLE dbo.nonclusteredIdx
GO
CREATE TABLE dbo.nonclusteredIdx(
n int NOT NULL,
singleChar char(1) NOT NULL,
stringData char(2000) NOT NULL,
bigIntData bigint NOT NULL,
decimalData decimal(18, 0) NOT NULL
)
GO
-- Ook deze tabel wordt met dezelfde data gevuld
INSERT INTO
dbo.nonclusteredIdx(n, singleChar, stringData, bigIntData, decimalData)
SELECT
n, singleChar, stringData, bigIntData, decimalData
FROM
dbo.pkIndex
GO
-- Vervolgens wordt daar een nonclustered index op gelegd
CREATE UNIQUE NONCLUSTERED INDEX nonclusteredIdx_n
ON dbo.nonclusteredIdx (n, singleChar)
GO
-- Tenslotte wordt er een tabel met beide indexen aangemaakt
IF OBJECT_ID('bothIdx', 'U') IS NOT NULL
DROP TABLE dbo.bothIdx
GO
CREATE TABLE dbo.bothIdx(
n int NOT NULL,
singleChar char(1) NOT NULL,
stringData char(2000) NOT NULL,
bigIntData bigint NOT NULL,
decimalData decimal(18, 0) NOT NULL,
CONSTRAINT PK_bothIdx PRIMARY KEY CLUSTERED (n, singleChar)
)
-- Ook deze tabel wordt met dezelfde data gevuld
INSERT INTO
dbo.bothIdx(n, singleChar, stringData, bigIntData, decimalData)
SELECT
n, singleChar, stringData, bigIntData, decimalData
FROM
dbo.pkIndex
GO
-- De clustered index wordt door de primary key gemaakt
-- Hieronder
CREATE UNIQUE NONCLUSTERED INDEX both_nonclusteredIdx_n
ON dbo.bothIdx (n, singleChar)
GO
Om ervoor te zorgen dat de test goed zichtbaar is, worden de volgende componenten geactiveerd
– Include actual query plan
– Live query statistics
– IO en TIME STATISTICS
– De cache leegmaken (doe dit NIET in productie-omgevingen!!)
Het aanzetten van statistics en het leeggooien van de cache hebben we in opmerkingen blokken geplaatst, om te voorkomen dat deze acties per ongeluk in productie worden uitgevoerd.
/*
SET STATISTICS TIME ON
SET STATISTICS IO ON
DBCC FREEPROCCACHE -- Niet op productie-omgevingen uitvoeren
DBCC DROPCLEANBUFFERS -- Niet op productie-omgevingen uitvoeren
*/
Vervolgens starten we een aantal query’s. Het is belangrijk de resultaten per query te zien, dus de query’s ook los van elkaar te starten. Voor de query worden dekkende indexen (covering indexes) gebruikt, zodat het resultaat zo optimaal mogelijk is. Let op: na elke uitgevoerde query moeten cache en buffers met het DBCC commando worden leeggemaakt om ervoor te zorgen dat de test zo zuiver mogelijk wordt uitgevoerd.
-- Eerst ophalen van data uit de tabel zonder index
select n, singleChar
from dbo.noIndex -- Table 'noIndex'. Scan count 9, logical reads 333334 // CPU time = 0 ms, elapsed time = 0 ms.
where n % 10 = 0
Het resultaat van bovenstaande query is:
– Doorlooptijd is 22 seconden
– Er wordt (uiteraard) een Table Scan gebruikt
-- Vervolgens wordt een test met clustered index gedaan (eerst cache leeggooien!)
select n, singleChar
from dbo.pkIndex -- Table 'pkIndex'. Scan count 1, logical reads 334654 // CPU time = 673 ms, elapsed time = 441 ms
where n % 10 = 0
order by n desc
Het resultaat van bovenstaande query is:
– Doorlooptijd is 36 seconden
– De query wordt met een table scan uitgevoerd
select n, singleChar
from dbo.nonclusteredIdx with(index(nonclusteredIdx_n)) -- Table 'nonclusteredIdx'. Scan count 9, logical reads 333334 // CPU time = 531 ms, elapsed time = 655 ms
where n % 10 = 0
Het resultaat van bovenstaande query is:
– Doorlooptijd is 1 seconde
– De query wordt met de nonclustered index scan uitgevoerd
select n, singleChar
from dbo.bothIdx -- Table 'bothIdx'. Scan count 9, logical reads 340428 // CPU time = 656 ms, elapsed time = 479 ms
where n % 10 = 0
Het resultaat van bovenstaande query is:
– Doorlooptijd is 1 seconde
– De query wordt met de nonclustered index scan uitgevoerd
Conclusie
Het gebruik van nonclustered indexen op bovenstaande tabellen geeft een significante betere performance dan wanneer de clustered index wordt gebruikt. Wanneer u met query performance tuning bezig bent en u heeft last van lange doorlooptijden van query’s, kijk dan eens of de tabel waarop de query draait uitsluitend een clustered index heeft. Is dat het geval, dan is het toevoegen van een nonclustered index een overweging waard. Let er wel op dat het toevoegen van een nonclustered index een prijs kent: bij het UPDATE, DELETE en INSERT query’s moet de extra index wel worden bijgewerkt en dat kost extra tijd. De afweging die u maakt, is of de extra tijd die het toevoegen van data kost, opweegt tegen de extra performance die de nonclustered index oplevert.