Onjuist instellen van LSBackup maakt secondary logship database kapot

Vandaag is er wat tijd door mij ingeruimd om het SQL Server proces van logshipping nog beter te begrijpen. De opdracht die ik mij gaf, was: “Maak de secondary logship database kapot door onjuist instellen van de LSBackup job op de primary database.”

Voor deze opdracht was er een demo-database ingericht met daarop een secondary database op basis van logshipping. De basis van logshipping is dat alle mutaties op de primary database (de ‘hoofddatabase’) in een zogenaamde transactielog wordt vastgelegd. Van deze transactielogs wordt periodiek een backup (SQL Server Agent LSBackup job). gemaakt (de standaard bij het opzetten van een logshipdatabase is 15 minuten). Vervolgens worden deze transactielogs naar de server gekopieerd (SQL Server Agent LSCopy job) waar de secondary database op basis van logshipping staat en worden deze transactielogs daar ingelezen (SQL Server Agent LSRestore job).

Eén van de situaties die zich het meest voordoet in een ‘gebroken logshipketen’ op de secondary database, is dat er transactielogs ‘verdwijnen’ door wat voor oorzaak dan ook. Mijn onderzoek richt zich op het ‘verdwijnen’ van transactielogs door de LSBackup job; bij het maken van de backups van transactielogs. Is dat mogelijk?? Het korte antwoord is ‘ja’! Hoe? Lees vooral verder!

In de job LSBackup (waarmee backups worden gemaakt van de transactielogs) zit een eigenschap die in het Engels “Delete files older than…” heet. Deze instelling speelt een cruciale rol in het geheel. Wanneer de retentieperiode (de periode waarin de transactielogbestanden moeten worden blijven bewaard) tekort wordt ingesteld, zullen transactielogs te snel worden verwijderd en verloren gaan. Wat is dan een goede retentieperiode? Dat hangt af van de frequentie waarin de SQL Server Agent Job LSCopy, de job die ervoor zorgt dat transactielogs naar de server met de secondary database op basis van logshipping worden gekopieerd, draait. De retentieperiode van de transactielogs in de LSBackup job moet in ieder geval langer zijn dan de frequentie waarin de LSCopy job draait. Wanneer dat niet het geval is, zullen transactielogs te vroeg verwijdert worden, waardoor de secondary database op basis van logshipping niet meer adequaat bijgewerkt kan worden.

Veldlengtes (n)varchar en (n)char velden vs. query performance vs. server performance

Vandaag werd ik door mijn collega getriggerd om onderzoek te doen naar extreme lengtes in (n)varchar velden en de impact daarvan op performance. Welnu… riemen vast, want dit is belangrijk.

Management samenvatting
Een goede inrichting en adequate keuze van veldlengtes bij (n)varchar en (n)char velden draagt bij aan een optimaler query plan en daarmee aan een betere query en server performance in het algemeen.

Hoe zit dit precies
U kent het wel: “Laten we dat tekstveld alvast maar wat langer maken, zodat we ook in de toekomst deze velden niet zo snel hoeven te verlengen”. Die beslissing kan impact hebben op de query performance van uw query’s op deze velden. SQL Server kijkt bij het maken van het query plan (die SQL Server bij query’s gebruikt) naar de grootte van de tekstvelden die in de query wordt gebruikt. Op basis daarvan maakt SQL Server een inschatting van het benodigd geheugen. Hoe meer de veldlengte van het veld in de tabel afwijkt van de daadwerkelijke vulling des te meer geheugen zal SQL Server onnodig aan deze query toewijzen. Gelukkig zal SQL Server (2019) u hier in het Actual Query Plan op wijzen bij het meest linkse (SELECT) component. U vindt in het Actual Query Plan op dat component een geel driehoekje met een waarschuwing.

Eén en ander is met onderstaand TestLab verduidelijkt. Deze set met query’s helpt u hopelijk beter te begrijpen wat er precies fout gaat. Dit is overigens geen ‘incident’. De onjuiste bepaling van het benodigd geheugen zal continue fout gaan wanneer de betrokken tabel (of, indien dit zich in meerdere tabellen voordoet, betrokken tabellen) in een query wordt betrokken. Draaien er parallel meerdere query’s, dan zal dit potentieel onnodig veel resources op het geheugen van uw SQL Server gaan leggen; en geheugen is schaars en kostbaar.

Vragen staat vrij!


/*
Auteur : Mickel Reemer
Datum : 23 maart 2022
Thanks to : Erik Darling
Credits by : https://www.brentozar.com/archive/2017/02/memory-grants-data-size/
SQL Server : SQL Server 2019 Developer Edition

Doel van het script:
--------------------
Aantonen dat varchar die te groot zijn aangemaakt
oorzaak zijn excessive memory grants op een SQL Server
*/
-- Zet tijdstatistieken aan
SET STATISTICS TIME ON

-- Verwijder de testtabel wanneer deze al bestaat
DROP TABLE IF EXISTS dbo.MemoryGrants;

-- Maak de testtabel aan
CREATE TABLE dbo.MemoryGrants
(
ID INT PRIMARY KEY CLUSTERED,
Ordering INT,
Field1 VARCHAR(10),
);

-- Voeg de records in, in de tabel
INSERT dbo.MemoryGrants WITH (TABLOCK)
( ID, Ordering, Field1)
SELECT
c,
c % 1000,
REPLICATE('X', c * 10 % 10)
FROM (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) x

-- Zet Actual Query Plan aan en draai de query hieronder
SELECT mg.ID, mg.Ordering, mg.Field1 --Returning a VARCHAR(10)
FROM dbo.MemoryGrants AS mg
ORDER BY mg.Ordering
GO
-- De doorlooptijd is 1751ms. De memory grant is 13MB

/*
Dat gaan we nu nog een keer testen, maar dan met varchar(8000)
*/

-- Verwijder de testtabel opnieuw wanneer deze bestaat
DROP TABLE IF EXISTS dbo.MemoryGrants;

-- Maak de testtabel aan
CREATE TABLE dbo.MemoryGrants
(
ID INT PRIMARY KEY CLUSTERED,
Ordering INT,
Field1 VARCHAR(8000), -- We gaan nu Field1 een lengte van 8000 geven
);

-- Voeg de records in, in de tabel
INSERT dbo.MemoryGrants WITH (TABLOCK)
( ID, Ordering, Field1)
SELECT
c,
c % 1000,
REPLICATE('X', c * 10 % 10)
FROM (
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS c
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) x

-- Zet Actual Query Plan aan en draai de query hieronder
SELECT mg.ID, mg.Ordering, mg.Field1 --Returning a VARCHAR(10)
FROM dbo.MemoryGrants AS mg
ORDER BY mg.Ordering
GO
-- De memory grant is 574MB. De doorlooptijd is 2706ms

Waarom duurt die database restore zo lang?

Bij maken van plannen voor disaster recovery is het een goed idee om te kijken hoe snel een full backup terug gezet kan worden. Onlangs duurde het terugzetten van één van de backups waar ik mee werkte zo’n 17 uur en dat is natuurlijk vrij fors. We gaan er uiteraard dan wél vanuit dat er geen parallelle processen op de machine draaien die de performance negatief beïnvloeden. Microsoft SQL Server biedt mogelijkheden om te kijken waar de restore precies zijn tijd aan kwijt is. Hiervoor worden trace flags gebruikt. Mijn tip is om de volgende trace flags te gebruiken:

– 3004; deze geeft inzicht in elke stap van de restore
– 3213; deze geeft inzicht in de restore configuratie zoals buffer en maxtransfersize
– 3605; schrijft de resultaten van de trace flags hierboven weg naar de SQL Server errorlog

Vergeet de trace flags na de restore niet uit te zetten, omdat de trace flags zelf ook de performance van de machine beïnvloeden!

Een aantal aandachtspunten onder het trage terug zetten van de database:

  • Controleer of in de SQL Server Logs de volgende melding voorkomt: SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete etc. Dit betekent vrijwel zeker dat de storage waaronder de SQL Server draait niet snel genoeg id.

  • Paul Randall heeft via deze pagina een script gedeeld waarmee de I/O latency van een SQL Server over een periode van tijd is vast te leggen. Met de cijfers kan het team dat verantwoordelijk is voor hardware kijken wat er nodig is.

  • Een derde probleem is wat in het engels ‘Zeroing’ wordt genoemd. Dit is de tijd die nodig is om het logbestand van de database door te nemen en alle verwerkte transactielogs te verwerken. Het is niet aan te raden, maar als het logbestand significant via SHRINKLOGFILE kan worden verkleind, komt dit de snelheid waarmee de backup kan worden teruggezet ten goede

Succes!

Ongebruikte indexen op tabellen in een database vinden

Het is als database administrator belangrijk periodiek te kijken of er indexen op tabellen staan die door SQL Server nooit meer worden gebruikt. Dit voorkomt dat deze indexen onnodig schijfruimte innemen en/of onnodige overhead (performance) veroorzaken bij het bewerken, toevoegen of verwijderen van records op de tabel. In deze video wordt daar uitgebreider door mij op ingegaan en wordt getoond op welke wijze hier het best een analyse op kan worden gemaakt.

Het bijbehorende script is hier te downloaden.

Hoe eenvoudig data tussen twee SQL Server databases wordt gekopieerd

Het komt wel eens voor dat er data tussen twee SQL Server databases moet worden gekopieerd. Daar zijn legio mogelijkheden voor. In deze bijdrage behandel ik er drie:

  • Via ‘Generate scripts…’
  • Via ‘Import/Export data…’
  • … en een combinatie van de twee bovenstaande opties

Take away:

  • In de import/export data wizard gebruikt u de data source Microsoft OLE DB Driver for SQL Server (Download)
  • De Microsoft OLE DB Provider for SQL Server en de SQL Server Native Client zijn vervallen; zie Microsoft Docs
  • De import/export wizard houdt geen rekening met de tabel opties in de brontabel(len)
  • De ‘Generate scripts…’ wizard is niet heel geschikt voor heel grote tabellen
  • Voor grote tabellen kunt u gebruik maken van ‘Generate scripts…’ voor het aanmaken van de objecten en de import/export wizard voor het kopiëren van de data

Correct instellen van SQL Server tempdb (best-practice)

Nog zeer regelmatig kom ik SQL Servers tegen, waarbij de tempdb niet optimaal is ingericht. Af en toe bekruipt mij het gevoel dat de tempdb database het sluitstuk van een SQL Server installatie is. Aan die database administrators geef ik graag mee dat tempdb een essentiële bouwsteen van de SQL Server is, die de performance van SQL Server kunnen maken of breken.

Waar tempdb het meest voor wordt gebruikt

  • frequent maken en verwijderen van tijdelijke tabellen
  • tijdelijke objecten die bij CURSORS worden gebruikt
  • tijdelijke objecten die in combinatie met ORDER BY worden gebruikt
  • tijdelijke objecten die in combinatie met GROUP BY worden gebruikt
  • tijdelijke objecten die verband houden met het samenvoegen van data uit meerdere tabellen (HASH PLANS)

Bovenstaande acties kunnen tot opstoppingen in de tempdb leiden en daarmee de doorlooptijd van query’s.

Hoe wordt tempdb het beste ingericht

  • Verhoog het aantal data bestanden. Voor machines met minder dan 8 processoren: houd het aantal databestanden gelijk aan het aantal processoren; bij meer dan 8 processoren, gebruik om te beginnen 8 data bestanden. Mocht dat niet voldoende zijn om opstopping tegen te gaan, verhoog dan het aantal data bestanden met een veelvoud van vier totdat het probleem met opstoppingen is opgelost óf het aantal data bestanden gelijk is aan het aantal processoren. Het advies is, zeker wanneer er meer dan acht data bestanden voor tempdb nodig blijken, de opzet van de gebruikte query’s nog een keer na te lopen.
  • Zorg ervoor dat de databestanden van de tempdb altijd even groot zijn.
  • Het is beter om meerdere kleinere databestanden in de tempdb te hebben, dan één grote
  • Tot SQL Server 2014 kan het aanzetten van trace flag 1118 een significante verbetering in de performance betekeken. Trace flag 1118 verlicht de druk op de zogenoemde (S)GAM pagina’s, omdat alle enkelvoudige pagina toewijzingen worden uitgeschakeld. Dit gedrag is standaard vanaf SQL Server 2016; trace flag 1118 hoeft op deze versie en hoger niet te worden aangezet.
  • Zet de databestanden en logbestanden van de tempdb bij voorkeur op een aparte fysieke schijf; niet zijnde de C-schijf
  • Zorg voor een ruime autogrowth (in plaats van de standaard 64Mb die Microsoft gebruikt)
  • Monitor de groei van de tempdb

Gebruikte bronnen vanuit Microsoft:
Microsoft Docs: tempdb database
Support Microsoft: Recommendation to reduce allocation contention
Micorsoft Docs: Working with tempdb in SQL Server 2005

SQL Server Database in Recovery Pending

Vandaag vond ik één van mijn SQL Server testdatabases in status recovery pending. In deze bijdrage een korte weergave van mijn analyse, bevindingen en acties

Wanneer zich een incident op de SQL Server niveau voordoet, is het altijd een goed idee om in de SQL Server logfile te kijken, wat er is gebeurd, wanneer dat is gebeurd en onder welke omstandigheden iets is gebeurd.

Zo vond ik dat de foutmelding op deze database zich voordeed onder de omstandigheid dat de machine waarop SQL Server staat geïnstalleerd een herstart heeft gehad. SQL Server vermeldde in de log dat de betrokken database daarna niet beschikbaar was, omdat de database bestanden niet gevonden werden. Deze database bestanden staan voor deze testdatabase op een NAS, waar de machine op het moment van starten van SQL Server nog geen toegang toe had. Dan wordt dus duidelijk waar de foutmelding vandaan komt.

Ik heb vastgesteld dat de NAS wel weer beschikbaar was op de wijze zoals ik die had geconfigureerd. Ik heb de SQL Server services opnieuw gestart en het probleem was daarmee verholpen.

Lessons learnt: kijk bij incidenten altijd in de SQL Server logs.

Uw MS SQL Server database op een NAS?

Wellicht dat u dit herkent: u heeft een ontwikkeldatabase waar op uw servers geen ruimte meer voor is, maar u heeft nog wel een NAS staan met een hoop opslag beschikbaar. Het probleem: uw Microsoft SQL Server herkent de UNC adressen van de NAS niet en kan er niet goed mee om gaan. De oplossing: gebruik iSCSI. iSCSI zorgt ervoor dat (een deel van) uw NAS direct aan uw computer kan worden gekoppeld, daar een drive letter krijgt en door uw computer kan worden beheerd/benaderd. SQLTeam.NL beschikt over een Synology DS918+. Op de website van Synology wordt uitgelegd hoe op een Synology NAS een iSCSI Target-service kan worden aangemaakt en hoe deze op uw computer bekend wordt gemaakt.

  • Aanmaken van iSCSI Target-service op een Synology
  • Koppelen van de service aan uw computer
  • Het spreekt voor zich dat deze oplossing alleen voor ontwikkel- en testdatabases is bedoeld. Het draaien van database oplossingen op een NAS is nu eenmaal langzamer.

    ErrorCode en ErrorColumn in SSIS gereserveerd woord (keyword)!

    Vandaag reviewden mijn collega en ik een handleiding voor het samenstellen van een eenvoudig SSIS package in SQL Server Data Tools. Onderdeel van de handleiding was een voorbeeld SSIS package. Binnen het voorbeeld gebruikten we een dimensietabel waarin foutcodes en de omschrijving van die foutmeldingen waren vastgelegd. De tabelstructuur is in het Engels, dus de veldnamen waren ErrorCode en ErrorDescription.

    Microsoft heeft de woorden ErrorCode en ErrorColumn als ‘keywords’ (gereserveerde woorden) gemarkeerd. Deze woorden mogen niet in veldnamen van een tabel voorkomen. Doen ze dat wel, dan dient in de SSIS package het veldnaam een andere alias te krijgen.

    Een bug? Microsoft vindt van niet… en niet iedereen lijkt zich daarin te kunnen vinden. Zie dit Engelse artikel op Microsoft Connect.

    SQL Server logbestand van een database leegmaken

    Wellicht dat u dit probleem herkent: u heeft een testdatabase waarvan door het testen de logfile behoorlijk groot is geworden. Op een cursus heeft u wel eens gehoord van Transact-SQL functie “DBCC SHRINKFILE”. Via Microsoft Developer Network leest u vervolgens hoe DBCC SHRINKFILE moet worden gebruikt. Vervolgens past u dit op uw eigen omgeving toe en u merkt dat het logbestand na het gebruik van de functie DBCC SHRINKFILE (‘logical name’,1) nog steeds erg groot is. Wat nu?

    Een alternatieve manier om het logbestand kleiner te maken
    Er is een alternatieve manier om een logbestand kleiner te maken. Het proces is iets bewerkelijker en geeft het gewenste resultaat.

    Methode
    Deze procedure mag NOOIT op een productieomgeving worden uitgevoerd!
    De werkwijze om de logfile op een alternatieve manier te verkleinen, is als volgt:

    – Detach de database van SQL Server
    – Verplaats de logfile van de database naar een tijdelijke andere locatie
    – Attach de database, zonder logfile (bij melding ‘not found’ regel verwijderen)

    Verplaatsen logbestand
    Mocht de locatie van de logfile vervolgens anders moeten zijn, dan dienen
    aanvullend de volgende stappen te worden toegevoegd:

    – Detach de database opnieuw
    – Verplaats de logfile naar de nieuw locatie
    – Voer onderstaande query uit

    CREATE DATABASE [Naam van de database]
    ON (FILENAME = '[schijf/locatie]\[Naam mdf bestand].mdf'),
    (FILENAME = '[schijf/locatie]\[Naam log bestand].ldf')
    FOR ATTACH;

    Het verplaatsen van een bestand binnen een database wordt door Microsoft
    op deze locatie behandeld.