sp_server_diagnostics (T-SQL)

Artikel
04/10/2024

Berlaku untuk:SQL Server

Menangkap data diagnostik dan informasi kesehatan tentang SQL Server untuk mendeteksi potensi kegagalan. Prosedur berjalan dalam mode berulang dan mengirim hasil secara berkala. Ini dapat dipanggil dari koneksi reguler, atau koneksi admin khusus.

Konvensi sintaks transact-SQL

Sintaks

sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]

Argumen

[ @repeat_interval = ] 'repeat_interval'

Menunjukkan interval waktu di mana prosedur tersimpan berjalan berulang kali untuk mengirim informasi kesehatan.

@repeat_interval int dengan default 0. Nilai parameter yang valid adalah 0, atau nilai apa pun yang sama dengan atau lebih dari 5. Prosedur tersimpan harus berjalan setidaknya 5 detik untuk mengembalikan data lengkap. Nilai minimum untuk prosedur tersimpan untuk dijalankan dalam mode pengulangan adalah 5 detik.

Jika parameter ini tidak ditentukan, atau jika nilai yang ditentukan adalah 0, prosedur tersimpan mengembalikan data satu kali lalu keluar.

Jika nilai yang ditentukan kurang dari nilai minimum, nilai tersebut akan menimbulkan kesalahan dan tidak mengembalikan apa pun.

Jika nilai yang ditentukan sama dengan atau lebih dari 5, prosedur tersimpan berjalan berulang kali untuk mengembalikan status kesehatan hingga dibatalkan secara manual.

Mengembalikan nilai kode

0 (berhasil) atau 1 (kegagalan).

Tataan hasil

sp_server_diagnostics mengembalikan informasi berikut.

Kolom	Jenis data	Deskripsi
`create_time`	datetime	Menunjukkan stempel waktu pembuatan baris. Setiap baris dalam satu set baris memiliki stempel waktu yang sama.
`component_type`	nama sysname	Menunjukkan apakah baris berisi informasi untuk komponen tingkat instans SQL Server atau untuk grup ketersediaan AlwaysOn: `instance` `Always On:AvailabilityGroup`
`component_name`	nama sysname	Menunjukkan nama komponen atau nama grup ketersediaan: `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Menunjukkan status kesehatan komponen. Bisa menjadi salah satu nilai berikut: `0`, , `1`, `2`atau `3`
`state_desc`	nama sysname	Menjelaskan kolom status. Deskripsi yang sesuai dengan nilai dalam kolom status adalah: 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (maks)	Menentukan data yang khusus untuk komponen.

Berikut adalah deskripsi lima komponen:

sistem: Mengumpulkan data dari perspektif sistem pada spinlock, kondisi pemrosesan yang parah, tugas yang tidak menghasilkan, kesalahan halaman, dan penggunaan CPU. Informasi ini menghasilkan rekomendasi status kesehatan secara keseluruhan.
sumber daya: Mengumpulkan data dari perspektif sumber daya pada memori fisik dan virtual, kumpulan buffer, halaman, cache, dan objek memori lainnya. Informasi ini menghasilkan rekomendasi status kesehatan secara keseluruhan.
query_processing: Mengumpulkan data dari perspektif pemrosesan kueri pada utas pekerja, tugas, jenis tunggu, sesi intensif CPU, dan tugas pemblokiran. Informasi ini menghasilkan rekomendasi status kesehatan secara keseluruhan.
io_subsystem: Mengumpulkan data di IO. Selain data diagnostik, komponen ini menghasilkan status kesehatan sehat atau peringatan bersih hanya untuk subsistem IO.
peristiwa: Mengumpulkan data dan permukaan melalui prosedur tersimpan pada kesalahan dan peristiwa minat yang dicatat oleh server, termasuk detail tentang pengecualian buffer cincin, peristiwa buffer cincin tentang broker memori, kehabisan memori, monitor penjadwal, kumpulan buffer, spinlock, keamanan, dan konektivitas. Peristiwa selalu ditampilkan 0 sebagai status.
<nama grup> ketersediaan: Mengumpulkan data untuk grup ketersediaan yang ditentukan (jika component_type = "Always On:AvailabilityGroup").

Keterangan

Dari perspektif kegagalan, systemkomponen , , resourcedan query_processing digunakan untuk deteksi kegagalan sementara io_subsystem komponen dan events digunakan hanya untuk tujuan diagnostik.

Tabel berikut memetakan komponen ke status kesehatan terkait.

Komponen	Bersih (1)	Peringatan (2)	Kesalahan (3)	Tidak diketahui (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

x di setiap baris mewakili status kesehatan yang valid untuk komponen. Misalnya, io_subsystem menunjukkan sebagai clean atau warning. Ini tidak menunjukkan status kesalahan.

Catatan

Prosedur sp_server_diagnostics internal diimplementasikan pada utas preemptive dengan prioritas tinggi.

Izin

VIEW SERVER STATE Memerlukan izin pada server.

Contoh

Praktik terbaik menggunakan sesi Extended Events untuk mengambil informasi kesehatan dan menyimpannya ke file yang terletak di luar SQL Server. Oleh karena itu, Anda masih dapat mengaksesnya jika ada kegagalan.

J. Menyimpan output dari sesi Extended Events ke file

Contoh berikut menyimpan output dari sesi peristiwa ke file:

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. Membaca log sesi Extended Events

Kueri berikut membaca file log sesi Extended Events di 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. Mengambil `sp_server_diagnostics` output ke tabel

Contoh berikut menangkap output sp_server_diagnostics ke tabel dalam mode non-ulang:

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;

Kueri berikut membaca output ringkasan dari tabel contoh:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Membaca output terperinci dari setiap komponen

Contoh kueri berikut membaca beberapa output terperinci dari setiap komponen, dalam tabel yang dibuat dalam contoh sebelumnya.

Sistem:

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

Monitor Sumber Daya:

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

Penantian tidak terduga:

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

Penantian preemptive:

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

Permintaan intensif CPU:

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

Laporan proses yang diblokir:

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

Input/output:

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

Informasi peristiwa:

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

Kebijakan Failover untuk Instans Kluster Failover

sp_server_diagnostics (T-SQL)

Sintaks

Argumen

[ @repeat_interval = ] 'repeat_interval'

Mengembalikan nilai kode

Tataan hasil

Keterangan

Izin

Contoh

J. Menyimpan output dari sesi Extended Events ke file

B. Membaca log sesi Extended Events

C. Mengambil `sp_server_diagnostics` output ke tabel

D. Membaca output terperinci dari setiap komponen

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

sp_server_diagnostics (T-SQL)

Sintaks

Argumen

[ @repeat_interval = ] 'repeat_interval'

Mengembalikan nilai kode

Tataan hasil

Keterangan

Izin

Contoh

J. Menyimpan output dari sesi Extended Events ke file

B. Membaca log sesi Extended Events

C. Mengambil sp_server_diagnostics output ke tabel

D. Membaca output terperinci dari setiap komponen

Konten terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

C. Mengambil `sp_server_diagnostics` output ke tabel