Uge 3: Extent Management på SQL Server

Med uge 3 allerede godt i gang og sidste uges gennemgang af Data Pages, kommer her denne uges indlæg. Denne gang om Extent Management i SQL Server.

Har du misset det sidste indlæg, så kommer det herunder

Uge 2: Data Pages – grundlaget i SQL serveren

På et overordnet plan er Extents en samling af 8 pages på hver 8kb. Dermed er de dataelementer på 64kb. SQL server har to forskellige typer Extents.

  • Mixed Extents
  • Uniform Extents

Mixed og Uniform Extents

I en Mixed Extent er de 8 pages fra forskellige databaseobjekter, såsom tabeller og indexes. Det betyder også at en Mixed Extent kan pege på 8 forskellige objekter. Modsat en Uniform Extent, som kun indeholder pages fra et og samme databaseobjekt.

Det spændende er nu, hvordan SQL Serveren arbejder med denne forskel og hvorfor det er sådan.

Før årtusindeskiftet var diskkapacitet dyrt, meget dyrt. Målet var den gang at optimere så meget som muligt på anvendelsen af denne kapacitet. Derfor bliver de første 8 pages af nye tabeller eller indexes altid allokeret til Mixed Extents. Det betyder også at tabeller og indexes vokser i størrelse med 8kb ad gangen. Resultatet bliver at små tabeller forbliver meget små – forbruget af kapaciteten er så effektiv som muligt.

Så snart et databaseobjekt overstiger 8 pages og den 9. page skal allokeres, bliver der også allokeret en hel Extent til det objekt. Størrelsen stiger derfor fra 8kb til 72kb (8kb + 64kb). Med den 17 page stiger den til 136kb (8kb + 64kb + 64kb) osv.

Med de nuværende priser på diskkapacitet, så er det lidt hovedrystende at have dette designvalg, men set historisk har det været et vigtigt valg for at spare diskplads.

Extent Management

Extent Management

Men hvordan håndterer SQL Serveren så alle disse Extents? Forestil dig en database på 1 TB – det er en kæmpe bunke Extents og endnu flere pages. SQL Serveren anvender her 2 særlige pages, som igen (og som altid) er 8kb:

  • Global Allocation Map Pages (GAM)
  • Shared Global Allocation Map Pages (SGAM)

Uniform Extents er altid håndteret af GAM pages. SQL serveren bruger GAM pages 8.000 bytes (64.000 bits) til at danne et bitmap som repræsenterer en Uniform Extent. Hvis en bit er tændt bliver den specifikke Extent ikke brugt (den er dermed ledig), hvis den er slukket bliver den brugt. Det betyder at en GAM page kan håndtere 4 GB data (64.000 x 64 / 1024 / 1024). Derfor ses det også at GAM pages ligger vel spredt ud over datafilen til databasen – for hver 4 GB. Det samme er gældende for SGAM pages. Den håndterer også 4 GB data, men for Mixed Extents.

Når man indsætter en række i en tabel, finder SQL serveren, via SGAM pages, de Mixed Extents som har mindst en ledig data page. Hvis en tabel eller index er større end 64kb, så finder SQL Serveren den næste ledige Uniform Extent via gennemsøgning af GAM pages.

Når jeg senere kommer til at tale om TempDb, så kommer jeg også til at tale om seriøse performanceproblemer, når det workload man arbejder med opretter store mængder data i TempDb. Jeg kommer også med tips og tricks til hvordan man kan komme omkring det med konfiguration af TempDb (on-premise og managed instance i Azure).

Opsummering – Extent Management

Med denne gennemgang af Extent Management og de tidligere indlæg om Pages og motorrummet, begynder grundlaget for performancetuning at være på plads.

Har du fået blod på tanden til at vide mere om Extents på SQL Serveren, så er der lidt links nedenfor.

Næste gang skriver jeg lidt om Data Page Restrictions.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie.

T-SQL Tuesday – Tools I use

This is my first contribution to the T-SQL Tuesday. So wish me luck.

The T-SQL Tuesday is a monthly blog party that happens on the second Tuesday of each month. The very first one was started over a decade ago by Adam Machanic (blog|twitter), and currently is being maintained by Steve Jones (blog|twitter). More about the concept on its website.

This edition is hosted by Mikey Bronowski and covers all the awesome and outstanding tools that we all use in our daily work around the SQL server.

Dynamic Management Views for documentation

I, like most of us, are not “in the zone” when writing documentation for the completed work.

So I often tend to be a little lazy and try to figure out eazier ways to get the documentation done.

For instance – all the tables in a solution can be extracted via the Dynamic Management Views:

SELECT sys.objects.name AS TableName, ep.value AS Description
FROM sys.objects
CROSS APPLY fn_listextendedproperty(default,
                                    'SCHEMA', schema_name(schema_id),
                                    'TABLE', name, null, null) ep
WHERE sys.objects.name NOT IN ('sysdiagrams')
ORDER BY sys.objects.name

Above query gives me all the information I need for the documentation around tables in my solution. I often write desciptions in the META tags of the tables I’ve created, in order to help developers see some sort of usage and description directly in the database.

Relationships between tables – all the Foreign Keys’ relationship to Primary Keys between tables, can be extracted with below code snippet:

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    tr.name 'Refrenced table',
	fk.is_disabled as 'Non check constraint'
FROM sys.foreign_keys fk
INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
where fk.schema_id = 7
ORDER BY tp.name, cp.column_id
T-SQL Tuesday - Relationships
Relationships returned from above query

Columns and their attributes (with description) can be extracted with below code snippet:

SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, ep.value AS Description
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
CROSS APPLY fn_listextendedproperty(default,
                  'SCHEMA', schema_name(schema_id),
                  'TABLE', sys.objects.name, 'COLUMN', sys.columns.name) ep
ORDER BY sys.objects.name, sys.columns.column_id

The results are (sorry for the danish text in the Description field):

T-SQL Tuesday - Columns
Tables with columns and descriptions (sorry for the danish wording)

Summary – T-SQL Tuesday

This is just some of the tools I use for my daily work with the SQL Server. There are many others, and you can find all the awesome suggestions and blogpost about this here:

T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome

Bogliste – Lær SQL

Her en bogliste over gode titler på bøger som kunne være interessante at læse.

Der er ingen af disse bøger, der er reklame eller på anden vis betalt for at blive vist nedenfor. Det er min helt egen liste.

Husk at kigge forbi en gang imellem for at se flere bøger.

Bogliste

12-02-2021:
Master SQL Fundamentals.
En god bog til dem der gerne vil lidt videre end de to første bøger på listen.
Jeg har fundet dem på Amazon lige her:
Learning SQL – Master SQL Fundamentals
05-02-2021:
Lær at kode SQL på en weekend med denne bog.
Den er god som et startsted, og gør det let at komme i gang.
Du kan finde den på Amazon som Kindle version lige her:
Super Simple SQL: Learn SQL in one weekend!
05-02-2021:
Lær om databaser via en tegneserie. Det kan du her.
Der findes en række tegneserier i samme Manga-serie omhandlende tekniske aspekter.
Manga Guide to Databases kan du bl.a. finde på Amazon her:
The Manga Guide to Databases

en_USEnglish