Optimera med File och Filegroups
Förord
Optimera din SQL Servers prestanda med hjälp av File och FileGroup
Innehåll
»Optimera din SQL Servers prestanda med hjälp av File och FileGroup
av Alexander Chigrik
Det finns inga liknande strategier eller segment i SQL Server 7.0 och SQL Server 2000 av dem som fanns i SQL Server 6.5. Nu för tiden går databaserna efter filer från operativsystemet istället. Det finns tre olika typer av sådana filer:
- Primära
- Sekundära och
- Loggfiler
Varje databas innefattar åtminstone två filer; den ena är en primär datafil (med filtillägget .mdf som standard) och den andra är en loggfil (med filtillägget .ldf som standard). Du kan också välja att lägga till en sekundär datafil (med filtillägget .ndf som standard).
En databas kan bara ha en primär datafil, noll eller inga sekundära datafiler, samt en eller flera loggfiler. Så det finns inte längre någon möjlighet (såsom det fanns i SQL Server 6.5) att lagra dina databaser med dess loggfiler i samma figur (alltså i samma fil, med filtillägget .dat).
Datafilerna (.mdf och .ndf) är kombinerade in i FileGroups. En filgrupp är bara en samling av en eller flera databasfiler. Varje databasfil kan endast vara medlem i en filgrupp. Loggfiler däremot, är inte medlemmar i någon filgrupp, men kan behandlas separat.
Det finns tre typer av filgrupper:
1) Primära
2) Användardefinierade och
3) Standard
Varje databas har endast en primär filgrupp, endast en standardfilgrupp samt noll eller flera användardefinierade filgrupper. Om du inte anger att du vill ha användardefinierade filgrupper, så kommer det endast att finnas en primär filgrupp – som också får fungera som standardfilgrupp. Den primära filgruppen innefattar den primära datafilen, som innehåller alla systemobjekt (systemtabeller, systemets lagrade procedurer, utökade lagrade procedurer, osv). Du kan inte ta bort systemobjekt från den primära filgruppen, men du kan skapa användarobjekt i den användardefinierade filgruppen, avsedda för allokering, prestanda och administrationssyften.
Du skapar en användardefinierad filgrupp genom att använda CREATE DATABASE- eller ALTER DATABASE- uttrycket tillsammans med nyckelordet FILEGROUP. Standardfilgruppen är den filgrupp där alla nya användarobjekt kommer att skapas. Du kan ändra standardfilgruppen (från den Primära filgruppen) genom att använda ALTER DATABASE uttrycket tillsammans med nyckelordet DEFAULT.
Man kan konfigurera en SQL Servers databasfil till att växa eller krympa automatiskt, för att reducera behovet av handhavande från en aktiv databas och därmed eliminera dem problem som kan uppstå då loggfiler – eller databasfiler – inte längre har någon plats att fylla. Autogrow-funktionen är på som standard i alla versioner av SQL Server 7.0 och 2000, men Autoshrink-funktionen är på som standard endast i Desktop Edition.
När du skapar en databas så måste du tilldela både data- och loggfilerna en grundstorlek. Om du vill att databasfilerna ska växa automatiskt, så måste du specificera Autogrow tillskottet i kilobytes, megabytes, terabytes eller procent. Standarden är megabytes. Du kan också specificera en maximal filstorlek, för att förhindra att hårddiskarna får slut på plats.
Tips för hur du optimerar filgrupper
Om du vill optimera din SQL Servers prestanda bör du överväga följande:Tilldela din databas en rimlig storlek
Först av allt, innan databasen skapas, bör du uppskatta ungefär hur stor din databas kommer att bli. För att uppskatta en rimlig storlek på databasen så kan du uppskatta storleken för varje individuell tabell, och sedan summera de värden du får.
Tilldela din transaktionslogg en rimlig storlek
En generell tumregel då du ska sätta storleken på din transaktionslogg, är att ge den 20-25 % av storleken på din databas. Ju mindre din databas blir, desto större bör transaktionsloggen bli och vice versa. Om t ex din databasstorlek är uppskattad till 10 MB, så kan du sätta transaktionsloggen till 4-5 MB. Men om din databas beräknas bli över 500 MB, så räcker det med 50 MB för transaktionsloggen.
Låt Autogrow-funktionen vara på för datafiler och loggfiler
Genom att lämna den här funktionen på, så låter du SQL Server att (när det behövs) automatiskt öka antalet tilldelade resurser, utan att någon DBA behöver lägga sig i. Autogrow-funktionen är användbar då det inte finns någon DBA att tillgå i din firma, eller om din DBA inte är erfaren nog för att hantera det själv.
Tilldela Autogrow tillskottet ett rimligt värde
Att låta en databas växa automatiskt kan resultera i en försämrad prestanda, så det är viktigt att du sätter tillskottet till ett rimligt värde så att den inte växer automatiskt för ofta. Försök att sätta en grundstorlek för databasen och en grundstorlek för Autgrow tillskottet, så att den automatiska tillväxten sker en gång veckan eller mindre.
Initiera inte Autoshrink-funktionen
Att använda Autoshrink-funktionen resulterar i en försämrad prestanda. Därför bör du hellre förminska din databas manuellt eller skapa en schemalagd uppgift som förminskar databasen periodiskt, t ex under off-peak tillfällen, hellre än att sätta igång Autoshrink-funktionen.
Tilldela data- och loggfiler en maximal storlek
Specificera hur stora filerna maximalt får blir för att förhindra att hårddiskarna får slut på plats.
Skapa en användardefinierad filgrupp och gör den till standardfilgrupp
Det kan i många fall vara ett bra val att både lagra och hantera system- och användarobjekten separat från varandra, för att förhindra en konkurrens om plats i den primära filgruppen. Vanligtvis så skapas inte en användardefinierad filgrupp till små databaser (t ex mindre än 100 MB).
Skapa en användardefinierad filgrupp, och skapa sedan tabeller i den, som utför uppehållen (backups, DBCC, uppdateringsstatistik, osv) mot dessa tabeller.
SQL Server 7.0 (och högre) stöder inte längre LOAD TABLE och DUMP TABLE, men du kan placera en tabell i dess egen filgrupp för att sedan kunna köra backup samt återskapa tabellen därifrån. Du kan alltså samla ihop en grupp användarobjekt som delar samma uppehållskrav, till en egen filgrupp.
Om du har flera fysiska diskarrayer, så försök att skapa en fil för varje diskarray. Det kommer att höja prestandan, därför att när man försöker komma åt en tabell under upprepade tillfällen så skapas en separat länk för varje fil och för varje diskarray, för att kunna läsa tabellens data parallellt.
Skapa inte flera data- och loggfiler på en och samma diskarray.
Genom att låta Autogrow-funktionen vara på för data- och loggfiler, och om det finns flera sådana filer i en och samma diskarray, så kan ske en fragmentering av de filerna. I flesta fall så räcker det med att ha en eller två databasfiler på en och samma fysisk disk.
Om tabeller upplever en hög access, så kan man placera tabellerna inom en filgrupp och tabellernas Index inom en annan filgrupp på en annan fysisk diskarray. Det kommer att höja prestandan eftersom det kommer skapas separata länkar för att kunna komma åt tabellerna och Indexen.
Om tabeller med en text/bildkolumn upplever en hög access, så kan man placera tabellerna inom en filgrupp och tabellernas text/bildkolumner inom en annan filgrupp på en annan fysisk disk. För att placera text/bildkolumnen i en annan filgrupp så kan du använda CREATE TABLE uttrycket tillsammans med nyckelordet TEXTIMAGE_ON. Se SQL Server BOL för detaljer.
Du kan också placera loggfilerna på en annan fysisk diskarray än den som datafilerna finns på. Eftersom loggningen är mer skrivkrävande, så är det viktigt att den fysiska diskarray som SQL Serverns loggfiler ligger på, har tillräckligt med disk I/O prestanda.
Om någon av dina JOIN satser används mer än någon annan, så kan du placera de tabeller som används i den satsen i olika filgrupper på olika fysiska diskarrayer.
Om du har några tabeller som är read-only, så kan du placera även dem i olika filgrupper på olika fysiska diskarrayer. Använd sedan ALTER TABLE uttrycket för att göra just dem filgrupperna till READ ONLY. Det här höjer inte bara läsprestandan, utan det både förhindrar tabellen ytterligare från att ändras och låter dig kontrollera tillstånden till alla data.
Du kan genom att titta på Disk Queue Length countern i Windows NTs Performance Monitor, fastställa ett lämpligt antal data- och loggfiler som du bör ha på Servern. Om din Disk Queue Length visar på ett genomsnitt över 3, så bör du överväga att reducera antalet filer och filgrupper som du har till dina databaser. Och när du väl har gjort dina justeringar, så bör du fortsätta att övervaka, för att försäkra dig om att din disk I/O är optimal i det långa hela.
0 Kommentarer