Diagnostik i SQL Server att identifiera Avvisad och låsta i/o-operationer

Gäller för: SQL Server 2014 Business IntelligenceSQL Server 2014 Business IntelligenceSQL Server 2014 Developer Mer

Sammanfattning


Ett databashanteringssystem (DBMS) som SQL Server använder tidslinjerna för filen indata och utdata (I/O) operationer. Något av följande kan orsaka låsta eller Avvisad i/o-operationer och inverkar negativt på SQL-serverns svarstider och prestanda:

  • Felaktig maskinvara
  • Felaktigt konfigurerad maskinvara
  • Programvarans inställningar
  • Filterdrivrutiner
  • Komprimering
  • Programfel
  • Andra villkor i/o-sökväg
Dessa i/o-problem kan orsaka följande problem uppstår:

  • Blockera
  • Latch konkurrens och timeout-värden
  • Långa svarstider
  • Sträckning av resursen gränser
SQL Server innehåller startar i Microsoft SQL Server 2000 Service Pack 4 (SP4) logik som hjälper till att identifiera har låst sig och fast villkor för databasen i/o-läsningar och skrivningar och log file i/o-läsningar och skrivningar. När en i/o-åtgärden har väntande i 15 sekunder eller längre, utför SQL Server följande steg:

  1. Upptäcker att åtgärden har väntande
  2. Skriver ett informationsmeddelande till felloggen för SQL Server

    Loggmeddelandet text slag av följande:

Informationsmeddelande förklaring

MeddelandetextBeskrivning
<Nummer > förekomst/förekomsterAntalet i/o-begäranden som inte slutförde Läs- eller skrivåtgärd på mindre än 15 sekunder.
FilinformationDet fullständiga filnamnet, namnet på databasen och databas-identifikationsnummer (DBID).
HanteraHandtag för operativsystemet i filen. Du kan använda operativsystemet handtaget med filöverföringsprogram eller andra verktyg för att spåra i/o-begäran (IRP) packet.
FörskjutningFörskjutningen av sist fastnat i/o-åtgärden eller sist har låst sig i/o-åtgärden. Du kan använda förskjutning med filöverföringsprogram eller andra verktyg för att spåra begäranden för IRP.

Obs! När meddelandet skrivs till felloggen för SQL Server, kan inte längre i/o-åtgärden har fastnat eller har låst sig.
Det här meddelandet anger att aktuell belastning kan bero på något av följande villkor:

  • Arbetsbelastningen är överstiger funktionerna i/o-sökvägen.
  • Arbetsbelastningen är överstiger de aktuella funktionerna i systemet.
  • I/o-sökväg har felaktig programvara. kanske en inbyggd programvara eller ett problem med drivrutinen.
  • I/o-sökväg har felaktig maskinvarukomponenter.
Mer information om SQL Server 2000 I/O mönster finns på följande Microsoft-webbplats:Obs! I den här TechNet-artikeln gäller även Microsoft SQL Server 2005 och senare versioner.

Mer Information


Låsta i/o- och avvisad I/O

Fast I/O

Fast I/O definieras som en i/o-begäran som inte är klar. Fast I/O visar ofta en fast IRP. Lös ett fast villkor för i/o-du vanligtvis startar du om datorn eller utför en liknande åtgärd. Låsta i/o-villkor anger vanligtvis något av följande:

  • Felaktig maskinvara
  • Ett fel i en i/o-bankomponent

Avvisad I/O

Avvisad I/O definieras som en i/o-begäran som slut eller som tar lång tid att slutföra. Avvisad beror i/o-normalt på något av följande skäl:

  • Maskinvarukonfigurationen
  • Inbyggda programvarans inställningar
  • Ett filter drivrutin problem som kräver stöd från maskinvara eller programvaruleverantörer för att spåra och lösa

SQL Server har låst sig I/O och fastnat I/O registrering och rapportering

Stöd för Microsoft SQL Server hanterar många fall varje år som rör fast eller Avvisad i/o-problem. Dessa i/o-problem visas på olika sätt. I/o-problem är några av de svåraste problem att diagnostisera och felsöka och de kräver mycket tid och resurser för felsökning från Microsoft och kunden. Rapporteringsfunktioner som har lagts till SQL Server 2000 SP4 och senare avsevärt minska den tid som krävs för att identifiera ett i/o-fel.

Rapportering och registrering av i/o-begäranden är utformade på grundval av per fil. Identifiering och rapportering av Avvisad och låsta i/o-begäranden är två separata åtgärder.

Inspelning

Det finns två tidpunkter när en post inträffar i SQL Server. Först är när i/o-åtgärden verkligen är klar. Om ett i/o-begäran tar mer än 15 sekunder till slut, sker en post-åtgärd. Andra moment är när lazywrite körs. När lazywrite körs lazywrite kontrollerar alla väntande data och alla väntande log file i/o-begäranden. Om 15 sekunder tröskeln har överskridits, uppstår en post-åtgärd.

Rapportering

Rapportering sker i intervall som är 5 minuter eller mer från varandra. Rapportering sker när nästa i/o-begäran på filen. Om en poståtgärd har inträffat och 5 minuter eller mer har gått sedan inträffat den senaste rapporten, informationsmeddelande som nämns i avsnittet "Sammanfattning" skrivs till felloggen för SQL Server.

Tröskelvärdet på 15 sekunder är inte justerbar. Du kan dock inaktivera Avvisad eller låsta i/o-identifiering med spårningsflagga 830, men vi inte rekommenderar att du gör detta.

Inaktivera identifiering när SQL Server startas med den -T830 startparametern att inaktivera upptäckt av varje gång du startar den SQL-Server. Om du vill inaktivera identifiering av en instans av SQL Server som körs för närvarande använder du följande uttryck:

DBCC traceoff (830, -1)
Den här inställningen är effektiv endast för SQL Server-processen.

Obs! En i/o-begäran som blir har låst sig eller har fastnat rapporteras endast en gång. Till exempel om meddelandet rapporterar att 10 i/o-begäranden är har låst sig, uppstår rapporterna 10 inte igen. Om nästa meddelande rapporterar att 15 i/o-begäranden är har låst sig, innebär det att har låst har blivit sig på 15 nya i/o-begäranden.

Spårning i/o-begäranpaket (IRP)

SQL Server använder standard Microsoft Windows API-anrop för att läsa och skriva data. SQL Server använder till exempel följande funktioner:

  • WriteFile
  • ReadFile
  • WriteFileScatter
  • ReadFileGather
Läs- och skrivbehörigheter begäran hanteras av Windows som en i/o-begäranpaket (IRP). Använd båda av följande för att fastställa status för IRP:

  • Microsoft Platform Support hjälp
  • Kernel-felsökare
Mer information om IRP och IRP spårning, gå till följande Microsoft-webbplats och sökning på nyckelordet "IRP":Obs! Kernel-felsökning kan vara en invasiv process eftersom kernel-felsökning kan kräva att du slutar datorn att utföra åtgärder för felsökning. Vi rekommenderar att du kontrollerar alla tillgängliga uppdateringar för följande objekt:

  • BIOS
  • Den inbyggda programvaran
  • Alla andra i/o-bankomponenter
Kontakta maskinvaruleverantören innan du utför ytterligare åtgärder för felsökning. Felsökningssessionen kommer sannolikt drivrutinen, inbyggd programvara och drivrutinen filterkomponent.

Systemets prestanda och fråga plan för åtgärder

Övergripande kan systemprestanda spela en viktig roll i/o-bearbetningen. Du bör beakta allmänna hälsotillstånd systemet när du undersöker för närvarande rapporter av Avvisad eller låsta i/o-åtgärder. Alltför stor belastning kan orsaka långsam övergripande systemet. Detta inkluderar i/o-bearbetningen. Hur systemet när problemet uppstår kan vara en avgörande faktor för att fastställa orsaken till problemet. Till exempel kan om CPU-användningen är hög eller om CPU-användningen är hög när problemet uppstår problemet tyda på att en process på datorn använder så mycket CPU att andra processer påverkas negativt.

Prestandaräknare

Granska följande prestandaräknare för specifik information om i/o-sökvägen om du vill övervaka i/o-prestanda:

  • Medel s/disköverföring
  • Genomsnittlig diskkölängd
  • Aktuell diskkölängd
Medel s/disköverföring tiden på en dator som kör SQL Server är till exempel vanligtvis mindre än 15 millisekunder. Om värdet för genomsnittlig Disk sek/överföring ökar betyder det att IO-undersystemet inte är optimalt håller med i/o-begäran.

Var försiktig när du använder prestandaräknarna eftersom SQL Server tar nytta av asynkrona i/o-funktioner som push disk kölängder kraftigt. Därför innebär inte längre disk kölängder enbart ett problem.

Du kan granska räknare i Resursövervakaren i Windows är "fysisk Disk: Disk-byte/s" disk för varje påverkas och jämföra antalet aktiviteter mot räknare "Process: IO-byte/sek" och "Process: O andra byte per sekund" för varje process att avgöra om en viss uppsättning processer orsakar hög I/O begär. Det finns olika I/O-relaterade räknare i processen objekt som visar mer detaljerad information. Läs nästa avsnitt på "Index och parallellitet" om du anser att en instans av SQL Server är ansvarig för hög i/o-belastningen på servern. Läs avsnittet "I/o-flaskhalsar" i MSDN whitepaper Felsökning av prestandaproblem i SQL Server 2008 eller Felsökning av prestandaproblem i SQL Server 2005för en detaljerad diskussion om identifiera och lösa i/o-flaskhalsar.

Index och parallellitet

Belastning för I/O uppstår ofta, eftersom det saknas ett index. Detta kan allvarligt push-i/o-sökvägen. Ett pass som använder Index stänga guiden (ITW) kan hjälpa till att lösa i/o-trycket på systemet. Om en fråga som gynnas av ett index i stället för en registergenomsökning eller kanske den använder en sortering eller hash-systemet kan få följande fördelar:

  • Ett avdrag görs i fysiska I/O som krävs för att slutföra en åtgärd som direkt skapar prestandafördelar för frågan.
  • Färre sidor i Datacachen måste omsättas. Dessa sidor finns i Datacachen förblir därför relevanta för aktiva frågor.

  • Sorterar och hash-värden används eftersom det saknas ett index eller statistik är för gammal. Du kan minska tempdb användning och konkurrens genom att lägga till ett eller flera index.
  • Ett avdrag görs i resurser eller den parallella operationer. Eftersom SQL Server inte garantera parallella Frågekörningen, och belastningen på systemet anses, är det bäst att optimera alla frågor för seriell körning. Optimera en fråga, öppna Query Analyzer och ange sp_configure värde för högsta grad av parallellitet alternativ 1. Om alla frågor är justerade för att köra snabbt en seriell åtgärd är parallellkörning ofta bara ett bättre resultat. Många gånger parallellkörning väljs, eftersom mängden data är precis stor. För ett index som saknas kanske en stor sortering ska ske. Flera arbetare som utför sorteringen skapar ett snabbare svar. Den här åtgärden kan avsevärt öka trycket på systemet. Stora Läs förfrågningar från många arbetstagare kan orsaka ett i/o-burst och ökad användning av CPU från flera arbetare. Många gånger en fråga kan ställas in att köras snabbare och använder färre resurser om du lägger till ett index eller om en annan justering sker.

Praktiska exempel från Microsoft SQL Server-stöd

I följande exempel har hanterats av Microsoft SQL Server Support och stöd för eskalering av plattformar. Exemplen är avsedda att ge en referensram och hjälpfunktion dina förväntningar om har låst sig och fastnat i/o-situationer och om hur ett system kan påverkas och kan svara. Det finns ingen specifik maskinvara eller drivrutiner som utgör en särskild risk eller ökad över en annan. Alla system är samma i detta avseende.

Exempel 1: En logg skrivning som har fastnat i 45 sekunder

Ett försök att skriva en SQL Server-loggfilen med jämna mellanrum blir fastnat i cirka 45 sekunder. Logg-Skriv klart inte i tid. Detta skapar en blockerande villkor som gör att klienten 30 sekunders timeout.

Ansökan en bekräftelse till SQL Server och överföringen blev fastnat som en logga skriva väntar. Detta medför att frågan att fortsätta låser och blockera inkommande förfrågningar från andra klienter. Andra klienter startas sedan timeout. Detta föreningar problemet eftersom programmet inte återställs öppna transaktioner när en fråga timeout uppstår. Detta skapar hundratals öppna transaktioner som låser. Därför inträffar en allvarlig blockering.

Mer information om transaktionen hantering och blockera finns i följande artikel i Microsoft Knowledge Base:

Programmet services en webbplats genom att använda anslutningspooler. Eftersom fler anslutningar blir spärrad skapar webbplatsen fler anslutningar. Dessa anslutningar blir blockerat och cykeln fortsätter.

Efter cirka 45 sekunder logg skrivningen är klar. Men efter denna tid säkerhetskopieras hundratals anslutningar. Allvarliga problem orsaka några minuters återhämtningstiden för SQL Server och programmet. Avvisad i/o-villkor har kombineras med programmet problem, en mycket negativ effekt på systemet.
Lösning
Problemet var spåras låsta i/o-begäran i en drivrutin för Host Bus nätverkskort (HBA). Datorn har flera HBA-kort med stöd för växling vid fel. När en HBA var bakom eller kunde inte kommunicera med Storage Area Network (SAN), har "försök innan växling vid fel" timeout-värdet konfigurerats på 45 sekunder. När tidsgränsen har överskridits cirkulerades i/o-begäran till andra HBA. Andra HBA hanteras begäran och klar snabbt. För att förhindra att sådana villkor bås, bör tillverkaren inställningen "försök innan växling vid fel" i 5 sekunder.

Exempel 2: Filter driver intervention

Många antivirusprogram och säkerhetskopiering produkter använder filterdrivrutiner för i/o. Dessa i/o-filterdrivrutiner blir en del av stapeln i/o-begäran, och de har tillgång till IRP: S begäran. Microsoft Product Support Services har sett olika problem från buggar som skapar fastnat i/o-villkor eller har låst sig i/o-villkoren i ett filter driver genomförande.

Ett villkor var en filterdrivrutin för säkerhetskopiering behandling får du en säkerhetskopia av filer som var öppna när säkerhetskopieringen utfördes. Systemadministratör hade med filkatalogen för SQL Server-data i filen urvalet för säkerhetskopieringen. När säkerhetskopieringen utfördes säkerhetskopian har försökt samla rätt bild av filen när säkerhetskopieringen har startat. På så vis fördröjd i/o-begäranden. I/o-begäranden tilläts till slut bara en i taget så de hanterats av programvaran.

När säkerhetskopieringen har startat bort SQL Server-prestanda dramatiskt eftersom-i/o. SQL Server var tvungen att avsluta en i taget. Utfärdande av foderblandningar, var logik "taget" sådan att i/o-åtgärden inte kunde utföras asynkront. Därför när SQL Server förväntas att bokföra en i/o-begäran och fortsätta, har arbetaren fastnat i läs- eller Skriv call tills i/o-begäran har slutförts. Bearbetningar som en SQL Server read-ahead inaktiverades effektivt genom åtgärder av filterdrivrutinen. Ett annat fel i drivrutinen för filter vänster dessutom "en i taget"-åtgärder i processen, även när säkerhetskopieringen har slutförts. Det enda sättet att återställa SQL Server-prestanda var att stänga och öppna databasen och starta om SQL Server så att filhandtaget har publicerat och leveransförbehåll utan filter driver interaktionen.
Lösning
Lös det här problemet datafiler SQL Server har tagits bort från filen säkerhetskopieringen. Tillverkning programvara korrigeras också problemet som lämnat filen i "en i taget"-läge.

Exempel 3: Dolda fel

Många högre slutet system har flerkanals i/o-sökvägar för att hantera belastningen och liknande verksamheter. Microsoft Product Support har hittat ett problem med program där ett i/o-begäran misslyckas men felet hanteras inte korrekt av programvaran för belastningsutjämning. Programmet kan försöka oändligt antal återförsök. I/o-åtgärden blir kvar och SQL Server går inte att slutföra den angivna åtgärden. Sätt som loggen skriver villkor som beskrivs ovan, kan många dåliga system problem uppstå när sådana villkor wedges systemet.
Lösning
Lös problemet genom krävs att starta om SQL Server ofta. Men ibland måste du starta om operativsystemet för att återställa bearbetning. Vi rekommenderar att du hämtar en uppdatering från i/o-leverantör.

Exempel 4: Fjärrlagring, spegling och raid-enheter

Många system använder spegling eller vidta liknande åtgärder för att förhindra förlust av data. Vissa system som använder spegling är programvarubaserad och vissa är maskinvarubaserad. Den situation som normalt upptäcks av Microsoft Support för dessa system är ökad svarstid.

En ökning av den totala i/o-tiden inträffar när I/O måste slutföras i spegeln innan I/O anses vara fullständig. För fjärransluten spegling installationer kan nätverk försök delta. När enhetsfel uppstår och återskapar raid-system, avbrytas i/o-mönstret också.
Lösning
Strikt konfigurationsinställningarna är minska svarstiden speglingar eller raid återskapa-operationer.


Granska kraven för SQL Server för att stödja fjärrspegling av databaserna för mer information.

Exempel 5: komprimering

Microsoft stöder inte Microsoft SQL Server 7.0 eller Microsoft SQL Server 2000-datafiler och loggfiler på komprimerade enheter. NTFS-komprimering är inte säker för SQL Server eftersom NTFS-komprimering bryter skriva före inloggning (WAL)-protokollet. NTFS-komprimering kräver också ökad bearbetning för varje i/o-åtgärden. Komprimering skapar "en i taget" som problemet som orsakar allvarliga prestandaproblem uppstår.
Lösning
Lös problemet genom att expandera data- och loggfiler.

Granska beskrivningen av stöd för SQL Server-databaser på komprimerade volymer för mer information.

Ytterligare datapunkter

PAGEIOLATCH_ * och writelog väntar i sys.dm_os_wait_stats dynamisk hantering vyer (DMV) är viktiga indictors att undersöka i/o-prestanda för sökvägen. Om betydande PAGEIOLATCH väntar visas innebär det att SQL Server väntar på IO-undersystemet. Mängden PAGEIOLATCH väntar är normalt och förväntat. Men om den genomsnittliga PAGEIOLATCH väntetid är alltid större än 10 millisekunder (ms), bör du undersöka varför IO-undersystemet är under tryck. Mer information finns i följande dokument:



SQL Server kräver att system stöder "garanterad leverans till stabila media" enligt SQL Server i/o-tillförlitlighet Program krav. Mer information om kraven på indata- och utdatafilter för databasmotorn SQL Server finns i följande artikel i Microsoft Knowledge Base: