Förord

Denna artikel är en god introduktion till hur du skriver optimerade OLTP applikationer för SQL Server.

Innehåll

  » Inledning
  » Den specifika OLTP miljön
  » SQL Server 7.0 och dataskiktet
  » Bestäm OLTP vägen
  » Designa datamodellen
  » Designa lagrade procedurer
  » Konstruktion och Indexstrategi
  » Optimera applikationsdesignen

Implementera SQL Server i OLTP

av Eric Charran

Inledning

Applikationsutvecklingen i företag existerar i en alltmer komplicerad process. De informationsteknologihanterare och utvecklare som arbetar i ett företag som kräver företagsapplikationer, måste granska och implementera lämpliga modelleringsstrategier för applikationer samt lämplig mjukvara, databasteknologi och lösningsmodeller.

Under själva processen för applikationsutvecklingen så är det mycket möjligt att teknologierna, taktikerna och utvecklingsmetoderna kan ändras dramatiskt, på grund av en otalig mängd förändringar inom dessa områden. Organisationens mål är att utveckla en applikation som stöder den aktuella företagsprocessen samtidigt som den använder teknologier och tekniker som förblir framåtkompatibla med nya utvecklingar inom informationsteknikfältet.

En av de första applikationer som ett företag utvecklar är vanligtvis en applikation som förenklar transaktioner inom företaget. En transaktionsapplikation fungerar ofta som en plattform för att kunna hantera företagsinformation från kunder och klienter, där dessa data sedan bearbetas innan det levereras en fördefinierad produkt eller tjänst. En Online Transaction Processing (OLTP) applikationsmiljö kommer definitivt att förenkla utvecklingen och operationen av en sådan applikation.

Den specifika OLTP miljön

OLTP miljön består av åtskilliga lager, där bottenlagret datalagret. Datalagret (eller dataskiktet) innehåller alla data som krävs av organisationens företagsbehov för att kunna genomföra företagstransaktioner.

Dataskiktet kan innefatta arvssystem som innehåller arvsdata i huvudframes, relationsdatabaser i SQL Server eller Oracle, icke-relationsdata (såsom Exchange Server data) likväl som annan data, t ex data som ges från kontosystem mjukvara och andra applikationer.

Företagets logikskikt består av element som ger ut företagsregler och logik till dataskiktet. Företagets logikskikt lagrar företags- och organisationsregler samt procedurer till applikationens transaktioner. Logikskiktet innehåller också teknologier som ska förenkla transaktioner av organisationens affärer, som t ex Exchange Server, Internet Information Server och andra applikationstjänster.

Presentationslagret består av den ”tunna” klientdelen av transaktionsapplikationerna. Presentations- och företagsobjektskiktet, som tidigare användes av klienten tillsammans, existerar nu åtskiljda som ett ”fönster” mot de faktiska företags- och dataskikten. Presentationslagret ger klienten en formaterad vy av de egentliga funktionerna av företags- och dataskikten.

Klienten, som endast finns till som en mekanism för att förenkla samspelet mellan användaren och applikationen, är oftast ett litet eller ”tunt” engagerande som kan implementeras över ett stort antal olika plattformer, inklusive Internet. Teknologier som t ex Active Server Pages (ASP), VBScript, JavaScript och XML är designade till att tillåta implementeringen av en tunn klient med en hög funktionalitet.

SQL Server 7.0 och dataskiktet

SQL Server 7.0 är en integrerad del av dataskiktet, och den är designad till att fungera effektivt i ett antal implementeringsstrategier och lösningar för applikationer. SQL Server existerar som ett multiskikt klient/Server databassystem. I tidigare implementeringar av OLTP modeller så ingick SQL Server i en dubbelskiktig klient/Server miljö. Klientmaskinen körde en exe-baserad tjock klientapplikation som anslöts till SQL Server för att genomföra företagstransaktioner. Den tjocka klientapplikationen innefattade företagslogiken samt koden för att visa en output till användaren.

I en multiskiktig klient/Server miljö så antar SQL Server 7.0 en mer aktiv roll i OLTP processen. Genom att använda ett flertal nya funktioner och teknologier så kan SQL Server 7.0 hjälpa till vid användandet av en distribuerad OLTP applikation och en tunn klient. I en multiskiktig miljö så är företagslogiken lokaliserad på en separat Server, och den tunna klienten är ansvarig för koden som ger användaren en output, och accepterar även input.

Genom användandet av SQL Server funktioner som t ex applikationsroller, Windows NT verifikation samt databasroller och rättigheter, så kan SQL Server hjälpa till vid användandet av en tunn klientapplikation över flera olika plattformer, inklusive distribuerade Internetapplikationer.

Mellanskiktet företagslogikverktyg som t ex Internet Information Server 4.0/5.0 (IIS) och Microsoft Transaction Server (MTS) är integrerade fullt ut med SQL Server 7.0. Denna integration kan också hjälpa till vid skapandet av en tunn klient. Om man håller den här klienten så tunn som möjligt så blir det lättare för utvecklare att använda OLTP behoven till nya och mer lovande teknologier.

En organisations utvecklare kan t ex flytta en klientapplikation som kommunicerar med MTS från en Visual Basic rik klient till ett Visual Studio tjockt klientgränssnitt, genom att använda webbformer som kommunicerar med MTS och SQL Server via IIS i en Internet- eller Intranetdistribuerad miljö.

Bestäm OLTP vägen

At avgöra en implementering av multiskiktmodellen involverar identifiering och utvärdering av flera olika faktorer. Först och främst så måste organisationens företagsmanagement utveckla en vision och en vidd för applikationen, i samarbete med avdelningen för informationsteknologi. När den tekniska utvärderingen av den multiskiktiga OLTP implementeringen väl har påbörjats så måste själva vidden för hur OLTP applikationen ska kunna möta upp företagets behov redan vara avklarat, tillsammans med lämplig dokumentation.

Dessutom så måste företagsreglerna och dokumentationen vara läst och förstådd av alla parter som är involverade i utvecklingen av applikationen. När man väl har klarat av företagsreglerna så kan granskningen av OLTP strategin påbörjas.

Bestäm presentationslagret
Typen av klient som används kommer att fungera som grunden för påminnelsen av OLTP modellens planeringsprocess. En tunn klient är att föredra över en tjock klient, eftersom den är lättare att designa, distribuera och hantera.

Att bestämma klienttyp involverar valet av plattform, på vilken klienten ska byggas. Man kan konstruera tunna klienter med hjälp av Active Server Pages (ASP) tillsammans med VBScript eller Java, och distribuerad i en Internet- eller Intranetmiljö. Klienterna kan också vara Visual Basic executables, Visual Studio klienter eller bli använda av andra teknologier, såsom Cold Fusion.

Typen av tunn klient är vanligtvis en funktion av organisationens utvecklares kunskapskärna. En organisations management kan bestämma att välja bort klientens skapelseprocess till fördel för de önskade funktionerna (t ex; en Internetdistribuerad applikation mot en Visual Basic executable).

Bestäm mellanskiktet
De teknologier som är involverade i mellanskiktet bestäms delvis genom vilken typ av klient som organisationen planerar att använda. Den komplicerade härvan med företagsregler och logik i samband med andra faktorer, bidrar också till utvärdering och godkännande av mellanskiktets teknologier. Faktorer som t ex företagets storlek, nätverkstrafik samt klienthantering och administrationsbehov, deltar allihop till valet av teknologier som ska användas som en del av mellanskiktet.

Teknologier

Microsoft Transaction Server
* Balanserad klientladdning, inklusive distribution och hantering av flera klienttransaktioner
* Hantering av företagsobjekt och regler
* Direkt kommunikation med dataskiktet i SQL Server 7.0

Microsoft Internet Information Server
* Web Application Services, inklusive säker verifikation av klienter (användare)
* Hosting av dynamiskt webbinnehåll, inklusive Active Server applikationer och andra Internetdistribuerade applikationer
* Stöd för transaktionsapplikationer på webben

Bestäm dataskiktet
Den dataplattform som organisationen väljer kommer att bli en funktion av företagets existerade databasteknologier, databasens kapacitet att integrera och sammanföra homogena data från olika källor likväl som personalen som hjälper till i utveckling och underhåll av RDBMS.

SQL Server 7.0’s funktioner sänker den totala kostnaden för ägarskap, den tid som krävs för traditionella DBA aktiviteter samt fyller ut med samling och presentation av data från relations- och ickerelationskällor. Genom att använda funktioner som Data Transformation Services (DTS), OLE DB, samt dess utökade och omdesignade transaktionshanteringssystem, så är SQL Server 7.0 ofta det optimala valet till en organisations OLTP behov.

SQL Server teknologier

• Data Transformation Services (DTS) för ETLen av relations och ickerelationsdata
  
• Reducering av administrativa uppgifter genom ökad dynamisk resursallokering samt självhantering och utökad transaktionshantering
  
• Stöd för en bred variation av APIs, inklusive ADO, OLE DB, DAO, ESQL, T-SQL och XML
  
• Lätt integrering med Microsoft Back Office familjen och Windows NT verifikation
  

Designa datamodellen

Det som karaktäriserar en Online Transaction Processing miljö är den höga volymen av användare och klienter som kräver simultan anslutning. Varje klient kan förmodligen påbörja flera olika datamanipuleringsuttryck (DML) som SQL Server accepterar, loggar, bearbetar och exekverar. Som ett resultat så måste det finnas en fördefinierad modell innan man skapar den fysiska modellen.

Denna modell som skapas av databasutvecklare och administratörer bör med en gång stödja en mångfald av INSERT-, UPDATE- och DELETE-uttryck, vilka hör hemma i en vanlig OLTP applikation. När man skapar den logiska datamodellen så måste alla designers ta med följande i beräkningarna; användarsimultanitet, bindning, hastigheten för returnerande av information samt hastigheten som alla records kan uppdaterad eller skrivas på.

Det karaktäristiska med en OLTP datamodell inkluderar en hög nivå av normalisering. Normalisering av data till relationstabeller optimerar hastigheten av de transaktioner som utförs av OLTP applikationen.

Normalisering
En normalisering av tabellerna optimerar effektiviteten av de transaktioner som utförs i en OLTP miljö. Genom att använda formella metoder för att separera data till flera relaterade tabeller så kan användaraktiviteterna i databaserna bli mycket mer effektiv under produktion.

En avancerad nivå av normalisering kan bli gynnsam till mångfalden av DML uttryck och läsningar i en transaktionsmiljö. Den här förbättringen i prestanda kommer att öka transaktionens konsistens och effektivitet.

Då nivån av normaliseringen ökar så ökar också kompliceringen av datamodellen. Mängden relationer och begränsningar (vilka måste finnas på modellen för att hålla kvar den referentiella integriteten) kan komma att bli lika intensiv och svår att administrera. Dessutom så kan många komplicerade JOINs mellan tabeller börja hålla tillbaka på prestandan. Vilken nivå av normalisering som ska användas bör grundas på en balans mellan metod av normaliseringsform och prestandaövervägningar.

Dataintegritet
OLTP databasdesign bör försöka följa de regler och former runt dataintegritet. När du designar en OLTP modell så bör du använda Entity Integrity, Domain Integrity och Referential Integrity. Genom användandet av checkbegränsningar, primärnycklar, standardvärden i kolumner samt relationsbegränsningar, så kommer modellen att aktivt vidhålla vid dataintegriteten.

Eftersom datamodellen innehåller företagsregler angående dataintegritet så är inte logik (inklusive lämpliga värden för kolumner), relationer eller datavalideringar längre integrerade i klienten. Detta gör att vi kan pressa ihop klienten till en ren presentationsanordning.

Procedurintegritet tillåter även en tunn klient genom att kvarhålla dataintegriteten med hjälp av användandet av lagrade procedurer, triggers och checkbegränsningar. För att försäkra sig om företagsregler på datamodellen så kan man använda dessa SQL Server programmeringsmetoder, och därmed låta klienten exkludera den programmering som krävs för att kunna hantera dessa procedurer.

SQL Serverns standard och regelobjekt är inte ANSI standard, och man bör överväga att de som en mekanism till bakåtkompatibilitet till endast SQL Server 6.5. Kolumnstandarder och checkbegränsningar bör användas istället för dessa ärftliga databasobjekt.

OLTP rapportering
Allteftersom en organisation samlar in data så kommer ett ökat behov av analysering av alla transaktionsdata uppstå, vilka sedan måste tilldelas företagsanvändare och analytiker. Men på grund av en OLTP miljös karaktär (flera användare som samtidigt INSERTar, UPDATEar och DELETEar records) så blir rapportering en mycket resurskrävande process.

Rapportering i en OLTP miljö kommer att leda till extra ”trafik” till datamodellen. Förutom de vanliga DML aktiviteterna så måste nu databasen stödja läsning och val av data till läsningssyften. På grund av den nivå av normalisering som finns i många OLTP modeller så kan flertalet JOINs och tabeller hindra läsprestandan, samt öka de resurser som krävs för rapportgenerering. Samtidigt så sätter läsoperationerna som krävs för rapporterna en låsning på alla records, skapar Index och utför JOINs för att samla ihop den information som krävs. Dessa operationer kommer att påverka hastigheten och effektiviteten av transaktionsoperationerna.

Online Analytical Processing (OLAP) som rapporterar innehållet av en datalagrare kommer effektivt att frigöra de begränsningar som är placerade på ett OLTP schema av rapporteringsbehoven. OLAP eller datalagringslösningar kommer att förhöja läseffektiviteten för informationskonsumerare genom att samla ihop information och konvertera OLTP data till ett stjärnschema. Stjärnschemat är optimerat för läseffektivitet och kan fyllas med information nattetid.

Innehållsdesign
Skapelsen och designen av tabellerna (innehållen) med deras fält (attributen) bör vara en process baserad på möten med företagets användare. Företagets support för applikationen kommer att vara viktig för att få rätt design av tabeller och relationer i OLTP datamodellen. Vi varje steg i en logisk modellering så bör företagsanvändarna ha möjligheten att verifiera och korrigera relationerna och tabellstrukturerna som presenteras av företagets utvecklare.

När man designar innehållet i SQL Server så krävs det en databasutvecklare som har full förståelse för datatyperna och dess konsekvenser vid användande. Vissa datatyper kan användas inom ett brett område av förhållanden, men alla datatyper passar inte in i en designsituation lika lätt som vissa andra.

Specifika datatyper

VARCHAR vs CHAR

• En VARCHAR är en datatyp med en variabel längd som innehåller teckendata. Den skiljer sig från en CHAR eftersom en CHAR är en datatyp med fixerad längd. Med andra ord så håller en VARCHAR (30) upp till 30 tecken, men om fältet lagrar ett värde som är endast 10 tecken långt så tas det bara upp ett utrymme på 10 tecken i SQL Server. En CHAR (30) som lagrar 10 tecken kräver dock ändå ett utrymme från SQL Server som är 30 tecken långt.
  
• VARCHAR bör användas då man förväntar sig att längden av teckendata i ett fält kommer att variera. Datatypen CHAR bör användas då man förväntar sig att alla data kommer att ha samma längd.
  
• Datatyperna NVARCHAR och NCHAR stöder Unicode data (internationella teckendata) och kräver dubbelt så mycket plats som dess icke-Unicode motsvarigheter. Dessa datatyper bör endast användas då man räknar med att lagra Unicode data.
  

INT vs DECIMAL (numeriska)

• Datatypen INT accepterar endast numeriska heltalsdata. Datatypen DECIMAL accepterar numeriska data med definierad skala och precision. Den numeriska datatypen är en synonym till den decimala datatypen, och kommer inte att stödjas i framtida versioner av SQL Server.
  
• Använd datatypen INT där det är lämpligt. Den kräver ett mindre lagringsutrymme än datatypen DECIMAL. Datatypen INT accepterar värden mellan 2 147 483 648 och 2 147 483 647. Men det skulle inte vara lämpligt att använda INT då du ska lagra hemdata, såsom telefonnummer. (Ett telefonnummer på 214-748-3650 eller 2147483650 skulle aldrig accepteras eftersom det överskrider det värde som datatypen INT kan lagra. Använd istället datatypen CHAR om du ska lagra telefonnummer. På det sättet skulle utvecklare och kodare kunna söka efter riktnummer eller andra mönster i telefonnummerfältet.).
  
• Andra frågor angående innehållsdesign innefattar åtskilliga övervägningar som databasutvecklare bör ha med i beräkningarna då de designar den logiska och fysiska modellen. Fältnamnen bör t ex vara logiskt beskrivna och relaterade till sin företagsfunktion och relevans.
  

Fältnamnen bör inte inkludera några tecken förutom alphanumeriska (helst alpha) tecken. Mellanslag, kontrollkaraktärer samt andra symboler leder allihop till en bidragande tillåtelse som utvecklare och kodare måste göra för att anpassa sig till utländska fältnamn.

Dessutom så bör fältnamnen vara korta och koncisa, inte med ett konstigt uttal eller överdrivet långa. På så sätt blir det mycket enklare för utvecklarna och kodarna, som måste använda dessa fältnamn i koden.

Designa lagrade procedurer

Den roll som lagrade procedurer spelar för en företagsapplikation är ovärderlig för hela lösningsprocessen. Alla företagstransaktionsapplikationer bör använda sig av lagrade procedurer till att exekvera läs- och DML uttryck från SQL Server. Även om det mittersta företagslogikskiktet innehåller majoriteten av företagslogiken så bör företagslagret använda sig av lagrade procedurer för att kommunicera direkt med SQL Server. Lagrade procedurer erbjuder en tydlig prestandaförtjänst även om procedurerna ger read-only data till en applikation genom företagslogikskiktet.

Lagrade procedurer är förkompilerade i SQL Serverns procedurcache. När en Serverprodukt för applikation- eller företagsskiktet (t ex MTS) levererar data till SQL Server för att bli behandlade så inträffar inte de vanliga steg som sker hos en ad-hoc SQL-satsbehandling. Sådana steg som syntaxkontroll, objektverifiering och SQL-satsplanens initiering samt granskning och verifiering behöver inte inträffa. Eftersom den lagrade proceduren redan har skapats och kompilerats så har dessa nämnda steg redan klarats av och är nu lagrade i SQL Serverns procedurcache. Exekveringsplanen läses från Cachen och exekveras därefter.

Kommunikation med lagrade procedurer

Data till klienten
Applikationer som använder Active X Data Objects (ADO) eller OLE DB kommer att samspela med de lagrade procedurernas output på en recordsetnivå. Alla läsdata som krävs av applikationen för att visas för användarna bör hämtas genom en lagrad procedur. Applikationen som använder ADO eller OLE DB kommer att tolka resultatet av SELECT-uttrycket i en lagrad procedur som ett recordset. Utvecklare kan sedan använda det recordsetet för att fylla former, rapporter eller andra klientgränssnitt.

Data från klienten
Applikationer kan göra en direkt referens till en lagrad procedur, oberoende av företagslogikskikt. Genom att använda ADO så kan en applikation samla in data från användargränssnittet (ASP-former, VB-former), anropa den lagrade proceduren och sedan skicka vidare formdatan till en lagrad procedur som en ADO parameter. SQL Serverns lagrade procedur accepterar inputparametrarna och exekverar SQL-uppgiften.

Produkter för företagslogikskiktet, såsom MTS, förenklar skapandet av de logiska objekt som klientapplikationen rekommenderar. När klienten rekommenderar en önskad transaktionsfunktion (t ex; Lägg till kund) så anropar klientapplikationen ett MTS objekt som sedan påbörjar kommunikationen med SQL Server enligt klientens önskemål. MTS objektet anropar den lagrade proceduren som sedan exekveras på SQL Server. Returnerande värde från procedurexekveringen kan skickas direkt vidare till klienten.

Lagrad procedurdesign
Korrekt programmering för lagrade procedurer har liknande koncept som när man programmerar T-SQL batches eller T-SQL skript. Innehållet i en lagrad procedur består av vilken T-SQL som helst som kan skrivas med hjälp av en batch eller ett skript. Alla SQL-programmeringstekniker bör implementeras, inklusive fasthållande ANSI kompatibilitet.

Lagrade procedurer kan inte innehålla många Data Definition Language (DDL) uttryck. Några objekt som den lagrade proceduren inte kan skapa när den anropas är vyer, standarder, nya lagrade procedurer eller triggers. Men man kan skapa andra databasobjekt, såsom tabeller eller temporära tabeller. Lagrade procedurer kan också referera till andra lagrade procedurer.

Errorhantering bör vara en integrerad del av en lagrad procedur. Genom att använda errorhanterande tekniker såsom RAISERROR uttrycket så kan T-SQL programmerare inkludera anpassade errormeddelanden som returneras till och tolkas av klienten. Klienten kan sedan presentera de exakta SQL Server errormeddelanden för användaren, eller anpassa de vidare baserat på programmeringen i klientens presentationslager. En integrering av anpassade errormeddelanden i sysmessages tabellen kan göras med hjälp av systemets lagrade procedur sp_addmessage, och det gör det lättare för meddelandehantering och innehåll.

Inputparametrar i en lagrad procedur bör sättas till ett null-värde. Genom att göra det så tillåter den lagrade proceduren ett villkor där klientapplikationen inte skickar vidare alla förväntade inputparametrar. Den lagrade proceduren kan ändå exekvera genom att granska de andra parametrarna.

Namngivning av lagrade procedurer bör följa en logisk och metodisk formel. Precis som när du namnger tabellobjekt så får inte namnen på de lagrade procedurerna innehålla kontrollkaraktärer, mellanslag eller symboler. Du bör också undvika att använda prefixet ’sp_’ i början av namnet eftersom det används till SQL Server systemets lagrade procedurer. Ett mer lämpligt metod skulle kunna vara att börja procedurnamnen med ’p_’ + objektnamnet.

Transaktionsstabilitet
Lagrade procedurer bör inkludera transaktionsstabilitet. Med andra ord, om en företagstransaktion som startas av en klient och går via företagsskiktets Serverobjekt (MTS) till SQL Server kräver flera UPDATEs, DELETEs eller INSERTs till många tabeller för att kunna avslutas, så bör alla de begärda SQL DML uttrycken exekveras. Annars skulle alla de SQL uttryck som definierar transaktionen rullas tillbaka om något katastrofalt skulle inträffa.

Genom att göra så här så kommer de transaktioner som kräver flera DML uttryck att slutföras i sin helhet, eller inte alls. Det här kommer att förhöja dataintegriteten där ett flertal transaktioner påbörjas, hälften av de begärda SQL DML uttrycken slutförs och sedan avbryts den (på grund av för lite ström eller katastrofala mjuk- eller hårdvarufel).

När SQL Server är återhämtat eller då Servern har bootats om och SQL Server tjänsten omstartad så scannas transaktionsloggen efter obundna transaktioner. Om inte transaktionen är bunden till den lagrade proceduren så kommer alla påbörjade SQL DML i transaktionen att rullas tillbaka. Transaktionen måste då exekveras än en gång och fullbordas i sin helhet.

Konstruktion och Indexstrategi

För att kunna öka effektivitet, simultanitet och prestanda i en SQL Server baserad företagsapplikation så krävs det Indexering av tabellerna. Indexen leder till snabbare sökningar, uppdateringar, raderingar och insättningar av records i ett OLTP schema. Genom att använda SQL Serverns Clustrade och icke-Clustrade Index så kan databasutvecklare reducera både tid och de resurser som krävs för att samla ihop data från SQL Server och rapportera de till en klient, samt implementera DML uttryck.

Indexering har en direkt påverkan på den tid som krävs för en fysisk access mot poster i en specifik tabell. Själva Indexeringsstrukturen finns som en separat balanserad trädstruktur (B-Tree) på hårddisken. Ett Clustrat Index gör en fysisk omstrukturering av records i en given tabell för att anpassa de till Indexstrukturen. Som ett resultat av detta så kan det bara finnas ett enda Clustrat Index per tabell.

Ett icke-Clustrat Index ändrar inte den fysiska ordningen av poster i en tabell, utan innehåller istället en pekare mot alla tabelldata direkt från dess organiserade B-Tree struktur. Om det finns ett icke-Clustrat Index på en tabell som har ett Clustrat Index så kommer det icke-Clustrade Indexet att använda det Clustrade Indexet som sin B-Tree lövnivå. På så sätt så kommer alla värden som söks i ett fält med ett icke-Clustrade Indexet att finnas med hjälp av det Clustrade Indexet. Om det istället finns ett icke-Clustrat Index på en tabell utan något Clustrat Index så kommer B-Tree roten av det icke-Clustrade Indexet att existera som en pekare mot postidentifieraren (RID).

Det är viktigt att databasutvecklare och applikationsdesigners förstår att Indexstrategins utveckling och implementering är en pågående process. Efter att den påbörjade test- och produktionsimplementeringen av företagsapplikationen har skett så måste utvecklare och databasadministratörer granska produktionsdatabasens aktivitet.

Genom att använda verktyg som SQL Server Performance Monitor Counters och SQL Server Profiler så bör utvecklarna ha en ganska god känsla för var hotspots i OLTP schemat inträffar och var man bör implementera Index. Och genom att använda SQL Server Index Tuning Wizard så kan utvecklarna låta SQL Servern analysera produktionsdatabasens aktivitet samt rekommendera Index från en sample tracefil.

Indexbestämmare
Databasutvecklare bör genomföra en noggrann granskning av den logiska modellen innan man implementerar den till produktion, för att på så sätt kunna avgöra den optimala Indexkonfigurationen baserat på en analys av förutsedda databas-”hotspots”. Man kan eliminera hotspots (centreringar av databasaktivitet med anledning av hur transaktionerna sker i applikationen) genom korrekt databasdesign. Men på grund av hur transaktioner sker i ett företag så kan man inte alltid reducera eller radera sådana hotspots. Indexering representerar en alternativ metod till att lösa upp hotspots, men en effektiv databasdesign är alltid den primära metoden för att reducera intensiv lokaliserad databasaktivitet.

På grund av en OLTP miljös karaktär så kommer en stor del av aktiviteten vara centrerad på söktabellerna. Även DML uttryck måste söka igenom tabeller för specifika värden då en WHERE klausul har angetts. På grund av de typiska komplicerade relationer som existerar mellan innehållet i en applikationsbaserad datamodell så kan Indexering optimera både läs- och skrivtiden.

Ett Index bör tillämpas där det behövs en snabb postsökning. Eftersom en OLTP miljö innefattar en hel del relationer så kommer många av dessa sökningar ske via primärnycklar och externa nycklar. Dessa nyckelfält bör vara Integervärden (datatypen INT) och är de primära kandidaterna till Index. Ett Clustrat Index passar bäst på en primärnyckel, vilken är den som oftast används till att söka upp värden. Icke-Clustrade Index kan förhöja JOIN prestandan om den appliceras på en extern nyckelkolumn.

Indexen härleder sin storlek baserat på storleken av de data som finns i kolumnen. Storleken på ett Clustrat Index bör hållas så snäv som möjligt, först och främst därför att de andra icke-Clustrade Indexen i samma tabell använder det Clustrade Indexet som sina slutgiltiga lövnoder.

Optimera applikationsdesignen

Designen och funktionaliteten av applikationskoden beror traditionellt sett på applikationsutvecklaren. Men själva rollen som SQL Server utvecklare/administratör bör också inkludera kommunikation av vissa nyckelkoncept för att försäkra sig om optimering och funktionalitet av applikationen. Under hela applikationsutvecklingen och testprocessen så bör man försöka uppnå vissa mål för applikationsdesignen och gränssnittet med SQL Server.

Eliminera onödig nätverkstrafik
Genom att använda lagrade procedurer så kan man reducera nätverkstrafiken. Och genom att reducera antalet gånger som en applikation eller ett företagsobjekt kommunicerar med SQL Server i en eller flera transaktioner så kommer applikationsprestandan att höjas markant.

Lagrade procedurer som innehåller flertalet uttryck skickar tillbaka meddelanden till den anropande applikationen som standard. Resultatet blir att varje gång som ett SQL-uttryck exekveras framgångsrikt genom en lagrad procedur så måste SQL Server kommunicera med applikationen i fråga för att meddela att exekveringen av SQL-uttrycket gick bra. I sådana fall där applikationen inte behöver få någon utvärdering från varje framgångsrika uttryck så kan du stänga av den här funktionen genom att sätta alternativet SET NOCOUNT till ’on’ i deklarationssektionen av den lagrade proceduren.

Använd små resultatset
I applikationer där returnerandet av data till klienten kräver en sökning genom stora resultatset så är det viktigt att utvecklaren förstår konsekvenserna av att returnera stora resultatset från SQL Server. En applikation som kräver stora resultatset från SQL Server kommer självklart att begränsa användar- och presentationslagrets skalbarhet. Dessutom så kommer det externa användandet av applikationen att begränsas markant. Server I/O, resurser och nätverkstrafiken kommer också att öka på grund av den mängd data som sänds till klienten via SQL Server. Utvecklarna bör aktivera presentationslagret till att ge användaren tillräckligt med information för att minska storleken på resultatsetet som skickas tillbaka till klienten.

Metoder för att ställa in SQL-sats timouts och avbryt
Att sätta timout på SQL-satser som kör väldigt länge kommer också att öka nivån av effektivitet vid communityn med många användare. Långvariga SQL-satser eller applikationsförfrågningar från klienter kan degradera en SQL Servers prestanda på grund av mängden resurser och minne som krävs. Genom att identifiera långvariga SQL-satser (med hjälp av SQL Server Profiler) samt att låta klientens API avbryta långvariga SQL-satser så kan man kvarhålla Serverns prestandastabilitet.

Man kan också hålla kvar Servern prestandastabilitet genom att låta klienten använda en avbrytarmetod vid långvariga SQL-satser. Men om man avbryter en SQL-sats mitt under en företagstransaktion så släpper man inte på tabellåsningarna. Dessutom så måste man om man avbryter en transaktion låta lämpliga procedurer rulla tillbaka de redan avklarade transaktionerna.

Undvik pekare
Man kan dra vissa paralleller mellan en klientsidan ADO recordset och en SQL Server pekare. Båda objekten involverar behandling på postnivå. SQL Server pekare visar på prestandarelaterade problem om dem används i en distribuerad applikation. Om det krävs en behandling på postnivå så ska det ske på klienten. SQL Server uppgifter bör begränsas till en set-orienterad behandling och returnering av data. Eftersom SQL Server håller låsningar på postnivå på pekardata så kommer både simultaniteten och applikationens prestanda att drabbas. SQL Server pekare bör alltid undvikas, speciellt då en transaktion kräver uppdateringar, raderingar eller inläggningar av data. Genom att exekvera någon av dessa uttryck med hjälp av en pekare så kommer varje uttryck att exekveras baserat på postnivån, vilket ökar de erhållna låsningarna, nätverkstrafiken samt klientens anslutningstid.

Undvik Deadlocking och blockering
När som helst som en klientapplikation måste exekvera en serie av komplicerade databasoperationer så är det viktigt att granska längden på den SQL Server transaktion som ska utföra dessa operationer. De SQL Server transaktioner som är definierade inom en lagrad procedur bör existera som en logisk grupp av operationer. Långvariga transaktioner som erhåller låsningar på databasobjekten ökar oddsen för att en låsning kommer att uppstå. En instans av den klientapplikation som exekverar en långvarig transaktion kan hålla låsningar på objekt som krävs av en annan instans. Den andra instansen kan då blockeras (beroende på operationen).

Mindre, mer effektiva transaktioner, kan reducera uppkomsten av serverblockeringar och deadlocking, likväl som nätverkstrafik. Applikationsdesigners bör låta klienten avbryta då det önskas.

Sträva efter en statuslös applikation
Applikationens effektivitet kan också avgöras genom dess statusnivå. En applikations statusnivå kan avgöras genom frekvensen och längden på anslutningarna till dataskiktet. Applikationer som håller en status håller också en konstant anslutning till dataskiktet. På liknande sätt så minskar dessa applikationer effektiviteten genom att öka nätverkstrafiken, låsningar på databasobjekt och serverbelastning. Men applikationer med låg statusnivå ansluter bara till databasen för att initiera lagrade procedurer och för att erhålla data från dem. Efter varje databasoperation så kommer en statuslös applikation att stänga av anslutningen till dataskiktet, för att endast ansluta igen då det behövs.

När en applikations lösningsstrategi använder ett mellanskikt såsom MTS, så kommer mellanskiktet att hantera all anslutning till databasen. Det är då viktigt att försäkra sig om att även mellanskiktet strävar efter en statuslös nivå.

Använd applikationsroller
Applikationsrollerna i SQL Server 7.0 tillåter att rättigheter kan definieras baserat på den anslutande applikationen, och inte på användaren. I en Windows NT miljö så är det typiskt att applikationen ansluter direkt till dataskiktet genom ett mellanskikt som t ex IIS verifierade användare baserat på dess NT credentials. Användarens NT credentials granskas då de verifieras till IIS, och än en gång då de verifieras till SQL Server (om applikationen och IIS är designade till att inte använda anonym access). Genom att använda applikationsroller så kommer SQL Server att granska användarens NT credentials för att avgöra om användaren kan ansluta till SQL Servern. Applikationen anropar då systemets lagrade procedur sp_setapprole för att aktivera SQL Serverns applikationsroll. Då upphävs användarens NT rättigheter baserat på deras NT credentials, och istället aktiveras de databasrättigheter som är tilldelade till applikationsrollen.

Applikationsroller har en direkt kontroll på användarens aktiviteter i databasen medan applikationsrollen är aktiverad. Men i en miljö som använder ett annorlunda mellanskikt för som kontrollerar belastningsbalans och databasaktiviteter såsom MTS så behöver man inte alltid applikationsroller. MTS kontrollerar all databasaktivitet och applikationen gör anrop till MTS objekten (t ex COM, COM+ eller DCOM). Dessa MTS objekt håller sedan en status i relation till databasen. MTS använder också roller för att avgöra vilka användare som kan anropa specifika MTS objekt. Resultatet blir att applikationsroller blir onödiga eftersom mellanskiktet kontrollerar all aktivitet i dataskiktet.

0 Kommentarer

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: