Utför en prestandaanalys av din SQL Server #6
Förord
Innehåll
»»
»
»
»
»
»
»
»
»
»
»
»
Checklista för prestanda i SQL Serverns databaskonfigurationer
av Brad M. McGee
Checklista för prestandaanalys
Skriv in dina resultat i tabellen ovan.
Var och en av databaserna måste analyseras
Som en del i din analys så måste du granska var och en av de databaser som är lokaliserade på din Server, och du måste se över några av de grundläggande inställningarna för databaserna. Jämfört med de andra prestandaanalyseringarna som du har gjort, så kommer du att märka att den här är den lättaste. För bekvämlighets skull så bör du skriva ut en kopia av ovanstående tabell för var och en av de databaser som du ska analysera. I din analysering av databasinställningarna så ska vi titta på två olika sorters inställningar; databasalternativ samt databasens konfigurationsinställningar. Och precis som i de tidigare analyseringarna så ska vi bara fokusera oss på de inställningarna som är prestandarelaterade, och ignorera resten.
Både databasalternativen och databasens konfigurationsinställningar kan ses över och modifieras med hjälp av Enterprise Manager (vilket jag föredrar eftersom det är enklast), eller enbart modifieras med kommandot ALTER DATABASE. Du kan även se över och modifiera databasalternativen med systemets lagrade procedur sp_dboption, men Microsoft försöker få bort den proceduren (som i SQL Server 2000).
Vi kommer i det första avsnittet i vår prestandachecklista för databasens inställningar att fokusera oss på databasalternativen, innan vi tar oss an databasens konfigurationsinställningar.
Se över databasalternativen
I det här avsnittet ska vi ta en titt på sex av de många databasalternativ som finns, och det är dem som på ett eller annat sätt kan påverka databasens prestanda. Den bästa metoden för att se över dessa inställningar är genom att gå igenom följande steg i Enterprise Manager (förutsatt att du använder SQL Server 2000):- I Enterprise Manager så visar du alla databaser som finns i din Server.
- Högerklicka på den databas som du vill analysera och välj ”Properties”.
- I dialogboxen som kommer upp så klickar du på fliken ”Options”.
Härifrån kan du se alla relevanta databasalternativ. Notera att du inte kan se precis alla av alternativen, men de som vi är intresserade av finns här. Låt oss nu ta en titt på de alternativ som är prestandarelaterade, och hur de påverkar SQL Serverns prestanda.
Auto_close
Det här alternativet är designat för att användas med Desktop versionen av SQL Server 7.0 och 2000, inte för Server versionerna. På grund av det så bör alternativet inte vara på (vilket det är som standard). Vad det här alternativet gör är att den stänger ner databasen efter att den sista användaren har kopplat från. När sedan en ny anslutningsförfrågan kommer om access mot databasen så måste den återöppnas, vilket kräver både tid och arbetslast.Problemet med det här är att om databasen accessas frekvent så kommer den att vara tvungen att öppnas och stängas ofta, vilket drar ner på prestandan på både SQL Servern samt på applikationerna eller användarna som ansluter till databasen.
Om du i din analys ser att det här databasalternativet är på, och om det inte är Desktop versionen, så måste du ta reda på varför det har aktiverats. Om du inte får svar eller om svaret inte var tillfredsställande nog, så bör du stänga av alternativet.
Auto_Create_Statistics
När auto_create_statistics är aktiverad (vilket den är som standard), så kommer den automatiskt att skapa statistik på de kolumner som används i WHERE klausulen i en SQL-sats. Det sker när en SQL-sats blir optimerad av Query Optimizer för första gången (förutsatt att det inte redan har skapats statistik på kolumnen). Dessutom så kan kolumnstatistiken hjälpa Query Optimizer att generera en så optimal exekveringsplan för SQL-satsen som möjligt.Om inte det här alternativet är aktiverat så skapas inte kolumnstatistiken automatiskt. Det innebär att Query Optimizer inte kommer att kunna generera den optimala exekveringsplanen, och SQL-satsens prestanda kan då ta skada. Men trots att alternativet kanske inte är aktiverat, så kan du fortfarande skapa statistiken manuellt.
Det finns ingen egentlig nackdel med att använda det här alternativet. Den första gången som en SQL-sats ska exekveras så kan det dock ta lite längre tid, eftersom kolumnstatistiken skapas precis innan SQL-satsen körs för första gången. Men när statistiken väl har skapats så kommer varje SQL-sats att hädanefter köra effektivare än om inte kolumnstatistiken hade funnits där i första taget.
Om du i din analys finner att alternativet är avstängt, så bör du ta reda på varför man har stängt av det. Om du inte kan få reda på varför, eller om svaret inte är tillfredsställande, så bör du aktivera alternativet igen.
Auto_Update_Statistics
För att din Query Optimizer ska kunna förses med de senast uppdaterade statistikerna så att den kan göra de smartaste optimeringarna för dina satser, så bör du låta auto_update_statistics vara på (vilket den är som standard). På så sätt försäkrar du dig om att Optimizerns statistik är giltig, samt att SQL-satserna alltid är optimerade varje gång de körs. Men det här alternativet är inte någon lösning. När en SQL Server är väldigt högt belastad så kan det bli att auto_update_statistics funktionen uppdaterar statistiken på stora tabeller väldigt olägligt, som t ex under den mest sysselsatta tidpunkten på dagen.
Om du märker att funktionen körs vid olämpliga tidpunkter, så bör du överväga att stänga av den. Du kan istället uppdatera statistiken manuellt (med hjälp av kommandot UPDATE STATISTICS) vid tillfällen då databasen inte är fullt lika sysselsatt.
Men om du väljer att stänga av funktionen så finns det något du bör tänka på. Trots att du genom att stänga av funktionen kan reducera Serverns arbetslast (eftersom funktionen inte kommer att köras under fel tidpunkter på dagen), så kan den istället orsaka att SQL-satserna inte blir ordentligt optimerade – vilket också kan skapa arbetslast under stressade tidpunkter.
Precis som med andra optimeringsfrågor så måste du experimentera dig fram för att veta vilket alternativ som är mest effektiv i just din situation. En tumregel är dock att om inte din Server är helt slutkörd, så bör du låta det här alternativet vara.
Auto_Shrink
Många databaser måste regelbundet förminskas för att rensa bort de lediga utrymmen som blir då gamla data raderas från databaserna. Men frestas inte till att använda dig av auto_shrink funktionen till det, eftersom den slösar onödigt mycket på SQL Serverns resurser.Som standard så är auto_shrink funktionen avstängd, vilket innebär att det enda sättet att rensa bort lediga utrymmen i en databas på är genom att göra det manuellt. Om du aktiverar den, så kommer SQL Server att kontrollera varje 30 minuter för att se om databasen behöver minskas. Det här tar inte bara upp resurser som skulle kunna göra bättre nytta någon annanstans, utan det kan också skapa otrevliga flaskhalsar då auto_shrink processen kickar in och gör sitt jobb.
Om du vill minska databasen regelbundet, så kan du göra det manuellt genom att använda något av kommandona DBCC SHRINKDATABASE eller DBCC SHRINKFILE. Du kan också göra det genom att använda dig av antingen SQL Server Agent, eller genom att skapa en Database Maintenance Plan för att schemalägga dina förminskningar av filerna.
Om du i din analys finner att alternativet är aktiverat, så bör du ta reda på varför man har aktiverat den. Om du inte kan få svar, eller om svaret inte är tillfredsställande, så bör du stänga av funktionen.
Read_Only
Om din databas enbart kommer att läsas ifrån (som t ex vid rapporteringar), så bör du aktivera den här funktionen (den är avstängd som standard). På så sätt kommer du att eliminera den arbetslast som blir vid låsningar, vilket leder till högre prestanda för de SQL-satser som kommer att köras mot databasen. Om du måste göra ändringar i databasen så kan du stänga av funktionen, göra dina ändringar, och sedan aktivera funktionen igen.
Torn_Page_Detection
Eftersom datasidorna i SQL Server (8k) och NT Server eller Windows 2000 Server (512 bytes) är olika stora, så kan det bli så att dina databaser förstörs vid eventuella strömproblem, eller vid problem med hårddiskar eller fysiska diskar. Det beror på följande. Varje gång som operativsystemet skriver en 8k SQL Server datasida till disken, så måste den dela upp datan till flera 512 bytes sidor. Efter att de första 512 bytes av data har skrivits, så räknar SQL Servern med att alla 8k data har skrivits ut till disken framgångsrikt. Så om strömmen skulle försvinna innan alla 512 bytes sidor som 8k sidan består av har skrivits klart, så vet inte SQL Server vad som har hänt. Det här är känt som en ”torn page” (splittrad sida).
Som du kan förstå så förstörs datasidan, och därmed förstörs hela databasen. Det finns inget sätt att återskapa en databas som har förstörts på grund av en ”torn page”, förutom att ha en bra backup. Ett av de bästa sätten att förhindra det här på är genom att försäkra dig om att din Server har batteribackup. Men det förhindrar inte alla problem, eftersom defekta hårddiskar kan skapa liknande problem (jag har sett det själv).
Om du är orolig för att få ”torn pages” i din SQL Server databas, så kan du få SQL Server till att meddela dig om det skulle inträffa. Databasalternativet torn_page_detection kan sättas på och stängas av på databasnivå. Varje databas måste hållas separat. Kom ihåg att det här alternativet inte förhindrar torn pages, det bara meddelar om du skulle ha någon. Och när den väl har hittat en så markeras din databas som förstörd, och du har då inget annat val än att återskapa din databas med den senaste backupen.
I SQL Server 7.0 så är funktionen som standard avstängd, och du måste aktivera den själv för varje databas som du vill ha funktionen aktiverad på. I SQL Server 2000 så är funktionen som standard aktiverad för alla databaser.
Så vad är problemet? Varför inte bara aktivera den och sen känna dig säker? Jo, problemet är att du skadar SQL Serverns prestanda genom att ha den här funktionen aktiverad. Inte särskilt mycket i och för sig, men om din SQL Server redan är helt slutkörd så kan det bli en betydlig skillnad. Som en DBA så måste du väga för- och nackdelarna mot varandra, och sen välja det bästa alternativet för din situation.
Se över databasens konfigurationsinställningar
I det här avsnittet ska vi ta en titt på tre av databasens konfigurationsinställningar, och se hur dem kan påverka prestandan. Det bästa sättet att se över dessa inställningar på är genom att gå igenom följande steg i Enterprise Manager (förutsatt att du har SQL Server 2000):- I Enterprise Manager visar du alla databaser i din Server.
- Högerklicka på den databas som du vill analysera och välj ”Properties”.
- Från dialogboxen för Properties klickar du på ”Options” fliken för att se kompatibilitetsnivån, ”Data files” fliken för att se databasens ”auto grow” inställningar samt ”Transaction Log” fliken för att se transaktionsloggarnas ”auto grow” inställningar.
Låt oss ta en titt på var och en av de tre relevanta konfigurationsinställningarna för databasen:
Compatibility Level
SQL Server 7.0 och 2000 har ett kompatibilitetsläge som låter andra applikationer (som har skrivits under tidigare versioner av SQL Server) att köras under SQL Server 7.0 och 2000. Om du vill ha maximal prestanda för din SQL Server, så bör du inte låta din databas köra i kompatibilitetsläget (det är inte alla prestanda-relaterade funktioner som stöds).Du bör istället köra dina applikationer i ett SQL Server 7.0 eller 2000 läge (beroende på vilken av versionerna du kör för tillfället). Det innebär att du självklart måste modifiera dina applikationer för att de ska bli SQL Server 7.0- eller 2000-kompatibla. Men det arbete som det kräver kommer att betala sig i bättre prestanda.
Kompatibilitetsnivån för SQL Server 7.0 är refererat som ”70”, medan kompatibilitetsnivån för SQL Server 2000 är refererat som ”80”.
Databasens och transaktionsloggens Auto Grow
Vi kommer att diskutera dessa två funktioner under samma avsnitt, eftersom de är så nära relaterade till varandra.Om du ställer in dina SQL Server- databaser och transaktionsloggar till att växa automatiskt (vilket är standardinställningen), så måste du komma ihåg att varje gång som den här funktionen går igång, så kommer den att kräva lite extra CPU och I/O tid. Det idealiska vore att minimera antalet gånger som detta sker. Ett sätt att göra detta på är genom att ställa in databasernas och transaktionsloggarna aktuella storlekar, till en storlek som ligger nära den ”slutgiltiga” storleken. Visst, det kan vara ganska omöjligt att gissa sig fram exakt rätt storlek, men ju närmare du kan uppskatta den slutgiltiga storleken (och du kommer att få prova dig fram länge innan du lyckas uppskatta någorlunda korrekt), desto färre gånger behöver SQL Server förstora sina databaser och transaktionsloggar automatiskt – vilket leder till bättre prestanda för applikationerna.
Den här rekommendationen är särskilt viktig att följa för dina transaktionsloggar. Det beror på att ju oftare som SQL Server måste expandera dina transaktionsloggar, desto fler virtuella transaktionsloggfiler måste skapas och uppehållas av SQL Server. Det kommer i sin tur att förlänga återskapningstiden ifall din transaktionslogg måste återskapas. En virtuell transaktionsloggfil används av SQL Server för att internt dela upp och hantera den fysiska transaktionsloggfilen.
Standardökningen för databaserna och transaktionsloggarna ligger på 10 %. Detta automatiska expansionsvärde kan, men behöver inte, vara det ideala värdet för just din databas. Om du tycker att din databas expanderar automatiskt väldigt ofta (såsom varje dag, eller flera dagar i veckan), så bör du överväga att höja detta procentuella expansionsvärde till exempelvis 20 % eller 30 %. Varje gång som databasen måste expandera, så kommer SQL Serverns prestanda att påverkas negativt. Men genom att öka den procentuella mängden som databasen expanderar varje gång, så behöver den inte göra det lika många gånger.
Om din databas är väldigt stor, 10 GB eller större, så kan du överväga att tillsätta ett fixerat expansionsvärde istället för ett procentuellt expansionsvärde. Det beror på att den procentuella expansionen kan bli väldigt stor på stora databaser. Om du t ex har ett 10 % expansionsvärde för en databas som är 10 GB stor, så innebär det att den kommer att växa med 1 GB. Det skulle kunna vara så du vill ha det, eller så är det inte det. Om det är för mycket för dig, så kan du istället sätta expansionsvärdet till ett lämpligare fixerat värde, som t ex 100 MB.
Som en del i din analys så bör du göra en noggrann undersökning av dina databaser, för att se hur ovanstående råd passar in på dem, sen måste du ta till de nödvändiga åtgärder som krävs.
Och nu då?
Vårt mål är att utföra den del av prestandaanalyseringen som vi har diskuterat i den här artikeln, på var och en av databaserna i din SQL Server, och sedan använda den informationen du får ut till att göra eventuella korrigeringar – om du kan.När du har klarat av den här delen i analyseringen, så är du nu redo för att analysera användandet av Index i dina databaser.
0 Kommentarer