Beskrivning av loggnings- och datalagringsalgoritmer som utökar datatillförlitligheten i SQL Server

Ursprunglig produktversion: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Ursprungligt KB-nummer: 230785

Sammanfattning

I den här artikeln beskrivs hur Loggning och dataalgoritmer i Microsoft SQL Server utökar datatillförlitligheten och integriteten.

Mer information om de underliggande begreppen för motorerna och om algoritmen för återställning och isolering som utnyttjar semantik (ARIES) finns i följande dokument om ACM-transaktioner på databassystem (under volym 17, nummer 1, mars 1992):

Extern länk: ARIES: En transaktionsåterställningsmetod som stöder finkornighetslåsning och partiell återställning med hjälp av loggning framåt

Dokumentet behandlar SQL Server-teknikerna för att utöka datatillförlitligheten och integriteten i samband med fel.

Vi rekommenderar att du läser följande artiklar i Microsoft Knowledge Base för mer information om cachelagring och diskussioner om alternativt felläge:

• Beskrivning av cachelagring av diskkontrollanter i SQL Server

• Information om hur du använder diskenhetscacheminnen med SQL Server som alla databasadministratörer bör känna till

Termer som används i den här artikeln

Innan vi börjar den djupgående diskussionen definieras några av de termer som används i den här artikeln i följande tabell.

Period	Definition
Batteribackad	Separat och lokaliserad batteriSäkerhetskopieringsanläggning som är direkt tillgänglig och styrs av cachelagringsmekanismen för att förhindra dataförlust. Detta är inte en avbrottsfri strömförsörjning (UPS). En UPS garanterar inte några skrivaktiviteter och kan kopplas från cachelagringsenheten.
Cache	Mellanliggande lagringsmekanism som används för att optimera fysiska I/O-åtgärder och förbättra prestanda.
Felaktig sida	Sida som innehåller dataändringar som ännu inte har tömts till stabil lagring. Mer information om felaktiga sidbuffertar finns i Skriva sidor på SQL Server Books Online. Innehållet gäller även för Microsoft SQL Server 2012 och senare versioner.
Fel	Allt som kan orsaka ett oväntat avbrott i SQL Server-processen. Exempel är: strömavbrott, datoråterställning, minnesfel, andra maskinvaruproblem, dåliga sektorer, driftavbrott, systemfel och så vidare.
Spola	Tvinga fram en cachebuffert till stabil lagring.
Hasp	Synkroniseringsobjekt som används för att skydda fysisk konsekvens för en resurs.
Icke-volatil lagring	Alla medium som fortfarande är tillgängliga för systemfel.
Fäst sida	Sidan som finns kvar i datacachen och som inte kan tömmas till stabil lagring förrän alla associerade loggposter skyddas på en stabil lagringsplats.
Stabil lagring	Samma som icke-volatil lagring.
Flyktig lagring	Alla medium som inte förblir intakta över fel.

Wal-protokoll (Write-Ahead Logging)

Termprotokollet är ett utmärkt sätt att beskriva WAL. Det är en specifik och definierad uppsättning implementeringssteg som krävs för att se till att data lagras och utbyts korrekt och kan återställas till ett känt tillstånd om det uppstår ett fel. Precis som ett nätverk innehåller ett definierat protokoll för att utbyta data på ett konsekvent och skyddat sätt, så beskriver WAL även protokollet för att skydda data.

ARIES-dokumentet definierar WAL enligt följande:

WAL-protokollet hävdar att loggposterna som representerar ändringar av vissa data redan måste vara i stabil lagring innan ändrade data tillåts ersätta den tidigare versionen av data i icke-volatil lagring. Det betyder att systemet inte får skriva en uppdaterad sida till sidans icke-volatila lagringsversion förrän åtminstone de ångrade delarna av loggposterna, som beskriver att uppdateringarna på sidan har skrivits till stabil lagring.

Mer information om loggning före skrivning finns i avsnittet Om transaktionslogg för skrivning framåt i SQL Server Books Online.

SQL Server och WAL

SQL Server använder WAL-protokollet. För att säkerställa att en transaktion har checkats in korrekt måste alla loggposter som är associerade med transaktionen skyddas i stabil lagring.

För att klargöra den här situationen bör du överväga följande specifika exempel.

Kommentar

Anta i det här exemplet att det inte finns något index och att den berörda sidan är sida 150.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Dela sedan upp aktiviteten i förenklade loggningssteg, enligt beskrivningen i följande tabell.

Utdrag	Åtgärder som utförts
PÅBÖRJA TRANSAKTION	Skrivs till loggcacheområdet. Det är dock inte nödvändigt att tömma till stabil lagring eftersom SQL Server inte har gjort några fysiska ändringar.
INFOGA I tblTest	1. Datasidan 150 hämtas till SQL Server-datacache, om den inte redan är tillgänglig. 2. Sidan är låst, fäst och märkt smutsig och lämpliga lås erhålls. 3. En Infoga loggpost skapas och läggs till i loggcachen. 4. En ny rad läggs till på datasidan. 5. Låset släpps. 6. Loggposterna som är associerade med transaktionen eller sidan behöver inte tömmas just nu eftersom alla ändringar förblir i flyktig lagring.
CHECKA IN TRANSAKTION	1. En incheckningsloggpost skapas och loggposterna som är associerade med transaktionen måste skrivas till stabil lagring. Transaktionen anses inte vara bekräftad förrän loggposterna har tilldelats korrekt till stabil lagring. 2. Datasidan 150 finns kvar i SQL Server-datacachen och töms inte omedelbart till stabil lagring. När loggposterna är korrekt skyddade kan återställningen göra om åtgärden om det behövs. 3. Transaktionslås frigörs.

Bli inte förvirrad av termerna "låsning" och "loggning". Även om det är viktigt är låsning och loggning separata problem när du hanterar WAL. I föregående exempel har SQL Server vanligtvis spärren på sidan 150 under den tid som krävs för att utföra de fysiska infogningsändringarna på sidan, inte hela tiden för transaktionen. Lämplig låstyp upprättas för att skydda raden, intervallet, sidan eller tabellen efter behov. Mer information om låstyper finns i låsningsavsnitten för SQL Server Books Online.

Om du tittar närmare på exemplet kan du fråga dig vad som händer när LazyWriter- eller CheckPoint-processerna körs. SQL Server utfärdar alla lämpliga tömningar till stabil lagring för transaktionsloggposter som är associerade med den smutsiga och fästa sidan. Detta säkerställer att wal-protokollets datasida aldrig kan skrivas till stabil lagring förrän de associerade transaktionsloggposterna har tömts.

SQL Server och stabil lagring

SQL Server förbättrar logg- och datasidans åtgärder genom att inkludera kunskap om disksektorstorlekar (vanligtvis 4 096 byte eller 512 byte).

För att upprätthålla ACID-egenskaperna för en transaktion måste SQL Server ta hänsyn till felpunkter. Under ett fel garanterar många diskenhetsspecifikationer bara ett begränsat antal sektorskrivningsåtgärder. De flesta specifikationer garanterar slutförande av en enskild sektorskrivning när ett fel inträffar.

SQL Server använder 8 KB-datasidor och loggen (om den töms) på multiplar av sektorstorleken. (De flesta diskenheter använder 512 byte som standardsektorstorlek.) Om det uppstår ett fel kan SQL Server ta hänsyn till skrivåtgärder som är större än en sektor genom att använda loggparitet och slitna skrivtekniker.

Identifiering av sönderriven sida

Med det här alternativet kan SQL Server identifiera ofullständiga I/O-åtgärder som orsakas av strömavbrott eller andra systemavbrott. När det är sant gör det att en bit vänds för varje 512 byte-sektor på en databassida på 8 kilobyte (KB) när sidan skrivs till disk. Om en bit är i fel tillstånd när sidan senare läse av SQL Server skrevs sidan felaktigt. en sönderriven sida identifieras. Skadade sidor identifieras under återställningen eftersom alla sidor som skrivits felaktigt troligen kommer att läsas av återställning.

Även om SQL Server-databassidor är 8 kB utför diskar I/O-åtgärder med hjälp av en sektor på 512 byte. Därför skrivs 16 sektorer per databassida. En sönderriven sida kan inträffa om systemet misslyckas (till exempel på grund av ett strömavbrott) mellan den tid då operativsystemet skriver den första 512 byte-sektorn till disken och slutförandet av I/O-åtgärden på 8 KB. Om den första sektorn på en databassida har skrivits innan felet visas databassidan på disken som uppdaterad, även om den kanske inte har lyckats.

Genom att använda batteribaserade diskstyrenhetscacheminnen kan du se till att data skrivs till disken eller inte skrivs alls. I den här situationen anger du inte identifiering av sönderrivna sidor till "true" eftersom det inte är nödvändigt.

Kommentar

Identifiering av slitna sidor är inte aktiverat som standard i SQL Server. Mer information finns i ALTER DATABASE SET Options (Transact-SQL).

Loggparitet

Loggparitetskontroll liknar identifiering av slitna sidor. Varje sektor på 512 byte innehåller paritetsbitar. Dessa paritetsbitar skrivs alltid med loggposten och utvärderas när loggposten hämtas. Genom att tvinga loggskrivningar på en gräns på 512 byte kan SQL Server se till att incheckningsåtgärder skrivs till de fysiska disksektorerna.

Prestandapåverkan

Alla versioner av SQL Server öppnar logg- och datafilerna med hjälp av funktionen Win32 CreateFile. DwFlagsAndAttributes-medlemmen innehåller FILE_FLAG_WRITE_THROUGH alternativet när de öppnas av SQL Server.

FILE_FLAG_WRITE_THROUGH instruerar systemet att skriva via en mellanliggande cache och gå direkt till disken. Systemet kan fortfarande cachelagrat skrivåtgärder, men kan inte lätt tömma dem.

Alternativet FILE_FLAG_WRITE_THROUGH ser till att när en skrivåtgärd returnerar ett lyckat slutförande lagras data korrekt i stabil lagring. Detta överensstämmer med WAL-protokollet som säkerställer data.

Många diskenheter (SCSI och IDE) innehåller inbyggda cacheminnen på 512 KB, 1 MB eller större. Enhetens cacheminnen förlitar sig dock vanligtvis på en kondensator och inte på en batteribaserad lösning. Dessa cachelagringsmekanismer kan inte garantera skrivningar över en effektcykel eller liknande felpunkt. De garanterar bara att sektorns skrivåtgärder slutförs. Detta är särskilt anledningen till att den sönderrivna skrivnings- och loggparitetsidentifieringen byggdes in i SQL Server 7.0 och senare versioner. När enheterna fortsätter att växa i storlek blir cacheminnena större och de kan exponera större mängder data under ett fel.

Många maskinvaruleverantörer tillhandahåller batteribaserade diskstyrenhetslösningar. Dessa kontrollantcacheminnen kan underhålla data i cacheminnet i flera dagar och till och med tillåta att cachelagringsmaskinvaran placeras på en andra dator. När strömmen återställs korrekt rensas oskrivna data innan ytterligare dataåtkomst tillåts. Många av dem tillåter att en procentandel läs- och skrivcache upprättas för optimala prestanda. Vissa innehåller stora minnesutrymmen. För ett visst segment av marknaden tillhandahåller vissa maskinvaruleverantörer avancerade system för batteribaserad diskcachelagring med 6 GB cachelagring. Dessa kan avsevärt förbättra databasens prestanda.

Avancerade cachelagringsimplementeringar hanterar FILE_FLAG_WRITE_THROUGH begäran genom att inte inaktivera kontrollantcachen eftersom de kan tillhandahålla verkliga omskrivningsfunktioner i händelse av systemåterställning, strömavbrott eller andra felpunkter.

I/O-överföringar utan användning av en cache kan vara längre på grund av den mekaniska tid som krävs för att flytta enhetshuvuden, rotationshastigheter och andra begränsande faktorer.

Sektorordning

En vanlig teknik som används för att öka I/O-prestanda är sektorordning. För att undvika mekaniska huvudrörelser sorteras läs-/skrivbegäranden, vilket ger en mer konsekvent rörelse i huvudet för att hämta eller lagra data.

Cacheminnet kan innehålla flera logg- och dataskrivningsbegäranden samtidigt. WAL-protokollet och SQL Server-implementeringen av WAL-protokollet kräver tömning av loggskrivningarna till stabil lagring innan sidskrivningen kan utfärdas. Användningen av cachen kan dock returnera framgång från en loggskrivningsbegäran utan att data skrivs till den faktiska enheten (det vill: skrivs till stabil lagring). Detta kan leda till att SQL Server utfärdar begäran om skrivning av datasidor.

Med inblandning av skrivcachen anses data fortfarande vara i flyktig lagring. Men från Win32 API WriteFile-anropet, exakt hur SQL Server ser aktiviteten, erhölls en lyckad returkod. SQL Server eller någon process som använder WriteFile API-anropet kan bara avgöra att data har fått stabil lagring på rätt sätt.

I diskussionssyfte förutsätter vi att alla sektorer på datasidan sorteras för att skriva före sektorerna för matchande loggposter. Detta bryter omedelbart mot WAL-protokollet. Cachen skriver en datasida före loggposterna. Om cacheminnet inte är helt batteribaserat kan ett fel orsaka katastrofala resultat.

När du utvärderar optimala prestandafaktorer för en databasserver finns det många faktorer att tänka på. Det viktigaste av dessa är"Tillåter mitt system giltiga FILE_FLAG_WRITE_THROUGH funktioner?"

Kommentar

Alla cacheminnen som du använder måste ha fullt stöd för en batteribaserad lösning. Alla andra cachelagringsmekanismer är benägna att skada data och förlora data. SQL Server gör allt för att säkerställa WAL genom att aktivera FILE_FLAG_WRITE_THROUGH.

Testningen har visat att många diskenhetskonfigurationer kan innehålla skrivcachelagring utan lämplig batterisäkerhetskopia. SCSI-, IDE- och EIDE-enheter drar full nytta av skrivcacheminnen. Mer information om hur SSD:er fungerar tillsammans med SQL Server finns i följande CSS SQL Server Engineers-bloggartikel:

SQL Server och SSD – RDORR:s utbildningsanteckningar – del 1

I många konfigurationer är det enda sättet att korrekt inaktivera skrivcachelagringen för en IDE- eller EIDE-enhet genom att använda ett specifikt tillverkaresverktyg eller genom att använda byglar som finns på själva enheten. Kontakta enhetstillverkaren för att se till att skrivcachen är inaktiverad för själva enheten.

SCSI-enheter har också skrivcacheminnen. Dessa cacheminnen kan dock ofta inaktiveras av operativsystemet. Om det finns någon fråga kontaktar du enhetstillverkaren för lämpliga verktyg.

Skriv cachestapling

Stackning av skrivcachen liknar sektorordning. Följande definition togs direkt från en ledande IDE-enhetstillverkares webbplats:

Normalt är det här läget aktivt. Skrivcacheläget accepterar värdens skrivdata till bufferten tills bufferten är full eller värdöverföringen är klar.

En diskskrivningsuppgift börjar lagra värddata på disken. Värdskrivningskommandon fortsätter att accepteras och data överförs till bufferten tills antingen skrivkommandostacken är full eller databufferten är full. Enheten kan ändra ordning på skrivkommandon för att optimera enhetens dataflöde.

Automatisk omfördelning av skrivning (AWR)

En annan vanlig teknik som används för att skydda data är att identifiera dåliga sektorer under datamanipulering. Följande förklaring kommer från en ledande IDE-enhetstillverkares webbplats:

Den här funktionen är en del av skrivcachen och minskar risken för dataförlust under uppskjutna skrivåtgärder. Om ett diskfel uppstår under diskskrivningsprocessen stoppas diskaktiviteten och den misstänkta sektorn omallokeras till en pool med alternativa sektorer som finns i slutet av enheten. Efter omfördelningen fortsätter diskskrivningsaktiviteten tills den är klar.

Detta kan vara en kraftfull funktion om batterisäkerhetskopiering tillhandahålls för cacheminnet. Detta ger lämplig ändring vid omstart. Det är bättre att identifiera diskfelen, men datasäkerheten i WAL-protokollet kräver återigen att detta görs i realtid och inte på ett uppskjutet sätt. I WAL-parametrarna kan AWR-tekniken inte ta hänsyn till en situation där en loggskrivning misslyckas på grund av ett sektorfel men enheten är full. Databasmotorn måste omedelbart känna till felet så att transaktionen kan avbrytas korrekt, administratören kan aviseras och korrigera de åtgärder som vidtagits för att skydda data och åtgärda mediefel.

Datasäkerhet

Det finns flera försiktighetsåtgärder som en databasadministratör bör vidta för att säkerställa säkerheten för data.

• Det är alltid en bra idé att se till att din strategi för säkerhetskopiering räcker för att återställa efter ett oåterkalleligt fel. Lagring på annan plats och andra försiktighetsåtgärder är lämpliga.
• Testa databasåterställningsåtgärden i en sekundär databas eller testdatabas regelbundet.
• Kontrollera att alla cachelagringsenheter kan hantera alla felsituationer (strömavbrott, dåliga sektorer, dåliga enheter, systemfel, låsningar, strömtoppar och så vidare).
• Kontrollera att din cachelagringsenhet:
  • Har integrerad batterisäkerhetskopiering
  • Kan återissue skrivningar på power-up
  • Kan inaktiveras helt om det är nödvändigt
  • Hanterar felaktig ommappning av sektorn i realtid
• Aktivera identifiering av sönderrivna sidor. (Detta har liten effekt på prestanda.)
• Konfigurera RAID-enheter som möjliggör frekvent växling av en felaktig diskenhet, om det är möjligt.
• Använd nyare cachelagringsstyrenheter som gör att du kan lägga till mer diskutrymme utan att starta om operativsystemet. Detta kan vara en idealisk lösning.

Testa enheter

För att skydda dina data fullständigt bör du se till att all datacachelagring hanteras korrekt. I många situationer måste du inaktivera skrivcachelagring av diskenheten.

Kommentar

Kontrollera att en alternativ cachelagringsmekanism kan hantera flera typer av fel på rätt sätt.

Microsoft har utfört tester på flera SCSI- och IDE-enheter med hjälp SQLIOSim av verktyget. Det här verktyget simulerar tung asynkron läs-/skrivaktivitet till en simulerad dataenhet och loggenhet. Testprestandastatistik visar de genomsnittliga skrivåtgärderna per sekund mellan 50 och 70 för en enhet med inaktiverad skrivcachelagring och ett RPM-intervall mellan 5 200 och 7 200.

Mer information om verktyget finns SQLIOSim i följande artikel i Microsoft Knowledge Base:

Så här använder du SQLIOSim-verktyget för att simulera SQL Server-aktivitet på ett diskundersystem

Många datortillverkare beställer enheterna genom att ha skrivcachen inaktiverad. Testningen visar dock att så kanske inte alltid är fallet. Därför bör du alltid testa helt.

Dataenheter

I alla utom icke-loggade situationer kräver SQL Server endast att loggposterna rensas. När du utför icke-loggade åtgärder måste datasidorna också tömmas till stabil lagring. Det finns inga enskilda loggposter som återskapar åtgärderna vid ett fel.

Datasidorna kan vara kvar i cacheminnet tills LazyWriter- eller CheckPoint-processen rensar dem till stabil lagring. Om du använder WAL-protokollet för att se till att loggposterna lagras korrekt ser du till att återställningen kan återställa en datasida till ett känt tillstånd.

Detta innebär inte att det är lämpligt att placera datafiler på en cachelagrad enhet. När SQL Server tömer datasidorna till stabil lagring kan loggposterna trunkeras från transaktionsloggen. Om datasidorna lagras i flyktig cache går det att trunkera loggposter som skulle användas för att återställa en sida i händelse av ett fel. Kontrollera att både dina data- och loggenheter har plats för stabil lagring på rätt sätt.

Öka prestanda

Den första frågan som kan uppstå för dig är: "Jag har en IDE-enhet som cachelagra. Men när jag inaktiverade det blev min prestanda mindre än förväntat. Varför?"

Många av de IDE-enheter som testas av Microsoft körs med 5 200 RPM och SCSI-enheterna med 7 200 RPM. När du inaktiverar skrivcachelagring av IDE-enheten kan den mekaniska prestandan bli en faktor.

För att åtgärda prestandaskillnaden är metoden som ska följa tydlig: "Åtgärda transaktionsfrekvensen".

Många OLTP-system (Online Transaction Processing) kräver en hög transaktionshastighet. För dessa system bör du överväga att använda en cachelagringsstyrenhet som har rätt stöd för en skrivcache och ger önskad prestandaökning samtidigt som dataintegriteten säkerställs.

För att observera betydande prestandaändringar som inträffar i SQL Server på en cachelagringsenhet ökades transaktionsfrekvensen med hjälp av små transaktioner.

Testning visar att hög skrivaktivitet för buffertar som är mindre än 512 KB eller större än 2 MB kan orsaka långsamma prestanda.

Ta följande som exempel:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Följande är exempeltestresultat för SQL Server:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

Processen att omsluta hela serien med INSERT åtgärder i en enda transaktion körs på ungefär fyra sekunder i alla konfigurationer. Detta beror på antalet loggvallningar som krävs. Om du inte skapar en enda transaktion bearbetas varje INSERT transaktion som en separat transaktion. Därför måste alla loggposter för transaktionen rensas. Varje tömning är 512 byte i storlek. Detta kräver betydande mekaniska ingrepp.

När en enskild transaktion används kan loggposterna för transaktionen paketeras och en enda större skrivning kan användas för att rensa de insamlade loggposterna. Detta minskar avsevärt den mekaniska interventionen.

Varning

Vi rekommenderar att du inte ökar transaktionsomfånget. Långvariga transaktioner kan orsaka överdriven och oönskad blockering och ökade omkostnader. Använd SQL Server:Databases SQL Server-prestandaräknare för att visa transaktionsloggbaserade räknare. Mer specifikt kan loggbyte tömda/sek indikera många små transaktioner som kan orsaka hög mekanisk diskaktivitet.

Granska de instruktioner som är associerade med loggens tömning för att avgöra om värdet För tömda loggbyte/sek kan minskas. I föregående exempel användes en enda transaktion. I många scenarier kan detta dock orsaka oönskade låsbeteenden. Granska transaktionens utformning. Du kan använda kod som liknar följande kod för att köra batchar för att minska den frekventa och små loggspolningsaktiviteten:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server kräver att systemen stöder garanterad leverans till stabila medier, enligt beskrivningen i nedladdningsdokumentet för GRANSKNINGskrav för SQL Server I/O-tillförlitlighetsprogram. Mer information om indata- och utdatakraven för SQL Server-databasmotorn finns i Indata-/utdatakrav för Microsoft SQL Server Database Engine.