Anhang A: Überwachen des SQL Server-Zustands
Aktualisiert: 2009-04-30
Microsoft SQL Server 2005 bietet einige Tools zur Überwachung von Datenbanken. Eine Möglichkeit sind dynamische Verwaltungssichten. Dynamische Verwaltungssichten und dynamische Verwaltungsfunktionen geben Informationen über den Serverzustand zurück, die zur Überwachung des Zustands einer Serverinstanz, zur Problemdiagnose und zur Leistungsoptimierung verwendet werden können.
Allgemeine dynamische Verwaltungsobjekte für Server sind u. a.:
dm_db_*: Datenbanken und Datenbankobjekte
dm_exec_*: Ausführung von Benutzercode und zugeordneten Verbindungen
dm_os_*: Speicher, Sperren und Planung
dm_tran_*: Transaktionen und Isolation
dm_io_*: Eingabe/Ausgabe im Netzwerk und auf Datenträgern
Dieser Abschnitt zeigt einige häufig verwendete Abfragen für diese dynamischen Verwaltungssichten und -funktionen zum Überwachen des SQL Server-Zustands.
Beispielabfrage
Mit der folgenden Abfrage können Sie die Namen aller dynamischen Verwaltungssichten und -funktionen abrufen:
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name
Überwachen von CPU-Engpässen
CPU-Engpässe werden häufig durch nicht optimale Abfragepläne, eine ungenaue Konfiguration, schlechtes Design oder unzureichende Hardwareressourcen verursacht. Im Folgenden sehen Sie einige häufig verwendete Abfragen zum Identifizieren der Ursachen eines CPU-Engpasses.
Die folgende Abfrage gibt Ihnen eine Gesamtübersicht der aktuell zwischengespeicherten Batches oder Prozeduren, die derzeit die meisten CPU-Ressourcen belegen.
SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
Die folgende Abfrage zeigt die aggregierte CPU-Verwendung von zwischengespeicherten Plänen mit SQL-Text.
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text
--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle --,
--MIN(statement_start_offset) AS statement_start_offset,
--MAX(statement_end_offset) AS statement_end_offset
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
Die folgende Abfrage zeigt die 50 SQL-Anweisungen mit der höchsten durchschnittlichen CPU-Verwendung.
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
Nachfolgend sehen Sie Abfragen für dynamische Verwaltungssichten, die übermäßige (Neu-)Kompilierungen ermitteln.
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'
Die folgende Beispielabfrage gibt die 25 am häufigsten neu kompilierten gespeicherten Prozeduren zurück. plan_generation_num gibt an, wie häufig die Abfrage neu kompiliert wurde.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
Ein ineffizienter Abfrageplan kann eine höhere CPU-Verwendung verursachen.
Die folgende Abfrage zeigt, welche Abfrage kumulativ die meisten CPU-Ressourcen verwendet.
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
Die folgende Abfrage zeigt einige Operatoren, die möglicherweise CPU-intensiv sind, z. B. %Hash Match%, %Sort%, um mögliche Kandidaten zu ermitteln.
select *
from
sys.dm_exec_cached_plans
cross apply sys.dm_exec_query_plan(plan_handle)
where
cast(query_plan as nvarchar(max)) like '%Sort%'
or cast(query_plan as nvarchar(max)) like '%Hash Match%'
Wenn Sie ineffiziente Abfragepläne erkannt haben, die eine hohe CPU-Verwendung verursachen, führen Sie UPDATE STATISTICS für die in die Abfrage einbezogenen Tabellen aus, und überprüfen Sie, ob das Problem weiterhin besteht. Sammeln Sie anschließend die Daten, und melden Sie das Problem dem PerformancePoint Planning-Support.
Wenn Ihr System übermäßige Kompilierungen und Neukompilierungen aufweist, könnte dies ein CPU-gebundenes Leistungsproblem auf dem System zur Folge haben.
Mit den folgenden Abfragen für dynamische Verwaltungssichten können Sie übermäßige (Neu-)Kompilierungen feststellen.
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'
Die folgende Beispielabfrage gibt die 25 am häufigsten neu kompilierten gespeicherten Prozeduren zurück. plan_generation_num gibt an, wie häufig die Abfrage neu kompiliert wurde.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
Wenn Sie eine übermäßige Kompilierung oder Neukompilierung festgestellt haben, erfassen Sie so viele Daten wie möglich, und melden Sie das Problem dem Planning-Support.
Speicherengpässe
Bevor Sie die Erkennung und Untersuchung von ungenügendem Arbeitsspeicher starten, überprüfen Sie, ob Sie die erweiterten Optionen in SQL Server aktiviert haben. Führen Sie die folgende Abfrage der Masterdatenbank aus, um zunächst diese Option zu aktivieren.
sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go
Führen Sie die folgende Abfrage aus, um zuerst die arbeitsspeicherbezogenen Konfigurationsoptionen zu überprüfen.
sp_configure 'awe_enabled'
go
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
sp_configure 'min memory per query'
go
sp_configure 'query wait'
go
Führen Sie die folgende Abfrage für dynamische Verwaltungssichten aus, um Informationen über die CPU, den Planerarbeitsspeicher und den Pufferpool anzuzeigen.
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
E/A-Engpässe
Identifizieren Sie E/A-Engpässe durch Untersuchen von Latchwartezeiten. Führen Sie die folgende Abfrage für dynamische Verwaltungssichten aus, um die Statistik der E/A-Latchwartezeiten abzurufen.
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' and waiting_tasks_count > 0
order by wait_type
Ein E/A-Problem liegt vor, wenn waiting_task_counts und wait_time_ms beträchtlich von den normalen Werten abweichen. Es ist wichtig, Basisdaten der Leistungsindikatoren und die Ausgaben wichtiger Abfragen für dynamische Verwaltungssichten zu erfassen, wenn SQL Server reibungslos ausgeführt wird.
wait_types können angeben, ob im E/A-Subsystem ein Engpass vorliegt.
Verwenden Sie die folgende Abfrage für dynamische Verwaltungssichten, um die aktuell ausstehenden E/A-Anforderungen zu suchen. Führen Sie diese Abfrage regelmäßig aus, um den Zustand des E/A-Subsystems zu überprüfen und physische Datenträger zu isolieren, die an E/A-Engpässen beteiligt sind.
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
Im Normalfall gibt die Abfrage nichts zurück. Wenn diese Abfrage einige Zeilen zurückgibt, müssen Sie den E/A-Engpass näher untersuchen.
Mit der folgenden Abfrage für dynamische Verwaltungssichten können Sie E/A-gebundene Abfragen suchen.
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_physical_reads,
Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc
Mit der folgenden Abfrage für dynamische Verwaltungssichten können Sie die Batches/Anforderungen suchen, die die meiste E/A-Aktivität verursachen. Eine Abfrage für dynamische Verwaltungssichten wie die folgende sucht die fünf Anforderungen, die die meiste E/A-Aktivität verursachen. Durch Optimieren dieser Abfragen kann die Systemleistung verbessert werden.
select top 5
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by (total_logical_reads + total_logical_writes) Desc
Sperren
Mit der folgenden Abfrage können Sie blockierende Sitzungen ermitteln.
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
Mit diesem Aufruf können Sie ermitteln, welche SQL-Daten von blocking_session_id zurückgegeben werden. Wenn blocking_session_id z. B. den Wert 87 besitzt, können Sie mit dieser Abfrage die SQL-Daten abrufen.
dbcc INPUTBUFFER(87)
Die folgende Abfrage zeigt die Analyse der SQL-Wartezeiten und die 10 Ressourcen mit den längsten Wartezeiten.
select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc
Wenn Sie herausfinden möchten, welche SPID eine andere SPID blockiert, erstellen Sie die folgende gespeicherte Prozedur in der Datenbank und führen diese anschließend aus. Diese gespeicherte Prozedur meldet die blockierende Situation. Geben Sie sp_who ein, um @spid zu ermitteln; @spid ist ein optionaler Parameter.
create proc dbo.sp_block (@spid bigint=NULL)
as
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
Im Folgenden finden Sie Beispiele für die Verwendung dieser gespeicherten Prozedur.
exec sp_block
exec sp_block @spid = 7