Vikten av rätt inställningar på din databas – Autogrowth

Operationen att gå till disk och skriva/läsa är något som tar lite extra tid för SQL. Vi kommer alltid att behöva göra detta på något sätt, men genom att minska antalet operationer kan vi  bespara tid för användaren.

När databasen växer måste också datafilen växa. När stora batchar och transaktioner körs behöver även loggfilen växa. Har du databasen i FULL RECVOCERY MODEL samlas alla transaktioner i loggfilen till dess att en transaktionslogg-backup görs och dom härdas i datafilen. Det finns många scenarion där datafil och loggfil kan behöva växa på disk och därför är det viktigt att ha lämpliga inställningar på databasen för automatisk tillväxt, Autogrowth.

När du skapar en databas hämtas default-värden för dessa inställningar från Model-databasen. I dagsläget innebär default-inställningen att datafilen växer med 1 MB per gång och loggfilen med 10 %. I många lägen passar inte dessa förinställda värden. Här kommer några exempel:

• Har du en databas som samlar in mycket data och växer kontinuerligt blir det ganska många operationer på disk om den varje gång som databasen växer med 1 MB måste gå till disk och utöka datafilen.

• En loggfil som växer med 10 % får växa väldigt många gånger när den är liten, vilket bland annat är dåligt för antalet VLF:er (Virtual Log Files) och har en negativ påverkan på prestandan

• En stor datafil/loggfil som växer med 10 % kan göra att operationen att utöka loggfilen på disk tar väldigt lång tid, särskilt om man inte har Instant File Initialization aktiverat.

• En stor loggfil/datafil som växer med % kan göra att du inte hinner få varningar om att diskutrymmet håller på att ta slut
  Räkneexempel: Datadisken är 200 GB, Övervakningen är satt att varna när det är mindre än 10 % ledigt utrymme. Datafilen är satt att växa med 20 % (ja, det förekommer). Om databasen är 170 GB ser det ut som att det är lugnt med diskytan, men nästa gång databasen skall växa så skall den växa med 34 GB (20 % av 170) och det går inte för det finns inte tillräckligt med ledigt utrymme. Då uppstår problem med att applikationen stannar för den kan inte fortsätta skriva.

Det finns anledning att tänka sig för och kontrollera inställningarna för databaserna och att Instant File Initialization är aktiverat. Vet du hur stora filer som behövs är det allra bästa att sätta storleken direkt. Jag rekommenderar alltid mina kunder att titta på respektive databas och utifrån dessa sätta fasta värden på hur datafilen och loggfilen får växa. Hoppa över procent-inställningen och sätt ett lagom stort värde.

Här är ett bra script för att hämta information om inställningarna på dina databaser:

select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_setting]
, CASE mf.is_percent_growth
WHEN 1 THEN
CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN
CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf

Har du många instanser kan du med fördel använda dig av CMS och köra multi-queries:
http://www.sqlservice.se/kom-igang-med-central-management-server-cms/

Här kan du ladda ner vår guide med fem tips som förenklar din administration av SQL Server