Inuti SQL Server Maintenance Plans
Förord
Som så många andra databasadministratörer som hanterar många SQL Server databaser så har jag ibland problem med underhåll- och administrationsuppgifterna. Några av dessa uppgifter kräver massivt arbete, vilka måste skapas och underhållas för att försäkra sig om att databasen är robust och trovärdig. Ett av de verktyg jag ofta använder för att göra livet lite lättare är Database Maintenance Wizard, vilken används till att skapa SQL Server underhållsplaner.
Inuti SQL Server Maintenance Plans
av Andrzej Kozlowski
Trots att Database Maintenance Wizard, samt de planer den skapar, generellt sett är bra så är de inte alltid perfekta. Dessutom så är det inte så uppenbart vad som sker bakom kulisserna, då en underhållsplan körs. Så nyligen valde jag att ta reda på vad som händer bakom kulisserna, med hjälp av SQL Server Profiler.
För att fånga in min Profilertrace (i SQL Server 2000) så använde jag den fördefinierade Profiler templaten som kallas SQLProfilerStandard, med två små förändringar. Jag använde
- RPC: Starting
- SQL: BatchStarting
istället för
- RPC: Completed
- SQL: BatchCompleted
Alla andra tracekonfigurationer lämnade jag som de var. Dessa två ändringar i SQLProfilerStandard låter oss på ett enkelt sätt fånga in de relevanta tracedata.
Se över integritetsinställningarna för underhållsplanen
Sen skapade jag en underhållsplan med hjälp av integritetsalternativen (se figuren som referenspunkt), för att kunna se vilka SQL-uttryck som användes.
Därefter startade jag Profiler Trace och körde det jobb som underhållsplanen skapade, för att se vilka resultat som skulle uppstå. Sen gick jag tillbaka och gjorde några ändringar av de inställningar som finns på bilden ovan, för att se vad som skulle hända.
Nedan ser du resultaten i en tabell, för att du lättare ska kunna se.
Kommentarer på den här testkörningen:
1. Använd inte inställningen “Attempt to repair any minor problems”, eftersom det skulle orsaka att DBCC Checkdb skulle köras med inställningen REPAIR_FAST. Den här inställningen utför mindre reparationer utan att riskera att data faller bort. Men den givna databasen måste befinna sig i en ”single-user” mode för att kunna använda det här värdet, vilket kan orsaka problem om det är en produktionsdatabas.
2. Om inte databasen befinner sig i en ”single-user” mode så kommer inte DBCC Checkdb med REPAIR_FAST att köras. Tyvärr så kommer ändå text- eller HTML rapportfilerna att rapportera en lyckad operation.
3. Genom att kryssa i ”Perform these tests before backing up the database or transaction log” så kommer SQL Server att modifiera Backup-jobben (full- och loggbackup), genom att lägga till två inställningar till underhållsplanen:
BkUpOnlyIfClean
CkDB | CkDBNoIdx | CkDBRepair
4. Använd inte ovanstående inställning om du planerar att lägga till transaktionsloggbackups till samma underhållsplan, eftersom det kommer att skapa DBCC aktivitet innan varje påföljande backup, vilket skadar prestanda.
5. ”WITH NO_INFOMSGS” används för att hålla tillbaka alla informationsmeddelanden.
Se över optimeringsinställningarna för underhållsplanen
I det här steget så vill jag se vad som händer under täckmanteln då olika optimeringsinställningar (se illustrationen nedan) från Database Maintenance Wizard söks med Profilern. Precis som förut så kommer jag först att experimentera med olika inställningar, och sen köra resultaten manuellt för att fånga in mina traces. 
Du kan se resultaten i tabellen nedan.
Kommentarer på den här testkörningen:
1. DBCC DBREINDEX använder det fria utrymmet i motsatt förhållande till fyllfaktorn. Så i den första posten i ovanstående tabell har vi ett fritt utrymme på 0 %, vilket är detsamma som 0 i fyllfaktor. På den andra platsen ser vi ett värde på 30 % fritt utrymme, vilket är detsamma som en fyllfaktor på 70 %.
2. Om du har valt DBREINDEX för en tabell så sker UPDATE STATISTICS automatiskt.
3. Trots att kommandot UPDATE STATISTICS ger oss möjligheten att välja antingen ett SAMPLE alternativ av PERCENT eller antalet ROWS, så låter underhållsplanen dig att endast specificera PERCENT.
Se över andra inställningar för underhållsplanen
Jag tänker inte beskriva Database Wizardens databas- och loggbackupinställningar, för dem är väldigt rakt på och fungerar såsom du förväntar dig att de ska. Jag skulle däremot vilja förklara några av underhållsplanens andra inställningar, vilka du endast kan komma åt genom xp_sqlmaint verktyget, och inte från Maintenance Plan Wizard.- HtmlRpt "e:\mssql7\LOG\ak_test.html": Skapar en HTML version av databasens underhållsrapport.
- DelHtmlRpt Skapar en underhållscykel för HTML filer, t ex 2 WEEKS.
- CkCat: Kör DBCC CHECKCATALOG
- CkAl, CkAlNoIdx: Kör DBCC NEWALLOC
- CkTxtAl: Kör DBCC TEXTALL
Kommentarer om dessa kommandon:
1. Var försiktig med syntaxen på ovanstående switchar, de måste skrivas exakt såsom jag har listat dem ovan. Om du t ex skriver Htmlrpt (istället för HtmlRpt) så kommer arbetet att fallera.
2. Använd inte NEWALLOC och TEXTALL, för de är bara inkluderade för bakåtkompabilitet. CHECKALLOC bytte ut NEWALLOC och CHECKTABLE TEXTALL. Det är bättre att använda CHECKDB, vilken utför en CHECKTABLE för varje tabell i databasen för att se över integriteten av alla data, och CHECKALLOC för att se över allokeringen av alla sidor.
Tabellerna för underhållsplanen
Nu är det dags för några detaljer om hur underhållsplanerna är lagrade inuti SQL Server. Ju mer du vet om det här, desto lättare blir det för dig att automatisera insamlingen av användbar information om dina underhållsplaner, om du vill det.Det finns fyra tabeller i msdb databasen som lagrar information om underhållsplanerna.
sysdbmaintplan_databases: Innehåller en post från varje tabell. Notera att du kan använda speciella strängar för grupper av tabeller, som i Database Maintenance Plan Wizard.
Sampledata från sysdbmaintplan_databases:
sysdbmaintplans: Innehåller en post från varje underhållsplan.
Sampledata från sysdbmaintplans (ej hela tabellen):
sysdbmaintplan_history: Underhåller historieinformationen om varje underhållsplan.
sysdbmaintplan_jobs: Underhåller relationen mellan underhållsplanernas ID och arbetets IDt.
Kommentarer om dessa systemtabeller:
Notera att det endast finns en fördefinierad underhållsplan vid namn ”All ad-hoc plans” med en udda ägare vid namn ”REDMOND\_sqlbld”. Den används till alla ad-hoc arbeten med användning av xp_sqlmaint verktyget. Max_history_rows för den här tabellen är satt till 0, men det finns historieentries för det i sysdbmaintplan_history tabellen för det.
För att demonstrera hur dessa ”speciella” entries fungerar så har jag skapat ett vanligt arbete med följande kommando:
EXECUTE master.dbo.xp_sqlmaint N'-D WWW_ak -Rpt "e:\mssql7\LOG\www_ak.txt" -WriteHistory -CkDB '
Efter att ha kört den två gånger så får vi en historietabell som den nedan:
Skapa en personlig underhållsplan
Som du kan se så låter inte SQL Server Database Maintenance Plan Wizard oss inkludera alla potentiella och användbara inställningar (HTML rapporter, några DBCC grejer). För att komma runt det så föreslår jag att du följer en av de två följande alternativen när du skapar en egen underhållsplan.1. Skapa varje ny plan med hjälp av Wizarden, och modifiera sen varje arbete manuellt för de alternativ som inte finns tillgängliga från Wizarden.
2. Undvik Wizarden helt och hållet, och skapa alla arbeten manuellt med hjälp av xp_sqlmaint verktyget.
Jag rekommenderar följande strategi för SQL Serverns underhållsarbeten:
1. Skapa arbeten (ad-hoc eller med Wizarden) för total backup av dina databaser med DBCC CHECKDB och CHECKCATALOG, och ställ in inställningen att utföra backupen endast om de inte rapporterade några Error. Skapa det här arbetet för alla de databaser som inte kräver transaktionsloggbackups.
2. Skapa separata arbeten för endast transaktionsloggbackups till alla databaser som behöver det.
3. Skapa separata jobb för dataoptimeringar, beroende på dina krav.
4. Lägg till HTML rapportalternativet till alla dina jobb, och dirigera dem till en gemensam punkt för en enkel access med en browser.
0 Kommentarer