MS Sql 70-461: Chapter 17

Les 1

Bij de uitvoering van een query kan invloed worden uitgeoefend met behulp van hints. Hints zijn bevellen over hoe de query moet worden uitgevoerd:

• Table hints: hoe moet een tabel gebruikt worden in een query
• query hints: hints op query level. Bijv welk join algoritme moet gebruikt worden
• join hints: enkel voor een één join.

Sql Server voert een query uit m.b.v. physical operators. Omdat deze operators itereren over rowsets worden zij ook wel iterators genoemd.

Access Methods

Als een tabel is georganiseerd als een heap, dan is een table scan de enige acces method. De scan wordt dan uitgevoerd in geen specifieke logische volgorde. Sql Server gebruikt Index Allocation Map (IAM) pages om de scan uit te voeren in physical allocation order. Deze allocation scan kan ook worden gebruikt bij een clustered tabel. Een allocation order scan is sneller als de tabel minder gefragmenteerd is. De scan is langzamer als de fysieke fragmentatie hoger is.

Sql Server gebruikt een allocation order scan:

• voor een clustered tabel als een query geen specifieke volgorde kent
• als de isolation leven Read Uncomitted is
• als wordt gewerkt in een read-only omgeving

Als Sql Server een clustered index scant kan het ook de logical order van de index lezen d.m.v. de index order scan. In alle gevallen wordt de Clustered Index Scan iterator gebruikt. Sql Server gebruikt de index leaf-level's linked list om een index order scan uit te voeren. Index order scans worden negatief beïnvloed door zowel logical als physical fragmentatie.

Een nonclustered index kan een query coveren. Covering betekent dat Sql Server alle benodigde data van een query kan vinden in een nonclustered index en geen lookups hoeft te doen in de base table. Sql Server gebruikt de Index Scan iterator om een nonclustered index te scannen. Net als bij de Clustered Index Scan iterator kan Sql Server een allocation of een index order scan uitvoeren als een nonclustered index gescand wordt.

In sommige gevallen kan Sql Server besluiten om een query te coveren met meerdere nonclustered indexes. Sql Server kan nonclustered indexes samenvoegen. Alle nonclustered indexes van een tabel hebben altijd overeenkomstige data die gebruikt kan worden voor een join.

Sql Server is niet gelimiteerd om een full scan uit te voeren als een index wordt gescant. Als de rowset een filter kent en geordend is, dan kan Sql Server zoeken naar de eerste waarde (een nonclustered/clustered index seek) en vervolgens een partial scan uitvoeren.

De meest voorkomende access method die Sql Server gebruikt in Online Transaction Processing (OLTP) omgevingen is een nonclustered index seek met een ordered partial scan en dan een lookup in de base tabel voor elke rij die gevonden is in de nonclustered index. Dit zijn standaard plannen voor selectieve queries. Als de tabel georganiseerd is als een heap dan gebruikt Sql Server de RID Lookup operator om de rijen op te halen uit de base tabel. Deze rijen worden gevonden op basis van hun RID (Row IDentifier).
Als een tabel geclustered is dan gebruikt Sql Server de Key Lookup operator in plaats van de RID lookup operator.

Join algorithms

Bij het uitvoeren van joins kunnen er verschillende algoritmes worden gebruikt. Sql Server kent drie basis algoritmes: nested loops, merge joins en hash joins. Een hash join kan verder worden geoptimaliseerd door gebruik te maken van een bitmap filtering: een bitmap filtered hash join kan behandeld worden als een vierde algoritme of als een versterking van het hash algoritme. 

Het nested loops algoritme is een erg eenvoudig en vaak ook erg efficiënt algoritme. Sql Server gebruikt de ene tabel voor de buitenste loop, vaak de tabel met de minste rijen. Voor elke rij in de buitenste loop zoekt Sql Server de match in de tweede tabel, welke de binnenste loop vormt. Sql Server gebruikt de join conditie om de rijen te matchen. De join kan een non-equijoin zijn wat betekent dat de Equals operator geen onderdeel hoeft te zijn van de join predicate. Als de binnenste tabel geen ondersteunende indexes kent voor een seek, dan scant Sql Server de inner input voor elke rij voor de outer input. Dit is niet effectief. Een nested loop join is efficiënt als Sql Server een index seek kan toepassen op de binnenste tabel.

Merge join is een zeer efficiënt join algoritme. Voor dit algoritme is op zijn minst één equijoin predicate nodig en gesorteerde input van beide kanten. Dit betekent dat de merge join ondersteund zou moeten worden door indexes die op beide tabellen aanwezig zijn. Een nested loop join kan efficiënter zijn als de ene input veel kleiner is dan de andere.

Bij een 1-op-1 of 1-n scenario scant de merge join beide inputs maar één keer. Hij begint bij het vinden van de eerste rijen van beide kanten. Als het einde nog niet is bereikt checkt de merge join de join predicate om te bepalen of de rijen matchen. Als de rijen matchen worden zij toegevoegd aan de output. Vervolgens checkt het algoritme de volgende rijen van de andere kant en voegt deze toe aan de output totdat zij overeenkomen met de predicate. Als de rijen van de inputs niet overeenkomen dan leest het algoritme de volgende rij van de kant met de laagste waarde. Vanaf deze kant wordt nu gelezen en vergelijkt de rij met de rij van de andere kant totdat de waarde groter is dan de waarde van de andere kant. Vervolgens gaat hij door met lezen van de andere kant, enz.

In een veel-op-veel scenario gebruikt de merge join een worktable om de rijen van één input kant op te slaan en opzij te zetten voor hergebruik als dubbele rijen overeenkomen van de andere kant.

Als de inputs niet worden ondersteund door een index en een equijoin wordt gebruikt dan zou het hash join algoritme het meest efficiënt zijn. Die maakt gebruik van een zoekstructuur, genaamd een hash table. Deze tabel wordt door Sql Server zelf intern aangemaakt. Het gebruikt een hash functie om de rijen te splitsen in buckets van de kleinere input. Dit is de build fase. Sql Server gebruikt de kleinste input om de hash table te bouwen omdat deze tabel in het geheugen wordt opgeslagen. De buckets zijn allemaal ongeveer van dezelfde grootte.

Nadat de hash table is gemaakt past Sql Server de hash functie toe op elke rij van de andere input. Hij gaat na in welke bucket de row past. Vervolgens worden alle rijen in die bucket gescant. Dit is de probe fase.

Een hash join is een soort compromie tussen het maken van een full balanced tree index met een ander join algoritme en het uitvoeren van een full scan van de ene kant van de input voor elke rij van de andere input. In de eerste fase wordt een seek van de betreffende bucket gebruikt.
In een single-thread mode is de merge langzamer dan een nested loops join algoritme. Hoewel, Sql Server kan van te voren rijen opsplitsen en partial joins uitvoeren in meerdere threads. De hash join is daarom erg schaalbaar. Deze optimalisatie van een hash join heet bitmap filtered hash join. Vaak wordt deze gebruikt in een daatwarehouse scenario waar er veel input is en weinig gelijktijdige gebruikers zodat Sql Server de query parallel kan uitvoeren. Een regulieren hash join kan ook parallel worden uitgevoerd, maar een bitmap filtered hash join is efficiënter omdat Sql Server bitmaps kan gebruiken om vroegtijdig rijen van de grotere tabel te elimineren die niet gebruikt worden in de join.

Animatie over werking verschillende join algoritmes: https://www.youtube.com/watch?v=o1dMJ6-CKzU

Other plan iterators

De Sort operator wordt gebruikt om de input te sorteren. Deze operator kan gebruikt worden om het merge join algoritme toe te passen of als de query een "ORDER BY" clause heeft. Aan te raden is om de sort operator voor kleine resultsets te gebruiken omdat dit een zware operator is.

Sql Server gebruikt twee verschillende algoritmes voor het berekenen van aggregations (samenvoegingen). Als de input geordend is door de kolommen in de GROUP BY clause dan gebruikt Sql Server de stream aggregation. Deze Stream Aggregate operator is erg efficiënt. Als de input niet geordend is en de input is zo groot dat sortering inefficiënt is, dan wordt het hash aggregation algoritme gebruikt. De operator hiervoor is de Hash Match Aggregator en maakt net als de Hash Match Join operator een hash tabel aan en buckets. Alleen in dit geval worden de buckets gebruikt om de groups in op te slaan.

Les 2

Sql Server probeert de queries te parameteriseren zodat ze kunnen worden herbruikt.

Parameterized queries

Ad hoc queries worden automatisch geparameteriseerd. Sql Server gebruikt enkel een plan uit de cache als hij 100% zeker weet als het plan juist is voor de query. Veranderingen zoals een ander data type, sommige SET opties, security context, en meer kunnen zorgen voor een nieuw execution plan.

Met de dynamic management function "sys.dm_exec_query_stats" kan gekeken worden hoevaak een plan opnieuw gebruikt is. De exacte tekst kan opgehaald worden met "sys.dm_exec_sql_text" functie. Met het "DBCC FREEPROCCACHE T-SQL" command kan de cache worden leeggemaakt.

Ook als dezelfde parameter types gebruikt zouden worden in eenzelfde query, kan het voorkomen dat er toch meerdere plannen worden aangemaakt:

-- One row

SELECT orderid, custid, empid, orderdate

FROM Sales.Orders

WHERE custid = 13;

GO

-- Two rows

SELECT orderid, custid, empid, orderdate

FROM Sales.Orders

WHERE custid = 33;

GO

-- 31 rows

SELECT orderid, custid, empid, orderdate

FROM Sales.Orders

WHERE custid = 71;

GO

In cache:

cnt   text

---    ----

1      SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 33;

1      SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 13;

1      SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = 71;

Het gebruik van SPs bevordert de kans dat gecachte plannen vaker gebruikt worden en niet vaker worden aangemaakt.

Batch processing

Met bitmap filters worden rijen die niet nodig zijn in een hash join geëlimineerd. Dit wordt een bitmap filtered hash join genoemd. Dit wordt vaak in datawarehousing gebruikt waar grote inputs kunnen bestaan en weinig tegelijktijdige gebruikers zodat Sql Server de query parallel kan uitvoeren. Deze join wordt ook wel een star join genoemd: één tabel in het midden met daaromheen allemaal afhankelijke tabellen.

Als de CPU een bottleneck kan worden in een query kan gebruikt gemaakt worden van iterators die batches van rijen verwerken in plaats van rij voor rij. De beste resultaten worden geboekt met een colomnstore index. Sql Server kan batch en row operators mixen. Onderstaande operators ondersteunen batch mode verwerking:

- Filter

- Project

- Scan

- Local hash (partial) aggregation

- Hash inner join

- batch hash table build

Les 3

Optimizer hints

De optimizer hints zijn eigenlijk geen hints, maar instructies. Ze kunnen gebruikt worden met SELECT statements en Data Modification Language statements. Er zijn 3 verschillende soorten hints: table, query en join hints.

Bij  SELECT, INSERT, UPDATE, DELETE, en MERGE statements worden de hints opgegeven in de OPTION clause. Bij subqueries kunnen geen hints worden aangegeven. Bij een union kan enkel bij de laatste query een hint worden opgegeven. Als bij een INSERT gebruik wordt gemaakt van een SELECT kan er geen hint worden opgegeven.

-- Hash match aggregate

SELECT qty, COUNT(*) AS num

FROM Sales.OrderDetails

GROUP BY qty;

-- Forcing stream aggregate

SELECT qty, COUNT(*) AS num

FROM Sales.OrderDetails

GROUP BY qty

OPTION (ORDER GROUP);

Overzicht van alle query hints:
- { HASH | ORDER } GROUP
- { CONCAT | HASH | MERGE } UNION
- { LOOP | MERGE | HASH } JOIN
- EXPAND VIEWS
- FAST number_rows
- FORCE ORDER
- IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
- KEEP PLAN
- KEEPFIXED PLAN
- MAXDOP number_of_processors
- MAXRECURSION number
- OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ ,...n ] )
- OPTIMIZE FOR UNKNOWN
- PARAMETERIZATION { SIMPLE | FORCED }
- RECOMPILE
- ROBUST PLAN
- USE PLAN N'xml_plan'
- TABLE HINT ( exposed_object_name [ , [ [, ]...n ] ]

Table hints beinvloeden locking en de access method voor een tabel of een view. Table hints worden opgegeven in de FROM clause middels de WITH clause. 

-- Clustered index scan

SELECT orderid, productid, qty

FROM Sales.OrderDetails

WHERE productid BETWEEN 10 AND 30

ORDER BY productid;

-- Forcing a nonclustered index usage

SELECT orderid, productid, qty

FROM Sales.OrderDetails WITH (INDEX(idx_nc_productid))

WHERE productid BETWEEN 10 AND 30

ORDER BY productid;

Ander voorbeeld met joins

-- Nested loops join

SELECT O.custid, O.orderdate, OD.orderid, OD.productid,OD.qty

FROM Sales.Orders AS O

 INNER JOIN Sales.OrderDetails AS OD

 ON O.orderid = OD.orderid

WHERE O.orderid < 10250;

-- Forced merge join

SELECT O.custid, O.orderdate, OD.orderid, OD.productid,OD.qty

FROM Sales.Orders AS O

 INNER MERGE JOIN Sales.OrderDetails AS OD

 ON O.orderid = OD.orderid

WHERE O.orderid < 10250;

Overzicht table hints:

- NOEXPAND
- INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
- FORCESEEK [ ( index_value ( index_column_name [ ,... ] ) ) ]
- FORCESCAN
- FORCESEEK
- KEEPIDENTITY
- KEEPDEFAULTS
- IGNORE_CONSTRAINTS
- IGNORE_TRIGGERS
- HOLDLOCK
- NOLOCK
- NOWAIT
- PAGLOCK
- READCOMMITTED
- READCOMMITTEDLOCK
- READPAST
- READUNCOMMITTED
- REPEATABLEREAD
- ROWLOCK
- SERIALIZABLE
- SPATIAL_WINDOW_MAX_CELLS = integer
- TABLOCK
- TABLOCKX
- UPDLOCK
- XLOCK

Plan Guides

Bij een plan guide kan de OPTION clause gebruikt worden of een specifiek query plan. Ook wordt het Sql statement gespecificeerd om aan te geven voor welk statement de plan guide bedoeld is. Er zijn drie soorten plan guides:

• OBJECT plan guides worden gebruikt door de Optimizer om queries te matchen in SP's , scalar user defined functions multistatement table-valued user-defined functions, en DML triggers.
• SQL plan guides worden door de Optimizer gebruikt om stand-alone queries te matchen of queries in ad-hoc batches
• TEMPLATE plan guides worden door de Optimizer gebruikt om stand-alone queries te matchen die geparameteriseerd kunnen worden.

Met de system stored procedure "sys.sp_create_plan_guide" kunnen plan guides worden aangemaakt. Met de SP "sys.sp_control_plan_guide" kan een plan guide worden disabled, enabled of worden verwijderd.

Aanmaken plan guide:

EXEC sys.sp_create_plan_guide

 @name = N'Cust71',

 @stmt = N'

 SELECT orderid, custid, empid, orderdate

 FROM Sales.Orders

 WHERE custid = @custid;',

 @type = N'OBJECT',

 @module_or_batch = N'Sales.GetCustomerOrders',

 @params = NULL,

 @hints = N'OPTION (OPTIMIZE FOR (@custid = 71))';

Met onderstaande query kunnen alle plan guides bekeken worden:

SELECT plan_guide_id, name, scope_type_desc, is_disabled, query_text, hints

FROM sys.plan_guides;