Fine Tuning your Database Design in SQL 2005


En översikt av index-tuning verktyg Som en del av mitt jobb på arbetar som dba och SQL kunder över hela EU, från finansinstitut att onlineåterförsäljare till små och stora partner. Jag arbetar främst för att leda prestandajustering sin SQL Server-system 2005/2008.

När ett system har en prestanda problem, och tiden är kort (vilket det oundvikligen är), det är frestande att bara “döda den med järn” – lägga till mer minne, mer CPU hästkrafter, större och bättre delsystem disk och så vidare. Problemet är att om din design är synnerligen bristfälligt, så finns det en stor chans att din ansökan inte kommer att skala: fördubbla dina hästkrafter kommer inte dubbel prestanda. Du kan sluta kasta en massa hårdvara (och mycket pengar) på problemet för att nå dina resultatmål.

I stället bör ditt första anhalten vara med utformningen av ansökan, och förmodligen det “lägsta-hängande frukt” här – det alternativ som ger maximal vinst med minsta påverkan på befintliga system och processer – är att undersöka din indexering strategi.

Index Usage Report (nuvarande)
Börja med den här rapporten för att se hur din befintliga index som används av den mängd frågor som körs mot servern. Varje index som har använts av en förfrågan är här. Varje refererade index har ett värde Procent Användning som anger andelen uttalanden i din arbetsbörda som refererade till detta index.

Om ett index inte finns med här, betyder det att den inte har använts av alla frågor i din arbetsbörda.

TIPS: identifiera index som inte används
Om du är säker på att alla de frågor som körs mot din server har registrerats med ditt Profiler spår, då kan du använda den här rapporten för att identifiera index som inte behövs och eventuellt ta bort dem.

Index Usage Report (rekommenderas)
Titta sedan på denna rapport att kartlägga hur indexanvändning kommer att förändras om den rekommenderade index genomförs. Om man jämför dessa två rapporter kommer du att se att indexet användningen av vissa av de nuvarande index har fallit samtidigt som några nya index har tagits med en högre användning procent, vilket tyder på ett annorlunda utförande plan för din arbetsbörda och bättre prestanda.

Uttalande Kostnadsrapport
I rapporten räknas enskilda uttalanden i din arbetsbörda och det beräknade resultatet förbättras för var och en av dem. Med den här rapporten kan du identifiera dina dåliga resultat frågor och se slags förbättring du kan förvänta dig om du genomföra de rekommendationer som DTA.

Du kommer att tycka att vissa uppgifter inte har några förbättringar (Procent förbättring = 0). Detta beror på antingen ett uttalande inte var inställd av någon anledning eller om det redan har alla de index som den behöver för att prestera optimalt.

Genomförande DTA rekommendationer Nu har vi samlat en arbetsbelastning som använder Profiler, konsumeras det med hjälp av DTA och fick en rad rekommendationer för att förbättra prestanda. Du får då välja mellan att antingen:

• Spara rekommendationer – du kan spara rekommendationerna i en SQL-script genom att navigera ÅTGÄRDER | Spara rekommendationer. Du kan sedan manuellt köra skriptet i Management Studio för att skapa alla rekommenderade PDS.
• Applicera rekommendationer som gjorts på DTA – om du är nöjd med antal rekommendationer då enkelt navigera ÅTGÄRDER  tillämpa de rekommendationer. Du kan även schemalägga ett senare tillfälle att tillämpa dessa rekommendationer (under lågtrafik, till exempel)
Oftast använder jag Spara rekommendationer alternativet. Det betyder att jag lätt kan jämföra den resulterande PDS som fastställts för olika scenarier. Rekommendationen rapporterna är också användbara för framtida referens. Eftersom arbetsbelastningen på servern ändringarna måste du omvärdera indexanvändning och gör korrigeringar (drop överflödiga index eller skapa nya). Du kan jämföra indexanvändning på regelbunden basis (kan kvartalsvis eller så) och se hur användningen förändras.

Utföra vad händer om analyser med hjälp av DTA. Detta är en väldigt cool funktion i DTA.  Anta att du inte vill tillämpa alla de rekommendationer som DTA tillhandahålls. Eftersom den förväntade förbättringen värde kan endast uppnås om man tillämpar samtliga av dessa rekommendationer tillsammans, du är inte riktigt säker på vilken typ av effekter det kommer att få om du bara välja att tillämpa en undergrupp av dessa rekommendationer.

Avmarkera de rekommendationer som du inte vill använda. Nu, gå till åtgärder UTVÄRDERA  rekommendationer.

Detta kommer att inleda en ny session med samma alternativ som den tidigare. Men nu när du klickar på STARTA analys kommer DTA tillhandahålla uppgifter om uppskattade prestandaförbättringar, som bygger på just denna undergrupp av rekommendationerna.

Återigen är nyckeln sak att komma ihåg att DTA utför denna “what-if-analys” utan att faktiskt genomföra något i databasen.

TIPS: kontroll för uppsagda objekt akterseglade av DTA
I teorin borde DTA inte lämna några extra “hypotetical” PDS (index etc.) i databasen, efter analysen är klar. Men du kan lätt kontrollera detta genom att köra följande fråga:

Välj namn från sys.indexes där is_hypothetical = 1 Using STATISTIK XML om du bara vill bestämma den optimala index som fastställts för en viss fråga, då STATISTIK XML-alternativet ger ett snabbt alternativ till DTA.When en fråga utförs med STATISTIK XML aktiverat, SQL Server inte bara returnerar frågan resultat-set, men också utför T-SQL-uttryck som hämtar i handlingsplanen för frågan, i form av en XML-fil (Showplan.xml).

Prova till exempel att köra följande SQL-uttryck:

SET STATISTICS XML ON

GO

Use Adventureworks

SELECT Kundnr, PurchaseOrderNumber, TotalDueFROM Sales.SalesOrderHeader

WHERE ShipMethodID> 2 och TotalDue> 200.00 AND TerritoryID = 3,

GO SET STATISTIK XML OFF

GO

Nedan frågeresultaten bör du se en länk till ett XML- fil. De viktigaste faktorerna för att söka i XML-filen är:
– värdet av StatementText kommer frågan som du bara sprang. Du kan bekräfta att detta stämmer med frågan som du körde i Management Studio.
– uppgifter om saknade index som optimeraren tror skulle förbättra resultatet för denna fråga är inloggad här För ovanstående fråga, kan det relevanta avsnittet i din XML-fil se ut så här: </ ColumnGroup Usage=”INEQUALITY”> Det visar att vi har en saknas index på Sales.SalesOrderHeader bordet. Titta efter värdet av effekten i element, som uppskattar den procentuella ökningen i responstid du kan förvänta dig att se om du genomföra alla de index betonas i detta XML.Within den element:

• listor index kolumner som används för jämställdhet i fråga (TerritortyID, i vårt fall). Av alla de villkor som anges i frågan ser Frågeoptimeraren först för ett index om jämställdhet kolumnen för att utvärdera de rader som uppfyller kriterierna
• listor index kolumner som används för att utvärdera ojämlikhet villkor i frågan (ShipMethodID och TotalDue, i detta fall)
• listor index kolumner som finns för att täcka frågan (PurchaseOrderNumber och kundnummer).

Du kan ange ingår kolumner för icke-klustrade index för att undvika ett bokmärke uppslagning i din exekveringsplanen OBS: Om du vill veta mer om ingår kolumner på icke-klustrade index hänvisas till Index med Ingår kolumner artikeln om MSDN.To skapa detta saknas index bör du använda följande mall: Skapa NONCLUSTERED INDEX <> ON <> (equality_columns, följt av inequality_columns) omfattar (included_columns) Så för att skapa den saknade index i detta fall är det kommando som följande:

CREATE NONCLUSTERED INDEX TestIndex PÅ Sales.SalesOrderHeader (TerritoryID, ShipMethodID, TotalDue) INCLUDE (PurchaseOrderNumber, Kundnr), använder index-relaterade Dynamic Management Visningar (DMVs) Dynamic Management åsikter är en ny funktion i SQL 2005.

De har hävdat hela servern stat och konfigurationsinformation för din SQL Server installation. Det finns mer än 50 DMVs i SQL 2005 och den information som lagras i dem kan användas för att övervaka hälsan hos en server exempelvis diagnostisera problem och finjustera prestanda. Det finns flera uppsättningar DMVs, där varje butik särskild server information.

En sådan uppsättning DMVs lagra information om index och deras användning. Om Frågeoptimeraren finner att de index som krävs för att genomföra en viss exekveringsplanen inte är närvarande, kommer det att logga information om dessa saknas indexen i index-relaterade DMVs. Denna information är kvarstod i DMVs, tills nästa omstart av SQL Server eller när de metadata som är associerade med objekten blir dropped.You kan fråga dessa DMVs att hitta den saknade index eller lagra uppgifter för en periodisk analys. Låt oss ta en titt på hur du kan använda informationen i DMVs och hur du kan tolka utdata skapas erforderliga index. Innan vi börjar, gå vidare och starta om SQL Server på din test maskin så att den information rensas i varje DMV. Därefter i en ny fråga fönster, köra samma fråga som i föregående avsnitt, men med den statistik som XML-alternativet inte är aktiverat:

SELECT CustomerID, PurchaseOrderNumber,

TotalDueFROM Sales.SalesOrderHeader

WHERE

ShipMethodID> 2 och TotalDue> 200.00 AND TerritoryID = 3, Frågeoptimeraren kommer spark i att optimera frågan och hitta den bästa möjliga utförande planen. Vi vet redan att vi har en försvunnen index, så Frågeoptimeraren kommer att nöja sig med en optimal plan. Det kommer dock att göra en notering om detta i relevant index relaterade DMV.To hämta den saknade index detaljer, helt enkelt fråga de berörda DMV, enligt följande:

SELECT * FROM sys.dm_db_missing_index_details  den frågan returnerar ID för objektet som du bör skapa indexet, som råkar vara Sales.SalesOrderHeader tillsammans med equality_columns, inequality_columns och included_columns, som enligt tidigare beskrivning. Som ni ser, rekommenderar DMV skapa samma “saknas” index som STATISTIK XML alternativ och syntaxen för att skapa detta index är också samma som beskrivs above.

Varje gång du startar SQL Server är dessa DMVs spolas ut. Så du kanske vill kopiera värdena i dessa DMVs i några andra tabeller i databasen att regelbundet genomföra analysis.Using rätt verktyg för job den STATISTIK XML-alternativet och index-relaterade DMVs är både bra verktyg för att bedöma effekterna av en viss fråga på din indexering krav. Den största begränsningen i varje enskilt fall är att den inte kan användas för att finjustera hela belastningen på servern. Om du kör kritiska applikationer som medför ett stort antal frågor, kommer denna metod mycket tid och arbetskraftsintensiv. Dessutom när du använder DMVs kan du komma åt bara rå information om kolumner som indexerar kan saknas. Detta tillvägagångssätt har också flera begränsningar och är mindre exakt för frågor där ojämlikhet predikat. För mer information om begränsningarna finns i Begränsningar för att använda den

*** Missande Indexes Feature artikel om BOL.Database Engine Tuning Advisor är ett mer sofistikerat och avancerat verktyg, och ger fler alternativ än någon av de andra metoder som beskrivs i denna artikel. När du använder DTA kan du använda en representativ arbetsbörda och, som en följd av de rekommendationer som DTA är mer exakta. DTA kan också användas mot SQL 2000, medan andra funktioner inte kan. Dessutom kan du göra vad-om analyser med hjälp av DTA utan att införa några ändringar i databasen. Som bästa praxis rekommendation Jag förespråkar starkt att du använder DMVs eller statistik XML alternativ endast som ett sätt att snabbt bedöma konsekvenserna av en fråga på indexering krav.
Du bör alltid validera dina resultat, och den beräknade förbättringar, med hjälp av Database Engine Tuning Advisor, med en representant arbetsbelastning.

När du kör en fråga, är SQL Server Frågeoptimeraren jobb att hitta den bästa möjliga utförande plan (baserad på intern kostar algoritmer) för att ge bästa svarstid. Om index som krävs för genomförandet av denna optimal exekveringsplan inte är närvarande, då Frågeoptimeraren är tvungen att välja ett optimalt utförande plan, som indexerar finns. Detta kan göra SQL Server att göra mer I / O och förlångsamma frågeprestandan. Behovet av att ha rätt index på plats kan inte nog understrykas.

Det är dock uppgiften att identifiera rätt index inte nödvändigtvis enkelt. Det kräver en god kännedom om av den typ av frågor som kommer att köras mot data, fördelningen av dessa uppgifter, och volymen av data, samt en förståelse för vilken typ av index passar dina behov bäst. Även denna förståelse fortfarande är nödvändig, erbjuder SQL Server 2005 en hjälpande hand i form av ett antal nya verktyg – främst Database Engine Tuning Advisor – som kan hjälpa dig att avgöra, melodi och övervaka dina index.

Under denna artikel kommer jag att visa hur man använder dessa verktyg för att komma svar på följande frågor:

• Vilka index behöver jag till mina frågor?
• Hur övervakar jag indexanvändning och effektivitet?
• Hur identifierar jag överflödig index som negativt kan påverka resultaten av min DML frågor (in, uppdaterar och tar bort)
• Som arbetsbördan förändringar, hur jag gör jag identifiera saknade index som kan förstärka effekten av min nya frågor?
Att hitta rätt index för din arbetsbörda

Fastställande exakt rätt index för ditt system kan vara ganska en beskattning process. Till exempel, måste du tänka på:

• Vilka kolumner ska indexeras (baserat på dina kunskaper om hur information som efterfrågas)
• Om att välja en enda kolumn index eller en multipel kolumn index
• Oavsett om du behöver ett grupperat index eller en icke-klustrade index
• Huruvida eller inte (i SQL 2005) kan du dra nytta av ett index med ingår kolumner för att undvika bokmärke uppslagningar
• Hur att utnyttja indexerade vyer (som optimeringsfunktionen kan få tillgång istället för underliggande tabeller för att hämta en delmängd av dina data)

Alla dessa alternativ öka komplexiteten i att identifiera rätt index för ditt system. Du kan frestas att släppa index överallt “just in case”, men man måste komma ihåg att med införandet av varje nytt index för att förbättra resultatet av SELECT arbetsbördan har Frågeoptimeraren att arbeta mer när du kör DML uttalanden ( infoga, uppdatera eller ta bort), som nu har att organisera detta index för att hantera de nyligen uppdaterade uppgifterna i tabellen. Det finns en avvägning här.

Även när du väl har bestämt prefekten uppsättning index, är ditt jobb inte färdigt. Din arbetsbörda kommer att förändras över tiden (nya förfrågningar kommer att läggas till, gamla tas bort från ansökan) och detta kan motivera granska de befintliga index, analysera sin användning och göra justeringar (ändring / tappa befintliga index eller skapa nya). Underhåll av index är kritiska för optimal prestanda på lång sikt.

SQL 2005 innehåller följande verktyg och funktioner som hjälper dig att hitta rätt index för dina frågor och sedan följa upp och stämma dem:

• Database Engine Tuning Advisor (DTA)
• Statistik XML-utdata
• Dynamic Management Visningar (DMVs)
Jag kommer att beskriva var och en av dem under den följande avsnitten.

Använda Database Advisor Engine Tuning (DTA)
Den DTA är ett nytt verktyg i SQL 2005, som ersätter Index Tuning Wizard i tidigare versioner av SQL Server. DTA kan analysera både OLTP och OLAP arbetsbelastning. Du kan antingen ställa en enda fråga eller hela arbetsbelastning som servern utsätts för. Baserat på de alternativ som du väljer, kan du använda DTA att utfärda rekommendationer för flera fysiska designen Structures (PDS), som omfattar:

• klustrade index
• Icke-klustrade index
• index med inkluderade kolumner (för att undvika bokmärke uppslagningar)
• indexerade vyer
• partitioner
Låt oss gå igenom stegen i att använda DTA effektivt.

Genererar DTA arbetsbelastning
Det första steget är att samla in en arbetsbörda för DTA att analysera. Du kan göra detta på två sätt:

• Använda Management Studio – om du behöver för att optimera utförandet av en enda sökning kan du använda Management Studio för att ge ett bidrag till DTA. Skriv bara in frågan i Management Studio, markera den och sedan högerklicka på den för att välja analysera Database Engine Tuning Advisor. Detta startar DTA med din fråga som ingång.
• Använda Profiler – om du vill vill bestämma den optimala index som fastställts för hela arbetsbördan för servern ska du samla ett Profiler spår med den inbyggda TUNING mall.

För att frigöra den verkliga effektiviteten i DTA, bör du alltid en representant profiler spår. Se till att du omfattas av din server för att alla de frågor som normalt kommer att köras mot data, medan du samlar in spår. Detta kan leda till en enorm spår fil, men det är normalt. Om du bara samlar ett Profiler spår över en 5-10 minuters period kan du vara ganska säker på att det inte kommer att vara fullt representativt för alla de frågor körs mot databasen.

Den TUNING mall fångar endast minimal händelser, så det bör inte finnas några större resultat inverkan på din server.
Som en del av koden ladda ner för den här artikeln, jag har gjort en Profiler spår (Workload2Analyze.trc) som fångades med TUNING mallen när den körs ett flertal frågor (Queries4Workload.sql) mot AdventureWorks databasen. Jag skulle starkt rekommendera att du använder detta spår, eller en från din server, för att få en hands-on perspektiv DTA.

Konsumerar arbetsbelastning med DTA
Ha samlat in ett verkligt representativt Profiler spår, kan du använda det som ett bidrag till DTA, som då kommer generera rekommendationer. Du kan utföra en av följande två typer av analys.

Scenario som jag: Håll mina befintliga PDS och berätta vad mer jag saknar Denna typ av analys är vanlig och är användbart om du tidigare har fastställt antal index som du anser vara mest användbar för viss arbetsbelastning, och söker ytterligare rekommendationer. Att genomföra denna analys:

1.Launch en ny session i DTA.
2.Välj den Profiler spår som ingång till denna session.
3.In Välj databaser och tabeller för att ställa avsnittet välja ditt mål databasen (AdventureWorks, om du använder mitt manus).
4.In databasen för arbetsbördan analys dropdown, kan du antingen använda samma databas eller tempdb (eller någon annan databas). Jag använder i allmänhet tempdb för sitt ändamål.
5.At Tuning fliken Alternativ, välj följande alternativ:
a.Physical Design Strukturer för att använda i databasen -> Index och indexerade vyer
b.Physical Design Strukturer att hålla databasen -> Behåll alla befintliga PDS
c.Uncheck kryssrutan för gräns tuning tid.
6.Hit START ANALYS och DTA kommer att börja konsumera din arbetsbörda.
När DTA är klar konsumerar arbetsbelastningen kommer den att lista alla sina rekommendationer enligt rekommendationerna fliken. Vi kommer att tala mer om läsning och genomföra DTA rekommendationer i avsnittet Reading produktionen från DTA.

Scenario II: Ignorera de befintliga PDS och berätta vad Frågeoptimeraren behov scenario som jag gör DTA rekommendationer för ofullständig index. Men detta betyder inte nödvändigtvis ditt befintliga index är optimala för Frågeoptimeraren. Du kan också överväga att genomföra en analys där DTA ignorerar all befintlig fysisk design strukturer och förespråkar det man anser vara den bästa möjliga av PDS för en given arbetsbelastning. På detta sätt kan du validera dina antaganden om vad index som krävs.

Att genomföra denna analys, följ steg 1 till 6 enligt ovan, utom att vid steg 5b, välj “Spara inte någon befintlig PDS”.

I motsats till hur det kan låta, kommer DTA faktiskt inte släppa eller ta bort befintliga PDSs. Detta är den största fördelen med att använda DTA, eftersom det innebär att du kan använda verktyg för att göra vad-om analyser utan att införa några ändringar av den underliggande schemat.

En anteckning om DTA Tuning Log
Eftersom det förbrukar arbetsbelastningen kommer DTA förvara i DTA Tuning log (finns under Framsteg fliken i nedre delen) Uppgifter om eventuella fel som den stöter på.

Vanligtvis kan du se ett meddelande i formatet “XX% av den förbrukade arbetsbördan har syntaxfel”. Stämningen log kommer att ge ytterligare information om dessa fel, av vilka många kan ignoreras. Till exempel:

• Uttalande inte referens någon tabell – uttalanden som SET eller förklara din arbetsbörda kommer att orsaka dessa fel, och de kan man normalt bortse
• Uttalande referenser endast små bord – DTA kommer inte att ställa en fråga om det referenser ett litet bord (10 datasidor eller mindre)
• Felaktig syntax eller föremål relaterade fel – om felen visar frågor statments innehåller nyckelord som börjar prova, INSERTED då kan du ignorera dem.
Om du vill veta mer om hur du analysera tuning loggen, se Om Tuning Logga in och avgöra om händelser kan anpassas för BOL

Läsa ut från DTA
Efter att ha förtärt arbetsbördan presenterar DTA, enligt rekommendationerna fliken en rad rekommendationer för att trimma din PDS. Jag tenderar att fokusera på följande avsnitt:

• Rekommendation – detta är den åtgärd som du behöver ta. Möjliga värden inkluderar Skapa eller Drop.
• Mål om rekommendation – detta är den föreslagna namnet på PDS som ska skapas. Namnkonventionen är typiskt för DTA och i allmänhet börjar med _dta  *. Men jag rekommenderar att du ändrar detta namn baserat på namnkonventionen i din databas.
• Definition – detta är den lista med kolumner som denna nya PDS kommer att innehålla. Om du klickar på hyperlänken kommer det att öppna ett nytt fönster med T-SQL script för att genomföra denna rekommendation.
• Beräknad Förbättringar – detta är den uppskattade procentuella förbättring som man kan förvänta sig i din arbetsbörda prestanda, om du genomföra alla de rekommendationer som gjorts av DTA.
• Utrymme som används av rekommendation (MB) – under Tuning Sammanfattning delen av fliken Rapporter kan du ta reda på det extra utrymmet i MB som du skulle behöva, om du bestämmer dig för att genomföra dessa rekommendationer.

.

Khan sqldba – MCTS

Advertisements

2 thoughts on “Fine Tuning your Database Design in SQL 2005

  1. It’s very straightforward to find out any topic on net as compared to textbooks, as I found this article at this site. akeecekkkeedacda

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s