INF: Begrijpen en oplossen van problemen met blokkering SQL Server

Samenvatting

In dit artikel wordt verwijst de term "verbinding" naar een aangemelde sessie van de database. Elke verbinding wordt weergegeven als een sessie-ID (SPID). Elk van deze SPID's wordt vaak genoemd naar een proces, hoewel dit niet de context van een afzonderlijk proces in de gebruikelijke zin. In plaats daarvan de SPID bestaat uit de serverbronnen en gegevensstructuren voor het aanvragen van een bepaalde client één verbinding nodig. Een enkele client-toepassing kan een of meer verbindingen hebben. Er is geen verschil tussen meerdere verbindingen vanaf een enkel clienttoepassing op één clientcomputer en meerdere verbindingen van meerdere toepassingen of meerdere clientcomputers vanuit het perspectief van SQL Server. Een verbinding kan een andere verbinding, ongeacht of zij uitgaan van de afzonderlijke toepassingen op twee verschillende clients of dezelfde toepassing blokkeren.

Meer informatie

Het blokkeren van is een onvermijdelijk kenmerk van een relationeel databasebeheersysteem (RDBMS) met gelijktijdigheid lock gebaseerd. Op SQL Server blokkeert vindt plaats wanneer een SPID een slot voor een specifieke bron bevat en een tweede SPID probeert aan te schaffen een conflicterende vergrendelingstype op dezelfde bron. De periode waarvoor de eerste SPID de bron vergrendelt is meestal erg klein. Wanneer deze de vergrendeling is de tweede verbinding gratis eigen vergrendelen op de bron en verder verwerken. Dit is normaal en gebeurt vaak tijdens de duur van een dag met geen merkbaar effect op de prestaties van het systeem.

De duur en de transactie context van een query bepaalt hoe lang de vergrendeld en dus hun invloed op de andere query's. Als de query niet wordt uitgevoerd binnen een transactie (en geen lock aanbevolen basisservers worden gebruikt), wordt de vergrendelingen voor SELECT-instructies alleen gehouden op een resource tijdens de die daadwerkelijk worden gelezen, niet voor de duur van de query. Voor de instructies INSERT, UPDATE en DELETE, de vergrendeld voor de duur van de query, zowel voor de consistentie van de gegevens en de query ongedaan worden indien nodig.

Voor query's binnen een transactie worden uitgevoerd, worden de duur waarvoor de vergrendeld worden bepaald door het type query, het isolatieniveau van de transactie en vergrendelen of aanbevolen basisservers gebruikt in de query. Zie de volgende onderwerpen in SQL Server Books Online voor een beschrijving van het vergrendelen van de lock hints en transactie-isolatieniveaus:

  • Vergrendelen in de Database-Engine

  • Aanpassen van de vergrendeling en rij versiebeheer

  • Modi

  • LOCK-compatibiliteit

  • Rij op basis van Versioning isolatieniveaus in de Database-Engine

  • Transacties (Database-Engine) beheren

Wanneer de vergrendeling en blokkering stijgen tot het punt wanneer er een nadelige invloed op de prestaties van het systeem, het is meestal een van de volgende redenen:

  • Een SPID bevat vergrendelingen op een reeks informatiebronnen geruime tijd voordat ze worden vrijgegeven. Blokkeren van dit type oplossing voor zichzelf na verloop van tijd, maar kan leiden tot verminderde prestaties.

  • Een SPID vergrendelingen voor een reeks resources bevat en deze nooit uitgebracht. Dit soort blokkering niet zelf wordt opgelost en voorkomt u dat toegang tot de desbetreffende resources voor onbepaalde tijd.

In het eerste scenario hierboven lost het blokkerende probleem zichzelf na verloop van tijd zodra de SPID de vergrendelingen uitgebracht. De situatie is zeer vloeibare als andere oorzaak SPID's blokkeren op verschillende bronnen over de tijd, het maken van een bewegend doel. Daarom kunnen in deze situaties lastig zijn om op te lossen met behulp van SQL Server Enterprise Manager of afzonderlijke SQL-query's. De tweede situatie resulteert in een consistente status die gemakkelijker te onderzoeken.

Verzamelen van informatie over het blokkeren

Om de moeilijkheden van het oplossen van problemen met blokkering neutraliseren, kan een databasebeheerder voortdurend de status bewaakt van de vergrendeling en blokkering in SQL Server SQL-scripts gebruiken. Deze scripts kunnen geven in de vorm van specifieke exemplaren na verloop van tijd leidt tot een algemene beschrijving van het probleem. Raadpleeg de volgende artikelen in de Microsoft Knowledge Base voor een beschrijving van de blokkering met SQL-scripts controleren:

271509 het controleren van de blokkering in SQL Server 2005 en SQL Server 2000

De scripts in dit artikel wordt de volgende taken uitvoeren. Waar mogelijk, wordt de methode voor het verkrijgen van deze informatie van SQL Server Management Studio gegeven.

  1. Identificeer de SPID (sessie-ID) aan het hoofd van de ketting blokkeren en de SQL-instructie.
    Naast het gebruik van de scripts in het eerder vermelde artikel in de Knowledge Base, kunt u de kop van de ketting blokkeren met behulp van functies die beschikbaar zijn via SQL Server Management Studio te identificeren. Gebruik hiervoor een van de volgende methoden:

    • Klik met de rechtermuisknop op het serverobject, vouw rapportenuit, vouw Standaardrapportenen klik op activiteit: alle transacties blokkeren. Dit rapport geeft de transacties aan het hoofd van de ketting blokkeren. Als u de transactie uitvouwt, worden de transacties die worden geblokkeerd door de hoofd-transactie in de lijst weergegeven. Dit rapport wordt ook weergegeven "Blokkeren SQL-instructie" en de "geblokkeerde SQL-instructie."

    • DBCC INPUTBUFFER(<spid>) gebruiken om de laatste instructie die is ingediend door een SPID.

  2. Het geneste niveau van transactie en processtatus van de blokkering SPID vinden.
    Het geneste niveau van de transactie van een SPID is beschikbaar in de globale variabele @@TRANCOUNT. Echter kan worden vastgesteld van buiten de SPID via query's in de tabel sysprocesses als volgt:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go

    De geretourneerde waarde is de waarde van de @@TRANCOUNT voor de SPID. Dit geeft het geneste niveau van de transactie voor de blokkerende SPID die op zijn beurt wordt uitgelegd waarom het bezit is van vergrendelingen. Als de waarde groter dan nul is, is de SPID bijvoorbeeld door een transactie (in welk geval verwacht wordt dat bepaalde vergrendelingen die het heeft verworven, afhankelijk van het isolatieniveau van de transactie blijft behouden).

    U kunt ook controleren of openstaande transacties op lange termijn aanwezig in de database met behulp van DBCC OPENTRAN
    database_name.

SQL Server Profiler Trace-informatie verzamelen

Naast de bovenstaande gegevens is het vaak nodig om vast te leggen van de activiteiten op de server om een grondig onderzoek een blokkerende probleem op SQL Server Profiler traceren. Als een SPID meerdere instructies binnen een transactie uitvoert, alleen de laatste statementthat wordt weergegeven in het rapport, invoerbuffer of activiteit monitor uitvoer is ingediend. Een van de eerdere opdrachten mag evenwel de reden nog steeds wordt vergrendeld. Een trace Profiler kunt u voor een overzicht van alle opdrachten die worden uitgevoerd door een SPID binnen de huidige transactie. De volgende stappen kunt u SQL Server Profiler instellen voor het vastleggen van een tracering.

  1. Open SQL Server Profiler.

  2. In het menu bestand , wijs Nieuwaan en klik op overtrekken.

  3. Geef de naam van een trace en een bestandsnaam voor het vastleggen van de gegevens op het tabblad Algemeen .

    Belangrijk Het traceringsbestand moet naar een snelle lokale of gedeelde schijf worden geschreven. Vermijd traceren met een trage schijf of netwerkstation. Controleer ook of de dat server verwerkt trace gegevens is geselecteerd.

  4. Klik op het tabblad Gebeurtenissen selectie om het weergeven van alle gebeurtenissen en de selectievakjes voor alle kolommen weergeven te selecteren.

  5. Toevoegen op het tabblad Gebeurtenissen selectie de typen gebeurtenissen die worden vermeld in tabel 1 de traceren.

    U mag bovendien de extra typen gebeurtenissen die worden vermeld in tabel 2 opnemen voor meer informatie. Als u in een productieomgeving met hoge volumes, besluiten u alleen de gebeurtenissen in tabel 1, gebruiken zoals ze meestal voldoende zijn voor de meeste problemen met blokkering. Extra gebeurtenissen met inbegrip van de in tabel 2 wellicht gemakkelijker snel de oorzaak van een probleem vaststellen (of deze gebeurtenissen kunnen worden voor het identificeren van de boosdoener-instructie in een procedure met meerdere instructies). Echter wordt, gebeurtenissen met inbegrip van de in tabel 2 ook toevoegen aan de belasting van het systeem en trace-uitvoer vergroten.

Tabel 1: Gebeurtenistypen

Kop

Gebeurtenis

Fouten en waarschuwingen

Uitzondering

Fouten en waarschuwingen

Aandacht

Beveiligingscontrole

Audit-aanmelding

Beveiligingscontrole

Logout controleren

Sessies

Bestaande verbinding

Opgeslagen Procedures

RPC:Starting

TSQL

SQL:BatchStarting


Tabel 2: Extra gebeurtenistypen

Kop

Gebeurtenis

Transacties

DTCTransaction

Transacties

SQL-transactie

Opgeslagen Procedures

RPC:Completed

TSQL

SQL:BatchCompleted

Opgeslagen Procedures

SP:StmtStarting

Opgeslagen Procedures

SP:StmtCompleted


Zie SQL Server Books Online voor meer informatie over het gebruik van SQL Server Profiler.

Identificeren en oplossen van algemene scenario's voor het blokkeren

Door het onderzoeken van de bovenstaande gegevens, kunt u de oorzaak van de meeste problemen met blokkering bepalen. De rest van dit artikel is een bespreking van hoe u deze gegevens gebruiken om u te identificeren en oplossen van bepaalde gevallen wordt de blokkering. Deze discussie wordt ervan uitgegaan dat u hebt gebruikt de blokkerende scripts in artikel 271509 (waarnaar eerder is verwezen) voor het vastleggen van informatie over de blokkerende SPID's en een trace Profiler hebt gemaakt met de hierboven beschreven gebeurtenissen.

Bekijken van de uitvoer van het Script blokkeren

Bekijk de uitvoer sys.sysprocesses om te bepalen van de hoofden van de blokkerende kettingen

Als u geen snelle modus voor de blokkerende scripts hebt opgegeven, zal er een sectie "SPID's aan het hoofd van de blokkerende kettingen" waarin de SPID's waardoor andere SPID's in de uitvoer van het script wordt geblokkeerd.

SPIDs at the head of blocking chains

Als u de optie snel opgegeven, kunt u nog steeds de blokkerende hoofden bepalen door te kijken bij de uitvoer sys.sysprocesses en volgens de hiërarchie van de SPID die wordt vermeld in de kolom geblokkeerd.

Bekijk de uitvoer sys.sysprocesses voor meer informatie over de SPID's aan het hoofd van de ketting blokkeren.

Het is belangrijk voor de evaluatie van de volgende sys.sysprocesses velden:

Status

Deze kolom bevat de status van een bepaalde SPID. Slapende status geeft doorgaans aan dat de SPID is uitgevoerd en wacht totdat de toepassing in te dienen voor een andere query of batch. Runnable, uitgevoerdof sos_scheduler_yield de status geeft aan dat de SPID momenteel door een query verwerkt wordt. In de volgende tabel geeft een korte uitleg van de verschillende statuswaarden.

Status

Betekenis

Achtergrond

De SPID is een achtergrondtaak, zoals de deadlock-detectie wordt uitgevoerd.

In de slaapstand staat

De SPID wordt momenteel niet uitgevoerd. Dit geeft meestal aan dat de SPID wacht op een opdracht van de toepassing.

Wordt uitgevoerd

De SPID wordt momenteel uitgevoerd op een planner.

Runnable

De SPID is in de wachtrij van een planner en wachten op de planner tijd voor runnable.

Sos_scheduler_yield

De SPID is uitgevoerd, maar deze vrijwillig hebben de tijdsegment op de scheduler wilt toestaan een andere SPID planner tijd verkrijgen heeft opgeleverd.

Geschorst

De SPID wacht op een gebeurtenis, zoals een slot of een vergrendeling.

Ongedaan maken

De SPID is in het terugdraaien van een transactie.

Defwakeup

Geeft aan dat de SPID nog voor een resource die wordt vrijgemaakt. Het veld waitresource moet geven aan de desbetreffende resource.


Open_tran

In dit veld wordt de transactie het geneste niveau van de SPID. Als deze waarde groter dan 0 is, wordt de SPID is in een open transactie en worden de vergrendelingen kan aangeschaft door elke instructie op de transactie.

Lastwaittype, waittype en wachttijd

Het veld lastwaittype is een tekenreeksweergave van het veld waittype , dat een gereserveerde interne binaire kolom is. De waittype is 0x0000, de SPID niet op dit moment nog als de waarde lastwaittype geeft de laatste waittype had de SPID. Als de waittype niet nul is, is de waarde lastwaittype geeft aan dat de huidige waittype van de SPID.

Raadpleeg het volgende artikel in de Microsoft Knowledge base voor een korte beschrijving van de verschillende lastwaittype en waittype -waarden:

822101 beschrijving van de kolommen waittype en lastwaittype in de tabel master.dbo.sysprocesses in SQL Server 2000 en SQL Server 2005

Zie SQL Server Books Online voor meer informatie over sys.dm_os_wait_stats.

De waarde van de wachttijd kan worden gebruikt om te bepalen als de SPID voortgang boekt. Een query op de tabel sys.sysprocesses retourneert een waarde in de kolom wachttijd die kleiner is dan de waarde van de wachttijd van een vorige query van sys.sysprocesses, geeft aan dat de voorafgaande vergrendeling is verkregen en uitgebracht en is nu wachten op een nieuw slot (ervan uitgaande dat de wachttijd niet nul). Dit u kunt controleren door te vergelijken met de waitresource tussen uitvoer sys.sysprocesses .

Waitresource

Dit veld geeft aan dat de resource die op een SPID wacht. De volgende tabel bevat algemene indelingen van waitresource en hun betekenis:

Resource

Indeling

Voorbeeld

Tabel

DatabaseID:ObjectID:IndexID

TABBLAD: 5:261575970:1
In dit geval database-ID 5 wordt de voorbeelddatabase pubs en object-ID 261575970 wordt in de tabel titles en 1 is de geclusterde index.

Pagina

DatabaseID:FileID:PageID

PAGINA: 5:1:104
In dit geval database-ID 5 pubs, bestand-ID 1 het primaire gegevensbestand en 104 pagina een pagina die deel uitmaken van de tabel titles .

Ter identificatie van de object-id waartoe de pagina behoort, gebruikt u de opdracht DBCC PAGE (database-id, bestands-id, pageid, output_option) en kijk naar de m_objId. Bijvoorbeeld:

DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )

Sleutel

DatabaseID:Hobt_id (de hash-waarde voor Indexsleutel)

SLEUTEL: 5:72057594044284928 (3300a4f361aa)

In dit geval database-ID 5 is Pubs, Hobt_ID 72057594044284928 komt overeen met niet-geclusterde index_id 2 voor object-id 261575970 (tabeltitels ). Gebruik de weergave van de catalogus sys.partitions de hobt_id op een bepaalde index-id en de object-id koppelen. Er is geen enkele manier ongedaan te maken van de index key-hash naar een specifieke index sleutelwaarde.

Rij

DatabaseID:FileID:PageID:Slot(row)

VERWIJDEREN: 5:1:104:3

In dit geval database-ID 5 is pubs, bestand-ID 1 is het primaire gegevensbestand pagina 104 is een pagina die deel uitmaken van de tabel titles en sleuf 3 geeft de positie van de rij op de pagina.

Compileren

DatabaseID:ObjectID [[COMPILEREN]]

TABBLAD: 5:834102012 [[COMPILEREN]] dit geen is tabel is vergrendeld, maar in plaats daarvan een compilatie vergrendelen op een opgeslagen procedure. Database-ID 5 pubs, object-ID 834102012 wordt de opgeslagen procedure usp_myprocedure. Zie Knowledge Base-artikel 263889 voor meer informatie over blokkering veroorzaakt door compilatie vergrendelingen.

Andere kolommen

De overige kolommen in de sys.sysprocesses inzicht geven in de hoofdmap van een probleem ook. Hun nut varieert afhankelijk van de omstandigheden van het probleem. Zo kunt u bepalen als het probleem alleen van bepaalde clients (hostnaam), gebeurt er op bepaalde netwerkbibliotheken (net_library), wanneer de laatste batch ingediend door een SPID is (last_batch), enzovoort.

Bekijk de uitvoer van DBCC INPUTBUFFER.

Voor een SPID die aan het hoofd van een keten blokkeren of met een waittype niet-nul, wordt het script blokkeren DBCC INPUTBUFFER om te bepalen van de huidige query voor die SPID uitgevoerd.

In veel gevallen is dit de query die wordt veroorzaakt door de vergrendelingen die worden gehouden van andere gebruikers blokkeren. Echter, als de SPID binnen een transactie is, de vergrendelingen kunnen zijn verworven door een eerder uitgevoerde query niet op de huidige dia. Daarom moet u ook de Profiler uitvoer bekijken voor de SPID, niet alleen de inputbuffer.

Opmerking Omdat het script blokkeren uit meerdere stappen bestaat, is het mogelijk dat een SPID kan worden weergegeven in de eerste sectie als het hoofd van een keten blokkeren, maar op het moment dat de DBCC INPUTBUFFER query wordt uitgevoerd, niet langer blokkeert is en de INPUTBUFFER niet is vastgelegd. Dit betekent dat de blokkering is het oplossen van zelf voor dat de SPID en het al dan niet een probleem. Op dit punt, kunt u gebruikt de snelle versie van het script blokkeren om te proberen om dat het vastleggen van de inputbuffer voordat deze worden verwijderd (Hoewel er nog steeds geen garantie) of de Profiler gegevens uit die periode om te bepalen welke query werd uitgevoerd op de SPID weergeven.

De Profiler-gegevens bekijken

Profiler gegevens efficiënt bekijken is zeer belangrijk bij het oplossen van problemen met blokkering. Het belangrijkste om te realiseren dat u niet hoeft te kijken naar alles wat u vastgelegd; Wees voorzichtig. Profiler biedt mogelijkheden om u te helpen effectief opgenomen gegevens weergeven. In het dialoogvenster Eigenschappen (Klik op Eigenschappenin het menu bestand ), Profiler kunt u de gegevens die worden weergegeven door het verwijderen van kolommen met gegevens of gebeurtenissen, groeperen (sorteren) door de kolommen met gegevens en het toepassen van filters beperken. U kunt de volledige tracering of specifieke kolom voor specifieke waarden zoeken (Klik in het menu bewerken op Zoeken). U kunt ook de Profiler-gegevens opslaan in een SQL Server-tabel (in het menu bestand , wijst u OpslaanAls en klik vervolgens op tabel) en SQL-query's uitvoeren op deze.

Let erop dat u alleen op een eerder opgeslagen traceringsbestand filtering uitvoeren. Als u deze stappen op een actief overtrekken uitvoert, riskeert u gegevensverlies die sinds de start van de tracering is vastgelegd. Een actief overtrekken opslaan in een bestand of een andere tabel eerst (in het menu bestand op OpslaanAls) en opnieuw te openen (Klik in het menu bestand op openen) voordat u verdergaat. Als u werkt in een traceringsbestand opgeslagen, het filter definitief verwijdert u niet de gegevens die worden uitgefilterd, maar worden niet weergegeven de gegevens. U kunt toevoegen en verwijderen van gebeurtenissen en kolommen met gegevens om te helpen focus uw zoekopdrachten.

Wat u moet letten:

  • Welke opdrachten de SPID heeft aan het hoofd van een blokkerende keten binnen de huidige transactie uitgevoerd?
    De traceergegevens filteren op een bepaalde SPID die aan het hoofd van een keten blokkeren (in het menu bestand , klikt u op Eigenschappen, klik op het tabblad Filters geeft u de SPID-waarde). U kunt vervolgens de opdrachten die het heeft uitgevoerd, die is het blokkeren van andere SPID's tijdens de onderzoeken. Als u de transactiegebeurtenissen, ze gemakkelijk kunnen terugvinden wanneer u een transactie is gestart. Anders kunt u de kolom zoeken naar BEGIN, opslaan, COMMIT- of ROLLBACK TRANSACTION-bewerkingen. Gebruik de open_tran -waarde uit de tabel sysprocesses om ervoor te zorgen dat u werkelijk alle transactiegebeurtenissen. De opdrachten uitgevoerd en de transactiecontext weet, kunt u bepalen waarom een SPID is vergrendelingen.

    Denk eraan dat u kunt kolommen met gegevens en gebeurtenissen verwijderen. In plaats van kijken naar beide starten en voltooide gebeurtenissen, kies een. Als de blokkering SPID's niet opgeslagen procedures worden, verwijdert u de
    SP: starten van of SP: voltooid gebeurtenissen. de gebeurtenissen SQLBatch en RPC wordt de procedureoproep weergegeven. Alleen de SP gebeurtenissen als u wilt dat detailniveau weergeven.

  • Wat is de duur van de query's voor SPID's aan het hoofd van de ketens blokkeren?
    Als u de bovenstaande voltooide gebeurtenissen opneemt, wordt de uitvoeringstijd voor query weergegeven in de kolom duur . Hierdoor kunt u identificeren die worden veroorzaakt door het blokkeren van langdurige query's. Bekijken om te bepalen waarom de query trager, de CPU, lezen, en kolommen worden geschreven , alsmede de gebeurtenis Uitvoeringsplan .

Categoriseren van algemene scenario's voor blokkeren

Algemene symptomen in de onderstaande tabel toegewezen aan hun mogelijke oorzaken. Het nummer in de kolom Scenario overeenkomt met het nummer in de sectie "Algemene blokkeren scenario's en oplossingen" van dit artikel hieronder. De kolommen Waittypeen Open_Tran Status Zie sysprocesses . De wordt opgelost? kolom wordt aangegeven of de blokkering op zijn eigen wordt opgelost.

Scenario

Waittype

Open_Tran

Status

Oplossing voor?

Andere symptomen

1

Niet-nulwaarde

> = 0

runnable

Ja, wanneer de query is voltooid.

Physical_IO, CPU en/of Memusage kolommen zal na verloop van tijd toenemen. De duur van de query worden hoog zijn voltooid.

2

0x0000

> 0

in de slaapstand staat

Nee, maar de SPID kan worden beëindigd.

Een signaal aandacht kan worden gezien in het traceerlogboek Profiler voor dit SPID een time-out voor query's die aangeeft of annuleren is opgetreden.

3

0x0000

> = 0

runnable

Nr. Wordt niet opgelost door totdat de client alle rijen worden opgehaald of de verbinding wordt gesloten. SPID kan worden gedood, maar kan maximaal 30 seconden duren.

Als open_tran = 0, en de SPID kan, sloten en het isolatieniveau van de transactie is standaard (Lees COMMMITTED), is dit waarschijnlijk de oorzaak.

4

Varieert

> = 0

runnable

Nr. Wordt niet opgelost door totdat de client query's wordt geannuleerd of gesloten verbindingen. SPID's kunnen worden gedood, maar kunnen maximaal 30 seconden duren.

De kolom hostname in sysprocesses voor de SPID aan het hoofd van een blokkerende keten is hetzelfde als een van de SPID is het blokkeren.

5

0x0000

> 0

ongedaan maken

Ja.

Een signaal aandacht kan worden gezien in het traceerlogboek Profiler voor dit SPID een time-out voor query's die aangeeft of annuleren is opgetreden of gewoon een rollback-instructie is afgegeven.

6

0x0000

> 0

in de slaapstand staat

Uiteindelijk. Wanneer Windows NT dat de sessie actief is bepaalt, wordt de SQL Server-verbinding worden verbroken.

De waarde last_batch in sysprocesses is veel lager dan de huidige tijd.

Algemene scenario's voor het blokkeren en oplossingen

De scenario's die hieronder worden beschreven, heeft de in bovenstaande tabel genoemde kenmerken. Deze sectie bevat aanvullende gegevens, indien van toepassing, evenals de paden op resolutie.

  1. Blokkeren veroorzaakt door een normaal uitgevoerd Query met een lange uitvoeringstijd

    Resolutie:
    De oplossing voor dit soort problemen met blokkering is om te zoeken naar manieren om de query te optimaliseren. Daadwerkelijk, deze klasse van blokkerende probleem mogelijk alleen een probleem met de prestaties en moet u deze als zodanig nastreven. Zie het volgende Microsoft Knowledge Base-artikel voor meer informatie over het oplossen van een specifieke langzaam uitgevoerd query:

    243589 problemen oplossen met trage query's op SQL Server 7.0 of hoger

    Zie het volgende Knowledge Base-artikel voor de algehele prestaties van het oplossen van problemen:

    224587 procedure: prestaties met SQL Server oplossen

    Voor meer informatie Zie prestaties controleren en afstemmen van procedures on line boekbestanden voor SQL Server 2008 op de volgende MSDN-website:

    http://msdn.microsoft.com/en-us/library/ms187830.aspxAls er een langdurige query's dat wordt geblokkeerd door andere gebruikers en kan niet worden geoptimaliseerd, kunt u het verplaatsen van een OLTP-omgeving naar een beslissingen ondersteunend systeem.

  2. Blokkeren veroorzaakt door een slaapstand SPID die heeft verloren spoor van het geneste niveau van de transactie

    Dit soort blokkering kan vaak worden geïdentificeerd door een SPID die wachten op een opdracht of in de slaapstand staat, maar waarvan nestniveau van transactie (@@TRANCOUNT, open_tran van sysprocesses) groter dan nul is. Dit kan gebeuren als de toepassing een time-out voor query's ondervindt of problemen met een annuleren zonder ook het vereiste aantal ongedaan maken en/of COMMIT-instructies. Wanneer een SPID een time-out voor query's of annuleren krijgt, zal beëindigen van de huidige query en batch, maar niet automatisch terug dan de transactie doorgevoerd. De toepassing is verantwoordelijk voor dit als SQL Server niet van uitgaan dat een volledige transactie moet worden teruggedraaid gewoon door een enkele query wordt geannuleerd. De time-out voor query's of op Annuleren wordt weergegeven als een signaalgebeurtenis aandacht voor de SPID in de trace Profiler.

    Verlenen ter demonstratie de volgende eenvoudige query van Query Analyzer:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2

    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN

    Terwijl de query wordt uitgevoerd, klikt u op de rode knop Annuleren . Nadat de query is geannuleerd, selecteer @@TRANCOUNT geeft aan dat het geneste niveau van de transactie een. Dit was een DELETE- of BIJWERKQUERY's of HOLDLOCK niet is gebruikt op de Selecteer alle de vergrendelingen op zou nog steeds worden vastgehouden. Met de query, zelfs als een andere query heeft verworven en gehouden sloten eerder in de transactie, zou ze nog steeds worden gehouden bij het annuleren van de bovenstaande selecteren.

    Resoluties:

    • Toepassingen moeten goed transactie geneste niveaus beheren, of ze na de beëindiging van de query op deze manier blokkeert problemen veroorzaken. Dit kan op verschillende manieren doen:

      1. In de error handler van de clienttoepassing, dienen een IF @@TRANCOUNT > 0 ROLLBACK TRAN na een fout, zelfs als de clienttoepassing niet denkt een transactie dat wordt wordt geopend. Dit is nodig, omdat een opgeslagen procedure aangeroepen tijdens de batch kan een transactie zonder medeweten van de clienttoepassing hebt gestart. Houd er rekening mee dat bepaalde voorwaarden, zoals het annuleren van de query te voorkomen de procedure dat wordt uitgevoerd na de huidige instructie, zelfs dat als de procedure heeft de logica om te controleren als @@ERROR <> 0 en de transactie afgebroken, deze rollback-code niet worden uitgevoerd in deze gevallen.

      2. SET XACT_ABORT ON gebruiken voor de verbinding, of in opgeslagen procedures die beginnen met transacties en worden niet opgeruimd na een fout. Deze instelling wordt in geval van een runtime-fout breken openstaande transacties en besturing wordt teruggegeven aan de client. Houd er rekening mee dat T-SQL-instructies die volgen op de instructie die de fout heeft veroorzaakt niet worden uitgevoerd.

      3. Als groepsgewijze verbindingen wordt gebruikt in een toepassing die wordt geopend verbinding en een klein aantal query's voor het vrijgeven van de verbinding terug naar de groep van toepassingen, zoals een Web gebaseerde toepassing wordt uitgevoerd tijdelijk uitschakelen van groepsgewijze verbindingen kan helpen verlichten de probleem totdat de clienttoepassing voor het afhandelen van fouten op de juiste wijze is gewijzigd. Door het uitschakelen van groepsgewijze verbindingen, zal los van de verbinding een fysieke logout van de SQL Server-verbinding, waardoor de server alle openstaande transacties teruggedraaid.

      4. Als het groeperen van verbindingen is ingeschakeld en de server is SQL Server 2000, een upgrade uitvoeren naar MDAC 2.6 of hoger de client-computer kan het nuttig zijn. Deze versie van de MDAC-onderdelen code wordt toegevoegd aan het ODBC-stuurprogramma en de OLE DB-provider, zodat de verbinding worden 'reset zou' voordat deze opnieuw wordt gebruikt. Deze aanroep van sp_reset_connection server geïnitieerde transacties (DTC transacties geïnitieerd door de clienttoepassing niet beïnvloed) wordt afgebroken, herstelt de standaarddatabase, opties instellen, enzovoort. Houd er rekening mee dat de verbinding wordt pas opnieuw ingesteld nadat deze uit de verbindingsgroep wordt hergebruikt, dus het is mogelijk dat een gebruiker kan een transactie open en laat vervolgens de verbinding met de groep, maar kan niet worden gebruikt voor het aantal seconden gedurende welke de transactie zou open blijven. Als de verbinding niet opnieuw wordt gebruikt, wordt de transactie afgebroken wanneer de verbinding time-out en wordt verwijderd uit de groep. Het is dus optimaal voor de clienttoepassing afgebroken transacties in de foutafhandelingsroutine of SET XACT_ABORT ON gebruiken om te voorkomen dat deze mogelijke vertraging.

    • Eigenlijk, deze klasse van blokkerende probleem mogelijk ook problemen en moet u deze als zodanig nastreven. Als de uitvoeringstijd voor query kan worden afgenomen, de time-out voor query's of annuleren niet plaatsvindt. Het is belangrijk dat de toepassing verwerkt de time-out of annuleren scenario's kunnen worden indien zij zich voordoet, maar u kan ook profiteren van het onderzoek van de prestaties van de query.

  3. Blokkeren veroorzaakt door een SPID met bijbehorende clienttoepassing niet alle Resultatenrijen voltooid ophalen heeft

    Na het verzenden van een query naar de server, moeten alle toepassingen onmiddellijk alle Resultatenrijen ophalen voltooid. Als een toepassing niet alle Resultatenrijen fetch kunnen vergrendelingen blijven op de tabellen, andere gebruikers blokkeren. Als u een toepassing die transparant SQL-instructies naar de server verzendt, moet de toepassing alle Resultatenrijen ophalen. Als dit niet (en als het niet doen), wellicht niet mogelijk de blokkering probleem op te lossen. Als u wilt voorkomen dat het probleem, kunt u toepassingen slecht gedragen op een rapportage of een database ondersteuning voor de besluitvorming te beperken.

    Resolutie:

    De toepassing moet opnieuw worden geschreven voor het ophalen van alle rijen van het resultaat aan.

  4. Blokkeren veroorzaakt door een impasse gedistribueerde Client/Server

    In tegenstelling tot een conventionele impasse is een impasse niet aantoonbaar met RDBMS lock manager. Dit is vanwege het feit dat slechts één van de bronnen die betrokken zijn bij de deadlock een SQL Server-vergrendeling is. De andere kant van de impasse is op het niveau van client, welke SQL-Server geen controle heeft. Hier volgen twee voorbeelden van hoe dit kan gebeuren en de toepassing van de mogelijkheden te kunnen voorkomen.

    1. Client/Server gedistribueerd impasse met een enkele Client-Thread
      Als de client meerdere geopende verbindingen en één thread uitgevoerd heeft, treedt de volgende impasse. Kort te houden verwijst de term "dbproc" gebruikt hier op de client verbindingsstructuur.


      SPID1------blocked on lock------->SPID2
      /\ (waiting to write results
      | back to client)
      | |
      | | Server side
      | ================================|==================================
      | <-- single thread --> | Client side
      | \/
      dbproc1 <------------------- dbproc2
      (waiting to fetch (effectively blocked on dbproc1, awaiting
      next row) single thread of execution to run)
      In het bovenstaande geval heeft een enkele client toepassingsthread twee geopende verbindingen. Asynchroon dient een SQL-bewerking op dbproc1. Dit betekent dat niet wacht op de oproep om terug te keren voordat u verdergaat. De toepassing een andere SQL-bewerking op dbproc2 worden ingediend, en wacht op de resultaten om te starten met de geretourneerde gegevens te verwerken. Wanneer gegevens begint terugkomen (welke dbproc reageert eerst--ervan uitgaan dat dit dbproc1 is), aan alle gegevens op die dbproc geretourneerd worden verwerkt. Deze resultaten worden opgehaald uit dbproc1 totdat de SPID1 wordt geblokkeerd op een vergrendeling die wordt beheerd door de SPID2 (omdat de twee query's worden asynchroon uitgevoerd op de server). Dbproc1 wordt op dit moment voor onbepaalde tijd wachten op meer gegevens. SPID2 op een vergrendeling niet wordt geblokkeerd, maar probeert om gegevens te verzenden naar de client, dbproc2. Dbproc2 wordt echter effectief geblokkeerd op dbproc1 op de toepassingslaag als één uitvoeringsthread voor de toepassing gebruikt wordt door dbproc1. Dit resulteert in een impasse die SQL Server niet opsporen of oplossen omdat slechts één van de betrokken middelen een SQL Server-bron.

    2. Client/Server gedistribueerd impasse met een Thread per verbinding

      Zelfs als een afzonderlijke thread voor elke verbinding op de client aanwezig is, nog steeds een variatie op deze impasse optreden, zoals getoond door het volgende.


      SPID1------blocked on lock-------->SPID2
      /\ (waiting on net write) Server side
      | |
      | |
      | INSERT |SELECT
      | ================================|==================================
      | <-- thread per dbproc --> | Client side
      | \/
      dbproc1 <-----data row------- dbproc2
      (waiting on (blocked on dbproc1, waiting for it
      insert) to read the row from its buffer)
      In dit geval is vergelijkbaar met het voorbeeld A, behalve dbproc2 en SPID2 met een SELECT-instructie met de bedoeling uitvoeren van rij-op-een-time verwerking en afleveren van elke rij in een buffer op dbproc1 voor een INSERT-, UPDATE of-instructie op dezelfde tabel DELETE. Uiteindelijk, SPID1 (voor het uitvoeren van het invoegen, bijwerken of verwijderen) op een vergrendeling die door SPID2 wordt geblokkeerd (uitvoeren van de selecteren). SPID2 schrijft een resultatenrij naar de client-dbproc2. Dbproc2 probeert vervolgens de rij in een buffer doorgeven aan dbproc1, maar dbproc1 Hiermee bezig is (deze is geblokkeerd wacht op de SPID1 te voltooien van de huidige invoegen die op SPID2 wordt geblokkeerd). Dbproc2 is op dit moment op de toepassingslaag geblokkeerd door waarvan SPID (SPID1) op het databaseniveau van de wordt geblokkeerd door SPID2 dbproc1. Nogmaals, dit resulteert in een impasse die SQL Server niet opsporen of oplossen omdat slechts één van de betrokken middelen een SQL Server-bron.

    Beide voorbeelden A en B zijn de fundamentele problemen die ontwikkelaars van toepassingen moeten zich bewust zijn van. Zij moeten toepassingen goed overweg met deze gevallen code.

    Resoluties:

    Twee betrouwbare oplossingen zijn een time-out voor query's of afhankelijke verbindingen te gebruiken.

    • Time-out voor query 's
      Als er een time-out voor query's zijn geleverd, als de impasse optreedt, worden verbroken wanneer vervolgens time-out gebeurt. De DB-Library of de ODBC-documentatie voor meer informatie over het gebruik van een time-out voor query's zien.

    • Afhankelijk van de verbindingen
      Deze functie kan een client met meerdere verbindingen te binden in een enkele transactie ruimte, zodat de verbindingen niet geblokkeerd door elkaar. Zie voor meer informatie het onderwerp 'Afhankelijk van verbindingen met behulp van' in de on line boekbestanden voor SQL Server 7.0.

  5. Blokkeren veroorzaakt door een SPID die in een 'gouden' of Rollback, status

    Een query op een wijziging die wordt gedood of buiten een transactie door de gebruiker gedefinieerde geannuleerd worden teruggedraaid. Dit probleem kan ook optreden als een neveneffect van de client computer opnieuw op te starten en de netwerk-sessie verbreken. Ook worden een query die is geselecteerd als het slachtoffer van de impasse teruggedraaid. Een query op een wijziging worden vaak teruggedraaid sneller dan in eerste instantie worden de wijzigingen zijn toegepast. Bijvoorbeeld, als een instructie DELETE, INSERT of UPDATE actief waren gedurende een uur, kan duurt ten minste een uur terug te draaien. Dit is normaal, omdat de wijzigingen moeten worden volledig hersteld of transactionele en fysieke integriteit in de database zou worden aangetast. Omdat dit gebeuren moet, SQL Server markeert de SPID in een staat 'gouden' of rollback (hetgeen betekent dat niet kan worden gedood of geselecteerde een impasse bevindt). Dit kan vaak worden geïdentificeerd met inachtneming van de uitvoer van sp_who, die mogelijk de opdracht ongedaan maken. De kolom Status van sys.sysprocesses geeft een ROLLBACK-status ook in de uitvoer van sp_who of in SQL Server Management Studio activiteiten controleren weergegeven wordt.
    Resolutie:

    U moet wachten tot de SPID te voltooien met het terugdraaien van de wijzigingen die zijn aangebracht.

    Als de server wordt afgesloten door deze bewerking, uitgevoerd in de herstelmodus worden tijdens het opstarten van de database en deze niet toegankelijk totdat alle open worden transacties verwerkt. Starten Herstel duurt in principe evenveel tijd per transactie als een runtime-herstel en de database is niet toegankelijk tijdens deze periode. Dus dat de server naar fix een SPID in een rollback staat vaak worden ongewenste.

    Voorkom deze situatie geen grote batch INSERT, UPDATE, uitvoeren en verwijderen tijdens drukke uren op OLTP-systemen. Indien mogelijk, deze bewerkingen worden uitgevoerd tijdens perioden met weinig activiteit.

  6. Blokkeren veroorzaakt door een zwevende verbinding

    Als de toepassing client vallen of het clientwerkstation opnieuw wordt opgestart, de netwerksessie naar de server onder bepaalde omstandigheden niet onmiddellijk worden geannuleerd. Vanuit het oogpunt van de server, de client nog steeds aanwezig en eventuele vergrendelingen kunnen nog steeds worden bewaard. Voor meer informatie klikt u op het volgende artikel in de Microsoft Knowledge Base:

    137983 het zwevende verbindingen in SQL Server oplossen


    Resolutie:

    Als de clienttoepassing de verbinding verbroken heeft zonder de middelen op de juiste wijze worden opgeruimd, kunt u de SPID beëindigen met de opdracht KILL. De KILL-opdracht wordt de SPID als invoer. Bijvoorbeeld om de SPID 9 doden, gewoon Geef de volgende opdracht:

    KILL 9


    Opmerking De opdracht KILL kan voltooien als gevolg van het interval tussen de controles voor de opdracht KILL maximaal 30 seconden duren.

Toepassing betrokkenheid bij problemen met blokkering

Er zijn neiging te concentreren op de server-side afstemmen en platform problemen wanneer een blokkerende probleem geconfronteerd. Echter dit meestal niet tot een oplossing leidt, en kan absorberen, tijd en energie beter gericht op het onderzoeken van de clienttoepassing en de query's die worden ingediend. Ongeacht welk niveau van zichtbaarheid van de toepassing ook met betrekking tot de database oproepen vereist blokkeren probleem toch vaak zowel de inspectie van de exacte ingediend door de toepassing en van de toepassing het volledige SQL-instructies probleem met betrekking tot de query annuleren, Verbindingsbeheer, ophalen van alle rijen tot gevolg hebben, enzovoort. Als het hulpprogramma kan geen expliciete controle over het beheer, query annuleren, time-out voor query's, ophalen van resultaat, enzovoort, worden problemen met blokkering niet worden omgezet. Dit potentieel moet nauwkeurig worden onderzocht voordat u een hulpprogramma voor het ontwikkelen van toepassingen voor SQL Server, met name voor bedrijfskritieke OLTP-omgevingen.

Het is essentieel dat zeer zorgvuldig worden uitgeoefend tijdens de fase van het ontwerp en de bouw van de database en de toepassing. In het bijzonder moeten het resourceverbruik, isolatieniveau en weglengte transactie worden geëvalueerd voor elke query. Elke query- en moet zo licht mogelijk zijn. Goede verbinding management begrotingsdiscipline dient te worden uitgeoefend. Als dit niet gebeurt, is het mogelijk dat de toepassing wellicht aanvaardbare prestaties bij lage aantallen gebruikers hebt, maar de prestaties, aanzienlijk naarmate het aantal gebruikers schalen naar boven afnemen kunnen.

Met de juiste toepassing en het queryontwerp, Microsoft SQL Server in staat is ondersteuning van duizenden gebruikers tegelijk op één server met weinig blokkeren.

Meer hulp nodig?

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Microsoft insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagenten.

×