Nonclustered index soms sneller dan clustered index (performance)

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.

SQL Server kopiëren deel van tabellen naar een andere database

Wellicht dat u dit nog nooit aan de hand heeft gehad. Deze week werd ik geconfronteerd met het verzoek om uit een bestaande database een deel van de tabellen te halen en in een aparte database te plaatsen. Dit ging om een deel van productiedata die als input zou dienen voor een omgeving van waaruit presentatie gegeven konden geven. Dat bleek een grotere uitdaging dan ik had verwacht.

Mijn planning voorzag in het gebruik van de import & export wizard van SQL Server Management, aanvinken van ‘Enable identity insert’ en klaar is Kees. Dat bleek iets te kort door de bocht. Bij de import & export wizard worden uitsluitend tabellen en hun data overgezet; niet de constraints en andere tabeleigenschappen. Voor mijn opdracht was dat een probleem: de gehele tabelstructuur inclusief eventuele identity columns moesten worden overgezet. Uiteindelijk is het probleem op de volgende manier opgelost:

* Linked Server gelegd tussen de SQL Server waar de brondatabase op staat en de SQL Server waar de doeldatabase is gehuisvest
– Als alternatief kan hier ook een backup/restore naar dezelfde server als de doeldatabase worden gebruikt, maar verschillen in SQL Server versies kan hier een extra uitdaging in worden
* Vervolgens heb ik met een CURSOR op sys.tables een selectie gemaakt van de tabellen die ik wilde overzetten
* Tenslotte heb ik een T-SQL script door de CURSOR laten lopen en met een SELECT … INTO … de data overgezet.

Waarom dit werkt
SELECT … INTO … zorgt ervoor dat alle tabellen inclusief IDENTITY kolommen wordt aangemaakt. Precies de oplossing waar ik naar op zoek was.

Het is mij een raadsel waarom Microsoft het niet mogelijk maakt om tabellen aan te maken inclusief o.a. IDENTITY kolommen. Redgate lijkt met deze software in deze behoefte te voorzien. Voor het kopiëren van een deel van de tabellen vanuit een andere database werkt mijn script prima.

De T-SQL code

/*
Auteur : Mickel Reemer
Datum : 17 juli 2016

Doel van het script:
Kopiëren van data van één database naar een andere, rekeninghoudend
met onder andere IDENTITY kolommen

Achtergrond:
Bij de import/export wizard van SQL Server wordt geen rekening met IDENTITY
kolommen gehouden. Met andere woorden: heeft een tabel een IDENTITY kolom,
dan komt deze niet als IDENTITY kolom in de doeldatabase terecht. Dit is niet
altijd gewenst. Er kunnen situaties zijn waarbij de IDENTITY kolom(men) wél
in de doeldatabase moeten worden overgenomen.

Uitwerking:
Het script werkt op de volgende manier:
- Er moet verbinding worden gemaakt met de database waar de doelserver op is gehuisvest
- Het script werkt vanuit de brondatabase (deze moet op TWEE plaatsen worden ingesteld)
- Er worden parameters ingesteld voor o.a. bron- en doeldatabase
- Vervolgens worden uit sys.tables de bestaande tabellen in de brondatabase langsgelopen
- Eventueel kunnen tabellen in deze sectie worden uitgesloten via de WHERE clause
- De te kopiëren tabellen worden in een CURSOR geplaatst
- Er wordt een SELECT * INTO ... uitgevoerd vanuit de brontabel, zodat de tabelstructuur uit de brondatabase en de data in de doeldatabase terechtkomt
- Daarna wordt de volgende tabel verwerkt
*/

-- Parameters voor bron- en doeldatabase (stel ze eventueel anders in)
DECLARE @SOURCESERVER nvarchar(255) = 'Server waar de brondatabase op gehuisvest is'
DECLARE @SOURCEDATABASE nvarchar(255) = 'Brondatabase'
DECLARE @TARGETDATABASE nvarchar(255) = 'Doeldatabase'

DECLARE @SQLStatement nvarchar(max) = '' -- Parameter om SQL Statements met parameters samen te stellen
DECLARE @FIELDNAME nvarchar(255) -- Ophalen van veldnamen per veld
DECLARE @TABLENAME nvarchar(255) -- Ophalen van tabellen per tabel
DECLARE @FIELDS nvarchar(max) = '' -- Set van veldnamen per tabel (wordt bij INSERT gebruikt)
DECLARE @DBTableObject nvarchar(255) -- Dit is de tabelnaam inclusief schema
DECLARE @IS_IDENTITY bit = 0 -- Wordt gebruikt bij ophalen van is_identity kolom in sys.columns
DECLARE @SET_IDENTITY bit = 0 -- Wordt gebruikt bij bepaling of IDENTITY_INSERT nodig is

-- Samenstellen van de lijst met tabellen die moeten worden overgezet. Bij de WHERE clause kan dit worden getweaked
DECLARE TABLELIST CURSOR
FOR
SELECT
tbl.name,
'[' + schm.name + '].['+ tbl.name + ']' AS DBTableObject
FROM [Bronserver].[Brondatabase].[sys].[tables] tbl -- LET OP: HIER OOK DE BRONDATABASE (en BRONSERVER) INGEVEN!
JOIN [Bronserver].[Brondatabase].[sys].[schemas] schm on tbl.schema_id = schm.schema_id -- LET OP: HIER OOK DE BRONDATABASE (en BRONSERVER) INGEVEN!
WHERE
1=1
AND tbl.name NOT IN (
'Tabelnaam1',
'Tabelnaam2',
'Tabelnaam3'
)
AND tbl.name NOT LIKE 'BCK%' -- dit zijn vaak backup tabellen
ORDER BY tbl.name -- changelog v1.1 (volgorde van tabellen is nu van A tot Z)

-- Openen van de gevonden lijst met database tabellen die moeten worden overgezet
OPEN TABLELIST
FETCH NEXT FROM TABLELIST INTO @TABLENAME, @DBTableObject

WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL Statement samenstellen met alle benodigde acties
SET @SQLStatement = @SQLStatement +
'-- Processing table ... ' + @DBTableObject + CHAR(13)
SET @SQLStatement = @SQLStatement +
'SELECT * INTO [' + @TARGETDATABASE + '].[dbo].[' + @TABLENAME + ']' + -- Tabelstructuur in doeldatabase bouwen
' FROM [' + @SOURCESERVER + '].[' + @SOURCEDATABASE + '].' + @DBTableObject
SET @SQLStatement = @SQLStatement + CHAR(13)

PRINT @SQLStatement -- Laten zien welke acties er plaatsvinden
EXEC sp_executesql @SQLStatement -- acties daadwerkelijk uitvoeren

PRINT 'Table ' + @TABLENAME + ' processed!' + CHAR(13) -- Tonen wanneer actie gereed is

SET @SQLStatement = '' -- Nulstellen parameters
SET @FIELDS = ''
SET @SET_IDENTITY = 0
FETCH NEXT FROM TABLELIST INTO @TABLENAME, @DBTableObject

END

CLOSE TABLELIST
DEALLOCATE TABLELIST