Moving genomsnittet sql

SQL Server IO Performance Allt du behöver tänka på SQL Server IO-prestanda är avgörande för övergripande prestanda. Åtkomst till data på disken är mycket långsammare än i minnet, så det är viktigt att få ut mesta möjliga av lokal disk och SAN. Det finns mycket råd på webben och i böcker om SQL Server IO-prestanda, men jag har inte hittat en enda källa som innehåller allt att överväga. Detta är mitt försök att samla all information på ett ställe. Så här är en lista över allt jag kan tänka på som kan påverka IO-prestanda. Jag har beställt det som börjar på fysiska diskar och flyttar upp ledningen till servern och slutligen kod - och databasschemat. Misslyckad disk När en enhet misslyckas i en diskmatris måste den bytas ut. Inverkan på prestanda före utbyte beror på lagringsgrupp och RAID-konfiguration som används. RAID 5 och RAID 6 använder distribuerad paritet, och denna paritet används för att beräkna läsningen när en disk misslyckas. Läs prestanda förlorar fördelen av att läsa från flera diskar. Detta är också sant, även om det i mindre utsträckning, på RAID 1 (speglade) arrays. Läser förlorar fördelen av att läsa från flera ränder för data på den misslyckade disken, och skrivningar kan vara något långsammare på grund av ökningen i genomsnittlig söktid. Skriv cache När en transaktion har åtagits måste skrivningen till transaktionsloggen slutföras innan transaktionen är märkt som engagerad. Detta är viktigt för att säkerställa transaktionsintegritet. Det brukade vara att skrivcache inte rekommenderades, men många av de senaste lagringsarrayerna har batteribackade cachar som är fullt certifierade för användning med SQL Server. Om du har möjlighet att ändra minnesfördelningen mellan läs och skriv cache, försök att allokera så mycket som möjligt till skrivcachen. Detta beror på att SQL Server utför sin egen läsning av buffert via buffertpoolen, så att någon ytterligare läsbuffert på diskkontrollen inte har någon fördel. Tunn tillhandahållande Tunn tillhandahållande är en teknik som tillhandahålls av vissa SAN, varigenom den faktiska disklagringen som används är tillräckligt för datan, medan servern ser ut som full storlek, med massor av ledigt utrymme. Om den totala disken som allokeras till alla servrar överstiger mängden fysisk lagring är detta känt som över-provisioning. Vissa SAN-leverantörer försöker hävda att prestanda inte påverkas, men det är inte alltid sant. Jag såg denna fråga nyligen på en 3PAR-grupp. Sekventiella läsningar var signifikant långsammare på tunna provade LUN. Växling till tjocka avlagda LUN-skivor mer än fördubblades i den sekventiella läsningstiden. Var är skivorna Är de där du tror att de är Det är fullt möjligt att ansluta till ett lagringsutrymme, men för IO-förfrågningarna att passera genom den här matrisen till en annan. Detta görs ibland som ett billigt sätt att öka diskutrymmet - med hjälp av befintlig hårdvara som underutnyttjas är det billigare att köpa fler diskar. Problemet är att det här introducerar ytterligare en komponent i banan och skadar prestanda - och DBA kanske inte ens är medveten om det. Se till att du vet hur SAN är konfigurerad. Smart Tiering Detta heter olika saker av olika leverantörer. Lagringsmatrisen kommer att bestå av två eller flera typer av skivor med varierande prestanda och kostnad. Det finns de långsammare 10K-skivorna - dessa är de billigaste. Då har du 15K-skivorna. Dessa är snabbare men dyrare. Och då kan det finnas några super-snabba SSD-er. Dessa är ännu dyrare, även om priset kommer ner. Smart tiering migrerar data mellan nivåer så att mer vanligt tillgängliga data finns på den snabbare lagringen medan mindre vanligt förekommande data sjunker ner till den långsammare lagringen. Det här är ok i princip, men du är DBA. Du borde redan veta vilka data som behöver nås snabbt och som kan vara långsammare. Vill du verkligen ha en algoritm som gör det här beslutet för dig Och regelbundna underhållsuppgifter kan förvirra hela grejen ändå. Tänk på en belastning med indexåteruppbyggnader som går över natten. Låt oss anta att den sista databasen som ska behandlas är en arkivdatabas - vill du att det här ska vara att svänga SSD när användarna loggar in första gången på morgonen, medan missionskritiska databasen släpar ner i bottenkoden. Detta är en förenkling, självklart. Tieringsalgoritmerna är mer sofistikerade än det, men min punkt står. Du bör bestämma prioriteringarna för din SQL Server-data. Låt inte SAN-leverantörerna (eller lagringsadministratörer) övertyga dig annars. Lagringsnivåreplikation Lagringsnivåreplikation är en katastrofåterställningsfunktion som kopierar blocknivådata från primär SAN till en annan - som ofta finns i ett separat datacenter. SAN-leverantörerna hävdar ingen inverkan på prestanda, och det är sant om det är korrekt konfigurerat. Men jag har sett dåligt konfigurerad replikering har en allvarlig inverkan på prestanda. En klient led ett par år med dålig IO-prestanda. När jag gick med dem frågade jag om lagringsreplikationen var ansvarig. Jag fick höra att jag inte var så dum - leverantören har kontrollerat och det är inte problemet - det måste vara SQL Server själv Ett par månader senare blev jag kontaktad igen - de hade stängt av replikationen under pågående flyttning till en ny datacenter och gissa vilken skriv latens förbättrad med en storleksordning. Låt mig upprepa att detta berodde på dålig konfiguration och de flesta lagringsreplikationer påverkar inte prestanda märkbart. Men det är en annan sak att tänka på om du kämpar med SQL Server IO-prestanda. Host Bus Adapters Kontrollera att SAN och HBA-firmware är kompatibla. Ibland när en SAN uppgraderas förbises HBA på servrarna. Detta kan leda till oregelbundna fel eller till och med göra lagringen oåtkomlig. Ta en titt på HBA-ködjupet. En vanlig standard är 32, vilket kanske inte är optimalt. Vissa studier har visat att en ökning av detta till 64 eller högre kan förbättra prestanda. Det kan också göra det värre, beroende på arbetsbelastning, SAN-tillverkning och modell, diskutförande etc. Så testa noggrant om du kan. Vissa lagringsadministratörer avskräcker från att modifiera HBA-ködjupet eftersom de tror att alla vill ha samma på sina servrar och lagringsutrustningen kommer att översvämmas. Och de är rätt också. Övertyga dem att det bara är för dig. Löfte att inte berätta för någon annan. Vad som helst. Få bara ditt extra ködjup om du tycker att det kommer att gynna prestanda. För många servrar När ett företag lyfter ut en liten förmögenhet på ett lagringsnätverk, vill de få valuta för pengarna. Så naturligtvis kommer varje ny server som följer med att bli ansluten så att den kan utnyttja allt det här fina diskutrymmet. Det här är bra tills ett par servrar börjar utfärda många IO-förfrågningar och andra användare klagar över en prestandaförlängning. Det här ser jag upprepade gånger på så många kunder, och det finns ingen enkel lösning. Företaget vill inte eller har råd att köpa en annan SAN. Om du tror att det här är ett problem för dig, lägg ett schema tillsammans med alla jobb - över alla servrar - och försök att omplanera några så att arbetsbelastningen fördelas jämnare. Partition Alignment och formatering Jag kommer kortfattat att nämna partitioninriktning, även om Windows 2008 använder en standardförskjutning på 1 MB så det här är mindre av ett problem än det brukade vara. Jag är inte heller övertygad om att många moderna SAN har stor nytta av praktiken. Jag utförde ett test på en EVA för några år sedan och fann bara en 2 förbättring. Ändå är några procent fortfarande värt att sträva efter. Tyvärr måste du riva ner dina volymer och återskapa dina partitioner om det här ska fixas på ett befintligt system. Detta är förmodligen inte besväret om du inte strävar efter varje sista tum av prestanda. Formatering är något annat som ska utföras på rätt sätt. SQL Server lagrar data i 8KB-sidor, men dessa hämtas i block med 8, kallade extensions. Om skivorna är formaterade med 64 KB fördelningsenheter kan detta ha en betydande prestanda. Multipathing Om du inte använder lokal disk ska du ha någon redundans inbyggd i ditt lagringssystem. Om du har en SAN har du ett komplicerat nätverk av HBA, tyg, switchar och kontroller mellan SQL Server och diskarna. Det ska finnas minst två HBA, växlar, etc. och dessa ska alla anslutas på ett sådant sätt att det finns flera vägar till skivorna. Denna redundans är främst för hög tillgänglighet, men om multipathing har konfigurerats som aktiva kan du också se prestandafördelar. Nätverksansluten lagring Eftersom SQL Server 2008 R2 har det varit möjligt att skapa, återställa eller bifoga en databas på en fildelning. Detta har ett antal möjliga användningsområden, och i synnerhet för devtest miljöer kan det göra kapacitetshanteringen enklare och flytta databaser mellan servrar mycket snabbare. Frågan som ställs om är dock att du vill ha det här i productionquot Prestanda blir inte lika bra som lokala eller SAN-enheter. Det finns ytterligare komponenter i kedjan, så pålitligheten kanske inte är lika bra. Och med hjälp av nätverket använder din data samma infrastruktur som all annan TCPIP-trafik, vilket igen kan påverka prestanda. Men det är bra nyheter Medan tillgänglighet fortfarande är oro, har förbättringar i SMB på Windows Server 2012 (och via en uppdatering till WIndows Server 2008 R2) gjort det betydligt snabbare. Jag såg ett citat från en Microsoft-anställd någonstans som hävdade 97 av prestanda för lokal lagring. Jag kan inte hitta citatet nu, och jag kommer inte ihåg om han mätte latens eller genomströmning. Diskfragmentering Hur ofta använder du Diskdefragmenteringsverktyget på din dator för att analysera och defragmentera din C: - drivrutin Hur ofta kontrollerar du fragmentering på diskarna på dina SQL-servrar För de flesta som inte är så nära så ofta, Ill-satsning. Ändå är volymfragmentering lika skadlig för SQL Server-prestanda som för din dator. Du kan minska sannolikheten för diskfragmentering på ett antal sätt: Förstorleksdata och loggfiler istället för att förlita sig på automatisk tillväxt. Ställ in auto-tillväxtsteg till förnuftiga värden istället för standard. 10 Undvik krympningsdata och loggfiler. Aldrig, Använd alltid autoshrink-databasalternativet Se till att skivor är dedikerade till SQL Server och inte delas med andra program. Du kan kontrollera fragmentering med samma verktyg som på datorn. Diskdefragmenteraren är tillgänglig på alla serverns versioner av Windows. Ett annat sätt att kontrollera är via Win32Volume-klassen i WMI. Denna bit av PowerShell rapporterar fragmenteringen av filprocenten för alla volymer på en given server. Om du har betydande fragmentering finns det ett par sätt att fixa det. Mitt föredragna alternativ är som följer, men kräver lite driftstopp. Stoppa SQL-tjänsterna Säkerhetskopiera filerna på disken (speciellt MDF, NFD och LFF-filer - Säkerare än förlåt) Kör verktyget för Windows-diskdefragmentering Starta SQL-tjänsterna Kontrollera felloggen om du inte vill se några fel vid start. Kör CHECKDB mot alla databaser ( utom tempdb). Ive har aldrig sett defragverktyget orsakad korruption, men du kan inte vara för försiktig Ett annat alternativ som inte kräver nedetid är att använda ett tredjepartsverktyg som Diskeeper. Detta kan vara mycket effektivt vid fixering och förebyggande av diskfragmentering, men det kostar pengar och använder en filterdrivrutin - se mina kommentarer nedan. Filter drivrutiner En filterdrivrutin är en mjukvara som sitter mellan en IO-förfrågan och skriv till disken. Det låter skrivningen granskas och avvisas, ändras eller revideras. Den vanligaste typen av filterdrivrutin installeras av antivirusprogram. Du vill inte att antivirusprogram kontrollerar varje enskild skriv till dina databasfiler. Du vill inte heller att det kontrollerar dina säkerhetskopior antingen, eller skriver till felloggen eller standardspår. Om du har installerat AV-program kan du ange undantag. Uteslut alla mappar som används av SQL Server, plus de enheter som används av data och loggfiler, samt de mappar som används för säkerhetskopiering. Ännu bättre är att stänga av online-AV-kontroll och schemalägga en genomsökning på tyst tid. OLTP och BI på samma server Det är sällsynt att hitta ett system som är rent OLTP. De flesta kommer också att ha något slags rapporteringselement. Tyvärr samarbetar de två typerna av arbetsbelastning inte alltid lyckligt. Jag har läst många artiklar av Joe Chang, och i en artikel förklarar han varför detta är fallet. I grunden hämtar OLTP-sökplaner rader i små satser (mindre än ett tröskelvärde på 25 rader) och dessa IO-förfrågningar hanteras synkront av databasmotorn, vilket innebär att de väntar på att data hämtas innan de fortsätter. Stora BI-arbetsbelastningar och rapporteringsfrågor, ofta med parallella planer, utfärdar asynkrona IO-förfrågningar och utnyttjar HBA-förmågan att köförfrågningar fullt ut. Som ett resultat måste OLTP-förfrågningarna köras bakom BI-förfrågningarna, vilket orsakar att OLTP-prestanda försämras avsevärt. Automatisk växning och Instant File Initialization Det är bra att ha automatisk växling aktiverad, bara som en försiktighet, även om du också bör förforma data och loggfiler så att det sällan behövs. Men vad händer om en datafil växer och du inte har direktfilinitiering aktiverad speciellt om auto-växa är inställd för stor. Alla IO mot filen måste vänta på att filtillväxten ska slutföras, och detta kan rapporteras i de ökända kvoterna som tagits längre än 15 sekunder till completequot-meddelandet i felsökningen. Instant initialisering hjälper inte med loggtillväxt, så se till att loggarna för automatisk tillväxt inte är för höga. För mer information om instart av direktfil och hur du aktiverar det, se den här länken Databasinitialisering. Och när det gäller auto-växa, se avsnittet om proportionell fyllning, nedan. Transaktionslogg Prestanda Hur lång tid skriver din transaktionslogg Mindre än 1 ms Mer än 5 ms Titta på virtuell filstatistik, prestandaläger eller WRITELOG väntetiden för att se om loggens skrivfördröjning är ett problem för dig. Skrifter till transaktionsloggen är sekventiella, så skrivhuvudet på skivan borde helst vara var det var från den sista loggskrivningen. Det betyder ingen söktid och snabbt snabba skrivtider. Och eftersom en transaktion inte kan begå tills loggen har härdat till disk, lita du på dessa snabba skrivningar för ett effektivt system. Rådgivning i åratal har varit för transaktionsloggen för varje databas att vara på egen disk. Och detta råd är fortfarande bra för lokal disk, och för vissa lagringsarrayer. Men nu när många SAN-enheter har sitt eget batteribackade skrivcache, är detta råd inte lika viktigt som det brukade vara. Under förutsättning att cacheminnet är tillräckligt stort för att klara toppbrott av skrivaktivitet (och se mina tidigare kommentarer om att tilldela mer cache att skriva än att läsa) får du mycket låg latens. Så vad händer om du inte har lyx på en mega-dollar SAN och massor av skrivcache? Det råd som har funnits sedan 1990-talet är fortfarande giltigt: En transaktionsloggfil per databas på egen disk RAID 1, RAID 10 eller RAID 01 Så om du antar att du är nöjd med din loggfillayout, vad som annars kan sakta ner din log skriver virtuella loggfiler. Även om en transaktionslogg skrivs i följd kan själva filen bli fragmenterad internt. När den är skapad består den av flera bitar som heter virtuella loggfiler. Varje gång det växer, antingen manuellt eller automatiskt, läggs flera fler virtuella loggfiler. En transaktionsloggen som växer flera gånger kan hamna med tusentals virtuella loggfiler. Att ha för många VLF kan sakta ner loggningen och kan också sakta ner loggarna. Du måste också vara försiktig med att undvika VLF som är för stora. En inaktiv virtuell loggfil rensas inte förrän slutet är uppnått och nästa börjar användas. För fullständig återställningsmodell händer detta inte förrän nästa loggbackup. Så en logg backup kommer plötsligt ha mycket mer arbete till, och kan orsaka prestanda problem medan det äger rum. Svaret på en stor transaktionslogg är att ange en initial storlek på högst 8000 MB, och sedan växa manuellt i bitar på 8000 MB upp till målstorleken. Detta resulterar i en maximal VLF-storlek på 512 MB, utan att skapa ett alltför stort antal VLF. Obs! Detta råd är endast för manuell tillväxt. Växa inte automatiskt med 8000MB Alla transaktioner i databasen slutar medan extrautrymmet initialiseras. Autogrow bör vara mycket mindre - men försök att manuellt ställa in filen så att det inte är troligt att automatisk växa. Loghanteringsgränser Databasmotorn ställer in gränser för hur mycket logg som kan vara i flygning vid en viss tidpunkt. Detta är en gräns per databas, och beror på vilken version av SQL Server som används. SQL Server begränsar antalet utestående IO och MB per sekund. Gränserna varierar med version och om 32 bitar eller 64 bitar. Se Diagnosera Transaktionslogg Prestandafrågor och gränser för Loghanteraren för mer information. Det är därför skrivfördröjningen ska vara så låg som möjligt. Om det tar 20ms att skriva till transaktionsloggen, och du är begränsad till 32 IO i flygning åt gången, betyder det högst 1600 transaktioner per sekund, långt under vad många OLTP-databaser kräver. Detta understryker också vikten av att hålla transaktionsstorlekarna lilla eftersom en mycket stor transaktion skulle kunna tänkas hålla upp andra transaktioner när den begås. Om du tror att dessa gränser påverkar loggprestanda i dina databaser finns det flera sätt att ta itu med problemet: Arbeta med att öka loggprestanda Om du har minimal loggad verksamhet kan du byta databas för att använda BULK LOGGED-återställningsmodellen. Försiktig, dock måste en säkerhetskopiering som innehåller en minimalt loggad operation återställas i sin helhet. Punkt i återställning av tid är inte möjlig. Dela en högvolymdatabas i 2 eller flera databaser, eftersom logggränserna gäller per databas, icke-sekventiell loggaktivitet. Det finns åtgärder som utförs av databasmotorn som flyttar skrivhuvudet bort från slutet av loggfilen. Om transaktioner fortfarande är förbundna medan detta händer, har du ett försök att ställa upp och loggen blir sämre. Operationer som läser från loggfilerna inkluderar återlämning av stora transaktioner, loggar säkerhetskopieringar och replikering (loggläsaren). Det finns lite som du kan göra om de flesta av dessa, men att undvika stora återgångar är något som bör åtgärdas vid design och utveckling av en ansökan. Proportional Fill Mycket aktiva tabeller kan placeras i en filgrupp med flera datafiler. Detta kan förbättra läsprestanda om de finns på olika fysiska diskar, och det kan förbättra skrivresultatet genom att begränsa innehållet i allokationssidorna (speciellt sant för tempdb). Du förlorar dock några fördelar, om du inte utnyttjar den proportionella fyllningsalgoritmen. Proportionell fyllning är den process genom vilken databasen försöker fördela nya sidor i proportion till hur mycket ledig plats i varje datafil i filgruppen. För att få maximal nytta se till att varje fil har samma storlek och växer alltid med samma steg. Detta gäller både manuell och automatisk tillväxt. En sak att vara medveten om är hur den automatiska tillväxten fungerar. SQL Server gör sitt bästa för att fylla i filerna i samma takt, men man fyller alltid upp precis innan de andra, och den här filen kommer då automatiskt växa på egen hand. Detta får sedan fler nya sidallokeringar än de andra och blir en tillfällig hotspot tills de andra också växer automatiskt och hinna. Det här är osannolikt att det kommer att orsaka problem för de flesta databaser, men för tempdb kan det vara mer märkbart. Spårflagg 1117 gör att alla datafiler i en filgrupp växer tillsammans, så det är värt att överväga om det här är ett problem för dig. Personligen skulle jag helst manuellt ändra filerna så att automatisk tillväxt inte behövs. tempdb-konfiguration Låt oss börja med några saker som alla är överens om: Tempdb-filer ska placeras på det snabbaste lagret som finns tillgängligt. Lokal SSD är idealisk, och från SQL Server 2012 är det till och med möjligt i ett kluster Förstorlek data och loggfiler, eftersom automatisk tillväxt kan orsaka prestandafrågor när det inträffar. Nya temporära objekt skapas hela tiden, så strid i GAM , SGAM och PFS-sidor kan vara ett problem i vissa miljöer och nu några meningsskiljaktigheter: Det finns massor av råd över hela webben för att skapa en tempdb-datafil per kärna för att minska tilldelningsbeslutet. Paul Randall håller inte med (En SQL Server DBA myt om dagen: (1230) tempdb ska alltid ha en datafil per processorkärna). Han säger att för många filer faktiskt kan göra saker värre. Hans lösning är att skapa färre filer och bara öka om det behövs. Det finns flera råd, ofta upprepade, för att skilja tempdb-filer från andra databaser och lägga dem på sina egna fysiska spindlar. Joe Chang håller inte med och har ett mycket bra argument för att använda den gemensamma disken. (Placering av data, Log och Temp-fil). Jag låter dig bestämma vad jag ska göra AutoShrink AutoShrink-databasalternativet har funnits sedan jag började använda SQL Server, vilket medför många prestandaproblem för personer som har aktiverat det utan att fullt ut förstå vad det gör. Ofta kommer en tredjepartsprogram installera en databas med det här alternativet aktiverat, och DBA kanske inte märker det förrän senare. Så varför är det dåligt? Två anledningar: Den används alltid i samband med auto växa, och den kontinuerliga cykeln med växande krympväxling orsakar en stor mängd fysisk diskfragmentering. Ive täckte redan det ämnet tidigare i den här artikeln. Samtidigt som den utför krympningen finns det mycket ytterligare IO, vilket saktar ner systemet för allt annat. Inaktivera det. Tilldel tillräckligt med utrymme för data och loggfiler, och storlek dem därefter. Och glöm inte att fixa all den fragmenteringen medan du är på den. Otillräckligt minne Detta är en artikel om SQL Server IO-prestanda, inte minne. Så jag vill inte täcka det i detalj här - det är ett ämne för en annan artikel. Jag vill bara påminna dig om att SQL Server älskar minne - ju mer desto bättre. Om hela din databas (er) passar in i minnet kommer du att ha ett mycket snabbare system, kringgå allt det långsamma IO. Brist på minne kan leda till att smutsiga sidor sköljas till disken oftare för att göra plats för fler sidor som ska läsas. Brist på minne kan också leda till ökad tempdb IO, eftersom fler arbetsbord för sortering och hashoperation måste spola till disken. Hur som helst, punkten i det här avsnittet är verkligen att göra ett uttalande: Fyll dina servrar med så mycket minne som du har råd med, och så mycket som upplagan av SQL Server och Windows kan adressera. SQL Server 2014 har en ny funktion som gör det möjligt för vissa tabeller att behållas i minnet, och åtkomst via inbyggda lagrade procedurer. Någon omkonstruktion av någon av din befintliga kod kan behövas för att dra nytta av det här, men det ser ut som en bra prestationsökning för de OLTP-system som börjar använda den. Hög användning av tempdb tempdb kan vara en stor konsument av IO och kan påverka övergripande prestanda om den används för mycket. Det är värt att titta på de olika anledningarna till dess användning och undersöka ditt system för att du har minimerat dessa så långt som möjligt. Användarskapade temporära objekt De vanligaste av dessa är tillfälliga tabeller, tabellvariabler och markörer. Om det finns en hög skapningsgrad kan detta leda till att sidokonsekvensen tilldelas, även om det ökar antalet tempdb-datafiler delvis kan lindra detta. Processer som skapar mycket stora temporära tabeller eller tabellvariabler är ett stort nej, eftersom det kan orsaka mycket IO. Interna objekt Databasmotorn skapar arbetsbord i tempdb för hantering av hashförbindelser, sortering och spolning av intermediära resultatuppsättningar. När sortering av operationer eller hashförbindelser behöver mer minne än vad som har beviljats ​​sparar de till disk (med tempdb) och du kommer att se Hash-varningar och sortera varningar i standardspåret. Jag skrev ursprungligen ett par stycken om hur och varför detta händer och vad du kan göra för att förhindra det, men då hittade jag det här inlägget som förklarar det mycket bättre - Förstå Hash, Sorter och Exchange-händelser. Version Store Den tredje användningen av tempdb är för versionsbutiken. Detta används för radversionering. Radversioner skapas när ögonblickssolering eller läsinställd stillbildsoption används. De skapas även under uppdateringar av online index för uppdateringar och raderingar som gjorts under ombyggnaden och för hantering av dataändringar till flera aktiva resultatuppsättningar (MARS). En dålig skriftlig applikation (eller skurkrollig användare) utför en stor uppdatering som påverkar tusentals rader när en radversionsbaserad isoleringsnivå används, kan medföra snabb tillväxt i tempdb och påverka IO-prestanda för andra användare negativt. Tabell - och indexskanningar En tabellskanning är en skanning av en hög. En indexsökning är en skanning av ett grupperat eller icke-grupperat index. Båda kan vara det bästa alternativet om ett täckningsindex inte existerar och många rader kommer sannolikt att hämtas. En grupperad indexsökning fungerar bättre än en tabellskanning - ännu en anledning till att undvika högar. Men vad får en skanning att användas i första hand och hur kan du göra en sökning mer sannolikt Utdaterad statistik Innan du kontrollerar index och kod gör du säker på att statistiken är uppdaterad. Aktivera quotauto skapa statisticsquot. Om kvotautomatikuppdatering statisticsquot inte är aktiverad, se till att du regelbundet kör en manuell statistikuppdatering. Det här är en bra ide även om kvotautomatikuppdatering statisticsquot är aktiverad, eftersom tröskeln på cirka 20 ändrade rader innan automatisk uppdatering slår in är ofta inte tillräckligt, speciellt där nya rader läggs till med en stigande nyckel. Indexval Ibland används inte ett existerande index. Ta en titt på att förbättra sin selektivitet, eventuellt genom att lägga till ytterligare kolumner, eller ändra kolumnordningen. Tänk på om ett täckningsindex skulle kunna skapas. En sökning är mer sannolikt att utföras om det inte behövs några bokmärken. Se dessa inlägg på Quotering Pointquot av Kimberly Tripp. Tipppunkten. Ineffektiv TSQL Hur en fråga skrivs kan också resultera i en skanning, även om ett användbart index finns. Några av anledningarna till detta är: Icke-sargable uttryck i WHERE-klausulen. quotsargquot betyder enkel ARGument. Så flytta beräkningar bort från kolumnerna och till konstanterna istället. Så till exempel kommer detta inte att använda indexet på OrderDate: WHERE DATEADD (DAG. 1. OrderDate) gt GETDATE () Detta kommer att använda ett index om det existerar (och det är selektivt nog): VAR OrderDate gt DATEADD (DAG. - 1. GETDATE ()) Implikta omvandlingar i en fråga kan också resultera i en skanning. Se det här inlägget av Jonathan Kehayias Implicit Conversions som orsakar Index Scans. Dålig Parameter Sniffing Parameter sniffing är en bra sak. Det gör att planen kan återanvändas och förbättrar prestanda. Men ibland leder det till en mindre effektiv exekveringsplan för vissa parametrar. Indexunderhåll Varje index måste behållas. Jag pratar inte om underhållsplaner, men om att när rader läggs in, raderas och uppdateras, måste de icke-grupperade indexerna också ändras. Detta betyder ytterligare IO för varje index på en tabell. Så det är ett misstag att ha fler index än vad du behöver. Kontrollera att alla index används. Kontrollera om duplikat och redundanta index (där kolumnerna i en är en delmängd av kolumnerna i en annan). Kolla efter index där den första kolumnen är identisk men resten är inte - ibland kan dessa sammanfogas. Och förstås, test, test, test. Index fragmentering Index fragmentering påverkar IO prestanda på flera sätt. Områdesskanningar är mindre effektiva och mindre kan använda läsning framåt. Tomt utrymme som skapats på sidorna minskar dataens densitet, vilket betyder att mer läs IO är nödvändig. Uppdelningen i sig orsakas av sidavsnitt, vilket innebär att mer skriv IO Det finns ett antal saker som kan göras för att minska effekten av fragmentering, eller för att minska mängden fragmentering. Upprepa eller omorganisera indexer regelbundet Ange en lägre fyllningsfaktor så att sidsplittringar uppträder mindre ofta (men inte för låga, se nedan) Ändra det grupperade indexet för att använda en stigande nyckel så att nya rader läggs till i slutet istället för att införas i en slumpmässig plats i mitten Vidarebefordrade poster När en rad i en heap uppdateras och kräver mer utrymme kopieras den till en ny sida. Men icke-grupperade index är inte uppdaterade för att peka på den nya sidan. I stället läggs en pekare till den ursprungliga sidan för att visa var raden har flyttats till. Detta kallas en vidarekopplingspekare, och det kan eventuellt vara en lång kedja av dessa pekare att korsa för att hitta eventuella data. Det betyder naturligtvis mer IO. En hög kan inte defragmenteras genom att bygga upp indexet (det finns ingen). Det enda sättet att göra detta är att skapa ett klusterindex på högen och sedan släppa det efteråt. Var medveten om att detta kommer att leda till att alla icke-grupperade index återuppbyggs två gånger - en gång för det nya klusterindexet, och igen när det släpps. Om det finns många av dessa är det en bra idé att släppa de icke-grupperade indexerna först och återskapa dem efteråt. Det är ännu bättre att undvika dynor där det är möjligt. Jag accepterar att det kan finnas fall där de är det mer effektiva valet (till exempel i arkivbord), men överväga alltid om ett grupperat index skulle vara ett bättre alternativ - det brukar det vara. Slösat utrymme I en ideal värld skulle varje datasida på disken (och i minnet) vara 100 full. Detta skulle innebära att minst IO behövs för att läsa och skriva data. I praktiken finns det bortkastat utrymme på nästan alla sidor - ibland en mycket hög procent - och det finns många anledningar till varför detta inträffar. Låg fyllningsfaktor Ive nämnde fyllningsfaktorn redan. Om det är för högt och sidfel uppstår när rader läggs in eller uppdateras, är det rimligt att bygga upp indexet med en lägre fyllfaktor. Om fyllfaktorn är för låg kan du dock ha mycket slösat utrymme i databassidorna, vilket resulterar i mer IO och minnesanvändning. Det här är en av de som gör det och ser scenarier. Ibland behövs en kompromiss. Sidavsnitt Detta diskuteras också ovan. Men såväl som fragmentering kan sidavsnitt också resultera i bortkastat utrymme om det tomma utrymmet inte återanvänds. Lösningen är att defragmentera genom att regelbundet bygga om eller omorganisera index. Avfallande val av datatyper Använd de minsta datatyperna du kan. Och försök att undvika datatyper med fast längd, som CHAR (255), om du inte uppdaterar regelbundet till längsta längden och vill undvika sidavsnitt. Resonemanget är enkelt. Om du bara använder 20 tecken av 200, är ​​det 90 slöseri och mer IO som resultat. Ju högre densitet av data per sida desto bättre. Lätt tänkande kan göra utvecklare skapa AddressLine1, AddressLine2, etc som CHAR (255), eftersom de inte vet vad den längsta ska vara. I det här fallet gör du någon undersökning, ta reda på att den längsta är 50 tecken (till exempel) och reducera dem till CHAR (50), eller använd en datatyp med variabel längd. Schema Design Ive nämnde redan val av datatyper ovan, men det finns andra scheman designbeslut som kan påverka den mängd IO som genereras av en applikationsdatabas. Den vanligaste är att utforma tabeller som är för breda. Jag ser ibland ett bord med 20, 30, 50, till och med 100 kolumner. This means fewer rows fit on a page, and for some extreme cases there is room for just one row per page - and often a lot of wasted space as well (if the row is just slightly wider than half a page, thats 50 wasted). If you really do need 50 columns for your Customer table, ask yourself how many of these are regularly accessed. An alternative is to split into 2 tables. Customer, with just a few of the commonly used columns, and CustomerDetail with the rest. Of course, the choice of which columns to move is important. You dont want to start joining the tables for every query as that defeats the object of the exercise. Page or Row Compression Compression is another way of compacting the data onto a page to reduce disk space and IO. Use of row or page compression can dramatically improve IO performance, but CPU usage does increase. As long as you are not already seeing CPU bottlenecks, compression may be an option to consider. Be aware that compression is an Enterprise edition feature only. Backup Compression Since SQL Server 2008 R2, backup compression has been available on Standard edition as well as Enterprise. This is major benefit and I recommend that it be enabled on all instances. As well as creating smaller backups, it is also quicker and means less write IO. The small increase in CPU usage is well worth it. Enable it by default so that if someone sets off an ad hoc backup it will have minimal IO impact. Synchronous MirroringAlwaysOn High safety mode in database mirroring, or synchronous commit mode in AlwaysOn, both emphasise availability over performance. A transaction on the mirroring principal server or primary replica does not commit until it receives a message back from the mirror or secondary replica that the transaction has been hardened to the transaction log. This increases transactional latency, particularly when the servers are in different physical locations. Resource Governor in 2014 Up until and including SQL Server 2012 resource governor has only been able to throttle CPU and memory usage. Finally the ability to include IO in a resource pool has been added to SQL Server 2014. This has obvious use as a way of limiting the impact of reports on the system from a particular user, department or application. Gathering The Evidence There are a lot of ways you can measure SQL Server IO performance and identify which areas need looking at. Most of what follows is available in SQL CoPilot in graphical and tabular form, both as averages since last service start and as snapshots of current activity. Wait Types Use sys. dmoswaitstats to check number of waits and wait times for IOCOMPLETION, LOGBUFFER, WRITELOG and PAGEIOLATCH. Use this script to focus on the IO wait types: SELECT waittype. waitingtaskscount. waittimems - signalwaittimems AS totalwaittimems , 1. ( waittimems - signalwaittimems ) CASE WHEN waitingtaskscount 0 THEN 1 ELSE waitingtaskscount END AS avgwaitms FROM sys. dmoswaitstats WHERE waittype IN ( IOCOMPLETION. LOGBUFFER. WRITELOG. PAGEIOLATCHSH. PAGEIOLATCHUP. PAGEIOLATCHEX. PAGEIOLATCHDT. PAGEIOLATCHKP ) This shows averages since the last service restart, or since the wait stats were last cleared. To clear the wait stats, use DBCC SQLPERF (sys. dmoswaitstats, CLEAR) You can also check sys. dmoswaitingtasks to see what is currently being waited for. Virtual File Stats Query sys. dmiovirtualfilestats to find out which data and log files get the most read and write IO, and the latency for each file calculated using the stall in ms. SELECT d. name AS databasename. mf. name AS logicalfilename. numofbytesread. numofbyteswritten. numofreads. numofwrites. 1. iostallreadms ( numofreads 1 ) avgreadstallms. 1. iostallwritems ( numofwrites 1 ) avgwritestallms FROM sys. dmiovirtualfilestats (NULL, NULL) vfs JOIN sys. masterfiles mf ON vfs. databaseid mf. databaseid AND vfs. FILEID mf. FILEID JOIN sys. databases d ON mf. databaseid d. databaseid Performance Counters There are two ways of looking at performance counters. Select from sys. dmosperformancecounters, which shows all the SQL Server counters, or use Windows Performance Monitor (perfmon) to see the other OS counters as well. Some counters to look at are: SQL Server:Buffer Manager Lazy writessec The number of times per second that dirty pages are flushed to disk by the Lazy Writer process. An indication of low memory, but listed here as it causes more IO. Checkpoint pagessec The number of dirty pages flushed to disk per second by the checkpoint process. Page readssec Number of physical pages read from disk per second Page writessec Number of physical pages written to disk per second Readahead pagessec Pages read from disk in advance of them being needed. Expect to see high values in BI workloads, but not for OLTP SQL Server:Access Methods Forwarded recordssec Should be as low as possible. See above for explanation of forwarded records. Full scanssec The number of unrestricted full scans. Use of UDFs and table variables can contribute to this, but concentrating on seeks will help to keep the value down Page splitssec The number of page splits per second - combining splits due to pages being added to the end of a clustered index as well as quotgenuinequot splits when a row is moved to a new page. Use the technique from the link in the section on index fragmentation, above, to get a more accurate breakdown Skipped ghosted recordssec For information about ghosted records see An In-depth Look at Ghost Records in SQL Server Workfiles createdsec A measure of tempdb activity Worktables createdsec A measure of tempdb activity SQL Server:Databases Log bytes flushedsec The rate at which log records are written to disk Log flush wait time The duration of the last log flush for each database Log flush waitssec The number of commits per second waiting for a log flush Logical Disk Avg Disk secsRead Avg Disk secsWrite Avg Disk Read bytessec Avg Disk Write bytessec Using the sys. dmosperformancecounters DMV, a lot of counters display a raw value, which has to be monitored over time to see values per second. Others have to be divided by a base value to get a percentage. This makes this DMV less useful unless you perform these calculations and either monitor over time or take an average since the last server restart. This script uses the tempdb creation date to get the number of seconds since the service started and calculates the averages for these counters. It also retrieves all other counters and calculates those that are derived from a base value. USE master SET NOCOUNT ON DECLARE upsecs bigint SELECT upsecs DATEDIFF ( second. createdate. GETDATE ()) FROM sys. databases WHERE name tempdb SELECT RTRIM ( objectname ) objectname. RTRIM ( instancename ) instancename. RTRIM ( countername ) countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 65792 UNION ALL SELECT RTRIM ( objectname ), RTRIM ( instancename ), RTRIM ( countername ), 1. CAST ( cntrvalue AS bigint ) upsecs FROM sys. dmosperformancecounters WHERE cntrtype 272696576 UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 100. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 537003264 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND RTRIM ( v. countername ) base RTRIM ( b. countername ) UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 1. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073874176 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND REPLACE ( RTRIM ( v. countername ), (ms). ) Base RTRIM ( b. countername ) ORDER BY objectname. instancename. countername Dynamic Management Views and Functions As well as the DMVs in the above scripts, there are a number of others that are useful for diagnosing SQL Server IO performance problems. Here are all the ones I use. Ill add some sample scripts when I get the time: sys. dmoswaitstats sys. dmiovirtualfilestats sys. dmosperformancecounters sys. dmiopendingiorequests sys. dmdbindexoperationalstats sys. dmdbindexusagestats sys. dmdbindexphysicalstats sys. dmosbufferdescriptors It can also be useful to see what activity there is on the instance. Here are your options: The Profiler tool is quick and easy to use - you can start tracing in a matter of seconds. However, there is some overhead and it may impact performance itself - especially when a lot of columns are selected. A server side trace is a better option. A server-side trace has less of an impact than running Profiler. It has to be scripted using system stored procedures, but Profiler has the ability to generate the script for you. Extended Event Sessions Extended events were first introduced in SQL Server 2008, and have been considerably enhanced in SQL 2012. They are very lightweight, and the use of server-side traces and Profiler is now deprecated. Nevertheless, use of extended events may impact performance of high transaction systems if you are not careful. Use an asynchronous target and avoid complicated predicates to limit the overhead. There are a number of tools for gathering performance data from your servers. SQLIO is a simple tool that creates a file on disk and tests latency and throughput for randomsequential IO, at various block sizes and with a variable number of threads. These are all fully configurable. SQLIO is a great way of getting a baseline on a new server or storage, for future comparison. Third party tools are another option for viewing performance metrics. Some show you what is happening on the server right now. Others are built into more complex (and expensive) monitoring solutions. Performance metrics obtained on virtual servers are unreliable. Performance counters and wait stats may give the impression that everything is OK, when it is not. I recommend the use of the performance monitoring tools provided by the VM vendor. In the case of VMWare, this is very easy to use and is built into Virtual Center. This turned into a much bigger article than I expected - SQL Server IO performance is a big subject I started with everything I knew, and double checked my facts by searching the web and checking books. In the process I learnt a whole lot of new stuff and found a lot of useful links. It has been a useful exercise. Hopefully this has been useful for you too. Window Functions (OVER Clause)Help Make a Difference If I had to name one concept in standard SQL that I thought was the most important one, and that is worth Microsoftrsquos investment for future versions of SQL Server, Irsquod say window functions, hands down, without a doubt. Window functions are a subset of what the standard calls set functions, meaning, functions that are applied to a set of rows. The term window is used to describe the set of rows that the function operates on, and the language provides a clause called OVER where you provide the window specification. So whatrsquos the big deal, and what makes window functions more important than other features that are missing in SQL Server There are so many reasonshellip But first Irsquoll give a bit more background about window functions, and then Irsquoll get to the reasons and demonstrate use caseshellip First, to clarify, SQL Server 2005 already introduced some support for window functionsmdashthe ranking calculations: ROWNUMBER, RANK, DENSERANK and NTILE, and partial support for window aggregate functions with only the partitioning part implemented. SQL Server 2005 was a great release for developers with so many cool and practical T-SQL features. The number of solutions that I simplified and optimized just with the ROWNUMBER function and CTEs is amazing. Still, there are many standard features related to window functions that SQL Server didnrsquot yet implement (as of SQL Server 2008 R2) and that can help address quite a wide variety of business problems with simpler and more efficient solutions. These days the next major release of Microsoft SQL Servermdashversion 11mdashis being developed. These are pivotal days for candidate features where decisions are made whether they will or will not make it to the final release. And even though I think that more complete support for window functions is so important to developers and to the success of SQL Server, Irsquom not sure at all that we will see those in the product. This is time for us as part of the SQL Server community to express our strong opinion. Hopefully Microsoft will realize how important it is for us to have those features in the product, as well as to show that the SQL Server communityrsquos opinion matters. In this article I will explain some of the key features that are missing in SQL Server and why itrsquos important to add support for such features. If you share my opinion, and havenrsquot done so already, you can cast your vote in the following feature request items: Like with any thing in life that yoursquore not aware of, you donrsquot know how it can help you if you donrsquot know that it exists. My feeling is that many developers are not really aware of the capabilities of the standard window functions and therefore Microsoft doesnrsquot see a lot of demand for it. Education and raising the topic to peoplersquos awareness is therefore key to the realization of the benefits, and as a consequence, encourage people to ask Microsoft for more support. The unfortunate part is that all of SQL Serverrsquos leading competitors including Oracle, DB2 and Teradata for some time now already have a far more complete support for window functions. So even though my focus and passion is for SQL Server, I sometimes find myself in the awkward situation of demoing standard SQL window functions on Oracle when teaching or presenting. So whatrsquos missinghellip The most important missing features are probably ordering and framing options for window aggregate functions. Other key features that are still missing are distribution and offset functions, and reusability of window definitions. More details shortly. Why are window functions so powerful SQL is often referred to as a set-based language. The reason is that the language is based on the relational model, which in turn is based, in part, on mathematical set theory. When writing SQL queries yoursquore supposed to deal with a table (or relation, which is a set) as a whole, as opposed to the tablersquos individual rows. Also, since sets have no order, yoursquore not supposed to make any assumptions in regards to the physical ordering of the data. The reality is that for many developers set-based thinking is far from being intuitive, and it can take a few good years to truly think in SQL terms. This is why often developers tend to use cursorsmdashbecause using those feel like an extension to what they already know. Cursors allow you to deal with one row at a time, and also rely on specified order of the data. Window functions have an ingenious design. They do operate on sets, or windows, while allowing you to indicate ordering as part of the calculation where relevant. Not to confuse with cursors, window functions allow defining ordering for the calculation without making any expectations in regards to ordering of the input data given to the query or the output coming out of the query. In other words, no relational concepts are violated. Ordering is only part of the specification of the calculation. Similarly, other common elements in querying problems, like partitioning, framing of applicable rows, are all intuitive parts of the window specification. So in a sense, I see window functions as bridging the big gap that exists between cursoriterative and set-based thinking. Now, thatrsquos a lot of words before showing even one example. So letrsquos look at a few more concrete examples of some of the missing featureshellip Most of the examples Irsquoll show are against a database called InsideTSQL2008. You can find the script creating it here: InsideTSQLbookssourcecodeInsideTSQL2008.zip. In addition, the following view will be used in some of the examples: SET NOCOUNT ON USE InsideTSQL2008 GO IF OBJECTID ( 39Sales. EmpOrders39. 39V39 ) IS NOT NULL DROP VIEW Sales. EmpOrders GO CREATE VIEW Sales. EmpOrders WITH SCHEMABINDING AS SELECT O. empid , DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) AS ordermonth. SUM (OD. qty ) AS qty , CAST ( SUM (OD. qty OD. unitprice (1 - discount )) AS NUMERIC (12. 2 )) AS val , COUNT () AS numorders FROM Sales. Orders AS O JOIN Sales. OrderDetails AS OD ON OD. orderid O. orderid GROUP BY empid. DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) GO Ordering and Framing for Window Aggregate Functions As mentioned, currently window aggregate functions support only a partitioning element. Whatrsquos missing are ordering and framing options. The standard supports an ORDER BY clause to define ordering in the window and ROWS and RANGE clauses that frame the window based on the defined ordering. A classic example that would benefit from ordering and framing is running totals. Consider the following Accounts table definition: CREATE TABLE dbo. Accounts ( actid INT NOT NULL, -- partitioning column tranid INT NOT NULL, -- ordering column val MONEY NOT NULL -- measure CONSTRAINT PKAccounts PRIMARY KEY (actid. tranid ) ) The table represents deposit (positive value) and withdrawal (negative value) transactions in bank accounts. You need to calculate at each point what the account balance was. Like with many querying problems therersquos a partitioning element (actid), ordering element (tranid), and a measure that the calculation applies to (val). Window aggregate functions in standard SQL support all three elements. Herersquos how you would express the query calculating the balance at each point for each account: SELECT actid. tranid. val , SUM (val ) OVER ( PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS balance FROM dbo. Accounts You can achieve such calculations today in SQL Server using a subquery or a join: -- Set-Based Solution Using Subqueries SELECT actid. tranid. val , ( SELECT SUM (S2.val ) FROM dbo. Accounts AS S2 WHERE S2.actid S1.actid AND S2.tranid lt S1.tranid ) AS balance FROM dbo. Accounts AS S1 -- Set-Based Solution Using Joins SELECT S1.actid. S1.tranid. S1.val , SUM (S2.val ) AS balance FROM dbo. Accounts AS S1 JOIN dbo. Accounts AS S2 ON S2.actid S1.actid AND S2.tranid lt S1.tranid GROUP BY S1.actid. S1.tranid. S1.val But besides the fact that these solutions are not as straightforward and intuitive as the one using a window function, therersquos a big problem with the way SQL Server currently optimizes the subquery and join solutions. Assuming you defined a covering index on the partitioning column, followed by the ordering column, and including the aggregated measure, for each row SQL Server will scan all rows with the same partitioning value and an ordering value that is less than or equal to the current. Given p partitions with r rows in average, and fairly even distribution of rows in partitions, the total number of rows processed in such a plan is pr p(r r2)2. This means that in respect to the partition size, the algorithmic complexity, or scaling, of the solution s quadratic (N2). Thatrsquos bad. The window function form lends itself to good optimization, especially with the fast track case like the above (rows between unbounded preceding and current row). It should be straightforward to the optimizer to optimize this query with one ordered scan of the index, translating to simply pr rows being scanned. Another example for running totals is querying a table called EmpOrders with a row for each employee and month, and calculating the cumulative performance for each employee and month in other words, the total value for the employee from the beginning of hisher activity until the current month. Herersquos how you would express it with a window aggregate: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runqty FROM Sales. EmpOrders empid ordermonth qty runqty ----------- ----------------------- ----------- ----------- 1 2006-07-01 00:00:00.000 121 121 1 2006-08-01 00:00:00.000 247 368 1 2006-09-01 00:00:00.000 255 623 1 2006-10-01 00:00:00.000 143 766 1 2006-11-01 00:00:00.000 318 1084 . 2 2006-07-01 00:00:00.000 50 50 2 2006-08-01 00:00:00.000 94 144 2 2006-09-01 00:00:00.000 137 281 2 2006-10-01 00:00:00.000 248 529 2 2006-11-01 00:00:00.000 237 766 . There are many business examples where ordering and framing options can be useful besides calculating account balances. Those include inventory, running totals for reporting, moving averages, and so on. Herersquos an example for a query calculating the average of the last three recorded periods: SELECT empid. ordermonth , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avglastthree FROM Sales. EmpOrders There are also various temporal querying problems where running totals serve part of the solution. For simplicity I showed examples where framing is based on the ROWS clause where you indicate an offset in terms of number of rows. The standard also supports a RANGE clause that allows indicating an offset in terms of values, such as time intervals, as in the following example returning the average of the last three months: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth RANGE INTERVAL 39239 MONTH PRECEDING ) AS sum3mqty FROM Sales. EmpOrders ORDER BY empid. ordermonth The SQL standard defines several offset functions that would make developersrsquo life so much easier compared to the tools available today for similar needs. Among the missing offset functions are LAG and LEAD, returning a value from a row in a given offset from the current row based on specified ordering. For example, the following query will return, for each current order, also the order date of the previous and next orders: SELECT custid. orderdate. orderid , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prvod , LEAD (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS nxtod FROM Sales. Orders custid orderdate orderid prvod nxtod ------- ----------- -------- ----------- ----------- 1 2007-08-25 10643 NULL 2007-10-03 1 2007-10-03 10692 2007-08-25 2007-10-13 1 2007-10-13 10702 2007-10-03 2008-01-15 1 2008-01-15 10835 2007-10-13 2008-03-16 1 2008-03-16 10952 2008-01-15 2008-04-09 1 2008-04-09 11011 2008-03-16 NULL 2 2006-09-18 10308 NULL 2007-08-08 2 2007-08-08 10625 2006-09-18 2007-11-28 2 2007-11-28 10759 2007-08-08 2008-03-04 2 2008-03-04 10926 2007-11-28 NULL . Notice how elegant and intuitive this form is. The default offset is one row, but you can also be explicit if you need an offset that is other than one row, e. g. three rows: SELECT custid. orderdate. orderid , LAG (orderdate. 3 ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prv3od FROM Sales. Orders There are lots of business examples for the usefulness of these functions, like recency calculations, trend analysis, and others. Herersquos an example for a query addressing recency calculations, returning the difference in terms of days between the current and previous orders: SELECT custid. orderdate. orderid , DATEDIFF ( day , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ), orderdate ) AS diff FROM Sales. Orders Other missing offset functions are FIRSTVALUE, LASTVALUE, returning the value from the first or last rows in the partition based on specified ordering. Herersquos an example returning the value of the first and last orders per customer with each order: -- FIRSTVALUE, LASTVALUE SELECT custid. orderdate. orderid. val , FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS valfirstorder , LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS vallastorder FROM Sales. OrderValues custid orderdate orderid val valfirstorder vallastorder ------- ----------- -------- ------- --------------- -------------- 1 2007-08-25 10643 814.50 814.50 933.50 1 2007-10-03 10692 878.00 814.50 933.50 1 2007-10-13 10702 330.00 814.50 933.50 1 2008-01-15 10835 845.80 814.50 933.50 1 2008-03-16 10952 471.20 814.50 933.50 1 2008-04-09 11011 933.50 814.50 933.50 2 2006-09-18 10308 88.80 88.80 514.40 . And herersquos an example calculating the difference between the current order value and the first and last: SELECT custid. orderdate. orderid. val , val - FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS difffirst , val - LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS difflast FROM Sales. OrderValues Standard SQL supports window distribution functions that performing statistical calculations. Specifically it supports the PERCENTRANK and CUMDIST functions, calculating a percentile rank and cumulative distribution. These functions give you a relative rank of a row in respect to other rows in the window partition, expressed as ratiopercent. The specific formulas used by the two variants are: PERCENTRANK: (RK-1)(NR-1), where RK rank, NR number of rows in partition CUMEDIST: NPNR, where NP number of rows preceding or peer with current row (same as next rank - 1) Herersquos an example using these functions: SELECT custid. COUNT () AS numorders , PERCENTRANK () OVER ( ORDER BY COUNT ()) AS percentrank , CUMEDIST () OVER ( ORDER BY COUNT ()) AS cumedist FROM Sales. Orders GROUP BY custid custid numorders percentrank cumedist ------- ---------- ------------ --------- 13 1 0.0000 0.0112 33 2 0.0114 0.0337 43 2 0.0114 0.0337 42 3 0.0341 0.1124 53 3 0.0341 0.1124 . 37 19 0.9545 0.9663 24 19 0.9545 0.9663 63 28 0.9773 0.9775 20 30 0.9886 0.9888 71 31 1.0000 1.0000 Reuse of Window Definition using WINDOW Clause Suppose you need to write several window functions that rely on the same window definition (or part of it). You will end up with a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable. For example, instead of: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runsumqty , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runavgqty , FROM Sales. EmpOrders SELECT empid. ordermonth. qty , SUM (qty ) OVER W1 AS runsumqty , AVG (qty ) OVER W1 AS runavgqty , FROM Sales. EmpOrders WINDOW W1 AS ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As you can see, with the WINDOW clause the code is shorter, more readable, and easier to maintain. I showed just part of the standard support for window functions that SQL Server is still missing. Therersquos more, like window frame exclusion. There are also other set functions not implemented, like ordered set functions, and so on. But here I wanted to make a point in hope that Microsoft will realize how important it is to add such support in SQL Server 11. If you feel so as well, help make a difference by voting for the items, write about the topic, talk about it, increasing peoplersquos awareness. Hopefully this request will find open ears. As a reminder, here are the open items for some of the requests for enhancements: Thank you for expressing so well what I would haveshould have written a long time ago. Personally a SQLSErver fan, I have had to become expert at Oracle to support our platform for the many customers who still think Oracle is better. While my gripes with Oracle would take many pages to begin to express, I DO like its full support of windows functions, especially LEAD and LAG. No reason to add to what has been already so eloquently said in this post. If there is a polling place anywhere to encourage Microsoft to fully support windows function in a future release, please direct me to it Is it too late for 2011 Looking at the OVER clause in BOL at the moment the enhancements seem entirely under whelming msdn. microsoften-uslibraryms18946128SQL.11029.aspx Please Log In or Register to post comments. Importing SQL Server Data Using SSIS - Which Option is Fastest By: Daniel Calbimonte Read Comments (27) Related Tips: More Integration Services Development This article is useful for SSIS developers who do not know which tasks are best to use in an SSIS projects. The main problem is that at the end of development if performance is slow then you will need to rebuild the project and change components. This article shows various ways of importing data and shows which types of components perform best within SSIS. The contest will be between the following components: ODBC Tasks ADO NET Tasks OLEDB Task SQL Server Destination T-SQL Tasks I created different SSIS packages in order to test performance. In this demo I used SSIS 2012 and the database Adventureworks 2012 . In this demo I am going to import the table AdventureWorks2012.Sales. SalesOrderDetail to the test2 database which is on the same instance of SQL Server. SalesOrderDetails is the table with more rows in AdventureWorks2012. In order to create the database test2 and the destination table dbo. OrderDetails, use this T-SQL code: Test 1 - ODBC Tasks The first example will use ODBC Source and ODBC Destination as shown below: When we run the package we notice the average time is 5 minutes 57 seconds to import the rows: Test 2 - ADO NET Tasks As noticed, ODBC is pretty slow. Lets try another approach. We are going to truncate the destination table first: Lets try ADO tasks to import the same data and verify if these components are faster: The average elapsed time in my testing was 11 seconds. This is much better. Test 3 - OLEDB Tasks This time we are going to import the same data using the OLEDB Tasks. Again we will truncate the table in the test2 database first. The average elapsed time is 5 seconds. Note that I am using the fast load option with the Table Lock option in the OLE DB Destination Task: If we do not use the fast load option, the average elapsed time was 2 minutes and 21 seconds: OK. The fast load option really improves performance. I will return to that configuration. What about the OLE DB Source. By default I am using the option Table or view in the OLE DB Source as shown below: Lets use a SQL Command instead as shown below. The average elapsed time is 2.85 seconds . Test 4 - SQL Server Destination Now, lets try to use the SQL Destination as the destination instead of OLE DB Destination: The average elapsed time is 2.5 seconds. At this point it is the best option. Test 5 - Execute T-SQL Task Finally, some people think that the best option is to use the Execute T-SQL Task: I am using a simple insert statement to import data from one source to another: The average elapsed time is 1.8 seconds Finally Ive been told that if the query runs inside a stored procedure it is even faster: Lets create a stored procedure: After creating the stored procedure we are going to call it in the Execute T-SQL Task: The average elapsed time is 2.12 seconds . The stored procedures does not improve performance. Lets review the table with the results: You may think the morale of the story is to use the Execute T-SQL Task instead of other SSIS tasks. In this example we were importing data on the same instance, but this will not always be the case. So the morale of the story is that there are many alternatives when creating a SSIS project and we have to carefully study the alternatives in different scenarios. There are great SSIS tools and we do not always use the best options. With each new version of SSIS new tasks are added and performance may be improved with existing tasks. The main changes in SSIS for SQL 2008 and 2012 are related to performance improvements. Next Steps If you are working in a SSIS project make sure you are using the best tasks and also verify if there are other SSIS tasks that can be used in your project. Also make sure you are following the best practices recommended by the experts: Last Update: 7132012 Great read and analysis, but I have one caveat to add. If you need to move a large amount of data, you need to take care of the transaction log growth. This is not a much of a concern using SSIS. For instance, I needed to move 1.3 billion rows (15 columns) and began using TSQL which quickly filled my logs. However, using OLE DB Source and Destination (Bulk Inserts) with fast load, there was little impact to the log file. Thursday, September 20, 2012 - 9:19:12 AM - vinodhkumar Its very useful. great job. Thanks Monday, August 27, 2012 - 10:54:42 AM - Orlando Colamatteo I agree with some others that the testbed is a bit contrived. If youre looking to move data from one table to another on the same instance then SSIS will rarely be a viable option. Some form of T-SQL will almost certainly outperform an SSIS operation. A more realistic scenario is moving data between two disparate data sources. It is surpising how poorly the ODBC Destination performs, especially in light of what Microsoft has publicly said in that they will be moving away from OLE DB interfaces and standardizing on ODBC in future products: In the ODBC Destination I expected Microsoft to implement the loading of data via the bulk load API as they did with the FastLoad option of the OLE DB Destination. On a separate note regarding loading data into MySQL with SSIS: In the past I did some performance tests with the Cherry City OLE DB driver for MySQL and it is horribly slow as it only inserts one row at a time. This is not to mention the fact that it crashed BIDS regularly when developing with it. Given the lack of a benefit I would stick with the tools built into SSIS and avoid the hassle of installing and configuring a third party driver. If youre using SSIS 2005 I would recommend using a Script Component as a Destination and issuing batch-inserts against a connection made using the MySQL ODBC Driver: msdn. microsoften-uslibraryms135939.aspx If youre using SSIS 2008 I would recommend using an ADO NET Destination with the MySQL ODBC Driver. In my tests it was only able to achieve about 240 rowsminute throughput to MySQL which is quite disappointing: msdn. microsoften-uslibrarybb895291(vsql.105).aspx If youre using SSIS 2012 I would recommend using an ODBC Destination with the MySQL ODBC Driver. In my tests it outperformed the ADO NET Destination over 3 to 1 but still only achieved about 800 rowsminute throughput, which was still quite disappointing: msdn. microsoften-uslibraryhh758691(vsql.110).aspx