Sunday 5 November 2017

Flytte Gjennomsnittet Sql Spørring


Innhold williamrobertson Sette opp PLSQL Developer, del 2 Denne artikkelen ble skrevet for PLSQL Developer 8.0.4 ved hjelp av Oracle 11.2 og Windows XP, i en parallell virtuell maskin på min Mac, og derfor viser skjermbildene en blanding av XP Silver og Aqua windows. PLSQL Developer er et av flere integrerte utviklingsmiljøer (IDEs) som er tilgjengelige for Oracle. En av tingene jeg liker om det er hvordan konfigurerbar det er - du kan endre nesten alt, og med nedlastbare plugin-moduler som Browser Extender kan du legge til din egen funksjonalitet. Etter å ha flyttet PCer flere ganger og måtte installere PLSQL Developer på nytt hver gang, har jeg funnet ut at det er noen tilpasninger jeg ikke kunne leve uten, og jeg trodde at ID dokumenterte dem. Del 1 dekket innstillinger som fonter og skjermoppsett, og del 2 dekker sesjonens nettleser. Jeg skulle inkludere de egendefinerte høyre mouseclick-handlingene som Ive har lagt til ved hjelp av Browser Extender. men det er så mye du kan gjøre med sesjonsbrowseren at jeg må forlate det for del 3. Utvidelse av sesjonsbrowseren 1. Gjør sesjonsbrowseren enklere å finne I standardoppsettet er den begravet nedover i listen under Verktøy meny, men som noe du vil bruke hele tiden, er det mye bedre å ha en knapp for den. Hvis du savnet det i del 1. kan du tilpasse verktøylinjen ved å legge til et ikon for sesjonsbrowseren. Heres hva slags ting du kan gjøre: Standard verktøylinje Tilpasset verktøylinje Merk kryss nøklene ikonet andre fra venstre i den tilpassede verktøylinjen. 2. Se på standardinnstillingene Nå åpner du Session Browser og ser på standardoppsettet. (Egentlig ikke helt standard - Jeg har forandret skrifttypen til Corbel 8pt, som passer til mer informasjon på skjermen, samt å være mer attraktiv enn standard etter min mening. Tahoma fungerer også bra. Du vil se på denne skjermen mye, Tross alt.) Skjermen er en hoveddetaljrapport, med både master - og detalj-spørringer som kan konfigureres ved hjelp av skiftenøkkelikonet. Mesternavnet øverst i vinduet er definert under Filtre, og noen variasjoner på valg fra vsession er gitt. Under Detaljer er det fire svært enkle spørringer for åpne markører, gjeldende SQL-setning, øktstatistikk og lås: Merk bindingsvariabelen: sid i Cursors-spørringen. Den kule tingen om spørreundersøkelser for øktbrowser er at du kan referere til nåværende verdi for en hvilken som helst kolonne fra den øverste delen som en bindingsvariabel i detaljerte spørringer. Så, så lenge hovednavnet inneholder en kolonne som heter sid, kan vi bruke uttrykk som hvor sessionid: sid i noen detalj spørring. (Dette betyr imidlertid at du kanskje trenger å inkludere noen få kolonner i hovedspørsmålet, bare for å bruke som nøkler i detaljerte spørringer.) Et poeng å merke seg om detaljfeltboksen er at du legger til teksten sammenhenger etter at spørringen gjør PLSQL Developer slutter seg til alle produksjonslinjer i en stor blokk. Mens en fin funksjon, forhindrer dette også rulling, så jeg finner det en blandet velsignelse. 3. Skriv dine egne VSESSION-søk Aktive økter Standard søkene er alle velg fra vsession hvor. . som selvfølgelig er en fornuftig standardinnstilling som vil fungere på tvers av alle Oracle-versjoner. Nye og nyttige attributter blir lagt til vsession i hver utgave, og selvfølgelig koding av dem eksplisitt i sammenføyninger og oppslag betyr at spørringen kanskje ikke fungerer i en tidligere versjon.1 Hvis du jobber med flere Oracle-versjoner, kan det hende du må lagre mer enn ett søk i filtre-delen, og velg den rette som nødvendig (dessverre kan PLSQL Developer ikke sjekke versjonen og velge den for deg). Heres en bedre aktiv sessionsforespørsel for Oracle 10.2.0.2 og fremover (noter kolonnene plsqlentryobjectid og plsqlentrysubprogramid. Amongs andre, ble lagt til i denne versjonen så det vil ikke fungere i Oracle 10g XE). Alle økter som for tiden er aktive (unntatt Oracle-bakgrunnsprosesser som Log Writer), eller som blokkerer andre økter, eller eies av meg. Deres foreldre, hvis del av en parallell spørring Objektet som ventes på (vanligvis et bord eller en indeks) - så opp fra dbaobjects ved hjelp av rowwaitobj. PLSQL-oppføringen og gjeldende prosedyrer - så opp fra dbaprocedures ved hjelp av plsql-kolonnene lagt til i Oracle 10.2.0.2. Noen statistikker om CPU, leser, minnebruk og spørringsanalyse, fra vsessmetric. Når resultatene vises, kan du klikke på disse kolonnene for å sortere økter ved bruk av CPU. Enhver sesjon som blokkerer en annen, uavhengig av status, i tillegg til aktive forgrunnsøkter. RAC-forekomsten, for multi-node-klynger. Hvis du bare har en enkelt forekomst, blir det 1 (du vil kanskje flytte den til slutten av oppføringen for å få plass til andre kolonner). GV-visninger for RAC V-visningene (faktisk synonymer for sys. v-visninger) har alle g-prefikserte versjoner - for eksempel gvsession - som inkluderer forekomstnummeret, for bruk i RAC-systemer. For single-instancesystemer vil dette alltid være 1. Dokumentasjonen viser kun v-versjonen, så hvis du vil vite om gvsession. for eksempel, bare se opp vsession og antar at det vil være en ekstra kolonne kalt instid. Jeg har brukt de vanlige v - og RAC-klare gv-navnene om hverandre. Kopier spørsmålet nedenfor i spørringsboksen (etter å ha testet det i et SQL-vindu for å sikre at det fungerer med din Oracle-versjon og tillatelser - for å få tilgang til V-visningene du trenger SELECTCATALOGROLE). Legg merke til at det ikke er noen semikolon på slutten. Du vil kanskje også vurdere det mot vsession hvis det er nyttige kolonner som er nyttige for deg. Mine økter På et opptatt system, vil du noen ganger bare se dine egne økter og ekskludere alt annet. For dette bruker jeg en Sessionsforespørsel, som er den samme som den ovenfor, unntatt WHERE-klausulen, som er: Alle økter. Det er også noen ganger praktisk å ha en versjon som viser alle økter, inkludert Oracle log-forfatteren, prosessmonitoren osv. Lag en annen kopi av spørringen ovenfor, og bare slipp ut WHERE-klausulen. 4. Legg til dine egne detaljer-faner Dette bruker vsqlstats for å vise detaljert utførelsesstatistikk om sessjonens nåværende SQL-setning (identifisert av sqlid). Merk at det refererer til alle forekomster av markøren, ikke bare denne økten nåværende samtale. (Også da v-visningene gjenspeiler bare hva som er i minnet akkurat nå, kan det være annerledes enn det du ser i dbahist-visningene hvis du har Diagnostikkpakken.) Ideen om prosentandelen er å vise hvordan den totale forløpte tiden bryter ned I CPU, IO, venter samtidighet osv. Det er bare omtrentlig, og de legger ikke alltid opp til 100, fordi det kan være andre faktorer som ikke er registrert for for eksempel nettverksoverføringstider og applikasjonsbehandling, men de gir deg en ide om hvordan uttalelsen blir Bearbeidet. Nå bør du få en SQL Stats-fan som skjermbildet nedenfor for en hvilken som helst sesjon som utfører SQL. (Binds, Prev SQL etc er andre faner Ill definere i et øyeblikk.) Perfhistor denne markøren Hvis en SQL-setning tar lang tid, vil du kanskje sjekke ytelsesloggen (fra dbahistsqlstats) for å se om dette er normalt for markør eller om noe har endret seg. Det første spørsmålet nedenfor gir de forskjellige utførelsesplanene og deres tilhørende kjøretidsstatistikk, samlet for hele historien til markøren, slik at du kan se gjennomsnittlig kjøretid og om det finnes flere planer. (Legg merke til tilmeldingen til gvsqlplan - g som angir den RAC-aktiverte versjonen - som ser ut til å være den mest pålitelige måten å finne utførelsesplanen som for øyeblikket blir brukt som den inneholder barnnummeret. Som vsqlstats rapporteres kun én rad per tydelig sqlid . Det kan ikke vise planen for den versjonen som for øyeblikket kjøres.) Den andre versjonen - som jeg merker som Perf., merker denne markøren etter dato - deler den samme informasjonen om dagen, slik at du kan se om den kjørte raskt i tirsdag, eller om planen endret i morges: Følgende spørring vil liste eventuelle bindingsvariabler som holdes i vsqlbindcapture for den nåværende SQL-setningen. Ive filtrert resultatene for å utelukke duplikater. Vær oppmerksom på at Oracle ikke fanger hver eneste bindingsverdi, og holder bare den siste verdien som er fanget ved intervallmarkørbindingsfeltintervall og avhengig av mengden ledig plass opp til markørbindingskapasitet. Et alternativ er å få bindingsdataene som brukes til å analysere tid fra vsqlplan. selv om dette tar noen avkodning som holdes i RAW-format i en XML-kolonne - se Jonathan Lewis blogginnlegg Bind Capture. som lenker til å skape testskript med bindevariabler fra Kerry Osborne og spore bindingsverdien fra Dion Cho. Dette førte meg til følgende spørring ved hjelp av en ide fra Kyle Hailey i kommentarene til Jonathan Lewis-innlegget: I mine tester ved hjelp av Oracle 11.2.0.2 omgår dette bindingsnavnene. Uansett er det et stort emne å fange bindingsverdier, så jeg forlater deg med spørsmålene ovenfor for å eksperimentere med og fortsette. Tidligere SQL, tidligere SQL-statistikk Noen ganger er det nyttig å se hva den forrige setningen var. vsession inneholder flere tidligere kolonner, så dupliser bare detaljflikene for SQL Text og SQL Stats, men erstatt prevsqlid og prevchildnumber. Objektstatistikk Når du undersøker et ytelsesproblem, vil du ofte kontrollere statistikkens nåværende tilstand på tabellene som er involvert i spørringen. Spørringen nedenfor knytter seg til vsqlplanstatisticsall med dbatabstatistics for å liste denne informasjonen - det er ikke perfekt hvis partisjonerte tabeller er involvert, da problemet kan ligge med statistikk for en enkelt partisjon eller delpartisjon, men det er en start. Erstatt standard cursor-spørringen (velg fra vopencursor hvor sid: sid) med følgende for å legge til noen aktivitetsstatistikk. (Merk at henrettelsesstatistikken refererer til alle økter, ikke bare den nåværende økten.) Aktuell plan PLSQL Developers innebygd Forklar Planverktøy (F5) er alt bra og bra, men det kan bare være like godt som å forklare planen. Dvs., verktøyet bruker forklaringsplan for å forutsi utførelsesplanen, og viser deretter resultatene i grafisk form. Noen ganger er dette ikke det samme som den faktiske kjøretidplanen. Når jeg ser på nåværende sessioner, liker jeg å bruke dbmsxplan. displaycursor () for å se hva databasen faktisk gjør. Definer en faneblad med nåværende plan ved å bruke følgende: Den sammenkjente kommentaren vil gjøre PLSQL Developer wrap alle produksjonslinjene fra spørringen til en stor blokk. Dette gjør det enklere å lese, selv om det også forhindrer rulling, så jeg er ikke sikker på at alt er nyttig her. (Dessverre kan du ikke angi en monospace font for et enkelt menyelement, så standardvisningen er ikke så stor.) Den beste måten å lese den på er å kopiere og lime inn i et nytt SQL-vindu. Dette er lettere hvis du definerte en hurtigtast som Alt-S for File gt New gt SQL-vinduet som jeg foreslo i del 1. (Jeg har også en utvidelse av nettleserforlenger for å gjøre dette i ett høyreklikk, som jeg kommer til senere. ) Jeg bruker også en annen variant av dette spørsmålet, som jeg har merket Nåværende plan GPS (Samle planstatistikk - selv om kanskje utvidet plan ville være et bedre navn nå jeg tenker på det). Dette bruker ALLSTATS LAST i formatet argumentet til dbmsxplan. displaycursor for å få estimert og faktisk radtelling (kardinalitet) hvis spørringen brukte hinten for samlingsplanstatistikk, eller hvis parameterstatistikknivået ble satt til ALL for sesjonen. Den litt vanskelige delen med dette er at du ikke kan bruke den til spørringen er ferdig (fordi den faktiske raden teller arent ennå kjent), men når den er fullført, er det ikke lenger den aktuelle henrettelsen og forsvinner derfor fra vsession, og så når du oppdater sessionsleseren din bort. I stedet må du oppdatere skjermen mens spørringen utføres, men vent til den er fullført før du går til gjeldende plan GPS-fanen. Nåværende ventetid Selv om sesjonens nåværende ventetilstand allerede er vist i hovedspørsmålet ovenfor, liker jeg også å ha informasjonen i egen faneblad. Ive merket ventelobjektet så opp fra rowwaitobj som muligens ikke-relatert som en påminnelse om at selv om dette er det siste objektet som sesjonen ventet på, kan behandling nå ha gått videre til noe annet (sortering av utdata for eksempel eller venter på et program til behandle spørringsutdataene) og økten har ikke tilgang til dette objektet for øyeblikket. Siste 10 venter Følgende gir et raskt blikk på sesjonens siste aktivitet ved hjelp av vsessionwaithistory (ventetiden er hundrevis av sekunder): vsessionlongops viser statusen til ulike operasjoner som kjører i mer enn 6 sekunder. Disse operasjonene omfatter for tiden mange sikkerhetskopierings - og gjenopprettingsfunksjoner, statistikkinnsamling og forespørsel om utførelse, og flere operasjoner legges til for hver Oracle-utgivelse. Hvis en forespørsel bruker hash eller sorteringsoperasjoner, tabellskanninger, partisjoner osv. Som tar mer enn 6 sekunder, vil disse operasjonene vises i vsessionlongops, og du kan spore deres fremgang. (Merk at det er bare de enkelte individuelle operasjonene som er sporet, ikke hele spørringer.) Mange langvarige Oracle-prosesser er også instrumenterte, som manualen nevner. Andre som ikke er oppført ovenfor, inkluderer Database Replay og SQL Performance Analyzer-kjøringer (11g), og datapump importexportjobber - og selvfølgelig noen av dine egne prosesser der du inkluderte dbmsapplicationinfo. setsessionlongops, samtaler for å logge det totale arbeidet og mengden behandlet så langt. Jeg definerer også en Long ops denne spørrekategorien, som er en kopi av den ovenfor, men med et ekstra filter for å begrense det til den nåværende utførelsen: ASH sammendrag - økt vactivesessionhistory er et øyeblikksbilde av vsession tatt en gang hvert sekund, holdt for en begrenset periode (normalt 30 til 60 minutter) og deretter lagret i dbahistactivesesshistory. (For å bruke dette trenger du Diagnostikkpakken, så sørg for at du er lisensiert selv om du arbeider - du vil ikke at sjefen din skal få en uventet regning etter en Oracle-revisjon.) Det er mange kreative måter å få denne informasjonen på, og jeg bruker tre spørringer å spore hva en nåværende økt gjør. Siden ASH prøver hvert sekund, kan det være nyttig å oppsummere det ved SQL-setning og liste resultatene etter tid. Hvis du ser på en prosedyre eller en batch som kaller flere setninger, gir dette en oversikt over hvor økten spenderer sin tid (litt som å spore økten). Følgende spørring gir én rad per sqlid. i synkende rekkefølge av total tid, med totalt nederst. ASH sammendrag - henrettelser Jeg har også en mer detaljert versjon muliggjort i 11g ved sqlexecstart kolonnen i vactivesessionhistory. som lar meg se individuelle henrettelser av en SQL-setning i stedet for en enkelt aggregert rad. ASH-oppsummering - tidsmarkørobjekt Rapport som viser objekter som ventet på av alle SQL-setninger for den angitte økten. Dette er ment som en rask måte å se hva en økt har brukt sin tid på, i form av objekter i stedet for spørringer. ASH oppsummer dette spørsmålet med anroper. Neste jeg har en GROUP-BY-spørring for gjeldende sqlid. i synkende rekkefølge av utvalgstall. Tanken er å se hvor tiden blir brukt i den nåværende henrettelsen (i stedet for hvilke uttalelser som har tatt tid i den nåværende økten). Siden Active Session History bruker et 1 sekunders pollingintervall, har noe som skjer i 10 prøver, antatt å ta omtrent 10 sekunder. Merk at det bare filtrerer på sqlid. så flere henrettelser av det samme søket etter økten vil alle bli samlet sammen. (I 11g kan du bruke den nye sqlexecid-kolonnen for å skille mellom henrettelser.) Vær også oppmerksom på at ASH kan prøve aktivitet som På CPU sammen med en databaseobjekt - dette betyr bare at den siste gjenstanden ble tilgang til da prøven ble tatt, ikke at CPUen nødvendigvis var knyttet til objektet. Jeg har to smaker av denne, med og uten detaljer om den anropende PLSQL-prosedyren. ASH sammendrag denne spørringen kun SQL Dette er det samme som forrige spørring, men uten de anropende PLSQL-detaljene for å gi et tydeligere bilde av databasetilgang. ASH detalj denne sesjonen Til slutt har jeg en rett liste over vactivesessionhistorie for den nåværende økten, slik at du kan få en ide om hva det for øyeblikket gjør: Standardoppsettet kommer med Låser-fanen. La oss si at en økt utfører følgende handlinger: Standardlås-fanen viser dette: Endring av det til følgende gir noen flere detaljer: Optimizer nonstandard Jeg finner dette nyttig for å sjekke hvilke optimaliseringsinnstillinger som brukes av en bestemt økt (som kanskje ikke er den samme innstillinger som økten din eller standardinnstillingene for instanser). Dette knytter seg til vsysoptimizerenv (optimeringsrelaterte systemparametere) med vsesoptimizerenv (optimeringsrelaterte øktparametere, først arvet fra systemnivåinnstillingene, men reflekterer eventuelle endringer av alter sessionskommandoer) og rapporterer forskjeller. Temp mellomrom Hvor mye temp mellomrom er denne sesjonen bruker for hash-tilmeldinger, sorterer osv. Kopier William Robertson 2011 Abonner på artikler Abonner på kode og skriptSQL Korrelert Subquery Sammendrag. I denne opplæringen vil du lære om SQL-korrelert subquery. som er en subquery som avhenger av det ytre spørsmålet. Denne opplæringen krever god kunnskap om underkurs. Hvis du ikke vet noe om underundersøkelsen. sjekk det ut undervisningsveiledningen før du går videre med denne opplæringen. Introduksjon til SQL-korrelert subquery En korrelert subquery er en subquery som avhenger av det ytre spørsmålet. Det betyr at WHERE-klausulen i den korrelerte subquery bruker dataene til den ytre spørringen. Hovedforskjellen mellom en korrelert subquery og en ikke-korrelert subquery er at du ikke kan utføre en korrelert subquery alene som en ikke-korrelert subquery. I tillegg utfører en korrelert subquery en gang for hver valgt rad fra det ytre spørsmålet. En korrelert subquery er også kjent som repeterende subquery eller synkronisert subquery. SQL-korrelerte subquery-eksempler Lar oss se på noen eksempler for å forstå ideen om den korrelerte subquery. SQL-korrelert subquery i SELECT-klausuleksempelet Følgende spørring velger topp fem kunder ved salg: Hvordan analysere SQL Server-ytelse 24. februar 2014 Så du har denne SQL Server-databasen som programmet bruker, og det virker som om det er langsomt. Hvordan feilsøker du dette problemet Hvor ser du ut Hva måler du? Jeg håper denne artikkelen vil svare på nok spørsmål for å komme i gang slik at du kan identifisere flaskehalsene selv, eller vite hva du skal søke etter for å utvide ditt arsenal og kunnskap. Hvordan fungerer SQL Server For å kunne feilsøke ytelsesproblemer må du ha en forståelse av hvordan SQL Server fungerer. Den grovt forenklede forklaringen er at SQL Server utfører spørringene dine på følgende måte: Programmet sender en forespørsel til serveren, som inneholder et lagret prosedyrenavn eller noen T-SQL-setninger. Forespørselen er plassert i en kø inne i SQL Server-minnet. En gratis tråd fra SQL Server8217s egen trådbasseng plukker opp forespørselen, kompilerer den og kjører den. Forespørselen er utført uttalelse etter setning, i rekkefølge. En uttalelse i en forespørsel må være ferdig før neste start, alltid. Lagrede prosedyrer utføres på samme måte, erklæring ved utsagn. Uttalelser kan lese eller endre data. Alle data er lest fra databasen i minnet (bufferbassenget). Hvis data ikke er i denne cachen, må den leses fra disk til hurtigbufferen. Alle oppdateringer er skrevet inn i databaseloggen og inn i minnet cachen (inn i bufferbassenget), i henhold til protokollen Skriv-fremoverlogging. Data låsing sikrer korrekthet under samtidighet. Når alle setninger i forespørselen har utført, er tråden fri til å hente en annen forespørsel og utføre den. En forespørsel er enten utført eller venter Dette kan høres trivielt, men forstå at forespørselen enten kjører eller venter på noe (er suspendert) er nøkkelen til feilsøking av SQL Server-ytelsen. Hvis forespørsler som sendes til SQL Server, tar lang tid å returnere resultater, har de heller lang tid å kjøre, eller de tar lang tid å vente. Å vite om det er tilfelle eller det andre er avgjørende for å finne ut ytelsen flaskehalser. I tillegg, hvis forespørsler tar lang tid å vente, kan vi grave videre og finne ut hva de venter på og hvor lenge. Forklaring forespørsel Venter Ventende forespørsler har ventet informasjonsdata Når en forespørsel er suspendert, vil SQL Server av en eller annen grunn samle inn informasjon om hvorfor den ble suspendert og i hvor lang tid. I den interne SQL Server-koden er det rett og slett ingen måte å ringe til funksjonen som suspenderer en oppgave uten å gi de nødvendige ventetiddataene. Og disse dataene blir deretter samlet og gjort tilgjengelig for deg på mange måter. Denne informasjonen om ventetiden er avgjørende for å bestemme ytelsesproblemer: En forespørsel som for tiden utføres, er suspendert akkurat nå kan du se hva som venter på og hvor lenge har ventet på. En forespørsel som for tiden utføres som kjører akkurat nå, kan du se hva som er den siste tingen det ventet på. Du kan forstå når forespørsler venter på andre forespørsler. Du kan få aggregater for hva som er mest ventet på (opptatte) ressurser på hele serveren. Du kan forstå hvilke fysiske (hardware) ressurser som er mettede og forårsaker ytelsesproblemer. Vent Info for øyeblikkelig utførelse av forespørsler For hver forespørsel som kjøres på SQL Server er det en rad i sys. dmexecrequests. Når du spør om dette DMV, gir du et raskt syn på alt som kjører rett da. Waittype. waittime og lastwaittype kolonner vil gi deg en umiddelbar følelse av hva er 8216runnig8217 vs hva er 8216waiting8217 og hva blir ventet på: Forespørsel om økt 53 er en SELECT og venter for tiden. Det venter på en lås. Session 54 er en INSERT og venter for tiden. Det venter på en sidelås. Session 55 er en INSERT og kjører for tiden. Tidligere ventetiden var en sidelås. Session 56 er en INSERT og venter for tiden. Det venter på databasen logg for å spyle (forplikter en transaksjon). Session 57 er en INSERT og venter for tiden. Det venter på loggboken for å skylle. Merk at forespørselen etter økt 54 har status for å kjøre, men det venter faktisk. Dette skyldes at latch-ventene forventes å være korte. Blockingsessionid forteller oss også, for noen av ventende oppgaver, hvilken annen forespørsel innebærer at ressursen for tiden ventes. Session 53 venter på sesjon 56 for en KEY lock ressurs, som betyr at SELECT prøver å lese en rad låst av INSERT. SELECT vil ikke fortsette før INSERT forplikter seg. Session 54 venter på økt 55 for en sidelås, noe som betyr at sesjonen 55 INSERT endrer datasiden akkurat nå, og dataene på siden er ustabile, ikke tillatt å bli lest. Økter 56 og 57 venter, men det er ingen andre økter som blokkerer dem. De venter på loggen til 8216flush8217, noe som betyr at de må sørge for at meldingsrekordet for transaksjonen har blitt varig skrevet til disk. De vil ikke fortsette før diskkontrolleren bekrefter at loggen ble skrevet. Her er et annet eksempel: Forespørsel på sesjon 53 er et COMMIT og venter for tiden. Det venter på loggboken for å skylle. Session 54 er en SELECT og venter for tiden. Det venter på lås. Session 55 er en SELECT og venter for tiden. Det venter på lås. Session 56 er en SELECT og venter for tiden. Det venter på lås. Session 57 er en SELECT og venter for tiden. Det venter på lås. Merk at i dette eksempelet er alle forespørsler faktisk suspendert. I øyeblikket gjør serveren i utgangspunktet ingenting. Vi har 4 økter (54, 55, 56 og 57) som venter på samme lås (raden med nøkkellåsressursen KEY: 5: 72057594039369728 (ac11a2bc89a9)). Session 54 venter på sesjon 55 og sesjon 55 venter på sesjon 53, hvilket betyr at sesjon 54 venter på sesjon 53. Så alle venter på sesjon 53, som venter på diskkontrolleren for å skrive en loggoppføring på disk. Vi kan også se fra waittime-kolonnen, hvor lenge har hver økt ventet på: ca 160 millisekunder. Legg merke til hvordan i det forrige eksempelet vi bare hadde en forespørsel som faktisk løper ut av 5. I8217m kjører disse spørringene på en arbeidsstasjon med 8 kerner, mye RAM og en anstendig disk, så det er nok av maskinvare til å bære disse forespørslene, men i stedet de er de fleste ganger venter i stedet for å utføre. For å gjøre dine forespørsler raskere må du få dem til å bli utført i stedet for å vente. Har de fleste (eller alle) forespørsler venter, i stedet for å kjøre, noe uvanlig. Ikke i det hele tatt, dette er normen. Hver gang vil du se på en SQL Server-utførelse En jevn moderat last, du får se at de fleste forespørsler venter og bare noen få kjøres. Det du trenger å passe på for lang ventetid eller kort, men gjentatte ganger, venter det. Lang ventetid indikerer en ressurs som holdes i lange tider, og vanligvis skjer dette med låser. Gjentatt kort ventetid indikerer en ressurs som blir mettet, muligens et bra sted for ytelse. Før jeg går videre, vil jeg bare vise sys. dmoswaitingtasks. som er en SQL Server DMV spesielt utviklet for å vise ventende oppgaver i øyeblikket: Session 53 venter loggen for å spyle Session 57 venter på 40 ms for en lås på rad og er blokkert av sesjon 53 (som derfor må eie låsen) Session 54 venter på 37 ms for en lås på rad og er blokkert av sesjon 57 (men sesjon 57 er igjen blokkert av sesjon 53) Situasjonen vi har her er mye lik den forrige, hvor vi hadde 4 SELECT-sesjoner blokkert av en INSERT. Han kan se her to forespørsler blokkert ved å forsøke å lese en rad (så de8217 er sannsynligvis SELECT), og de blokkerer økten, og venter på at transaksjonen skal forankres. Informasjonen i denne DMV er veldig lik den i sys. dmexecrequests og den senere har mer informasjon, men det er et viktig tilfelle der informasjonen i sys. dmexecrequests er villedende: parallelle spørringer. For å forstå CXPACKET ventetypene må du se på parallelle oppgaver for barn Når en setning kan ha nytte av parallell kjøring, vil motoren opprette flere oppgaver for forespørselen, hver behandler en delmengde av dataene. Hver enkelt av disse oppgaver kan utføres på en egen CPUcore. Forespørselen kommuniserer med disse oppgavene i utgangspunktet en produsent-forbrukerkø. Spørringsoperatøren som implementerer denne køen kalles en Exchange-operatør (I8217m virkelig forenkler her, les Parallelism Operator (aka Exchange) for en mer nøyaktig beskrivelse). Hvis denne produsent-forbruker køen er tom (det betyr at produsentene ikke presset noen data inn i den), må forbrukeren suspendere og vente og den tilsvarende ventetypen er CXPACKET-ventetypen. Forespørsler som viser denne ventetypen viser virkelig at oppgavene som skulle ha produsert data for å konsumere, ikke produserer noen (eller nok) data. Disse produsentene kan i sin tur bli suspendert, venter på en annen ventetype, og det er det som blokkerer forespørselen din, ikke bytteoperatøren. Her er et eksempel: Her kan vi se at forespørselen på sesjon 54 har 3 oppgaver venter, og mens forespørselen waittype viser CXPACKET. En av de parallelle barnoppgaver venter faktisk på en sidelås. Samlet ventetall: sys. dmoswaitstats SQL Server aggregater statistikk om alle ventetypene og eksponerer dem i en sys. dmoswaitstats. Hvis vi ser på de for øyeblikket utførende forespørslene og venter oppgavene viste oss hva som ble ventet når som helst, gir samlet statistikk en kumulativ status siden serverstart. Å spørre om dette DMV er rettferdig, men tolking av resultatene er litt vanskeligere: For det første, hva er avtalen med alle de ventetypene som tar lion8217s med på toppen av resultatet DIRTYPAGEPOOL. REQUESTFORDEADLOCKSEARCH. LAZYWRITERSLEEP. Vi så ikke noen forespørsel som ventet på disse. Det er derfor: ved å legge til WHERE sessionid 50 filtrerte vi ut bakgrunnsoppgaver, tråder intern for SQL Server som har oppgaven med ulike vedlikeholdsjobber. Mange av disse bakgrunnsoppgaver følger et mønster som 8220 venter på en hendelse, når hendelsen signaliseres, gjør noe arbeid, vent igjen8221, mens andre er mønstret i et sovesyklusmønster (8220sleep i 5 sekunder, våkne opp og gjør noe, gå tilbake å sove 5 sekunder8221). Siden ingen oppgave kan suspendere seg uten å gi ventetype, er det ventetypene som skal hentes når disse bakgrunnsoppgaver suspenderer seg. Ettersom disse utførelsesmønstrene resulterer i at oppgaven faktisk suspenderes nesten hele tiden, triller den aggregerte ventetiden for disse ventetypene ofte hver annen ventetype. SQL Server-fellesskapet kom for å nevne disse ventetypene 8216benign wait types8217 og de fleste eksperter har en liste over ventetypene de bare filtrerer ut, for eksempel se Filtrering ut godartet venter: Nå har vi et mer sammenhengende bilde av ventetiden. bildet forteller oss hvilke ventetypene som er mest utbredt, samlet sett på denne SQL Server-forekomsten. Dette kan være et viktig skritt mot å identifisere en flaskehalsårsak. Videre er dataene subjektive, når de tolker dataene. Er den samlede WRITELOG-verdien på 4636805 millisekunder bra eller dårlig jeg vet ikke. Er et aggregat, akkumulert siden denne prosessen kjører, så vil det åpenbart øke sammenhengende. Kanskje det hadde akkumulert verdier fra perioder da serveren kjørte jevnt og fra perioder da det kjørte dårlig. Imidlertid vet jeg at fra alle 8216non bening8217 ventetyper er WRITELOG den som har den høyeste totale ventetiden. Jeg vet også at maxwaittime for disse ventetypene, så jeg vet at det var minst en gang da en oppgave måtte vente 1,4 sekunder for loggen til å spyle. Waittaskcount forteller meg hvor mange ganger en oppgave ventet på en bestemt ventetype, og deling av waittimemswaittaskcount vil fortelle meg den gjennomsnittlige tiden en bestemt ventetype har blitt ventet på. Og jeg ser flere lås relaterte ventetypene i topp: LCKMS. LCKMIS og LCKMIX. Jeg kan allerede fortelle noen ting om serverens generelle oppførsel, fra et ytelsesperspektiv: Spyling av loggen er den mest ventede ressursen på serveren, og låsingsproblemet synes å være det neste store problemet. CXPACKET ventetypene er der oppe, men vi vet at de fleste ganger CXPACKET ventetypene er bare surrogat for andre ventetyper, som omtalt ovenfor. Hva med de andre med typer på listen, som SOSSCHEDULERYIELD. PAGELATCHEXSH eller PAGEIOLATCHEXSHUP Vanlige ventetyper For å undersøke ventetyper og ventetider, enten samlet eller for en enkelt spørring, må man forstå hva ventetypenavnene betyr. Mange av navnene er selvbeskrivende, noen er kryptiske i deres navn, og noen er nede på høyre deceitfully navngitt. Jeg tror den beste ressursen til å lese en detaljert beskrivelse av navnene er Waits and Queues-hvitt papir. Den har en alfabetisk liste over ventetyper, beskrivelse og hvilken fysisk ressurs er korrelert med. Hvitt papir er litt gammelt (det dekker SQL Server 2005), men fortsatt svært relevant. The wait type names for important resources has not changed since SQL server 2005, what you8217ll be missing will be newer wait type names related to post SQL Server 2005 features. The wait types are briefly documented also in the sys. dmoswaittypes topic on MSDN. Disk and IO related wait types PAGEIOLATCH This is the quintessential IO, data read from disk and write to disk wait type. A task blocked on these wait type is waiting for data to be transferred between the disk and the in-memory data cache (the buffer pool). A system that has high aggregate PAGEIOLATCH wait types is very likely memory starved and is spending much time reading data from the disk into the buffer pool. Be careful not to confuse this wait type with the PAGELATCH wait type (not the missing IO in the name). WRITELOG This wait type occurs when a task is issuing a COMMIT and it waits for the log to write the transaction commit log record to disk. High average wait times on this wait type indicate that the disk is writing the log slowly and this slows down every transaction. Very frequent waits on this wait type are indicative of doing many small transaction and having to block frequently to wait for COMMIT (remember that all data writes require a transaction and one is implicitly created for each statement if no BEGIN TRANSACTION is explicitly used). IOCOMPLETION This wait type occurs for tasks that are waiting for something else that ordinary data IO. Eg. loading an assembly DLL, reading and writing TEMPDB sort files. some special DBCC data reading operations. ASYNCIOCOMPLETION This wait type is mostly associated with backup, restore, database and database file operations. If your wait time analysis finds that IO and disk are important wait types then your should focus on analyzing disk activity . Memory related wait types RESOURCESEMAPHORE This wait type indicate queries that are waiting for a memory grant. See Understanding SQL Server memory grant. OLTP type workload queries should not require memory grants. if you see this wait type on an OLTP system then you need to revisit your application design. OLAP workloads often are in need of (sometimes large) memory grants and large wait times on this wait type usually point toward RAM increase upgrades. SOSVIRTUALMEMORYLOW Are you still on 32 bit systems Move on Network related wait types ASYNCNETWORKIO This wait type indicate that the SQL Server has result sets to send to the application but the application odes not process them. This may indicate a slow network connection. But more often the problem is with the application code, it is either blocking while processing the result set or is requesting a huge result set. CPU, Contention and Concurrency related wait types LCK Locks. All wait types that start with LCK indicate a task suspended waiting for a lock. LCKMS wait types indicate a task that is waiting to read data (shared locks) and is blocked by another task that had modified the data (had acquired an exclusive LCKMX lock). LCKMSCH wait types indicate object schema modification locks and indicate that access to an object (eg. a table) is blocked by another task that did a DDL modfication to that object ( ALTER ). PAGELATCH Do not confuse this wait types with the PAGEIOLATCH wait types. High wait times on these wait types indicate a hot spot in the database, a region of the data that is very frequently updated (eg. it could be a single record in a table that is constantly modified). To further analyze, I recommend the Diagnosing and Resolving Latch Contention on SQL Server white paper. LATCH These wait types indicate contention on internal SQL Server resources, not on data (unlike PAGELATCH they do not indicate a hot spot in the data). To investigate these waits you need to dig further using the sys. dmoslatchstats DMV which will detail the wait times by latch type. Again, the best resource is Diagnosing and Resolving Latch Contention on SQL Server white paper. CMEMTHREAD This wait type occurs when tasks block on waiting to access a shared memory allocator. I placed thi here, in concurrency section, and not in 8216memory8217 section because the problem is related to internal SQL Server concurrency. If you see high CMEMTHREAD wait types you should make sure you are at the latest SQL Server available Service Pack and Cumulative Update for your version, because some of these kind of problems denote internal SQL Server issues and are often addressed in newer releases. SOSSCHEDULERYIELD This wait type can indicate spinlock contention. Spinlocks are extremely light wait locking primitives in SQL Server used for protecting access to resources that can be modified within few CPU instructions. SQL Server tasks acquire spinlocks by doing interlocked CPU operations in a tight loop, so contention on spinlocks burns a lot of CPU time (CPU usage counters show 90-100 usage, but progress is slow). Further analysis need to be done using sys. dmosspinlockstats : RESOURCESEMAPHOREQUERYCOMPILE This wait type indicate that a task is waiting to compile its request. High wait times of this type indicate that query compilation is a bottleneck. For more details I recommend reading Troubleshooting Plan Cache Issues. SQLCLRQUANTUMPUNISHMENT This wait type occurs if you run CLR code inside the SQL Server engine and your CLR code does not yield the CPU in time. This results in an throttling of the CLR code (8220punishment8221). If you have CLR code that can potentially hijack the CPU for a long time you must call Thread. BeginThreadAffinity() . For more details I recommend watching Data, Faster: Microsoft SQL Server Performance Techniques with SQLCLR. Special wait types TRACEWRITE This wait type indicate that tasks are being blocked by SQL Profiler. This wait type occurs only if you have SQL Profiler attached to the server. This wait type occur often during investigation if you had set up a SQL Profiler trace that is too aggressive (gets too many events). PREEMPTIVEOSWRITEFILEGATHER This wait type occurs, among other things, when auto-growth of files is triggered. Auto-growth occurs when a insufficiently sized file is grown by SQL Server and is a hugely expensive event. During growth event all activity on the database is frozen. Data file growth can be made fast by enabling instant file growth, see Database File Initialization. But log growth cannot benefit from instant file initialization so log growth is always slow, sometimes very slow. Log Auto-Growth events can be diagnosed by simply looking at the Log Growths performance counter in the SQL Server, Databases Object. if is anything but 0 it means log auto-growth had occurred at least once. Real-time monitoring can be done by watching for Data File Auto Grow Event Class and Log File Auto Grow Event Class events in SQL Profiler. I did not cover many wait types here (eg. CLR . SQLCLR . SOSHOST . HADR . DTC and many more). If you encounter a wait type you do not understand, usually just searching online for its name will reveal information about what that wait type is what potential bottleneck it indicates, if any. Analyze disk activity: IO stats SQL Server needs to read and write to the disk. All writes (inserts, updates, deletes) must be written to disk. Queries always return data from the in-memory cache (the buffer pool) but the cache may not contain the desired data and has to be read from disk. Understanding if the IO is a bottleneck for your performance is a necessary step in any performance investigation. SQL Server collects, aggregates and exposes information about every data and log IO request. First thing I like to look at is sys. dmiovirtualfilestats . This DMV exposes the number of writes and reads on each file for every database in the system, along with the aggregated read and write IO 8216stall8217 times. Stall times are the total time tasks had to block waiting for transfer of data to and from disk. The per file IO stats are aggregated since server start up but be aware that they reset for each file if it ever goes offline. The total number of bytes transferred (reads and writes) is a good indicator of how busy is a database from IO point of view. The stalls indicate which IO subsytem (which disk) is busy and may even be saturated. Analyzing individual query execution When analyzing an individual statement or query for performance allows us to understand what the query is doing, how much is executing vs. what is waiting on. The most simple analysis is to use the execution statistics. These work by first running the appropriate SET STATISTICS. ON in your session and then executing the statement or query you want to analyze. In addition to the usual result, the query will also report execution statistics, as follows: SET STATISTICS TIME ON The query parse time, compile time and execution time are reported. If you execute a batch or a stored procedure with multiple statements then you get back the time for each statement, which helps determining which statement in a procedure is the expensive one. But we8217ll see that analyzing the execution plan reveals the same information, and more. SET STATISTICS IO ON The IO statistics show, for each statement, the number of IO operations. The result shows several metrics for each table touched by the statement : scan count Number of times the scans or seeks were started on the table. Ideally each table should be scanned at most once. logical reads Number of data pages from which rows were read from the in-memory cache (the buffer pool). physical reads Number of data pages from which data was had to be transferred from disk in-memory cache (the buffer pool) and the task had to block and wait for the transfer to finish. read-ahead reads Number of data pages that were asynchronously transferred from disk into the buffer pool opportunistically and the task did not had to wait for the transfer. LOB logicalphysicalread-ahead reads Same as their non-lob counterparts, but referring to reading of large columns (LOBs). There is also the SET STATISTICS PROFILE ON . which provides a detailed execution information for a statement, but much of the information overlaps with the execution plan info and I think analyzing the execution plan is easier and yields better information. Logical Reads are the best measure of a statement cost. A large logical reads value for a statement indicates an expensive statement. For OLTP environments the number of logical reads should be in single digits. Large logical reads count translate to long execution times and high CPU consumption even under ideally conditions. Under less ideal conditions some or all of these logical reads turn into physical reads which mean long wait times waiting for disk transfer of data into the buffer pool. Large logical reads are indicative of large data scans, usually entire table end-to-end scan. They are most times caused by missing indexes. In OLAP, decision and DW environments, scans and large logical reads may be unavoidable as indexing for an OLAP workload is tricky, and indexing for an ad-hoc analytic workload is basically impossible. For these later cases the answer usually relies in using DW specific technologies like columnstores . Information about query execution statistics can also be obtained using SQL Profiler. The SQL:StmtCompleted . SQL:BatchCompleted , SP:StmtCompleted and RPC:Completed capture the number of page reads, the number of page writes and the execution time of an individual invocation of a statement, a stored procedure or a SQL batch. The number of reads is logical reads. Note that monitoring with SQL Profiler for individual statement completed events will have effect on performance and on a big busy server it can cause significant performance degradation. You can also see the number of logical reads, reads and writes as it happens by looking at the sys. dmexecrequests but this information is visible only if while a request is running. Furthermore the number aggregate all statements in a request so you cannot determine the cost of an individual statement using this DMV. Analyzing individual query execution wait times If you need to drill down more detail that execution time and IO statistics for a query or for a stored procedure, the best information is the wait types and wait times that occurred during execution. Obtaining this information is no longer straightforward though, it involves using Extended Events monitoring. The method I8217m presenting here is based on the Debugging slow response times in SQL Server 2008 article. It involves creating an extended event session that captures the sqlos. waitinfo info and filter the extended events session for a specific execution session (SPID): With the Extended Events session created and started, you can now run the query or procedure you want to analyze. After that, stop the Extended Event session and look at the captured data: As you can see the Extended Events session captured each individual wait, each incident when the session was suspended. If we click on an individual captured event we see the event data XML: This is a 4 millisecond wait for an IX mode lock. Another 2 milliseconds had spent until the task resumed, after the lock was granted. You can shred out the XML into columns for a better view: Finally, we can aggregate all the data in the captured Extended Events session: This is an exceptional wealth of information about what happened during the execution of my request. 742 times it waited on the log commit to be written to disk for a total of 14 seconds wait time, 12 times it waited for a lock, 2 times for a page latch etc. If your curious, my workload I captured was a single row INSERT in a loop. Analyzing Execution Plans SQL Server can expose the actual execution plan for queries. The actual query tree gets serialized as XML and there are several ways to obtain it: SET SHOWPLANXML ON in the session you execute your query. Use the SQL Profiler, see Displaying Execution Plans by Using SQL Server Profiler Event Classes . Enable the Include Actual Execution Plan in SSMS, see Displaying Graphical Execution Plans . Use sys. dmexecqueryplan function to obtain the plan XML from a plan handle. The plan handle can be obtained from DMVs like sys. dmexecrequests (currently executing queries) or sys. dmexecquerystats (past executed queries). If you dont know anything about execution plans then focus on thick arrows I will not enter into much detail into how to analyze a query plan because the subject is way too vast for this article. Execution plans capture what the query did . namely what operators it executed, how many times was each operator invoked, how much data did each operator process and how much data it produced in turn, and how much actual time it took for each operation. Unlike the wait times analysis, analyzing the execution focuses on runtime and where was the actual CPU time consumed on. The graphical display of SSMS simplifies the information in an execution plan XML to make it more easily consumable and the graphical representation makes easy to stop the bottlenecks. Thick arrows represent large data transfers between operators, meaning that operators had to look at a lot of rows to produce their output. Needless to say, the more data an operator needs to process, the more time it takes. Hovering over operators display a popup info note with details about the operator. A large actual row count shows large amount of data being processed. The popup include also the estimated row count which is how much data SQL Server had predicted the operator will process, based on the table statistics. A wild discrepancy between actual and estimated often is responsible for bad performance and can be tracked to outdated statistics on the table. Another are of interest in the execution plan are the operators themselves. Certain operators are inherently expensive, for example sort operators. An expensive operator coupled with a large data input is usually the cause of a huge execution time. Even if you do no know which operators are expensive and which are not, the execution plan contains the cost associated with each operator and you can use this information to get an idea for where problems may be. Identifying problem queries SQL Server exposes the runtime execution statistics for most queries it had run in sys. dmexecquerystats . This DMV shows the number of times a query had run, totalmaxminlast active CPU time (worker time), totalmaxminlast elapsed wall-clock time, totalmaxminlast logical reads and logical writes, totalmaxminlast number of rows returned. This kind of information is a real treasure cove to identify problems in an application: Large execution count Queries that are run frequently are the most sensitive for performance. Even if they perform acceptable, if they are run often then small improvements in each individual execution can yield overall significant speed. But even better is to look, with a critical eye, into why is the query executed often. Application changes can reduce the number of execution count. Large logical reads A query that has to scan large amounts of data is going to be a slow query, there is no question about this. The focus should be in reducing the amount of data scanned, typically the problem is a missing index. A bad query plan can also be a problem. A high number of logical reads is accompanied by a high worker time but the issue is not high CPU usage, but the focus should be on large logical reads. Large physical reads This is basically the same problem as large logical reads, but it also indicates that your server does not have sufficient RAM. Luckily this is, by far, the easiest problem to solve: just buy more RAM, max out the server RAM slots. Youre still going to have a large logical reads, and you should address that problem too, but addressing an application design issue is seldom as easy as ordering 1TB of RAM. High worker time with low logical reads This case is not very common and indicates an operation that is very CPU intensive even with little data to process. String processing and XML processing are typical culprits and you should look at moving the CPU burn toward the periphery, in other words perform the CPU intensive operations on the client, not on the server. High elapsed time with log worker time A query that takes a long wall-clock time but utilizes few CPU cycles is indicative of blocking. This is a query that was suspended most of the time, waiting on something. Wait analysis should indicate the contention cause, the resource being waited for. High total rows count Large result sets requested by the application can be justified in an extremely low number of cases. This problem must be addressed in the application design. Finding a problem query using sys. dmexecquerystats becomes a simply exercise of ordering by the desired criteria (by execution count to find frequently run queries, by logical reads, by elapsed time etc). Tho get the text of the query in question use sys. dmexecsqltext . and to get the query execution plan use sys. dmexecqueryplan : If you do not know what kind of problem query to look for to start with, my advice is to focus in this order: High execution count. Identifying what queries are run most often is in my opinion more important that identifying which queries are particularly slow. More often than not the queries found to be run most often are a surprise and simply limiting the execution count yield significant performance improvements. High logical reads. Large data scans are the usual culprit for most performance problems. They may be caused by missing indices, by a poorly designed data model, by bad plans caused by outdated statistics or parameter sniffing and other causes. High elapsed time with low worker time. Blocking queries do not tax the server much, but your application users do not care if the time they waited in front of the screen for a refresh was spent active or blocked. Missing Indexes Repeatedly I said that performance problems are caused by missing indexes, and SQL Server has a feature aptly named Missing Indexes : The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance. Personally I seldom focus my analysis on the built in missing index feature. To start with the feature has known limitations. but more importantly is that the feature is starting with the solution, wo first searching for a cause. I always start with identifying problem queries first, searching for ones which would yield the highest bang for the buck. Once a problem query is identified as causing large scans, the missing index DMVs can be consulted to identify a candidate. But you should always take the recommendations with a grain of salt. The missing index feature does not know what the query is doing from an application business logic point of view. Reviewing the query can potentially find a better solution than what the automated missing index feature may recommend. Experienced administrators have in place automation that periodically inspects the missing index DMVs and find potential hot spots and problems. This is a very good proactive approach. But in most cases the performance problems are hot and immediate, and finding them using wait analysis or query execution stats is a more direct process. For a more in depth discussion of missing indexes DMVs I recommend spBlitzIndex . TEMPDB Performance tempdb is the special SQL Server database that is used to store any transient data, including things like temporary tables, static cursors, table variables, sort runs, internal worktables, the version store and piles more. Even though the problems that can occur with tempdb are ultimately CPU, IO or contention problems, tempdb deserves a special topic because some of these problems can be understood at a higher level as tempdb problems, as opposed to, say, low level latch contention problems. tempdb also tends to manifest a set of problems entirely of its own, specially due to the very high rate of creation and destruction of objects in tempdb. MSDN has an Optimizing tempdb Performance topic which contains some practical advice, and Cindy Gross has compiled a collection of SQL Server TempDB IO Best Practices . Make each tempdb data file the same size In general I tried to emphasize the importance of measuring and understanding SQL Server performance characteristics instead of blindly following advice. But here is one piece of advice that I always give, wo any secondary consideration: make sure tempdb is stored on files of identical size, with identical growth characteristics. Ive spent just too many hours chasing difficult to diagnose issues to discover that theyre ultimately caused by unaligned tempdb files. I would also recommend as mandatory reading Martin Smiths answer to this dba. stackexchange question:Whats the difference between a temp table and table variable in SQL Server. The answer goes in great detail not only from a tempdb usage point of view, but also impact on locking, compilation, parallelism and indexability, all critical components of performance. SQL Server Performance Counters Performance counters offer a different perspective into SQL Server performance than the wait analysis and execution DMVs do. The biggest advantage of performance counters is that they are extremely cheap to collect and store so they lend themselves as the natural choice for long term monitoring. Looking at current performance counter numbers can reveal potential problems, but interpreting the values is subject to experience and there are a lot of lore and myths on certain magical values. For a analyzing performance problems on a server that you have access and is misbehaving right now . a more direct approach using wait analysis and execution stats is my recommended approach. Where performance counters shine is comparing current behavior with past behavior. for which, obviously, you need to have performance counters values collected and saved from said past. These should be part of an established baseline, which has captured the server behavior under workload when the server was behaving correctly and was not experiencing problems. If you do no have a baseline, consider establishing one. The performance counters infrastructure workhorse is the logman. exe utility. For a quick intro, read Two Minute Drill: LOGMAN. EXE. I recommend you familiarize yourself with this tool. I favor it over the graphic interface ( perfmon. exe ) because, as a command line interface (CLI) tool, it allows for easy scripting. Telling someone run the attached script is always easier that telling click the button, select A from the list, then choose menu B, click twice in the middle of the red square etc etc. The graphic perfmon. exe tool is the tool of choice for displaying performance counters. Performance counters, if collected at sufficient resolution, are also an excellent tool for determining correlation. The graphic display makes easy to spot counters that tend to spike together, or in an anti-correlated pattern (one spikes the other dips). Other patterns also become evident in the graphic visualization, like lag (eg. a counter spike is always preceded by another one increasing). Short of having a data scientist doing factorial analysis on your counters, your own eyes are the best analysis tool. SQL Server usage performance counters Batch Requestssec If you want one counter to best represent the overall performance throughput of your server, this is it. It captures the workload pressure (how many requests the application is making), and how is the server coping with the workload. Trends in this counter need to be further analysed: an increase in the value could mean the server is performing better, because it can accept and handle more requests, but it can also mean the application is sending more requests (bigger, heavier workload), for example more your application is serving more client requests. Transactions The number of active transactions (any type). Be aware that there are at least two counters named Transactions . one in the SQL Server:General Statistics object and another one in the SQL Server:Transactions object. Im referring to the later one. Knowing the number of active transactions is important to set a background on your understanding of the workload. Processes blocked This is specially useful when troubleshooting spikes or jagged performance. For example if you can correlate moments where performance drops with moments when the blocking increase you have found an area to focus your investigation on: blocking. Errorssec Understanding whether errors occur in your workload is a key point in performance troubleshooting. Eliminating errors is an easy path toward performance increase. Number of Deadlockssec Related to the number of Errorssec, monitoring the rate of deadlocks can quickly point toward a performance problem. Is hard to believe that a you are not aware that deadlocks are occurring, given the impact they have on the application. But Ive seen cases where the deadlocks were quietly swallowed in the application or simply there was no communication between the organization monitoring the application and the DBA organization monitoring the server. Log Growths I can hardly think of any event in SQL Server execution that has a more dramatic impact on performance than log growth. During log growth every write request effectively freezes as it waits for available log to generate the write-ahead log records necessary to record any update. Log growth cannot use instant file initialization so it must wait until the log file is grown and the newly allocated disk space is filled with 0s. Your application users might as well watch paint dry. Often cases this is aggravated by a log file gone out of control with 10 increments which is now growing in hundreds of megabytes or even gigabytes at a time. The impact on performance is usually devastating, and is also difficult to track down after the fact. It shows as a sudden performance dip (actually often a complete freeze) that lasts few seconds or even minutes, and then everything is again fast until the next log growth event. The article How to shrink the SQL Server log offers an explanation why log growth can occur. Data and log growth can also be tracked by non-zero values for the PREEMPTIVEOSWRITEFILEGATHER wait info statistics. Data File(s) Size (KB) Monitoring the data size is interesting on its own, but there is another reason why this counter should be collected: it can show when database growth events occur. Unlike Log Growths, data growths do not have counter of their own (shame), but the events can be deduced from sudden jumps in the data file size. Note that data growth can leverage instant file initialization so the impact may not be as dramatic. You should always ensure instant file initialization is enabled for SQL Server, if is not the impact may be serious, specially if you encounter tempdb growth events (which in my experience occurs on most deployments). IO related performance counters Page readssec The rate at which data pages are read from disk. This will be the main driver factor for disk read IO. Normally your workload should read only a very small amount of data from disk, the data should be cached in memory. Constant reading is a sign of data scans (missing indexes) andor insufficient memory (RAM). Log Flushessec Together with the Log Flush Waitssec . Log Flush Wait Time and Log Flush Write Time (ms) you can quickly get a feel of how much blocking occurs due to transactions waiting to commit (log flush events). For well written OLTP intensive applications the rate at which transactions can be committed is often the main performance bottleneck. Page writessec I intentionally put data write after log writes, because most times is the log that is the problem. Only few workloads are data-write heavy. However data-write can interfere with disk IO, specially with log writes, and cause delays in log flush waits that are very visible as application performance drops. The typical pattern is some longer time of good performance with periodic short times of performance dip. Long and short are relative, sometime you see 1-2 minutes long and 10-15 seconds as short, but other times is 10-15 minutes long and 1-2 minutes short. The jagged pattern is the key, with a strong correlation between performance dips and data-writes spikes. The pattern is caused by checkpoints and the timing is driven by the recovery interval option. Looking at Checkpoint pagessec will quickly corroborate this hypothesis. Checkpoint pagessec This counter is the usual culprit to blame Page writessec counter spikes. Log write waits This performance counter exposes the wait info object for log writes. The advantage of using performance counters vs. the DMVs is to leverage the performance counters infrastructure, namely collecting the values in a continuous fashion using the operating system collectors. Page IO latch waits Same as above, this is a wait info exposed through performance counters. Strictly speaking this does not measure IO, it measures only when blocking caused by IO occurs. You cannot distinguish between read IO and write IO with this counter. BackupRestore Throughputsec Often the IO requirements of the maintenance backup operations are forgotten when doing performance analysis. Understanding the additional IO pressure a backup adds and tracking it through this counter is important on its own, but is even more important to understand correlation between spikes in this counter and general performance degradation. This is a scenario Ive see all too often: the maintenance plan kicks in and the overall throughput dips significantly, but nobody understands why. PhysicalDisk Object This category is the operating system counter for monitoring the IO operations. The typical focus is not on throughput, but on queue length, indicating IO operations that take a long time. For a more thorough explanation, read Windows Performance Monitor Disk Counters Explained. Monitoring Usage and Monitoring Queue Length. Operating system exposes IO counters for each individual drive separately, always corroborate findings with the SQL Servers own IO stats as reported by the virtual IO stats DMVs. Another good resource to read is this SQL Server Technet wiki page: Monitoring Disk Usage. ProcessIO Data Operationssec The Process counter category has a different instance for each OS executing process and is very important to analyze when the overall IO on the system does not match the IO as reported by SQL Server. Often times youll discover that another process is causing IO making the disks busy and increasing the response time in SQL Server. Unfortunately monitoring this counter is difficult long term because instances come and go as OS executable processes start and terminate. Pages sec Despite being in the Memory counter category, understanding if and when paging occurs is critical in understand IO performance as paging can introduce a large spike in IO requests. See Monitoring Paging. Memory related performance counters Page life expectancy PLE is measuring the average time a page stays cached in memory before being evicted because of the need to read more data from disk. The DBA lore give this counter a magical value of 300 (5 minutes). This originates with the The 5 Minute Rule for Trading Memory for Disc Accesses paper published back in 1985, then revised as The Five-Minute Rule Ten Years Later and again revised as The Five-Minute Rule 20 Years Later. This 2011 SQL Magazine QA with Paul Randal goes on to say: The value of 300 seconds is a Microsoft recommendation in a white paper that was written five years ago. Its not a relevant guideline nowadays, and even back in 2006 I would have argued that aiming for a set value isnt a valid recommendation. Analyzing PLE is useful as it gives a single value to get a feel for the server memory health, but do not panic if the value is 297 and relax is is 302. More important is to understand trends and the dynamic of this value. A jagged pattern (periodic dips) indicates something that comes and creates a great disturbance in the server memory, perhaps a periodic query that causes a scan, evicting much of the cached data. An overall trend in reducing PLE indicates your workload increases but the server hardware is the same and is a great indicator of potential future problems, better to alleviate now. On machines with NUMA nodes, which in this day and age means pretty much every server, you should pay attention to the PLE per node using the SQL Server:Buffer NodePage life expectancy counter. Read Page Life Expectancy isnt what you think. SQL Server:Memory Manager This category tracks the how is memory used inside the SQL Server process. the absolute values themselves are somehow esoteric, but having a baseline to compare against and looking at long term trends is a great way to determine what changed and what components start putting SQL Server under memory pressure. Memory Grants Pending Im calling out this counter in the Memory Manager category because is particularly important in understanding blocking caused by memory grant waits. Memory grants are only needed for certain query operators and queries requesting large memory grants tend to wreak havoc with your SQL Server performance. Read Understanding SQL server memory grant for more details. Memory grant queue waits Related to the above this is the memory grant wait info exposed as a performance counter. ProcessPrivate Bytes The Process category OS performance counter has an instance for each process in the system and allows you to easily track other processes that may consume memory, causing external memory pressure for SQL Server and ultimately performance degradation. CPU performance counters Processor Object I really hope this one doesnt need an explanation. The OS reported CPU usage counter. Page lookupssec Im mentioning this counter here, in the CPU category, because it is a critical counter to analyze when considering CPU usage in SQL Server. When this counter correlates strongly with CPU usage is a clear indicator of why the CPU usage increased: more pages are scanned, the queries simply look at more data and therefore consume more CPU. The usual culprit is, again, table scans (ie. missing index, poor data modeling or a bad query plan). Process Processor Time The Process category OS performance counter has an instance for each process in the system and allows you to easily track other processes that may consume CPU, causing SQL Server performance degradation. SQL Server blocking related performance counters SQL Server:Locks Monitoring and analyzing the locking performance counters can reveal if lock related blocking is a significant cause of performance problems. Look specially at the Lock Wait Time (ms) value, it will give you an insight on how much percent of the time is spent by queries being blocked waiting for locks. You wont be able to determine exactly what query is blocking what, but knowing that lock waits are significant allows you to focus on analyzing the block chains. spwhoIsActive is a great tool for that. SQL Server:Wait Statistics Most important wait info types are reported also as performance counters. Analyzing these is similar to analyzing the wait statistics using the DMVs, but the performance counters have the advantage of being available in the whole performance counters toolset ecosystem, making easy to collect, store and analyze. Not all wait info types are exposed as performance counters. SQL Server:Latches If you see high Average Latch Wait Time (ms) values you should perform an investigation to find the problem latches. I recommend you read Diagnosing and Resolving Latch Contention on SQL Server. Network performance counters ProTip: Network is not your bottleneck. Network performance comes into picture usually only for deployments using Database Mirroring or Availability Groups. These features are indeed network hungry and require a fast, high bandwidth-low latency connection between the nodes. Furthermore, both DBM and AG tend to push their problems up the stack, resulting in performance degradation when the network traffic cannot keep up with the workload. Network IO waits This counter shows the wait info stats related to the server being blocked because the client is not consuming the result sets. Most often this does no indicate a network problem, but an application design problem: the client is requesting large result sets. Send IO bytessec and Receive IO bytessec These counters show the network traffic generated by Service Broker and Database Mirroring. Availability Groups have a more specialized counter category in SQL Server:Availability Replica . Network Interface This is the performance counter where all the network activity is exposed, from all processes. IP Object. TCP Object These categories are important for monitoring the health of the traffic, not the amount. Specifically they can indicate network errors, dropped packets and retries. Defining your own counters You can actually report custom performance counters from T-SQL. The SQL Server:User Settable is specifically designed for this and allows you to report up the 10 user defined values to be monitored and collected by the system performance counters tools. You publish counter values by using spusercounter1 through spusercounter10 : For application developers is also possible to add performance counters to the application itself. Publishing performance counters from managed () application code is trivially easy, and you can automate the somehow boring process of declaring them, see Using XSLT to generate Performance Counters code . Advanced Performance Analysis Developers are familiar with performance analysis using sample profiling, with tools like VTune. F1 or WPA. The sample profiling technique consist of periodically taking a stack sample of each thread in the profiled process, up to several hundred times per second. The process is non-invasive, it does not require a specially instrumented process, any process can be profiled. It is also fairly lightweight, taking several hundred samples per second has no measurable impact on the performance of the process being sampled. The trick is interpreting the observations. Using the Microsoft public symbols server you can resolve the collected samples to actual function names in the source code (note that in my experience F1 and WPA do a much better job at handling the LTCG code of SQL Server than VTune does). With some common sense, one can make an educated guess what exactly was the SQL Server doing during the period being sampled. This technique makes sense only if you are seeing significant CPU usage. If the performance is bad and you see slow queries but low CPU usage it means the queries are waiting and you should focus instead on wait info analysis. Sample profiling of a blocked process will reveal nothing useful. WPA is one of my favorite performance tools and it can do more than just sample profiling. It can visualize XPerf ETW traces and this makes it extremely powerful. Existing documentation and whitepapers for WPA tends to focus on multimedia and game development, but if you are and adventurous spirit and want to learn about how your favorite database works, using WPA to analyse SQL Server works can reveal a great deal of information and help you spot performance improvements that can be leveraged in your application. One of my favorite use cases for WPA is to collect IO activity (this will collect every single IO request ) and then visualize the Disk Offset as a graph. This is an incredibly simple, yet powerful, way to visualize random IO vs. sequential IO patterns. If you have a feeling WPA and XPerfETW are somehow related to SQL Server Extended Events, that is because they are, very much so. Finally, some readings to get you started on using WPA: The USE method USE stands for Utilization . Saturation and Errors . and the USE Method is a formalized approach to investigating performance troubleshooting. Its focus is on identifying resources in a system and then for each one measure the utilization (percent in use compared to maximum bandwith), the saturation of the resource (extra work queued because the resource cannot service due to 100 utilization) and errors. When used in SQL Server context, the tricky part is to identify the resources and how to measure the utilizationsaturationerrors. Some resources are obvious: disk, CPU, memory, network. Sometimes even simply starting with the USE method and measuring these hardware parameters is a good way to start a performance investigation, at the very least youll know what hardware component is saturated, if any. But knowing how SQL Server works can reveal more detailed, internal resources that can be analyzed. In Understanding how SQL Server executes a query I have shown this image: Many of the components in the image above can be investigated as Resources in the USE method. Applying this methodology requires some thinking outside the box since you will not find much references online about how to apply the USE method to SQL Server, what resources to look for and how to measure the utilization, saturation and errors for each. I myself do not have a clear picture yet for each resource involved and Im still collecting facts and know-how on the subject. But I like the USE approach because it gives a method, a strategy to follow in troubleshooting performance. Kommentarer er stengt.

No comments:

Post a Comment