Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
van toepassing op:SQL Server-
Legt diagnostische gegevens en statusgegevens vast over SQL Server om mogelijke fouten te detecteren. De procedure wordt uitgevoerd in de herhalingsmodus en verzendt periodiek resultaten. Het kan worden aangeroepen vanuit een normale verbinding of een toegewezen beheerdersverbinding.
Transact-SQL syntaxisconventies
Syntaxis
sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]
Argumenten
Belangrijk
Argumenten voor uitgebreide opgeslagen procedures moeten worden ingevoerd in de specifieke volgorde, zoals beschreven in de sectie Syntaxis. Als de parameters niet in de volgorde zijn ingevoerd, treedt er een foutbericht op.
[ @repeat_interval = ] 'repeat_interval'
Geeft het tijdsinterval aan waarop de opgeslagen procedure herhaaldelijk wordt uitgevoerd om statusgegevens te verzenden.
@repeat_interval is int met de standaardwaarde van 0
. De geldige parameterwaarden zijn 0
of een waarde die gelijk is aan of meer dan 5
. De opgeslagen procedure moet ten minste 5 seconden worden uitgevoerd om volledige gegevens te retourneren. De minimale waarde voor de opgeslagen procedure die moet worden uitgevoerd in de herhalingsmodus is vijf seconden.
Als deze parameter niet is opgegeven of als de opgegeven waarde is 0
, retourneert de opgeslagen procedure gegevens eenmalig en sluit deze af.
Als de opgegeven waarde kleiner is dan de minimumwaarde, wordt er een fout gegenereerd en wordt er niets geretourneerd.
Als de opgegeven waarde gelijk is aan of meer dan 5
, wordt de opgeslagen procedure herhaaldelijk uitgevoerd om de status te retourneren totdat deze handmatig wordt geannuleerd.
Codewaarden retourneren
0
(geslaagd) of 1
(mislukt).
Resultatenset
sp_server_diagnostics
retourneert de volgende informatie.
Kolom | Gegevenstype | Beschrijving |
---|---|---|
create_time |
datum/tijd- | Geeft het tijdstempel aan van het maken van rijen. Elke rij in één rijset heeft hetzelfde tijdstempel. |
component_type |
sysname- | Geeft aan of de rij informatie bevat voor het sql Server-exemplaarniveauonderdeel of voor een AlwaysOn-beschikbaarheidsgroep:instance Always On:AvailabilityGroup |
component_name |
sysname- | Geeft de naam van het onderdeel of de naam van de beschikbaarheidsgroep aan:system resource query_processing io_subsystem events <name of the availability group> |
state |
Geeft de status van het onderdeel aan. Kan een van de volgende waarden zijn: 0 , 1 , 2 of 3 |
|
state_desc |
sysname- | Beschrijft de statuskolom. Beschrijvingen die overeenkomen met de waarden in de kolom Status zijn: 0: Unknown 1: clean 2: warning 3: error |
data |
varchar (max) | Hiermee geeft u gegevens op die specifiek zijn voor het onderdeel. |
Hier volgen de beschrijvingen van de vijf onderdelen:
systeem: verzamelt gegevens vanuit een systeemperspectief op spinlocks, ernstige verwerkingsomstandigheden, niet-opleverende taken, paginafouten en CPU-gebruik. Deze informatie produceert een algemene aanbeveling voor de status.
resource: verzamelt gegevens vanuit een resourceperspectief op fysiek en virtueel geheugen, buffergroepen, pagina's, cache en andere geheugenobjecten. Deze informatie produceert een algemene aanbeveling voor de status.
query_processing: verzamelt gegevens vanuit een queryverwerkingsperspectief op de werkrolthreads, taken, wachttypen, CPU-intensieve sessies en blokkerende taken. Deze informatie produceert een algemene aanbeveling voor de status.
io_subsystem: verzamelt gegevens over IO. Naast diagnostische gegevens produceert dit onderdeel alleen een schone status in orde of waarschuwingsstatus voor een IO-subsysteem.
gebeurtenissen: verzamelt gegevens en oppervlakken via de opgeslagen procedure op de fouten en gebeurtenissen van belang die door de server zijn vastgelegd, inclusief details over ringbufferuitzonderingen, ringbuffergebeurtenissen over geheugenbroker, uit het geheugen, scheduler-monitor, bufferpool, spinlocks, beveiliging en connectiviteit. Gebeurtenissen geven altijd
0
weer als de status.<naam van de beschikbaarheidsgroep>: verzamelt gegevens voor de opgegeven beschikbaarheidsgroep (indien
component_type = "Always On:AvailabilityGroup"
).
Opmerkingen
Vanuit een foutperspectief worden de system
, resource
en query_processing
onderdelen gebruikt voor foutdetectie, terwijl de io_subsystem
- en events
-onderdelen alleen worden gebruikt voor diagnostische doeleinden.
De volgende tabel wijst de onderdelen toe aan de bijbehorende statussen.
Onderdelen | Schoon (1) | Waarschuwing (2) | Fout (3) | Onbekenden (0) |
---|---|---|---|---|
system |
x | x | x | |
resource |
x | x | x | |
query_processing |
x | x | x | |
io_subsystem |
x | x | ||
events |
x |
De x
in elke rij vertegenwoordigt geldige statussen voor het onderdeel.
io_subsystem
wordt bijvoorbeeld weergegeven als clean
of warning
. De foutstatussen worden niet weergegeven.
Notitie
De sp_server_diagnostics
interne procedure wordt uitgevoerd op een preemptive thread met hoge prioriteit.
Machtigingen
Vereist VIEW SERVER STATE
machtiging op de server.
Voorbeelden
Het is raadzaam om uitgebreide gebeurtenissensessies te gebruiken om de statusgegevens vast te leggen en op te slaan in een bestand dat zich buiten SQL Server bevindt. Daarom kunt u deze nog steeds openen als er een fout opgetreden is.
Een. De uitvoer van een sessie met uitgebreide gebeurtenissen opslaan in een bestand
In het volgende voorbeeld wordt de uitvoer van een gebeurtenissessie opgeslagen in een bestand:
CREATE EVENT SESSION [diag]
ON SERVER
ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
ADD TARGET [asynchronous_file_target] (set filename='C:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
ON SERVER STATE = start;
GO
B. Het sessielogboek uitgebreide gebeurtenissen lezen
De volgende query leest het logboekbestand uitgebreide gebeurtenissensessie op SQL Server 2016 (13.x):
SELECT xml_data.value('(/event/@name)[1]', 'varchar(max)') AS Name,
xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package,
xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time',
xml_data.value('(/event/data[@name=''component_type'']/value)[1]', 'sysname') AS SYSNAME,
xml_data.value('(/event/data[@name=''component_name'']/value)[1]', 'sysname') AS Component,
xml_data.value('(/event/data[@name=''state'']/value)[1]', 'int') AS STATE,
xml_data.value('(/event/data[@name=''state_desc'']/value)[1]', 'sysname') AS State_desc,
xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM (
SELECT object_name AS event,
CONVERT(XML, event_data) AS xml_data
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY TIME;
C. Uitvoer van sp_server_diagnostics
vastleggen in een tabel
In het volgende voorbeeld wordt de uitvoer van sp_server_diagnostics
vastgelegd in een tabel in een niet-herhalende modus:
CREATE TABLE SpServerDiagnosticsResult (
create_time DATETIME,
component_type SYSNAME,
component_name SYSNAME,
[state] INT,
state_desc SYSNAME,
[data] XML
);
INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics;
De volgende query leest de samenvattingsuitvoer uit de voorbeeldtabel:
SELECT create_time,
component_name,
state_desc
FROM SpServerDiagnosticsResult;
D. Gedetailleerde uitvoer van elk onderdeel lezen
In het volgende voorbeeld worden enkele gedetailleerde uitvoer van elk onderdeel gelezen in de tabel die in het vorige voorbeeld is gemaakt.
Systeem:
SELECT data.value('(/system/@systemCpuUtilization)[1]', 'bigint') AS 'System_CPU',
data.value('(/system/@sqlCpuUtilization)[1]', 'bigint') AS 'SQL_CPU',
data.value('(/system/@nonYieldingTasksReported)[1]', 'bigint') AS 'NonYielding_Tasks',
data.value('(/system/@pageFaults)[1]', 'bigint') AS 'Page_Faults',
data.value('(/system/@latchWarnings)[1]', 'bigint') AS 'Latch_Warnings',
data.value('(/system/@BadPagesDetected)[1]', 'bigint') AS 'BadPages_Detected',
data.value('(/system/@BadPagesFixed)[1]', 'bigint') AS 'BadPages_Fixed'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'system'
GO
Resource Monitor:
SELECT data.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(max)') AS [Notification],
data.value('(/resource/memoryReport/entry[@description=''Working Set'']/@value)[1]', 'bigint') / 1024 AS [SQL_Mem_in_use_MB],
data.value('(/resource/memoryReport/entry[@description=''Available Paging File'']/@value)[1]', 'bigint') / 1024 AS [Avail_Pagefile_MB],
data.value('(/resource/memoryReport/entry[@description=''Available Physical Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_Physical_Mem_MB],
data.value('(/resource/memoryReport/entry[@description=''Available Virtual Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_VAS_MB],
data.value('(/resource/@lastNotification)[1]', 'varchar(100)') AS 'LastNotification',
data.value('(/resource/@outOfMemoryExceptions)[1]', 'bigint') AS 'OOM_Exceptions'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'resource'
GO
niet-preventieve wachttijden:
SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
waits.evt.value('(@waits)', 'bigint') AS 'Waits',
waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO
voorlopige wachttijden:
SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
waits.evt.value('(@waits)', 'bigint') AS 'Waits',
waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO
CPU-intensieve aanvragen:
SELECT cpureq.evt.value('(@sessionId)', 'bigint') AS 'SessionID',
cpureq.evt.value('(@command)', 'varchar(100)') AS 'Command',
cpureq.evt.value('(@cpuUtilization)', 'bigint') AS 'CPU_Utilization',
cpureq.evt.value('(@cpuTimeMs)', 'bigint') AS 'CPU_Time_ms'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS cpureq(evt)
WHERE component_name LIKE 'query_processing'
GO
rapport geblokkeerd proces:
SELECT blk.evt.query('.') AS 'Blocked_Process_Report_XML'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS blk(evt)
WHERE component_name LIKE 'query_processing'
GO
invoer/uitvoer:
SELECT data.value('(/ioSubsystem/@ioLatchTimeouts)[1]', 'bigint') AS 'Latch_Timeouts',
data.value('(/ioSubsystem/@totalLongIos)[1]', 'bigint') AS 'Total_Long_IOs'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'io_subsystem'
GO
gebeurtenisgegevens:
SELECT xevts.evt.value('(@name)', 'varchar(100)') AS 'xEvent_Name',
xevts.evt.value('(@package)', 'varchar(100)') AS 'Package',
xevts.evt.value('(@timestamp)', 'datetime') AS 'xEvent_Time',
xevts.evt.query('.') AS 'Event Data'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS xevts(evt)
WHERE component_name LIKE 'events'
GO