MS Sql 70-461: Chapter 15
Les 1
Als een object over de 8 pages komt reserveert Sql Server uniform extents voor dit object. Met deze organisatie wordt door kleine objecten weinig ruimte verspild en grote objecten zijn minder gefragmenteerd.
Heaps en balanced trees
Pages zijn fysieke structuren. Sql Server organiseert data in pages in logische structuren. Sql Server organiseert tabellen als heaps of als balanced trees. Een tabel die is georganiseerd als een balanced tree wordt ook wel een clustered table of clustered index genoemd.
Indexes zijn altijd georganiseerd als balanced trees. Andere indexes zoals indexes die niet alle data bevatten en dienen als pointers naar rijen in tabellen voor snelle seeks heten non-clustered indexes.
Heaps
Een heap is een eenvoudige structuur. Data in een heap is niet georganiseerd in geen enkele logische volgorde. Een heap is gewoon een (ongeordende) stapel van pages en extents.
Een speciaal systeem genaamd Index Allocation Map (IAM) pages wordt door Sql Server gebruikt om te bepalen welke pages en extents behoren tot een object. Elke tabel of index heeft op zijn minst één IAM page: 'first IAM'. Een enkele IAM page kan verwijzen naar ongeveer 4GB. Grote objecten kunnen meerdere IAM pages hebben. IAM pages voor een object zijn georganiseerd als een doubly linked list; elke page heeft een pointer naar zijn nakomeling en voorganger. Sql Server slaat pointers eerst op in first IAM pages in zijn eigen interne systeem tabellen.
Onderstaand een afbeelding die toont hoe de tabel van orders van customers er uit zien als het is georganiseerd als een heap:
Sql Server kan data enkel vinden door de gehele heap te scannen. Sql Server gebruikt IAM pages om heaps te scannen in fysieke volgorde, of allocation order. Ook als de query maar om een enkele rij vraagt moet Sql Server de gehele heap scannen. Sql Server slaat nieuwe rijen overal op in de heap. Het kan de rij opslaan in een bestaande page als deze nog niet vol is, of de rij toekennen aan een nieuwe page of extent. Dit heeft als gevolg dat heaps in de loop van de tijd erg gefragmenteerd worden.
Als bij bijv, het aanmaken van een tabel geen clustered index, PK of unique constraints zijn opgegeven, dan wordt de tabel georganiseerd als een heap. Als de tabel wordt aangemaakt worden er geen pages toegewezen. De eerste page en ook de eerste IAM page wordt toegewezen als de eerste rij wordt toegevoegd aan de tabel. In de sys.indexes catalog view kan algemene informatie worden gevonden over tabellen en indexen:
SELECT OBJECT_NAME(object_id) AS table_name, name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.TestStructure', N'U');
Output
table_name index_name type type_desc
------------- ---------- ---- ---------
TestStructure NULL 0 HEAP
De kolom type slaat 0 op voor heaps, 1 voor clustered tabled (indexes) en 2 voor nonclustered indexes.
Het aantal pages dat is toegewezen aan een object kan opgevraagd worden met de 'sys.dm_db_index+physical_stats' view of met behulp van van de 'dbo.sp_spaceused' SP:
SELECT index_type_desc, page_count,
record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');
EXEC dbo.sp_spaceused @objname = N'dbo.TestStructure', @updateusage = true;
Output:
index_type_desc page_count record_count avg_page_space_used_in_percent
--------------- ---------- ------------ ------------------------------
HEAP 0 0 0
name rows reserved data index_size unused
------------- ---- -------- ----- ---------- ------
TestStructure 0 0 KB 0 KB 0 KB 0 KB
De kolom 'avg_page_space_used_in_percent' toont de interne fragmentatie. Internal fragmentation betekent dat pages niet vol zijn. Hoe meer rijen er zijn opgeslagen in een enkele page, des te minder pages hoeft Sql Server te lezen om deze rijen op te halen en des te minder geheugen het gebruikt voor het cachen van de pages.
Stel de volgende output voor:
index_type_desc page_count record_count avg_page_space_used_in_percent
--------------- ---------- ------------ ------------------------------
HEAP 9 241 87.6465530022239
name rows reserved data index_size unused
------------- ---- -------- ----- ---------- ------
TestStructure 241 136 KB 72 KB 8 KB 56 KB
Er worden daadwerkelijk 9 pages gebruikt, maar er zijn 17 (136 / 8) pages gereserveerd. 56 KB is ongebruikt: 10 pages zijn gebruikt (incl de IAM page die niet wordt genoemd) keer 8kb (de grootte van een page = 80KB. De gereserveerde 136KB - de daadwerkelijk gebruikte 80KB = 56 KB. De ongebruikte ruimte betekent dat 7 pages (56 / 8) van een uniform extent leeg zijn. De eerste 8 pages blijven in de mixed extents. Omdat de tabel al groter is dan 8 pages, wijst Sql Server uniform extents toe als er extra ruimte nodig is.
Clustered indexes
Een tabel wordt georganiseerd als een balanced tree als een clustered index wordt aangemaakt. De structuur wordt een balanced tree genoemd omdat het lijkt op een omgekeerde boom. Elke balanced tree heeft één root page en op z'n minst één of meerdere leaf pages. Alle data in een clustered table is opgeslagen in leaf pages. Data is opgeslagen op logische volgorde van de clustering key. Een clustering key kan bestaan uit een enkele kolom of meerdere kolommen. Als de key bestaat uit meerdere kolommen dat is dit een composite key. Er kunnen maximaal 16 kolommen in een key en de grootte van alle kolommen samen in een composite key mogen niet groter zijn dan 900 bytes. Onthoud wel dat de data logisch is geordend, maar niet fysiek. Sql Server gebruikt nog altijd IAM pages voor de fysieke toewijzingen.
Pages boven leaf level verwijzen naar leaf-level pages. Een rij in een page boven een leaf level bevat een clustering key value en een pointer naar een page waar deze value begint in een logisch gerangschikt leaf niveau. Als een enkele page kan verwijzen naar alle leaf level pages dan maakt Sql Server de eerste intermediate-level pages, welke verwijzen naar leaf-level pages. De root page verwijst intermediate-level pages. Als de root page niet naar alle first-level intermediate pages kan verwijzen maakt Sql Server een nieuw level van intermediate pages aan. Pages van hetzelfde level zijn georganiseerd als een doubly linked list. Sql Server kan dan de vorige en volgende page vinden in logische volgorde voor elke specifieke page. Sql Server gebruikt IAM pages om fysieke toewijzingen van de balanced tree pages te vinden.
Er kunnen één kolom of meerdere kolommen gebruikt worden met unieke of niet-unieke waarden voor een sleutel van een clustered index. Sql Server onderhoud intern altijd de uniekheid van een clustering key. Er wordt hiervoor een unieke waarde gebruikt, wat een opvolgend nummer is, voor de herhalende waarde. De eerste waarde is opgeslagen zonder uniek nummer, de eerstvolgende herhalende waarde krijgt een unieke waarde met als waarde 1.
De index heeft 2 levels: de leaf level en de root page. The root page heeft 621 rijen die verwijzen naar 621 leaf pages. Index level 0 is het leaf level.
Er kunnen één kolom of meerdere kolommen gebruikt worden met unieke of niet-unieke waarden voor een sleutel van een clustered index. Sql Server onderhoud intern altijd de uniekheid van een clustering key. Er wordt hiervoor een unieke waarde gebruikt, wat een opvolgend nummer is, voor de herhalende waarde. De eerste waarde is opgeslagen zonder uniek nummer, de eerstvolgende herhalende waarde krijgt een unieke waarde met als waarde 1.
Om in bovenstaande afbeelding een rij te vinden hoeft Sql Server maar 3 pages te lezen. Als de tabel als een heap georganiseerd zou zijn, zou hij de gehele tabel moeten lezen, wat overeenkomt met het lezen van alle leaf-level pages. Een clustered index scan kan gedaan worden in logische volgorde of, als de logische volgorde niet nodig is, op fysieke volgorde.
Een nadeel van een clustered index t.o.v. de heap is als een nieuwe rij wordt toegevoegd aan de tabel. Als de rij moet worden toegevoegd aan een volle page, dan moet Sql Server deze page opsplitsen en verplaatst hij de helft van de rijen naar de nieuwe page. Dit gebeurt omdat Sql Server de logische volgorde van de rijen moet onderhouden. Dit leidt vervolgens tot interne fragmentatie, wat niet voorkomt bij een heap.
Fysieke volgorde van pages en extents van een clustered table hoeven niet te corresponderen met de logische volgorde. Als pages fysiek niet meer op volgorde staan, dan is de clustered table logisch gefragmenteerd. Dit is ook bekend als external fragmentatie. External fragmentatie vertraagd full of partial scans op logische volgorde.
Een short clustering betekent dat meer rijen passen op pages boven het leaf level. Daarom zijn er in potentie minder levels nodig. En minder levels betekent een efficientere index omdat Sql Server minder pages hoeft te lezen om een rij te vinden. Een uniquifier breidt de key uit; daarom heeft het hebben van een short en unieke key de voorkeur voor seeks.
Onderstaande query maakt een clustered index op de kolom id:
CREATE CLUSTERED INDEX idx_cl_id ON dbo.TestStructure(id);
Als een clustered index wordt aangemaakt wordt de tabel gereorganiseerd.Bij het uitlezen van de dynamic management functie 'sys.dm_db_index_physical_stats
' kan de volgende output ontstaan:
index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pct
--------------- ----------- ----------- ---------- ------------ ----------------------
CLUSTERED INDEX 2 0 621 18630 98.1961947121324
CLUSTERED INDEX 2 1 1 621 99.7158388930072
De index heeft 2 levels: de leaf level en de root page. The root page heeft 621 rijen die verwijzen naar 621 leaf pages. Index level 0 is het leaf level.
index_type_desc index_depth index_level page_count record_count avg_pg_spc_used_in_pct
--------------- ----------- ----------- ---------- ------------ ----------------------
CLUSTERED INDEX 3 0 622 18631 98.0435507783543
CLUSTERED INDEX 3 1 2 622 49.9258710155671
CLUSTERED INDEX 3 2 1 2 0.296515937731653
De index heeft nu 3 levels. Omdat een nieuwe page is toegewezen op leaf level kan de originele root page niet meer naar alle leaf levels verwijzen. Sql Server heeft daarom een intermediate level aangemaakt met twee pages die verwijzen naar 622 leaf pages en een nieuwe root page die verwijst naar de twee intermediate-level pages.
Deze query checkt de interne (avg_page_space_used_in_percent) en externe fragmentatie (avg_fragmentation_in_percent):
SELECT index_level, page_count,
avg_page_space_used_in_percent, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure'), NULL, NULL , 'DETAILED');
Output:
index_level page_count avg_page_space_used_in_percent avg_fragmentation_in_percent
----------- ---------- ------------------------------ ----------------------------
0 300 98.1961947121324 1.66666666666667
1 3 55.5720286632073 0
2 1 1.64319248826291 0
De index heeft drie levels. Er is geen interne fragmentatie op leaf level en bijna geen externe fragmentatie. Alle pages op leaf level zijn vol en de fysieke volgorde is bijna hetzelfde als de logische volgorde.
Een andere output:
index_level page_count avg_page_space_used_in_percent avg_fragmentation_in_percent
----------- ---------- ------------------------------ ----------------------------
0 432 68.1842599456387 98.6111111111111
1 4 60.0197677291821 50
2 1 2.19915987150976 0
De leaf-level pages hebben maar 68% van de ruimte gevuld met rijen. Dit komt omdat Sql Server meerdere page splits heeft uitgevoerd. De externe fragmentatie is ongeveer 99%; bijna geen enkele page is fysiek correct geordend. GUIDS voor clustered keys kunnen leiden tot inefficiente indexes. Externe fragmentatie veroorzaakt vaak langzame scans, wat niet veel voor zal komen in OLTP omgevingen, maar wel belangrijk is bij data warehousing. Interne fragmentatie is bij beide omgevingen een probleem omdat de tabel groter is dan het zou zijn bij een opvolgende key.
Met rebuilden of reorganizing een index kan fragmentatie voorkomen worden. Reorganizing een index is langzaam en minder opdringerig proces dan het rebuilden van de index. Als richtlijn zou een index reorganized moeten worden als de externe fragmentatie minder is dan 30%. Bij meer dan 30% zou de index rebuilded moeten worden:
ALTER INDEX idx_cl_filler1 ON dbo.TestStructure REBUILD; -- of REORGANIZE
Implementing Nonclustered indexes
Noncustered indexes hebben een gelijkwaardige structuur als clustered indexes. De root en de intermediate levels lijken op elkaar. Het leaf-level is verschillend omdat het niet alle data bevat. Wat opgeslagen wordt in het leaf-level is afhankelijk van de onderliggende organisatie van een tabel (heap of balanced tree). Er kunnen maximaal 999 nonclustered indexes op een enkele tabel.
Het leaf-level van een nonclustered index bevat de index keys en row locators. Nogmaals, er kunnen 16 kolommen in een key en gezamenlijke kolommen mogen niet groter zijn dan 900 bytes. Een row locator verwijst naar een rij in de onderliggende tabel. Als de tabel een heap is, dan heet de row locator een row identifier (RID). Dit is een 8-byte pointer die de database file ID en page ID bevat van de rij en het rij id op die page.
De afbeelding toont een nonclustered index op een heap en gebruikt dezelfde data als de afbeelding die de balanced tree bevat eerder in dit hoofdstuk. De orderid is gebruikt voor de key van de index.
Om een rij te zoeken moet Sql Server de index naar het leaf level doorlopen en dan de betreffende page uit de heap lezen om dan de rij uit de page te lezen. De manier om de rij op te halen uit de heap wordt RID lookup genoemd. Als een query erg selectief is en zoekt naar een rij of een paar rijen dan is een index seek met RID lookup efficient. Omdat pages op hetzelfde level van een index verbonden zijn in een doubly linked list, kan Sql Server een partial of full ordered scan uitvoeren op een nonclustered index en dan een RID lookup doen zonder te hoeven starten vanaf de root page voor elke rij. Maar als het aantal rijen van de query toeneemt wordt de ROD lookup duurder omdat
een RID lookup vaak één page per rij is.
Als de table georganiseerd is als een balanced tree, dan is de row locator de clustering key. Dit houdt in dat als Sql Server zoekt naar een rij, hij alle levels van een nonclustered index moet doorlopen en vervolgens alle levels van de clustered index. Deze manier wordt een key lookup genoemd. Dit lijkt slechter dan het opzoeken van een rij in de heap, maar omdat in dit geval de row locator verwijst naar een logische structuur en geen fysieke structuur, maakt het niet uit waar de rij in tabel zich fysiek bevind. Dit heeft als gevolg dat de clustered index vrijuit reorganized of gerebuild kan worden; zolang de clustering keys niet worden aangepast, hoeft Sql Server niet de nonclustered indexes aan te passen. Als een rij verplaatst wordt in de heap moet Sql Server alle nonclustered indexes bijwerken vanwege de nieuwe posities. Sql Server heeft wel een optimalisatie voor het bijwerken van de heap. Als een rij verplaatst moet worden naar een nieuwe page laat Sql Server een forwarding pointer achter naar de nieuwe locatie in de originele page.
Bovenstaande afbeelding toont een nonclustered index op een clustered tabel. De clustering key zou kort en uniek moeten zijn omdat het voorkomt in alle nonclustered indexes. De clustering zou ook niet vaak moeten veranderen, of liever nog, helemaal niet veranderen. Als een clustering key wordt bijgewerkt moeten alle nonclustered indexes ook allemaal bijgewerkt worden. Ook zou eerst een clustered index gemaakt worden en dan pas alle nonclustered indexes.
Er kunnen filtered nonclustered indexes gemaakt worden. Een filtered index bevat enkel een subset van kolomwaarden en wordt dus toegepast op een subset van rijen. Filtered nonclustered indexes zijn nuttig als sommige waarden niet vaak in een kolom voorkomen, waar andere waarden vaak voorkomen in die kolom. In zo'n geval kan een filtered index aangemaakt worden voor enkel de zeldzame waarden. Sql Server gebruikt deze index voor seeks voor zeldzame waarden en gebruikt scans voor waarden die regelmatige voorkomen. Filtered indexes zijn niet duur omdat deze enkel worden bijgewerkt als de zeldzame waarden veranderen. Een filtered index kan aangemaakt worden door een WHERE clause toe te voegen aan het CREATE INDEX statement.
Sql Server heeft een manier voor het opslaan van nonclustered indexes. Naast het opslaan van de data in rijen kan Sql Server 2012 de index data ook opslaan in kolommen. Dit heet columnstore index. Columnstore indexes kunnen data warehouse queries enorm versnellen.
Data opgeslagen per rij
|
Data opgeslagen per kolom
|
(meer info: https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/)
Een columnstore index is weer een andere nonclustered index op een tabel. De Sql Query Optimizer overweegt de columnstore index te gebruiken tijdens de query optimization fase, net zoals andere indexes.
Een columnstore index is weer een andere nonclustered index op een tabel. De Sql Query Optimizer overweegt de columnstore index te gebruiken tijdens de query optimization fase, net zoals andere indexes.
Een colmnstore index is compressed opgeslagen. De compressiefactor kan tot 10 keer de grootte van de index zijn. Als een query refereert naar een enkele kolom die onderdeel is van een columnstore index, dan pakt Sql Server enkel die kolom van de disk, hij pakt niet de gehele rij zoals bij row storage. Columnstore heeft zijn eigen compressiemethode. Row of Page compressie kan niet gebruikt worden bij een columnstore index.
Columnstore indexes versnellen data warehouse queries, niet OLTP workloads. Tabellen met een columnstore index zijn read only. Als een rij moet worden toegevoegd of gewijzigd, dan moet de index van de tabel worden gehaald.
De columnstore index is gescheiden in units die segmenten worden genoemd. Segments zijn opgeslagen als grote objecten en bestaan uit meerdere pages. Segments zijn de eenheid van verplaatsing van disk naar geheugen. Elk segment heeft in de metadata de minimum en maximum waarde van elke kolom opgeslagen voor dat segment. Sql Server laadt enkel die segmenten die opgevraagd worden door de query in het geheugen.
Implementing Indexed Views
Een indexed view is een view met een clustered index. Een clustered view kent vele beperkingen, restricties en vereisten. Bij het maken van een indexed view moet altijd de optie SCHEMABINDING gebruikt worden en moet de COUNT_BIG functie gebruikt worden als GROUP BY gebruikt wordt.
Les 2
Om queries gebruik te laten maken van indexes is het nodig om de juiste predicates te schrijven zodat de Sql Server Query Optimizer indexes kan toepassen.
Supporting queries met indexes
De WHERE clause is het belangrijkste onderdeel van een query die voordeel kan behalen met een index. Met een estimated of actual query plan kan gekeken worden of dat een index is gebruikt bij een query. Het gebruik van indexes kan ook achterhaald worden door de dynamic view 'sys.dm_index_usage_stats' te query'en (let wel, deze informatie is cumulatief sinds de start van sql server).
Het is aan te raden een in index te gebruiken bij een GROUP BY clause. Sql Server kan data combineren (agrregate) door gebruik te maken van een hash of een stream aggregate operator. Laatstegenoemde is sneller, maar heeft wel gesorteerde input nodig.
Het wordt aangeraden om bij een ORDER BY een index te gebruiken omdat het sorteren van grote datasets grote performance problemen geeft. De data moet worden gesorteerd in memory of moet worden gesorteerd in tempdb als het niet in het geheugen past.
Als Sql Server alle data kan vinden in nonclustered indexes dan is de covered door de nonclustered indexes en de indexes zijn covering indexes. Covered queries zijn erg efficient. Er kunnen meer kolommen toegevoegd worden aan een nonclustered index, maar hoe langer de key van de index wordt, des te minder efficient wordt de index. In Sql Server 2012 kunnen kolommen worden toegevoegd (included) aan een nonclustered index op leaf-level en niet als onderdeel van de index. Dit kan d.m.v. de INCLUDE clause of het CREATE INDEX statement. De included kolom is geen onderdeel van de key en Sql Server gebruikt het niet voor seeks. Included columns helpen queries te coveren,
Search arguments
De Query Optimizer kan enkel indexes toepassen als argumenten in een predicate zoekbaar zijn (search arguments - SARGS). Dit betekent dat een kolom die een index heeft in zijn eentje voor moet komen in een predicate en niet als onderdeel van een functie-parameter. De vorm is dan ook: kolom inclusive_operator inclusive_operator kolom. De kolom bevind zich dus altijd een één kant van de epressie. Inclusive operators zijn: =, >, <, =>, <=, BETWEEN en LIKE. De LIKE operator is enkel inclusive als de wildcord (% of _) niet wordt gebruikt aan het begin van een string. In onderstaande query wordt geen index gebruikt omdat de kolom onderdeel is van een functie:
SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE DATEDIFF(day, '20060709', orderdate) <= 2
AND DATEDIFF(day, '20060709', orderdate) > 0;
Execution plan:
De volgende query maakt wel gebruik van indexes omdat de kolom nu aan één kant van de expressie staat:
SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE DATEADD(day, 2, '20060709') >= orderdate
AND '20060709' < orderdate;
Execution plan:
De Query Optimizer converteert de IN operator naar OR. Beide queries zijn dus hetzelfde:
SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE orderdate IN ('20060710', '20060711');
SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE orderdate = '20060710'
OR orderdate = '20060711';
De AND operator in de WHERE clause heeft als gevolg dat elk deel van de predicate het resultaat limiteert. Als bijv. de eerste conditie 5 rijen teruggeeft, dan gaat de volgende conditie met de 5 rijen verder. Het resultaat van condities (predicates) met een OR operator worden bij elkaar samengevoegd. Als bijv. de ene predicate 5 rijen teruggeeft en de volgende predicate 3 rijen, dan zal de result set tussen de 3 en 8 rijen bevatten.
Aangeraden wordt om zoveel mogelijk met de AND operator te werken.
Les 3
Sql Server onderhoud statistieken in speciale system statistical pages. De Query Optimizer gebruikt deze statistieken om de kardinaliteit of het aantal rijen in te schatten van een query set.
Auto created statistics
Standaard maakt Sql Server statistieken aan. Sql Server maakt statistieken voor elke index en voor kolommen die gebruikt worden als SARGs in queries. Er zijn drie database opties die invloed hebben op het automatisch genereren van statistieken:
- AUTO_CREATE_STATISTICS
- Als deze optie op ON staat maakt Sql Server automatisch statistieken aan. Dit is de standaardwaarde.
- AUTO_UPDATE_STATISTICS
- Als deze optie op ON staat werkt Sql Server automatisch de statistieken bij als er voldoende veranderingen zijn in de onderliggende tabellen en indexes. Ook worden out-of-date statistieken bijgewerkt tijdens query optimalisatie. Sql Server gaat na of er outdated statistieke zijn voordat de query wordt gecompileerd en voordat een cached query wordt uitgevoerd.
- AUTO_UPDATE_STATISTICS_ASYNC
- Deze optie bepaalt of Sql Server synchroon of asynchroon de statistieken bijwerkt tijdens query optimalisatie. Als de statistieken asynchroon worden bijgewerkt kan Sql Server deze statistieken niet gebruiken voor de optimalisatie. Sql Server wacht niet op de bijgewerkte statistieken tijdens de optimalisatie fase.
Er is een limiet voor het aantal stappen in histogrammen. Een statistiek kan maximaal 200 stappen hebben. Het statistiek object bevat ook een header met metadata over de statistieken en een density vector om de cross-column correlatie te meten. Sql Server berekent het geschatte aantal rijen dat een query retourneerd met data in het statistiek object.
Informatie over statistieken kan opgehaald worden door de catalog views 'sys.stats' en 'sys.stats_columns'. Met het command DBCC SHOW_STATISTICS command kan gedetailleerde informatie worden opgehaald. Met de SP 'sys.sp_updatestats' kunnen handmatig de statistieken worden bijgewerkt voor alle tabellen binnen een database. Het uitvoeren van deze SP kan veel tijd en resources in beslag nemen.
SELECT OBJECT_NAME(object_id) AS table_name,
name AS statistics_name, auto_created
FROM sys.stats
WHERE object_id = OBJECT_ID(N'Sales.Orders', N'U');
Output:
table_name statistics_name auto_created
---------- --------------------- -----------
Orders PK_Orders 0
Orders idx_nc_custid 0
Orders idx_nc_empid 0
Orders idx_nc_shipperid 0
Orders idx_nc_orderdate 0
De auto_generated kolom bevat een 1 voor statistieken die Sql Server automatisch genereert voor kolommen die gebruikt worden als zoek argumenten.
Als een index wordt geREBUILD dan worden de statistieken hiervan bijgewerkt.
De volgende query toont de histogram van een index:
DBCC SHOW_STATISTICS(N'Sales.Orders',N'idx_nc_empid') WITH HISTOGRAM;
Output:
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
| 1 | 0 | 123 | 0 | 1 |
| 2 | 0 | 96 | 0 | 1 |
| 3 | 0 | 127 | 0 | 1 |
| 4 | 0 | 156 | 0 | 1 |
| 5 | 0 | 42 | 0 | 1 |
| 6 | 0 | 67 | 0 | 1 |
| 7 | 0 | 72 | 0 | 1 |
| 8 | 0 | 101 | 0 | 1 |
| 9 | 0 | 43 | 0 | 1 |
In dit geval zijn er 9 stappen omdat de empId kolom 9 distinct waarden heeft. Als in bovenstaande query WITH HISTOGRAM weggelaten zou worden, wordt alle informatie over de statistieken getoond, inclusief de header en de density vector. De header bevat o.a. informatie wanneer de statistiek voor het laatstst is bijgewerkt:
DBCC SHOW_STATISTICS(N'Sales.Orders',N'idx_nc_empid') WITH STAT_HEADER;
Denamen van alle auto-created statistieken beginnen met _WA_Sys_.
Manually maintaining statistics
Een voorbeeld om handmatig de statistieken bij te werken is als een query predicate meerdere kolommen bevat die cross-column relationships heeft. Statistieken op meerdere columns kunnen het query plan verbeteren.
Net zoals bij filtered indexes kunnen ook filtered statistieken worden aangemaakt. Statistieken die aangemaakt zijn door Sql Server zijn altijd aangemaakt voor alle rijen. Als queries vaak een subset van rijen selecteren van rijen die een unieke data distributie hebben, kunnen filtered statistieken de query plans verbeteren.
Update statistieken handmatig in de volgende gevallen:
- Als de query execution traag is en bekend is dat de query correct geschreven is en ondersteund wordt door de juiste indexes.
- Als insert queries gebeuren in op- of aflopende key columns. Statistieken worden niet bijgewerkt voor elke rij. Daarom kan het aantal rijen dat wordt toegevoegd te weinig zijn om de statistieken te laten bijwerken. Als vervolgens queries rijen selecteren van de toegevoegde rijen, kunnen de statistieken niet de cardinality estimates hebben voor deze nieuwe rijen.
- Het kan overwogen worden om de statistieken handmatig bij te werken als Sql Server zelf wordt bijgewerkt. Dit omdat de statistieken informatie kan veranderen bij een nieuwe versie van Sql Server.
Reacties
Een reactie posten