Förord

Tro inte att optimera prestanda på dina applikationer i SQL är relaterade till den sista delen av processen. Om du vill ha din applikation skalbar och köra i dess fulla potential måste du överväga skalbarhet och prestanda redan i de tidiga stegen av din utveckling.

Innehåll

  » Vad alla utvecklare och DBA måste veta om SQL server optimerin
  » Vad varje utvecklare måste känna till om SQL Server prestandao
  » Optimering är ingen vetenskap
  » Testa igenom alla steg då du utvecklar
  » Alla prestandaproblem är inte solklara
  » Det är inte alltid förslagen på hur du optimerar din prestanda
  » SQL Server Performance Tuning är något man måste lära sig
  » Hur du optimerar din Servers hårdvara
  » Hur du väljer hårdvara
  » Optimera din server
  » Hur du optimerar SQL Serverns konfigurationsinställningar
  » Hur du optimerar din applikations design
  » Hur du optimerar databasens design
  » Hur du optimerar din applikationskod för SQL Server
  » Använd OLE DB för att komma åt din SQL Server
  » Fånga din DML (Data Manipulation Language) i lagrade procedure
  » Fånga in din ADO kod i COM komponenter
  » Hur du optimerar din Transact-SQL kod
  » Välj lämpliga datatyper
  » Använd Triggers varsamt
  » Försök inte komma åt mer data än vad du behöver
  » Undvik att använda pekare (Cursors)
  » Använd relationerna på ett lämpligt sätt
  » Fånga in din kod i lagrade procedurer
  » Hur du väljer dina Index för att hålla databasens prestanda op
  » Går det att ha för många Index?
  » Tips för när du ska välja grupperade Index
  » Tips för när du ska välja icke-grupperade Index
  » Vad ska du göra härnäst?

Performance Tuning för SQL Server Utvecklare

Skribent: Brad M. McGehee

Om du har varit en DBA eller SQL utvecklare under en lång tid så har du troligtvis sprungit på åtskilliga långsamma sql-baserade applikationer. Och ofta när detta händer så börjar alla skylla på varandra för den försämrade prestandan, trots att det likaväl kan bero på nätverket, servern (eller SQL Servern), databasen eller kanske till och med användarna. Så fortsätter det, men det går ju faktiskt inte att skylla en långsam applikation på någon, eller något. För att bota det här problemet krävs en insikt av användarnas behov, design, optimering och en korrekt implementering.

Alla applikationer som används gentemot SQL kräver att skalbarhet och prestanda byggs in direkt från början. Så fort applikationen släpps är det väldigt svårt, och även kostsamt, för att lösa även det mest simpla prestandaproblem som kan förekomma.

I denna artikel kommer du att få lära dig de grundläggande stegen till att designa, koda och implementera skalbara och optimerade SQL Server applikationer. Du kommer inte lära dig allt, för det skulle kräva en hel bok. Utan fokuseringen på den här artikeln ligger i att lära sig det du minst behöver veta för att producera ett bra resultat. Detta är vad vi kommer gå igenom:

Vad alla utvecklare och DBA måste veta om SQL server optimering

• Hur man optimerar sin servers hårdvara
  
• Hur man optimerar sin SQL-Servers konfiguration
  
• Hur man optimerar sin applikationsdesign
  
• Hur man optimerar sin databasdesign
  
• Hur man optimerar sin kod för SQL-Server
  
• Hur man optimerar sin T-SQL kod
  
• Hur man optimerar sina Index för optimal prestanda
  
• Hur man använder "SQL-Server Performance Tuning Tools" för ytterligare optimering
  

Om du slutligen tar hänsyn till de råd och den informationen som artikeln ger - kommer du märka att prestanda och optimering för dina applikationer inte är ett så stort mysterium som du kanske trodde.

Vad varje utvecklare måste känna till om SQL Server prestandaoptimering

Som utvecklare finns det några grundläggande principer hur du skall gå till väga. Detta avsnitt presenterar dessa principer. Tänk på dem när du i den här artikeln läser om dessa specifika sätt att optimera, samt när du utför din optimering på en SQL Server.

Optimering är ingen vetenskap

Optimering är mer som en konstform än en vetenskap. Jag är säker att du inte vill höra detta, men det är ett faktum. Jag önskade att jag kunde berätta precis hur och vad du borde göra för att optimera din applikation. Problemet – som du säkert redan vet – är att alla moderna programvaror är en kombination av många komplicerade variabler. Tyvärr så är det så att oavsett hur mycket du försöker, så kommer du inte ha full kontroll över din applikation och den miljö som det körs under. Här följer t ex några (inte alla) faktorer som påverkar en applikations prestanda:

• SQL Server (själva programmet)
  
• SQL Server's konfigurationer
  
• Applikationens Transact-SQL kod
  
• Applikationens övriga SQL-kod
  
• Databasens design
  
• Operativsystemet (Server och klient)
  
• Mellanskiktet (Microsoft Transaction Server, Microsoft Messaging Server)
  
• Hårdvara (Server och klient)
  
• Nätverkets hårdvara och bandbredd (LAN och WAN)
  
• Antalet klienter
  
• Klienternas arbetsmönster
  
• Typ av kvalitet på data som är lagrade i SQL Server
  
• Huruvida applikationen är OLTP - eller OLAP baserad
  

Då det är virtuellt omöjligt att kontrollera alla faktorer som påverkar SQL serverns skalbarhet och prestanda, så går det i alla fall att påverka de flesta delarna.

Testa igenom alla steg då du utvecklar

Skalbarhet och prestandatest kan inte endast utföras efter att du har skrivit klart applikationen och den är klar för att användas. Tester skall alltid vara en del i utvecklingsprocessen, från de tidigaste stegen av databasdesignen och löpande genom hela processen. De flesta skalbarhet och prestandaproblemen kommer ofta från dålig grunddesign, och kan endast motverkas i ett tidigt skede. Om du väntar tills applikationen är komplett så kommer du tvingas leva med prestandaproblem, eller kanske få behöva skriva om hela applikationen.

När man utför ett test så skall man alltid vetenskapligt arbeta sig igenom en variabel i taget. Om du t ex är misstänksam om när du ska sätta ett Index till tabellen för att öka prestanda, men du är inte säker på vilket Index som ger bäst resultat, eller vilken typ som är bäst. Exprimentera med en i taget, testa varje Index individuellt för att se om resultatet blir vad du förväntar dig. Om du ändrar mer än en sak i taget så kommer du inte veta vilken förändring du gjorde som påverkade resultatet. Detta gäller givetvis alla sorts tester, oavsett om det gäller att lägga till Index, göra konfigurationer i SQL eller testa olika hårdvarukonfigurationer.

Försök alltid att testa under realistiska former. Med detta menas att du skall använda "riktig data", testa mot de största recordseten och använda hårdvara likt den som kommer användas i produktionsmiljön. Om du inte gör det kan du bli förvånad då det fungerar för 10 samtida användare, men fallerar när det är 500.

Alla prestandaproblem är inte solklara

Om du håller på mycket med prestandaoptimering så kommer du snart inse att många av dessa inte är så självklara. Detta eftersom många prestandarelaterade problem orsakas av två eller fler andra problem, inte ett separat problem. Det gör det svårt att finna problemet för att sedan lösa det. Men det finns ingen enkel lösning. Ett av de bästa sätten däremot är att isolera och rätta till ett problem i taget tills du funnit alla.

Det är inte alltid förslagen på hur du optimerar din prestanda fungerar

I den här artikeln – och från flera handböcker inom prestandaoptimering – så kommer du att finna dussintals idéer och tips om hur du kan höja din prestanda. Kom bara ihåg att vissa förslag på prestandaoptimering kan höja prestandan i vissa situationer, men i andra situationer så kanske de bara sänker din prestanda. Detta beror på att förslagen från Performance Optimizer endast fungerar under specifika förhållanden. Eftersom det är du som ansvarar för att hålla din prestanda hög, så måste du tänka igenom varje alternativ eller förslag som du stöter på för att bestämma om alternativet skulle hålla under just dina förhållanden. Med andra ord ska du inte lita blint på att varje alternativet passar just dig, utan var säker på att du förstår vad förslaget innebär innan du använder dig utav det.

SQL Server Performance Tuning är något man måste lära sig

Hur man hanterar SQL Servers prestandaoptimering är ingenting man lär sig över en natt. Faktum är att du lär dig mer om hur du bemästrar dina kunskaper, genom att experimentera än vad du skulle göra om du istället skulle läsa dig till det. Men för att kunna dra fördel av erfarenheten som du med tiden uppnår, så är det viktigt att du är insatt i de grundläggande teknologierna som påverkar din applikations prestanda.

Du måste t ex ha kunskap om programmeringsspråket som du skapar dina applikationer med, om databasdesignen, applikationsdesignen, Transact-SQL, hur SQL Server lagrar och listar upp dina data samt om hur Nätverkets och Serverns hårdvara verkligen fungerar. Ju djupare kunskap du har om de grundläggande, tillämpningsbara teknologierna, vilka du använder för att skapa och utveckla dina applikationer med, ju lättare kan du förstå vad det är som orsakar prestanda- eller skalningsproblem och desto lättare kan du därmed lösa problemen. Lär dig så mycket som du kan.

Hur du optimerar din Servers hårdvara

När den tid kommer då man känner att man vill ha någonting att skylla en dålig applikation på, så råkar det oftast bli Serverns hårdvara man skäller på. Det ironiska med det, är att det för det mesta inte beror på Serverns hårdvara alls. Faktum är att när det handlar om en SQL Server baserad applikations prestanda och skalbarhet, så spelar hårdvaran en mycket mindre roll än vad man kan tro.

Orsaken till varför en del applikationer är slöa beror inte på slö hårdvara, utan på en slö huvuddesign. Och orsaken till att man oftast skyller på hårdvaran är för att man inte märker prestandaproblemen förrän applikationen är uppe och igång. Och när en applikation sen väl är uppe och igång, så är det inte längre så lätt att ändra på dess design. Så det man gör för att höja prestandan är att man ändrar på hårdvaran. Och även fast den nya hårdvaran kan hjälpa en bit på vägen, så har det dock inte löst hela problemet. Därför skyller man på hårdvaran. Hårdvaran kan dock vara ett problem ibland, men inte i de flesta fall.

För att förhindra att din Server hårdvara sänker prestandan på dina SQL Server baserade applikationer (vilket kan hända om det är felinstallerat eller felkonfigurerat), så ska vi nu titta på några av de vanligaste hårdvaruinställningarna och optimeringarna.

Hur du väljer hårdvara

Det finns många olika faktorer att titta på när det handlar om vilken hårdvara du ska iförskaffa till dina SQL Server baserade applikationer, för att få så optimal prestanda som möjligt. Det kan t ex bero på databasens storlek, antalet användare, hur databasen körs (OLTP eller OLAP), osv. Det finns ingen mall för hur hög standard din Servers hårdvara bör ha, utan du får pröva dig fram genom att testa din applikation i ett tidigt stadium av utvecklandet. Märk väl att jag än en gång nämnde ordet ”testa”. Trots att många erfarna DBAs förmodligen kan ge dig en ganska bra uppskattning om hur hög standarden på din hårdvara bör vara, så är det endast genom noggranna, realistiska tester som du kan se vad som behövs i hårdvaruväg för att du ska kunna möta dina applikationers behov.

Och när det gäller serverns hårdvara så finns det vissa saker som du bör komma ihåg:

CPU: Se alltid till att köpa in en server som ger dig möjligheten att utöka ditt förråd av CPUn. Om t ex tester visar på att det skulle räcka med en enda CPU i din server, se ändå till att köpa en server med två platser för CPU – trots att du kanske bara kommer att använda den ena. Det samma gäller för servrar med fyra eller fler CPUn, se alltid till att det finns plats för att växa.

Minnen: Det här är förmodligen den hårdvara som mest kan komma att påverka din SQL Servers prestanda. Tanken är att hela din databas ska passa in i RAM minnet. Olyckligtvis så är det dock inte alltid möjligt. Du bör åtminstone försöka ha tillräckligt med RAM till att hantera den största tabellen som du tror att du kommer att ha. Och om du har råd, så bör du införskaffa så mycket RAM som din server kan hantera, vilket oftast ligger på 2 GB eller mer. Det finns ingenting som heter ”att ha för mycket RAM”.

I/O Subsystem: I/O Subsystem är den – näst efter RAM – viktigaste hårdvaran som kan påverka din prestanda på din SQL Server. Du borde minst införskaffa en hårdvarubaserad RAID för din databas. En tumregel är att du bör ha flera små diskar hellre än färre stora diskar. Ju fler diskar du har, desto snabbare kommer din I/O att fungera.

Nätverksanslutning: På din server bör du minst ha ett 100 MB nätverkskort, och den bör vara kopplad till en Switch. Egentligen bör du ha två nätverkskort i den Servern som är kopplad till en Switch, vilken helst ska vara satt till full-duplex mode.

Optimera din server

Inte ens den dyraste servern kommer att klara sig bra om den inte är korrekt konfigurerad och/eller optimerad. Jag har sett många hårdvarurelaterade prestandaproblem som är orsakade av att man inte har använt hårdvara och drivers som är godkända av Windows NT Server. Vissa av problemen som är relaterade till hårdvarumässiga prestandaproblem är väldigt svåra att felsöka i och därmed också att lösa. Se hellre då till att din hårdvara, inklusive Windows NT, är installerade och konfigurerade av en kompetent tekniker. Sen kan du testa dina applikationer under kontrollerade former för att leta efter potentiella prestandaproblem, innan du använder dem under produktion.

Ditt operativsystem måste också konfigureras korrekt. Det inkluderar många saker, för många för att jag ska kunna ta upp dem här. Men se dock till att operativsystemet – precis som hårdvaran – konfigureras korrekt och testas innan den tas upp i produktion.

För att hålla prestandan uppe på en server så bör SQL Server vara den enda applikationen som körs på servern, förutom vissa management program. Försök inte spara in några ören genom att lägga in IIS eller MTS på samma server som SQL Server ligger på. Det kommer inte bara att skada SQL Serverns prestanda, utan det gör det också svårare att utföra felsökningar och prestandaoptimeringar av SQL Server.

Hur du optimerar SQL Serverns konfigurationsinställningar

En annan missuppfattning när det handlar om att optimera din SQL Server är att du måste göra vissa småjusteringar i konfigurationsinställningarna för att få högsta möjliga prestanda. Gällande de äldre versionerna av SQL så kan det ha legat en viss sanning i det, men nu för tiden är det ingenting man behöver bry sig om – om det inte handlar om väldigt stora och belastade servrar.

Till största delen så är SQL Server självinställande. Och vad innebär det? Jo, att SQL Server själv känner av hur mycket som körs, varpå den automatiskt gör vissa interna justeringar. Dessa justeringar gör att SQL Server oftast går så optimalt som möjligt, med tanke på dem uppgifter och den hårdvara som ges.

När du utför ett prestandatest på en SQL Server bör du komma ihåg att den kan ta lite tid på sig innan den har justerat sig själv optimalt. Med andra ord så kan prestandan den visar på direkt efter att du har startat en SQL Server service, vara helt olika den prestanda som den visar på efter ett par timmar igen då arbetslasten har hunnit spela någon roll. Så se alltid till att utföra prestandatester efter att den har hunnit anpassa sig till din arbetslast.

Det finns 36 olika konfigurationsinställningar i SQL Server som kan justeras genom att antingen använda Enterprise Manager eller att använda de lagrade procedurerna i sp_configure. Så till vida att du inte har massvis av erfarenhet inom att optimera SQL Server, så rekommenderar jag att du inte ändrar någon inställning i SQL Server alls. Som en nybörjare så finns nämligen risken att du gör ändringar som i stället sänker prestandan. Om du t ex ändrar någonting manuellt så har du ”hårdkodat” en inställning. SQL Server har förmågan att själv ändra inställningar beroende på arbetslasten, men om du har hårdkodat någonting så har du på sätt och vis delvis ”tagit bort” SQL Servers förmåga göra finjusteringarna själv.

Om du däremot efter noga övervägning känner att en eller fler justeringar av konfigurationsinställningarna i SQL Server faktiskt skulle höja prestandan i just din miljö, så bör du göra dessa ändringar sakta och försiktigt. Innan du gör någon ändring så bör du se över SQL Serverns prestanda under en typisk arbetslast med hjälp av ett verktyg såsom t ex Performance Monitor (kommer senare). Sen gör du endast en justering i taget, för om du utför flera på en gång, så vet du inte vilken – om någon alls – av justeringarna som gjorde susen.

När du har gjort justeringarna så bör du än en gång se över SQL Serverns prestanda under samma arbetslast som förra gången, för att se om justeringarna höjde prestandan eller inte. Om justeringarna inte hjälpte till något – vilket oftast är fallet – så bör du ändra tillbaka till de gamla inställningarna igen. Om justeringarna däremot höjde prestandan, så kan du fortsätta med att se över om SQL Serverns prestanda påverkas lika bra under andra arbetslaster som servern kan komma att råka ut för. Den senare av testerna kan komma att påvisa hur justeringarna höjde prestandan under vissa arbetslaster, men sänkte dem under andra. Det är därför man inte rekommenderar några ändringar i SQL Serverns konfigurationer.

Om din applikation skulle råka ut för ett prestandarelaterat problem, så är det ganska låga odds till att du löser det genom att göra justeringar i konfigurationsinställningar.

Hur du optimerar din applikations design

Om du använder dig utav en n-lager design till din applikation – och vem gör inte det nu för tiden när det handlar om storskaliga applikationer – så är SQL Server bara en del av en större applikation. Och kanske är det mer betydelsefullt än du kan tänka dig, hur mycket mer din n-lager design påverkar applikationens prestanda än vad själva SQL Server gör. Ofta när det handlar om dålig prestanda på en applikation så skyller man tyvärr mer på SQL Server än på själva applikationens design, även fast det oftast är designen som orsakar de flesta prestanda-problemen. Jag tänker nu ge dig några förslag som kan hjälpa dig med själva applikationens design, så att du inte beskyller SQL Server det första du gör vid eventuell låg prestanda. Låt oss börja.

En av de första sakerna du måste göra när du designar en n-lager applikation är att välja vilken logisk och vilken fysisk design du vill ha. Det är i den fysiska designen som de flesta misstagen görs när det handlar om prestanda. Detta därför att det är här som det teoretiska (som är baserad på den logiska designen) måste implementeras till verkligheten. Och precis som med allting annat så finns det här ett flertal olika val att välja mellan, av vilka de flesta inte själva kan leda sig fram till skalbarhet och hög prestanda.

Vill du t ex implementera en fysisk tvålagers implementering med en stor mängd användare, en fysisk tvålagers implementering med en stor server, en fysisk trelagers implementering, en Internet implementering eller någon annan sorts implementering? När du har bestämt svaret på dessa frågor så måste du vidare fråga dig vilket utvecklingsspråk du vill använda dig av, och vilken browser, kommer du att använda dig av Microsoft Transaction Server (MTS), kommer du att använda Microsoft Message Queue Server (MSMQ), osv.

Var och en av dessa alternativ kommer på något sätt att påverka applikationens prestanda och skalbarhet. Och eftersom det finns så många olika alternativ att välja mellan så är det återigen viktigt att du i ett tidigt stadium av utvecklingen – med hjälp av en snabbprototyp – provar olika sorters design, för att till slut nå fram till den design som bäst kan möta dina användares behov.

Här följer nu ett antal generella rekommendationer som du bör följa när du utvecklar en design. Rekommendationerna hjälper dig att hålla prestanda och skalbarhet hög i din applikation.

• Utför så många av de informationsrelaterade uppgifterna som möjligt i form av procedurer. Undvik att göra manuella inställningar i presentations – och arbetsservicens lager.
  
• Försök inte att hålla upp statusen (dvs spara inte data från databasen) i arbetsservicens lager. Försök att göra det i databasen så mycket som möjligt.
  
• Skapa inte grupper eller djupgående hierarkier. Att skapa och använda grupperade klasser, eller en stor mängd objekt för att modellera komplicerade arbetsregler, är resurskrävande, vilket kan resultera i sämre prestanda och skalbarhet i din applikation. Det beror på att det tar tid och är kostsamt när minnet ska förflytta sig för att skapa och släppa dessa objekt.
  
• Försök att med hjälp av Microsoft Transaction Server (MTS) designa din applikation till att dra fördel av databasens anslutnings- och objektets kopplingar. MTS låter både databasens anslutningar och objekten att bindas ihop, vilket kan höja applikationens prestanda och skalbarhet markant.
  
• Om din applikation kör SQL-satser – som av naturen kan vara långa – mot din SQL Server, designa då din applikation till att kunna köra satserna asynkront. Om du gör det så behöver inte varje sats vänta på att den förra ska köras klart, innan den själv kan köras. Ett sätt att skapa denna funktion till din n-lagers applikation är genom att använda Microsoft Message Queue Server (MSMQ).
  

Trots att man genom att följa dessa förslag inte kan garantera en förbättrad prestanda eller skalbarhet av applikationen, så är det åtminstone någonting att utgå ifrån.

Hur du optimerar databasens design

Databasens design är, precis som applikationens design, en väldigt betydande faktor när det gäller att höja din SQL Server applikations prestanda och skalbarhet. Och om du – återigen precis som med applikationens design – inte gör ett bra jobb från början, så kan det vara väldigt svårt och kostsamt att göra ändringar i applikationen då den väl har gått i produktion. Här följer några nyckelråd som du bör följa när du designar en SQL Server databas till förmån för prestanda och skalbarhet.

Som alltid så bör du testa din databas så tidigt som möjligt genom att använda realistiska data. Detta innebär att du bör skapa en databasprototyp innehållande testdata, och testa designen med samma höga aktivitet som du räknar med att den kommer att få stå ut med då den har gått i produktion.

En av de första saker du bör bestämma dig för är huruvida databasen ska anpassa sig till OLAP eller OLTP. Märk väl att jag använde ordet ”eller”, för ett av de största misstagen som görs är att försöka få databasen att anpassa sig till behoven från både OLAP och OLTP. Dessa två applikationer är viktiga då du vill uppnå hög prestanda och skalbarhet.

OLTP databaser är generellt sett högt standardiserade, och kan hjälpa dig att reducera mängden data som måste lagras. Ju mindre data du lagrar, desto mindre I/O kräver SQL Server och desto snabbare går det att komma åt databasen. OLTP hjälper också till att hålla transaktionerna så korta som möjligt för att motverka låsande konflikter. Och slutligen så minimeras även Indexen, för att reducera arbetslasten vi stora mängder av INSERT, UPDATE och DELETE.

OLAP databaser däremot, är inte standardiserade. Det innebär att man inte använder sig utav transaktioner då databasen är read-only, och då behöver man inte oroa sig för låsande konflikter. Och självklart så används stora Index till att möta behoven från de många variationer av rapporteringar som sker.

Som du ser så används OLAP och OLTP till två helt skilda förhållanden, och det är virtuellt omöjligt att designa en databas till att möta bådas behov. Medan OLAPs databasdesign ligger utanför den här artikelns gränser, så vill jag dock nämna ett par prestandarelaterade förslag gällande OLTPs databasdesign.

När du designar din OLTP databas och går igenom en standardiseringsprocess, så är ditt huvudsakliga mål att normalisera databasen enligt de tre normaliseringsprinciperna som gäller vid standardisering. Nästa steg blir till att utföra ett preliminärt prestandatest, och speciellt då du räknar med att använda relationer på fyra eller fler tabeller samtidigt. Försäkra dig dock om att testerna baseras på realistiska testdata.

Om prestandan visar sig vara fullt godtagbar så är det ingen fara med att relatera fyra eller fler tabeller i din SQL-sats. Men om det visar sig vara tvärtom, att prestandan inte möter dina krav, så bör du utföra några selektiva denormaliseringar av tabellerna i fråga, för att reducera antalet relationer i SQL-satsen, och därmed öka prestandan.

Det är mycket lättare att finna ett problem i ett tidigt stadie i designen, än att finna problemen när den färdiga applikationen körs. Att utföra denormaliseringar av tabeller då applikationen är klar, är i det närmaste omöjligt. Ett varnande ord; Frestas inte till att denormalisera tabeller utan att göra grundliga tester. Det är väldigt svårt att göra logiska gissningar på hur en denormalisering kan komma att påverka prestandan. Det är enbart genom realistiska tester som du kan få reda på om du får ut någonting bra vid en denormalisering.

Hur du optimerar din applikationskod för SQL Server

Under utvecklingsprocessen så måste du faktiskt någon gång börja koda dina applikationer till att kunna jobba med SQL Server. Och när den tiden kommer, så bör du vara klar med både databasens- och applikationens design. Och de ska dessutom vara testade med avseende på hög prestanda och skalbarhet, genom att använda snabba prototyptekniker.

Precis så som databasens- och applikationens design spelar in, så är även kodningen i applikationen en avgörande faktor för hur man återger prestanda och skalbarhet. Ibland kan det bero på något så simpelt som att välja vilken kodningsteknik man ska använda. Det är sällan det bara finns ett enda sätt att koda fram någonting man vill ska utföras, men det kan hända att det bara finns ett enda sätt att koda för att få fram optimal prestanda och skalbarhet.
Det jag vill uppnå i kommande avsnitt är att visa på några speciella tekniker som kan komma att påverka applikationens och SQL Serverns prestanda.

Eftersom jag inte vet vilket utvecklingsspråk du använder, så kommer jag att räkna med att du använder Microsofts ADO (Active Data Objects) objektmodellering för att komma åt SQL Server från din applikation.

Använd OLE DB för att komma åt din SQL Server

Du kan komma åt SQL Server data genom att antingen använda dig utav ODBC eller OLE DB. Vilken du använder beror på anslutningskoden du skriver, då du använder ADO till att ansluta din SQL Server. För att uppnå högsta prestanda bör du använda OLE DB. OLE DB används internt av SQL Server och det är det effektivaste sättet att komma åt vilka som helst av dina SQL Serverdata på. På samma sätt så finns det två olika kodningssätt att välja mellan när du skapar en ADO anslutning mot din SQL Server; att ansluta med en DSN eller att skapa en DSN-fri (DSN-less) anslutning. För optimal prestanda bör du använda en DSN-fri anslutning. Genom att göra det , så förebygger du det extra arbetet som blir när applikationen körs och då OLE DB letar upp anslutningssträngen i listan hos klienten.

Fånga din DML (Data Manipulation Language) i lagrade procedurer

ADO erbjuder dig tre olika sätt att använda SELECT, INSERT, UPDATE och DELETE för data i SQL Serverns databas på. Du kan använda ADOs metoder, du kan använda dynamisk SQL eller så kan du använda lagrade procedurer. Låt oss titta lite snabbt på var och en av dem.

Det enklaste sättet att behandla dina data från din applikation på, är genom att använda ADOs egna metoder, så som rs.AddNew, rs.Update och rs.Delete. Trots att dessa metoder är väldigt enkla att både lära sig och tillämpa, så får du betala ett högt pris i form av arbetslast när du använder dig utav dem. ADOs metoder skapar ofta långsamma pekare och genererar stora mängder nätverkstrafik. Du lär kanske inte märka av skillnaderna om du har en mindre applikation, men om applikationen hanterar stora mängder data så kan din prestanda påverkas mycket negativt.

Ett annat sätt att behandla data som med ADO har lagrats i SQL Server, är genom att använda dynamisk SQL (som ofta refereras som ”ad hoc queries”). Det du gör här är att du skickar Transact-SQL från ADOn i form av strängar till den applikation som ska köras på SQL Server. När du använder dig utav dynamisk SQL så går det i och för sig snabbare än om du skulle använda ADOs egna metoder, men det erbjuder inte den bästa prestandan. Därför att när SQL Servern har tagit emot den dynamiska SQLen från din ADO-baserade applikation, så måste den först kompilera Transact-SQL koden, sedan skapa en SQL-sats plan för den och därefter exekvera den. Den första gången som koden blir kompilerad och får en SQL-plan så skapas det en viss arbetslast. Men när Transact-SQL koden väl har blivit kompilerad och fått en plan så kan planen återanvändas om och om igen (om vi räknar med att den nästa Transact-SQL koden som kommer är något identisk med den första) vilket i stället sparar på arbetsbördan.

För optimal prestanda bör du när du använder dig utav ADO anropa vissa lagrade procedurer på din server, för att låta dem bearbeta dina data. Fördelarna med att använda sig utav lagrade procedurer är många. Lagrade procedurerna är t ex redan förkompilerade och optimerade, så det momentet behöver inte upprepas varje gång som proceduren körs igång. Utan den första gången som en lagrad procedur körs så skapas en SQL plan som sparas i SQL Serverns minne för senare återanvändning, vilket sparar mer tid. En annan fördel med lagrade procedurer är att de hjälper dig reducera nätverkstrafiken. När din ADO kod anropar en lagrad procedur så gör den ett enda anrop över nätverket. All bearbetning av data sker sedan på SQL Servern, där det är mest effektiv att utföra en databehandling. Sedan – om det behövs – så skickas eventuell data tillbaka till din applikation. Detta reducerar nätverkstrafiken markant, och höjer samtidigt all prestanda och skalbarhet.

Lagrade procedurer är perfekta att använda när det gäller grundläggande bearbetning av data, men dem kan göra så mycket mer. Lagrade procedurer kan virtuellt köra vilken Transact-SQL kod som helst. Och eftersom Transact-SQL är det effektivaste sättet att bearbeta data på, så bör du inte använda COM komponenterna i arbetslagret på klientdatorn, utan låt istället alla dina data i applikationen bearbetas med hjälp av lagrade procedurer på din SQL Server.

När du använder ADO till att exekvera lagrade procedurer på SQL Server så finns det två huvudsakliga sätt att göra detta på. Du kan antingen få din ADO till att anropa en uppdateringsmetod (Refresh) till parameterlistan, och på så sätt spara in lite kodning. Din ADO måste nämligen veta vilka parametrar som används av den lagrade proceduren, och för att få fram de parametrarna så kan du ”fråga ut” proceduren i fråga på din SQL Server med hjälp av Refresh-metoden. Men som du förstår så skapar det onödig nätverkstrafik och arbetslast. Så trots att det kräver lite mer kodning, så är det genom att specifikt skapa parametrarna i koden som är det effektivaste sättet att anropa en lagrad procedur på din SQL Server. På så sätt har du eliminerat arbetslasten som annars skulle ha orsakats av Refresh metoden, och applikationen fungerar snabbare.

Fånga in din ADO kod i COM komponenter

För att skapa en skalbar och optimal n-lager applikation så bör du lägga in den ADO kod som ska komma åt dina SQL Server data i COM komponenter. Det bör du göra oavsett om du håller på att skapa en Visual Basic applikation eller en webbaserad ASP applikation. Detta ger dig alla standardfördelar med COM komponenter, såsom att kunna koppla ihop objekt med hjälp av MTS. Och när det gäller ASP-baserade applikationer så erbjuds en högre hastighet, eftersom ADO kod som har implementerats i COM objekt redan är kompilerade – till skillnad från ADO kod som vanligtvis går att finna i ASP sidor. Hur du ska implementera din databearbetande kod bör du ta ställning till efter att du har designat din applikation.

När du designar dina COM objekt så bör du inte sätta några fasta mått genom att använda dess Egenskaper. Försök istället att använda de Metoder som finns att tillgå när det handlar om dina informationsrelaterade uppgifter. Detta är särskilt viktigt att tänka på då du använder dig av MTS eftersom de objekt som har fasta mått sänker MTS förmåga att skala om, vilket i sin tur ökar arbetslasten och skadar din prestanda.

För optimal prestanda bör dina COM objekt kompileras som ”in-process DLLs” (vilket krävs om du kör dem under MTS). Du bör alltid använda en tidig relatering när du placerar ut COM objekt, och du bör skapa dem separat, inte tillsammans.

Hur du optimerar din Transact-SQL kod

Transact-SQL kod erbjuder – precis som andra programmeringsspråk – flera olika sätt att utföra samma uppgift. Som du kanske förstår så erbjuder vissa tekniker bättre prestanda än vad andra gör. I kommande avsnitt så kommer du att få lära dig några Tips och Tricks då du ska skriva Transact-SQL kod som ger hög prestanda.

Välj lämpliga datatyper

Trots att du kanske tycker att det här ämnet borde ha diskuterats under Databas Design, så har jag ändå valt att ta upp det i det här avsnittet, därför att Transact-SQL används till att skapa de fysiska tabellerna som designas i ett tidigt skede under Databasdesignen.

Att välja rätt datatyper kan påverka hur snabbt din SQL Server SELECT (väljer), INSERT (lägger in), UPDATE (uppdaterar) eller DELETE (raderar) dina data, och hur man ska kunna välja den mest optimala datatypen är inte alltid självklar. Här följer några förslag på hur du ska göra då du skapar en fysisk SQL Server tabell, för att hjälpa dig uppnå maximal prestanda.

Välj alltid den minsta datatypen du behöver för att hantera den data som du lagrar i en kolumn. Om du t ex tänker lagra dina data i en kolumn med siffrorna 1 – 10, så räcker det med en TINYINT istället för en vanlig INT. Detsamma gäller CHAR och VARCHAR. Ge inte plats åt fler tecken i kolumnerna än vad du egentligen behöver. Om du kan låta bli det, så låter du din SQL Server lagra fler poster i informationssidorna och indexsidan, vilket reducerar den I/O som krävs för att läsa dem. Det minskar också den mängd data som färdas mellan servern och klienterna, vilket i sin tur reducerar nätverkstrafiken.

Om längden av textdata som kan finnas i en kolumn varierar markant, sätt då datatypen till VARCHAR i stället för CHAR. Trots att VARCHAR kräver lite mer arbete än CHAR, så spar du ändå plats vid situationer som varierande längder på kolumner, och reducerar behovet av I/O, vilket också förbättrar prestandan.

Använd inte NVARCHAR eller NCHAR om du inte måste lagra 16-bitars (Unicode) data. De tar upp dubbelt så mycket plats som VARCHAR och CHAR, vilket ökar lasten markant för din servers I/O.

Om du måste lagra långa textsträngar som dock är mindre än 8 000 tecken, välj då VARCHAR hellre än TEXT. Datatypen TEXT drar gärna på sig en viss arbetslast som i sin tur drar ned på prestandan.

Om du har en kolumn som endast är avsedd för nummer. så bör du använda en numerisk datatyp, så som INTEGER, i stället för VARCHAR eller CHAR. Numeriska datatyper kräver generellt sett mindre plats till att lagra det numeriska värdet, till skillnad från andra teckenbaserade datatyper. Det gör att man reducerar storleken på kolumnen samt höjer prestandan då man antingen söker efter kolumnen (vid t ex WHERE) eller om man har använt JOIN för att koppla ihop den med en annan kolumn.

Använd Triggers varsamt

Triggers kan vara ett kraftfullt verktyg inom Transact-SQL, men eftersom den exekveras varje gång man använder INSERT, UPDATE eller DELETE på en tabell (beroende på hur Triggern är inställd), så kan det skapa en stor mängd arbetslast. Här får du några tips på hur du kan optimera Triggerns prestanda.

Försök ha så lite kod i Triggern som möjligt för att minska lasten. Ju mer kod som finns i Triggern när den exekveras, desto långsammare går anrop som t ex INSERT, UPDATE och DELETE.

Använd inte Triggern till uppgifter som lätt kan skötas av mer effektiva tekniker. Försök t ex inte att tvinga fram ett hänvisande, oberoende ifall SQL Servers inbyggda hänvisande integritet redan finns där för att hjälpa dig uppnå ditt mål. Det samma gäller om du kan välja mellan att använda Triggern eller att använda dig utav en CHECK limitation för att tvinga fram regler eller standarder. Eftersom CHECK limitationen är snabbare så bör du använda den, hellre än Triggern, i detta fall.

På grund av den börda som krävs, så bör du inte låta en Trigger ”gå tillbaka”. I stället för att låta Triggern hitta ett fel och sedan återgå, så bör du själv hitta felet (som förmodligen ligger i koden) innan Triggern har hunnit aktiverats. Om du hittar ett fel i ett tidigt skede (innan Triggern har utlösts) så slösar du mindre av Serverns resurser än vad du skulle ha gjort om du låtit Triggern återgå efter att den utlösts.

Försök inte komma åt mer data än vad du behöver

Trots att det som står i rubriken verkar självklart så är det inte alltid självklart för alla. Det är ett vanligt prestandarelaterat fel som jag finner om och om igen i mina SQL Server baserade applikationer. Här följer några idéer om hur man kan minska mängden data som returneras till klienten.

Returnera inte flera kolumner eller poster till klienten än vad som verkligen behövs. Det här ökar bara disk I/O och nätverkstrafik, av vilka båda skadar prestandan. Använd inte ”SELECT *” i dina SELECT-satser för att returnera posterna utan skriv istället ut dem exakta kolumnerna och posterna som verkligen behövs för just den här SQL-satsen. I många fall – där klienten bara behöver vissa specifika poster för att utföra en särskild uppgift – så kan du reducera antalet poster som returneras genom att skriva in en WHERE klausul i satsen.

Om du i din applikation tillåter användare att köra SQL-satser, men inte finner något sätt att förhindra att hundratals – kanske tusentals onödiga poster returneras, som inte behövs returneras, så borde du överväga att använda en ”TOP” operator i din SELECT-sats. På så sätt kan du begränsa antalet poster som returneras, trots att användaren inte har angett några egna begränsningsvillkor för att reducera det antalet poster som returneras till klienten.

Undvik att använda pekare (Cursors)

Transact-SQL är designad till att jobba bäst på resulterande set, och inte på individuella poster. Det är här som pekare kommer in i bilden, och pekare låter dig bearbeta individuella poster. Det är bara det att bearbetningen av just individuella poster tar lång tid, så när det gäller SQL Server baserade applikationer som kräver hög prestanda, så bör man undvika pekare.

Om du behöver utföra rad-för-rad operationer så bör du leta efter alternativa metoder till göra dem. Det finns alternativ som t ex låter dig göra dessa operationer på klientsidan i stället för på servern, genom att använda temporära databastabeller på servern, eller genom att köra en länkad sub-query.

Tvärr så går det inte alltid att undvika pekare. Skulle du råka ut för en sådan situation då pekare känns oundvikliga, så kanske följande förslag kan hjälpa dig.

SQL Server erbjuder dig många olika sorters pekare, var och en med sin egna karaktäristiska prestanda. Välj den pekare som kräver minst arbetslast, men som har de egenskaperna som krävs för att du ska kunna uppnå ditt mål. Den effektivaste pekaren du kan använda är den snabba pekaren som endast går framåt (forward-only).

När du använder en pekare på servern, försök att använda en som hämtar så små paket (set) som möjligt. Det inkluderar att endast hämta de poster och kolumner som klienten omedelbart måste ha. Ju mindre pekare du använder, oavsett vilken serverpekare du använder, desto mindre resurser kräver den och det förbättrar din prestanda.

När du är klar med en pekare så får du inte bara stänga den (CLOSE), utan du måste också ”lämna tillbaka” den (DEALLOCATE). Det krävs att du ”lämnar tillbaka” den för att befria dem resurser som pekaren krävde utav SQL Server. Om du bara stänger igen pekaren så är förbindelserna fria, men inte resurserna från SQL Server. Och om du inte lämnar tillbaka pekarna så kommer resurserna som håller i pekaren tynga serverns prestanda ända tills de blir fria.

Använd relationerna på ett lämpligt sätt

Tabellrelationer kan vara en stor bidragande faktor till prestandaproblem, speciellt om relationerna omfattar fler än två tabeller eller om tabellerna är väldigt stora. Tyvärr behövs relationer väldigt ofta när det gäller relationsdatabaser. Eftersom relationer är så vanliga, så är det viktigt att du nu tar dig tid och lär dig hur du kan försäkra dig om att dina relationer är så optimala som möjligt. Här följer några tips.

Om du har två eller fler tabeller som ofta är relaterade till varandra, så bör de kolumner som de är kopplade med ha så korrekta index som möjligt. Om de kolumner som används till relationerna inte är naturligt kompakta, så bör du överväga att lägga till surrogatnycklar till tabellerna i fråga, för att reducera storleken på nycklarna. Detta kommer att sänka I/O lasten under relationsprocessen, samt öka prestandan i allmänhet. Du kommer att få lära dig mer om indexing i ett senare avsnitt i artikeln.

För att få ut det bästa av prestandan, så bör posterna i tabellerna som kopplas ihop ha samma datatyper. Och de borde – om möjligt – ha numeriska datatyper hellre än teckenbaserade datatyper.

Undvik att koppla ihop tabeller där posterna innehåller endast få unika värden. Om posten som håller ihop relationerna inte är unik, så kommer SQL Serverns optimerare söka igenom tabellen för relationen, även fast tabellen innehåller ett index. För att uppnå högst prestanda så bör man endast göra relationer utifrån poster med unika index.

Om du regelbundet måste koppla ihop fyra eller fler tabeller för att skapa det Recordset som du behöver, överväg då att denormalisera tabellerna för att reducera antalet relaterade tabeller. Ofta kan du reducera antalet relaterade tabeller genom att flytta på en eller ett par kolumner från en tabell till en annan.

Fånga in din kod i lagrade procedurer

Virtuellt sett borde all din Transact-SQL kod som du använder i dina SQL Server baserade applikationer inte köras som dynamisk SQL eller som skript, utan den borde kapslas in i lagrade procedurer. Detta reducerar inte bara nätverkstrafiken (förutom EXECUTE och CALL som körs över nätverket mellan klienten och SQL Servern) utan den snabbar också på Transact-SQL koden, eftersom koden som finns i de lagrade procedurerna på serversidan redan är förkompilerade. Här följer ett par saker du bör tänka på när du skapar lagrade procedurer.

När en lagrad procedur exekveras för första gången (och då den inte har WITH RECOMPILE inställd) så optimeras den, samtidigt som en SQL plan kompileras och cachas i SQL Serverns minne. Om samma procedur anropas en gång till, så kommer den att använda den cachade SQL planen istället för att skapa en ny, vilket både sparar tid och höjer prestandan. Och den gör det vare sig du vill eller inte. Om SQL-satserna i proceduren är densamma vid varje exekvering, då är det bra, men om SQL-satserna är dynamiska (om t ex WHERE villkoret är olika vid varje exekvering), då är det inte bra, eftersom inte SQL-satsen optimeras vid varje exekvering. Detta kan komma att skada SQL-satsens prestanda.

Om du i förväg vet att SQL-satsen kommer att varieras för varje gång som proceduren exekveras, så kan du när du skapar proceduren lägga till en WITH RECOMPILE inställning. Detta kommer att tvinga fram en omkompilering varje gång som proceduren körs, för att försäkra sig om att SQL-satserna optimeras från körning till körning.

Se till att alltid lägga till kommandot ”SET NOCOUNT ON” när du skapar en procedur. Om du inte gör det så kommer SQL Servern att skicka ett meddelande till klienten i fråga varje gång som SQL-satsen körs, för att visa upp antalet träffade poster. Det är mycket sällan som klienterna behöver den informationen, så med hjälp av kommandot kan du reducera nätverkstrafiken mellan servern och klienten.

Det finns någonting som heter ”Deadlocking” som kan inträffa i en lagrad procedur då två användares processer har låst två separata objekt, och samtidigt försöker låsa det andra objektet, vilket redan är låst av den andra användaren. När detta inträffar så väljer SQL Server automatiskt ut en av processerna och avbryter den, medan den andra kan fortsätta, för att på så sätt avbryta den Deadlocking som uppstår. Den användare, vars process blev avbruten, får ett Errormeddelande, och den avbrutna transaktionen går tillbaka.

För att undvika Deadlocking i din SQL Server applikation, försök att designa din applikation enligt följande
1) Håll applikationens serverobjekt som ska kunna kommas åt, i samma ordning varje gång.
2) Tillåt inte användarinputs under transaktioner. Samla in sådana inputs innan transaktionen börjar.
3) Håll transaktionerna korta och i en enda paket.
4) Om det är möjligt, försök att hålla en så låg isolering som möjligt av den användaranslutning som håller i transaktionen.

Hur du väljer dina Index för att hålla databasens prestanda optimal

Hur man ska kunna välja Index, är för många SQL Server DBAs och utvecklare ett mysterium. Visst, vi vet vad de gör och hur de påverkar prestandan. Men problemet ligger oftast i att välja det ideala Indexet (grupperad eller icke-grupperad), antalet kolumner att Indexera (behöver jag multikolumn Index?) eller vilken kolumn du ska Indexera.

I följande avsnitt kommer vi att se över hur du ska svara på ovanstående frågor. Tyvärr finns det inget specifikt svar för varje tillfälle, utan precis som med SQL Serverns prestandainställningar och optimering, så måste du experimentera dig fram till det ideala Indexet. Så låt oss börja med att titta på några allmänna guidelinjer för att skapa Index. Därefter ska vi titta närmare på om du ska välja grupperade eller icke-grupperade Index.

Går det att ha för många Index?

Ja, några tror verkligen att om man har Indexerat allting så försvinner alla prestandaproblem. Men det fungerar inte riktigt så. Precis som ett Index kan höja åtkomsthastigheten på data, så kan det också reducera åtkomstmöjligheterna ifall det används på ett olämpligt sätt. Problemet med extra Index är att SQL Servern måste hålla kvar dem varje gång man kör en INSERT, UPDATE eller DELETE från en tabell. Att hålla kvar en eller två Index i tabellen påverkar inte lasten särskilt mycket för SQL Servern, men när det börjar komma upp i siffror som fem eller fler Index, då blir det en stor börda för tabellernas prestanda att hålla kvar dem. Generellt sett bör du ha så få Index som möjligt. Det är ofta en balansakt att hitta det ideala antalet Index i en tabell för att finna optimal prestanda.

En bra tumregel är att du inte bör lägga till Index bara för att det verkar vara det enda rätta, utan lägg bara till Index om du vet att de kommer att användas av SQL-satserna som körs mot tabellen i fråga. Om du inte vet vilka SQL-satser som kommer att köras mot tabellen, lägg då inte till några Index tills du tagit reda på det. Det är så lätt att gissa sig till vilka SQL-satser som kommer att köras mot tabellen och därmed skapa ett Index där, för att senare upptäcka att du hade fel. Du måste veta vilken sorts SQL-sats som kommer att köras mot tabellen, för att sedan analysera satsen, så att du kan välja de lämpligaste Indexen att sätta. Därefter måste du sätta dit dina Index och testa dem för att få reda på om de kommer att hjälpa eller inte.

Problemen med att välja rätt Index är särskilt svårt för OLTP applikationer, då de tenderar att behandla höga aktiviteter av INSERT, UPDATE och DELETE satser. Medan du behöver dina Index för att snabbt kunna lokalisera poster som måste bli SELECTED, UPDATED eller DELETED, så vill du inte att alla INSERTs, UPDATEs och DELETEs ska resultera i för mycket arbetslast bara för att du har för många Index. Om du å andra sidan skulle ha en OLAP applikation, vilken virtuellt är read-only, så skulle du kunna sätta Index på hur mycket som helst utan att det tog skada, eftersom OLAP inte kan hantera aktiviteter som INSERT, UPDATE eller DELETE. Som du kan se så spelar det stor roll hur du väljer applikation när det gäller din Indexstrategi.

En annan sak du bör tänka på när du väljer Index är att din SQL Server Query Optimizer kanske inte heller använder sig av de Index som du har valt. Och om inte Query Optimizer använder sig utav dem så är de bara en börda för SQL Server, och de bör tas bort. Så hur kommer det sig att SQL Server Query Optimizer inte använder sig av de satta Indexen när de väl finns där?

Det är i och för sig en alldeles för stor fråga för att jag ska kunna ge ett detaljerat svar på det här, men faktum är att det ibland går snabbare för SQL Server att söka igenom en tabell, än att använda sig utav de satta Indexen när man ska ta ut data. Det finns två anledningar till det. Det första är att tabellen antingen är ganska liten (få poster), och det andra att den kolumn där Indexet är satt är mindre än 95 % unikt. Hur ska du då kunna veta ifall SQL Server kommer att använda sig av det satta Indexet eller inte? Vi kommer att svara på det lite senare i artikeln, då vi behandlar hur du använder SQL Server Query Analyzer.

Tips för när du ska välja grupperade Index

Eftersom du bara kan skapa ett grupperat Index för varje tabell, bör du noggrant överväga hur du ska använda det. Bestäm först vilken typ av SQL-sats som kommer att köras mot tabellen i fråga. Sedan gör du en välriktad gissning om vilken sats du tror är mest kritisk och huruvida den kommer att dra fördel av att ha ett grupperat Index.
Försök att följa följande tumregler när du väljer vilken kolumn som eventuellt bör ha ett grupperat Index.

Primärnyckeln som du har satt för tabellen i fråga bör inte alltid vara ett grupperat Index. Om du skapar en primärnyckel – och inte gör några inställningar – så kommer SQL Server automatiskt att sätta primärnyckeln som ett grupperat Index. Sätt bara primärnyckeln som ett grupperat Index om det möter följande rekommendationer.

• Grupperade Index är idealiskt för SQL-satser som ska ta ut data inom ett visst intervall, eller data som ska sorteras, därför att dina data kommer att vara sorterade i Indexet. Det inkluderar när du t ex använder dig utav BETWEEN, <, >, ORDER BY, GROUP BY; eller när du minskar ner dina data genom att använda MAX, MIN eller COUNT i dina satser.
  
• Grupperade Index är också användbart då du använder satserna för att leta upp ett unikt värde (så som anställningsnummer) eller om du vill återfå det mesta eller all data i ett recordset, därför att SQL-satsen skyddas av Indexet.
  
• Grupperade Index är bra då du använder satser som ska komma åt kolumner med ett begränsat antal distinkta värden, såsom kolumner som innehåller data om länder eller län. Men om en kolumn innehåller värden som bara är lite distinkta, så som Ja/Nej eller Kvinna/Man, så bör du inte Indexera den kolumnen.
  
• Grupperade Index är bra för satser som innehåller JOIN eller GROUP BY klausuler.
  
• Grupperade Index är till sist bra för SQL-satser som ska återge en stor mängd poster, inte bara ett fåtal. Detta beror på att datan är instängd i Indexet och behöver inte letas upp någon annanstans.
  
• Om din tabell ofta får behandla INSERTs så bör du undvika att sätta grupperade Index på kolumner där värdet kan öka; såsom identitet, datum, eller liknande värden. Ett grupperat Index tvingar fram en fysisk sortering av dina data. Så ett grupperat Index i en värdeökande kolumn kan tvinga alla nya data att läggas in på samma sida i tabellen, vilket kan skapa en ”hot-spot” i tabellen, vilket i sin tur kan skapa flaskhalsar i din I/O. Försök i stället att välja en annan kolumn – eller andra kolumner – där du sätter dina grupperade Index.
  

Det som är så frustrerande med ovanstående rekommendationer, är att det ibland kan finnas fler än en kolumn som borde bli grupperade. Men, som vi alla vet, så går det bara att skapa ett grupperat Index per tabell. Det du bör göra är att utvärdera alla möjligheter (då det finns fler än en kolumn som du kan sätta grupperade Index på ) och sen välja den som bäst kommer att påverka prestandan.

Tips för när du ska välja icke-grupperade Index

Hur du väljer ett icke-grupperat Index är avsevärt mycket lättare än att välja ett grupperat Index, eftersom du kan välja så många du finner lämpliga för din tabell. Här följer några tips för vilka kolumner som kan dra fördel av att du sätter ett icke-grupperat Index

Icke-grupperade Index är bra för SQL-satser som returnerar få poster, även en enda post, och där Indexet har bra selektivitet (över 95 %).

Om en kolumn i en tabell inte är åtminstone 95 % unikt, så är det troligast att din SQL Server Query Optimizer inte automatiskt kommer att sätta den kolumnen som ett icke-grupperat Index. På grund av det här så bör du inte själv heller sätta en kolumn som är mindre än 95 % unikt som ett icke-grupperat Index. Ta t ex en kolumn som bara innehåller ”Ja” och ”Nej”, en sådan kolumn lär inte överskrida en 95 % unikhet.

Håll ”bredden” på dina Index så snäva som möjligt, speciellt när du skapar ett komposit (flera kolumner) Index. Det reducerar storleken på Indexet, som i sin tur reducerar kravet på antalet lästa, för att du ska kunna läsa av ditt Index. Och det höjer din prestanda.

Om det är möjligt så bör du skapa Index på kolumner som hellre innehåller numeriska värden än teckenvärden. De numeriska värdena kräver mindre arbetslast än vad teckenvärdena gör.

Om du vet att din applikation kommer att köra samma SQL-sats om och om igen mot samma tabell, så bör du överväga möjligheten att skapa ett täckande Index på tabellen. Ett täckande Index inkluderar de kolumnen som det refereras till från SQL-satsen. Genom att göra det så har du alla data som du kan behöva, vilket gör att SQL Servern inte behöver leta upp datan i tabellen. Detta reducerar både logisk och/eller fysisk I/O. Om ditt Index å andra sidan blir väldigt stor, så ökar det behovet att I/O och det sänker prestandan.

Ett index är bara användbart inom en SQL-sats då din WHERE klausul matchar kolumn(erna) som finns i ditt Index. Ta t ex om du skapar ett kompositindex, av t ex ”Stad, Stat”. Med en SQL-sats som ”WHERE Stad = ’Houston’” så kommer den att använda sig utav Indexet, men med en sats som ”WHERE Stat = ’TX’” så kommer den inte att göra det.

Det som generellt sett gäller är att om din tabell endast behöver ett Index, låt det bli ett grupperat sådant. Om tabellen behöver flera Index, så har du inget annat val än att använda ett icke-grupperat Index. Genom att följa ovanstående rekommendationer så är du väl på väg mot att välja det optimala Indexet för dina tabeller.

Vad ska du göra härnäst?

Den här artikeln innehåller en hel del information, men ändå har jag bara skrapat på ytan när det gäller att optimera din prestanda. Att läsa den här artikeln är ett första steg. Nu är det din tur att ta nästa steg, nämligen att lära dig allt du kan om hur SQL Server fungerar internt och lära dig mer om de verktyg som nämns i artikeln. Kom ihåg att SQL Server Performance Tuning mer är som en konstart än en vetenskap, och endast genom att bemästra grunderna, och genom att experimentera, så kan du komma att bli en expert inom SQL Server Performance Tuning.

0 Kommentarer

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: