Uge 10: Plan Caching

I denne uge vil jeg, som lovet i sidste uge, forsøge at hjælpe dig godt i gang med at forstå Plan Caching og de afledte effekter den mekanisme har på SQL serverens performance. Fra sidste uge kan du måske huske at enhver logisk query mod SQL serveren kræver en fysisk Execution Plan. Denne Execution Plan bliver gemt i Plan Cache for fremtidigt brug.

Ad-hoc SQL queries

Hver gang man eksekverer en ad-hoc SQL query mod SQL serveren, vil SQL serveren lave en unik Execution Plan for hver unik query. Men hvad er en unik query?

SQL serveren beregner en hash værdi af hele SQL querien (inkl. evt. hard-kodede parametre og disses værdier), og bruger denne hash værdi til at finde eksisterende Execution Plans i Plan Cache. Hvis en eksisterende plan eksisterer, vil denne blive brugt, ellers vil en ny plan blive udarbejdet og denne bliver så gemt i Plan Cache. Et eksempel:

select * from Sales.SalesOrderHeader
where CustomerID = 11000

GO

select * from Sales.SalesOrderHeader
where CustomerID = 30052

GO

select * from Sales.SalesOrderHeader
where CustomerID = 11223

GO

De 3 ovenstående queries vil hver generere en ny Execution Plan, fordi der er hard-kodede parametre. Og heraf bliver hash værdien for de 3 queries unik. Den afledte effekt er at der nu eksisterer 3 execution planer til næsten identiske queries. Dette problem bliver kaldt Plan Cache Pollution.

Plan Cachen bliver forurenet med nye Execution Plans som er svære at genbruge og der går værdifuld hukommelse til spilde som ellers kunne bruges på andre processser i SQL serveren. Målet er at have en så høj re-use count på Execution Plans fra Plan Cache.

Plan Stability

En måde at optimere genbrugen af Execution Plans fra Plan Cache, er at lave parameter værdier i SQL queries (med @parameternavn) eller, og endnu bedre, lave stored procedures som skal returnere data. På den måde kan SQL serveren genbruge Execution Plans fra Plan Cache meget effektivt.

Lad mig tage samme eksempel query som fra uge 8 som var den der udførte et Bookmark Lookup:

Som kendt fra uge 8, så giver Bookmark Lookup kun mening hvis man skal bruge få rækker fra sin tabel. Hvis man kommer over Tipping Point, så er det mere cost effektivt at lave hele tabel- eller clustered index scan. Men hvis SQL serveren genbruger en execution plan fra Plan Cache, så bliver denne beslutning slet ikke overvejet længere – SQL serveren genbruger blindt de gemte execution plans, også selv om performance bliver utrolig langsom.

Plan caching

Fra ovenstående eksempel, kan vi se at SQL serveren blindt genbruger en cached execution plan med Bookmark Lookup. Prøv at se forskellen på Estimated Number of Rows og Actual Number of Rows. SQL serveren genbruger en execution plan hvor der var 1 række i resultatsættet, mens der reelt i denne query er 1.499 rækker.

Årsan til dette er at der ikke er Plan Stability. Baseret på det estimerede antal rækker, anvender SQL serveren Bookmark Lookup i stedet for det mere effektive Table/Clustered Index Scan (hvis Tipping Point er nået). Dette er et ret udbredt og generelt problem for mange installationer af SQL serveren og disses performance problemer.

Men hvordan kan man så undgå disse Bookmark Lookups og genbrug af execution plans fra Plan Cache? Det kan du måske allerede gætte: Ved at lave et covering Non-Clustered Index som passer med den query der eksekveres. På den måde får du altid den bedste execution plan for din query og uanset hvordan du baserer dine parametre, vil SQL serveren altid svare hurtigt og effektivt. Men pas på med at lave for mange indexes for at tilgodese alle queries. Det er ret så vigtigt!

Opsummering – Plan Caching

En kort introduktion til Plan Cache og hvilke fordele og ulemper dette kan medføre på SQL serveren. Det er et tve-ægget sværd at arbejde med – på den ene side er Plan Cache et meget effektivt værktøj, da man kan genbruge allerede eksisterende execution plans, på den anden side et meget farligt værktøj, da man kan komme til at genbruge allerede eksisterende execution plans…

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

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

10-03-2021:
Pro T-SQL programmers guide.
En meget gennemgribende bog om T-SQL sproget og guides til hvordan man kan anvende det bedst muligt.
Den kan bl.a. købes på Amazon:
Pro T-SQL Programmer’s Guide
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