Performance & Indexing

1. Indexes

Indexes maken opzoeken veel sneller, maar zijn geen gratis oplossing — ze kosten opslagruimte en vertragen schrijfoperaties omdat ze mee bijgewerkt moeten worden.

Zonder index = full table scan: de database loopt elk record af, niet alleen voor = maar ook voor <, > en soms LIKE.

Design richtlijnen

Creëer index voor kolommen die vaak in JOIN of WHERE voorkomen
Beperk indexes met meerdere kolommen
Vermijd indexering van kleine tabellen (overkill)
Vermijd indexering van heel grote tabellen met veel inserts/updates

Soorten indexes

Type	Beschrijving
Heap	Geen index — data ongeorganiseerd
Clustered	Data gesorteerd en opgeslagen in boomstructuur
Nonclustered	Aparte boomstructuur met verwijzingen naar de data

2. Heap Index

Simpele data-structuur zonder logische volgorde.

Bevat een aantal pages met data
Data vinden = alle data scannen
Nieuwe data kan overal bewaard worden (bestaande page met ruimte, of nieuwe page)
→ Data snel gefragmenteerd → slechtere performantie

3. Clustered Index

Organiseert de data in een gebalanceerde boomstructuur (B-tree).

Begint van één enkele root page
Bevat 1 of meerdere tussenliggende levels
De data staat effectief in de leaf pages gesorteerd op de clustered key
Max. 16 kolommen in 1 sleutel
Gecombineerde sleutel max. 900 bytes

Nadeel: Page split

Als een page vol is: 1. Page wordt gesplitst 2. Helft records naar nieuwe pagina 3. → Fragmentatie mogelijk

Oplossingen voor fragmentatie

Oplossing	Beschrijving
`FILLFACTOR`	Bepaalt hoeveel % van een page gevuld mag zijn vóór splitsen
`PAD_INDEX`	Past FILLFACTOR ook toe op tussenliggende index pages

-- Fragmentatie controleren
ALTER INDEX ALL ON dbo.Employees REORGANIZE;

-- Volledige rebuild
ALTER INDEX ALL ON dbo.Employees REBUILD;

4. Nonclustered Index

Heeft een gelijkaardige boomstructuur als de clustered index, maar:

Geen data in de index — enkel een verwijzing naar de locatie in de clustered index
Om een rij te vinden: index overlopen → locatie bepalen → data ophalen via RID lookup

5. Index Aanmaken

Via SSMSVia T-SQL

Object Explorer → tabel → Indexes → rechtermuisklik → New Index → kies type

Types: Clustered Index, Non-Clustered Index, Clustered Columnstore Index, ...

USE ClassicModels;
GO

CREATE NONCLUSTERED INDEX idx_nc_lastname
    ON dbo.Employees(lastName);
GO

6. Index Gebruiken

Efficiënte queries starten met WHERE-clauses om data te filteren — WHERE-clauses maken gebruik van indexes.

Het execution plan toont hoe de data werd opgezocht (Index Scan, Clustered Index Scan, Index Seek, ...).

Query Optimizer

De query optimizer bepaalt onafhankelijk of een beschikbare index gebruikt wordt, op basis van hoe de WHERE-clause geformuleerd is.

Tip

Gebruik IN (waarde1, waarde2) of = waarde i.p.v. OR of berekeningen op de kolom — de optimizer kiest dan vaker een index.

Index verplichten

SELECT *
FROM   Employees WITH (INDEX([idx_nc_lastname]))
WHERE  lastName = 'Patterson';

Index usage opvragen

USE ClassicModels;
GO

SELECT object_name(S.object_id) AS table_name,
       I.name                   AS index_name,
       S.user_seeks,
       S.user_scans,
       S.user_lookups
FROM   sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS I
    ON S.object_id = I.object_id
    AND S.index_id = I.index_id
WHERE  S.object_id = object_id(N'dbo.Employees', N'U');
GO

7. Performantie

Snelheid

Queryprestaties hangen af van veel meer dan alleen de query zelf: - Server (CPU, RAM, I/O, ...) - Hoeveelheid data - Load (aantal queries/seconde) - Contention (locking) - Optimalisatie van queries en schema (o.a. via indexes) - Caching

Opslagruimte

Hoeveel plaats de database inneemt hangt af van: - Hoeveelheid data - Data types - Indexes - Cleanup (verwijderen oude data, vrijgeven resources)

8. Caching

De database cacht data automatisch — dit kan niet afgezet worden.

Om een query te testen zonder cache:

-- 1. Dirty pages wegschrijven naar schijf
CHECKPOINT;

-- 2. Geheugenbuffer leegmaken (zonder service restart)
DBCC DROPCLEANBUFFERS;

-- 3. Alle execution plans uit het geheugen verwijderen
DBCC FREEPROCCACHE;

9. Query History Opvragen

Methode	Beschrijving
`sys.dm_exec_requests`	Lopende queries op dit moment
`sys.dm_exec_query_stats`	Statistieken van gecachte query-plans
`sys.dm_exec_sql_text`	Tekst van gecachte SQL-queries
`sys.dm_exec_query_plan`	Uitvoeringsplan van gecachte queries
Activity Monitor	Grafische tool in SSMS
SQL Server Profiler	Trace-tool
Extended Events	Lichtgewicht event-monitoring
Query Store	Ingebouwde query-history (vanaf SQL Server 2016)

Lopende queries opvragen

SELECT r.start_time          [Start Time],
       session_id             [SPID],
       db_name(database_id)  [Database],
       substring(t.text, (r.statement_start_offset/2)+1,
           case when statement_end_offset = -1 OR statement_end_offset = 0
               then (datalength(t.Text)-r.statement_start_offset/2)+1
               else (r.statement_end_offset-r.statement_start_offset)/2+1
           end) [Executing SQL],
       status, command, wait_type,
       wait_time, wait_resource, last_wait_type
FROM   sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE  session_id != @@SPID
AND    session_id > 50
ORDER BY r.start_time;

10. Activity Monitor

Grafisch overzicht in SSMS van de serveractiviteit.

Via SSMS: rechtermuisklik op server → Activity Monitor

Secties: - Overzicht (CPU, wachttaken, batch-verzoeken per seconde) - Processes — alle actieve processen; rechtermuisklik → details voor T-SQL van die sessie - Resource Waits - Data File I/O - Recent Expensive Queries — hoge CPU of logische leesoperaties - Active Expensive Queries

11. Query Store

Voorziet inzicht in query plan selectie en performantie — beschikbaar vanaf SQL Server 2016.

Bewaard overzicht van: - Uitgevoerde queries - Gebruikte query plans - Runtime statistics

Informatie in tijdsblokken → vergelijkingen mogelijk.

3 opslag-componenten: 1. Uitvoeringsplaninformatie 2. Uitvoeringsstatistieken 3. Wachtstatistieken

Inschakelen

Via SSMSVia T-SQL

Rechtermuisklik op database → Properties → Query Store → Operation Mode: Read write

USE ClassicModels;
GO

ALTER DATABASE ClassicModels
    SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
GO

Query Store rapporten (via SSMS)

Rapport	Inhoud
Regressed Queries	Queries die achteruit zijn gegaan in performantie
Overall Resource Consumption	Totaal resourcegebruik per tijdsperiode
Top Resource Consuming Queries	Zwaarste queries (CPU, duur, geheugen)
Queries With Forced Plans	Queries met geforceerd uitvoeringsplan
Queries With High Variation	Queries met sterk wisselende performantie
Query Wait Statistics	Wachttijden per categorie
Tracked Queries	Specifiek te volgen queries

12. Event Viewer

De SQL Server event viewer bevat door de gebruiker gedefinieerde gebeurtenissen en systeemgebeurtenissen.

Via SSMS: Object Explorer → Management → SQL Server Logs → rechtermuisklik → View → SQL Server Log

Bevat: opstarttijdstipinfo, configuratie-instellingen, foutmeldingen, waarschuwingen.