Überwachen der Leistung von Microsoft Azure SQL Managed Instance mithilfe von dynamischen Verwaltungssichten

Artikel
10/16/2024

Gilt für: Azure SQL Managed Instance

Microsoft Azure SQL Managed Instance unterstützt eine Teilmenge dynamischer Verwaltungssichten (DMVs) für die Diagnose von Leistungsproblemen, die auf blockierte Abfragen oder Abfragen mit langen Laufzeiten, fehlerhafte Abfragepläne usw. zurückzuführen sind. Dieser Artikel enthält Informationen zum Erkennen häufiger Leistungsprobleme mithilfe von dynamischen Verwaltungssichten.

In diesem Artikel geht es um Azure SQL Managed Instance. Siehe auch Überwachen der Microsoft Azure SQL-Datenbank-Leistung Azure mithilfe von dynamischen Verwaltungssichten.

Berechtigungen

In Azure SQL Managed Instance ist zum Abfragen einer dynamischen Verwaltungssicht die Berechtigung VIEW SERVER STATE erforderlich.

GRANT VIEW SERVER STATE TO database_user;

Bei einer lokalen Instanz von SQL Server und in Azure SQL Managed Instance geben dynamische Verwaltungssichten Informationen zum Serverzustand zurück.

Identifizieren von CPU-Leistungsproblemen

Wenn die CPU-Auslastung über längere Zeit über 80 % liegt, sollten Sie die folgenden Problembehandlungsschritte in Betracht ziehen:

Das CPU-Problem tritt jetzt auf

Wenn das Problem aktuell auftritt, sind zwei Szenarien möglich:

Viele einzelne Abfragen, die zusammen zu einer hohen CPU-Auslastung führen

Verwenden Sie die folgende Abfrage, um die häufigsten Abfragehashes zu identifizieren:

PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';
SELECT TOP 10 GETDATE() runtime, *
FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"
    FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text
          FROM sys.dm_exec_requests AS req
                CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats
    GROUP BY query_hash) AS t
ORDER BY Total_Request_Cpu_Time_Ms DESC;

Abfragen mit langer Ausführungszeit, die CPU-Ressourcen verbrauchen, werden noch ausgeführt

Verwenden Sie die folgende Abfrage, um diese Abfragen zu identifizieren:

PRINT '--top 10 Active CPU Consuming Queries by sessions--';
SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY cpu_time DESC;
GO

Das CPU-Problem ist in der Vergangenheit aufgetreten

Wenn das Problem in der Vergangenheit aufgetreten ist und Sie eine Ursachenanalyse durchführen möchten, verwenden Sie den Abfragespeicher. Benutzer mit Datenbankzugriff können T-SQL verwenden, um die Daten aus dem Abfragespeicher abzufragen. Die Standardkonfigurationen des Abfragespeichers verwenden eine Granularität von 1 Stunde. Verwenden Sie die folgende Abfrage, um die Aktivität für Abfragen mit hohem CPU-Verbrauch zu untersuchen. Diese Abfrage gibt die häufigsten 15 Abfragen mit CPU-Ressourcenverbrauch zurück. Denken Sie daran, rsi.start_time >= DATEADD(hour, -2, GETUTCDATE() zu ändern:

-- Top 15 CPU consuming queries by query hash
-- note that a query  hash can have many query id if not parameterized or not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MAX(max_logical_io_reads) max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count, SUM(count_executions) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text
                       FROM sys.query_store_query_text AS qt
                            JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                            JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                            JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
                            JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
                       WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
                       GROUP BY q.query_hash), OrderedCPU AS (SELECT query_hash, total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, max_logical_reads, number_of_distinct_plans, number_of_distinct_query_ids, total_executions, Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count, sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
                                                              FROM AggregatedCPU)
SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, OD.max_logical_reads, OD.number_of_distinct_plans, OD.number_of_distinct_query_ids, OD.total_executions, OD.Aborted_Execution_Count, OD.Regular_Execution_Count, OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_millisec DESC;

Sobald Sie die problematischen Abfragen identifiziert haben, sollten Sie diese optimieren, um die CPU-Auslastung zu reduzieren. Wenn Sie keine Zeit haben, die Abfragen zu optimieren, können Sie auch das Servicelevelziel der verwalten Instanz erweitern, um das Problem zu umgehen.

Identifizieren von Problemen mit der E/A-Leistung

Wenn Sie Probleme mit der E/A-Leistung feststellen, sind dies die häufigsten Wartetypen in Zusammenhang mit E/A-Problemen:

PAGEIOLATCH_*

Für E/A-Probleme bei Datendateien (einschließlich PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Wenn der Name des Wartetyps die Buchstaben IO enthält, verweist er auf ein E/A-Problem. Wenn der Name des Pagelatch-Wartevorgangs die Buchstaben EA nicht enthält, verweist er auf einen anderen Problemtyp (z. B. einen tempdb-Konflikt).
WRITE_LOG

Für E/A-Probleme bei Transaktionsprotokollen.

Das E/A-Problem tritt jetzt auf

Verwenden Sie sys.dm_exec_requests oder sys.dm_os_waiting_tasks, um wait_type und wait_time anzuzeigen.

Für Option 2 können Sie die folgende Abfrage für pufferbezogene Vorgänge im Abfragespeicher verwenden, um die letzten zwei Stunden der überwachten Aktivitäten anzuzeigen:

-- top queries that waited on buffer
-- note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_desc
                    FROM sys.query_store_query_text AS qt
                         JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                         JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                         JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
                         JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
                    WHERE wait_category_desc='Buffer IO' AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
                    GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS RN
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RN
FROM Ordered AS OD
WHERE OD.RN<=15
ORDER BY total_wait_time_ms DESC;
GO

Anzeigen sämtlicher E/A-Vorgänge für WRITELOG-Wartevorgänge

Wenn der Wartetyp WRITELOG ist, verwenden Sie die folgende Abfrage, um sämtliche Protokoll-E/A-Vorgänge nach Anweisung anzuzeigen:

-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
           SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
           MAX(max_logical_io_reads) max_logical_reads,
           COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
           COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Aborted' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Aborted_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Regular' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Regular_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Exception' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Exception_Execution_Count,
           SUM(count_executions) AS total_executions,
           MIN(qt.query_sql_text) AS sampled_query_text
    FROM sys.query_store_query_text AS qt
        JOIN sys.query_store_query AS q
            ON qt.query_text_id = q.query_text_id
        JOIN sys.query_store_plan AS p
            ON q.query_id = p.query_id
        JOIN sys.query_store_runtime_stats AS rs
            ON rs.plan_id = p.plan_id
        JOIN sys.query_store_runtime_stats_interval AS rsi
            ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rs.execution_type_desc IN ( 'Regular', 'Aborted', 'Exception' )
          AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE())
    GROUP BY q.query_hash),
     OrderedLogUsed
AS (SELECT query_hash,
           total_log_bytes_used,
           number_of_distinct_plans,
           number_of_distinct_query_ids,
           total_executions,
           Aborted_Execution_Count,
           Regular_Execution_Count,
           Exception_Execution_Count,
           sampled_query_text,
           ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS RN
    FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
       OD.number_of_distinct_plans,
       OD.number_of_distinct_query_ids,
       OD.total_executions,
       OD.Aborted_Execution_Count,
       OD.Regular_Execution_Count,
       OD.Exception_Execution_Count,
       OD.sampled_query_text,
       OD.RN
FROM OrderedLogUsed AS OD
WHERE OD.RN <= 15
ORDER BY total_log_bytes_used DESC;
GO

Identifizieren von Problemen mit der `tempdb`-Leistung

Wenn Sie Probleme mit der E/A-Leistung feststellen, ist PAGELATCH_* (nicht PAGEIOLATCH_*) der häufigste Wartetyp in Zusammenhang mit tempdb-Problemen. PAGELATCH_*-Wartevorgänge bedeuten jedoch nicht immer, dass ein tempdb-Konflikt vorliegt. Dieser Wartevorgang kann auch auf einen Konflikt mit Benutzerobjekt-Datenseiten aufgrund von gleichzeitigen Anforderungen an dieselbe Datenseite hinweisen. Zur genaueren Ermittlung, ob es sich um einen tempdb-Konflikt handelt, verwenden Sie sys.dm_exec_requests, um zu bestätigen, dass der Wert von „wait_resource“ mit 2:x:y beginnt, wobei „2“ die tempdb-Datenbank-ID, x die Datei-ID und y die Seiten-ID ist.

Bei tempdb-Konflikten besteht eine gängige Methode darin, den Anwendungscode, der tempdb benötigt, zu reduzieren oder neu zu schreiben. In folgenden Bereichen wird tempdb meist genutzt:

Temporäre Tabellen
Tabellenvariablen
Tabellenwertparameter
Versionsspeichernutzung (in Zusammenhang mit Transaktionen mit langer Ausführungszeit)
Abfragen mit Abfrageplänen, die Sortiervorgänge, Hashjoins und Spoolvorgänge verwenden

Häufige Abfragen, die Tabellenvariablen und temporäre Tabellen verwenden

Verwenden Sie die folgende Abfrage, um die häufigsten Abfragen zu identifizieren, die Tabellenvariablen und temporäre Tabellen verwenden:

SELECT plan_handle, execution_count, query_plan
INTO #tmpPlan
FROM sys.dm_exec_query_stats
     CROSS APPLY sys.dm_exec_query_plan(plan_handle);
GO

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') 'Database', stmt.stmt_details.value('@Schema', 'varchar(max)') 'Schema', stmt.stmt_details.value('@Table', 'varchar(max)') 'table'
INTO #tmp2
FROM(SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p
    CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);
GO

SELECT t.plan_handle, [Database], [Schema], [table], execution_count
FROM(SELECT DISTINCT plan_handle, [Database], [Schema], [table]
     FROM #tmp2
     WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t
    JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;

Identifizieren von Transaktionen mit langer Ausführungszeit

Verwenden Sie die folgende Abfrage, um Transaktionen mit langer Ausführungszeit zu identifizieren. Transaktionen mit langer Ausführungszeit verhindern das Bereinigen von Versionsspeichern.

SELECT DB_NAME(dtr.database_id) 'database_name',
       sess.session_id,
       atr.name AS 'tran_name',
       atr.transaction_id,
       transaction_type,
       transaction_begin_time,
       database_transaction_begin_time, 
       transaction_state,
       is_user_transaction,
       sess.open_transaction_count,
       TRIM(REPLACE(
                REPLACE(
                            SUBSTRING(
                                        SUBSTRING(
                                                    txt.text,
                                                    (req.statement_start_offset / 2) + 1,
                                                    ((CASE req.statement_end_offset
                                                            WHEN -1 THEN
                                                                DATALENGTH(txt.text)
                                                            ELSE
                                                                req.statement_end_offset
                                                        END - req.statement_start_offset
                                                    ) / 2
                                                    ) + 1
                                                ),
                                        1,
                                        1000
                                    ),
                            CHAR(10),
                            ' '
                        ),
                CHAR(13),
                ' '
            )
            ) Running_stmt_text,
       recenttxt.text 'MostRecentSQLText'
FROM sys.dm_tran_active_transactions AS atr
    INNER JOIN sys.dm_tran_database_transactions AS dtr
        ON dtr.transaction_id = atr.transaction_id
    LEFT JOIN sys.dm_tran_session_transactions AS sess
        ON sess.transaction_id = atr.transaction_id
    LEFT JOIN sys.dm_exec_requests AS req
        ON req.session_id = sess.session_id
           AND req.transaction_id = sess.transaction_id
    LEFT JOIN sys.dm_exec_connections AS conn
        ON sess.session_id = conn.session_id
    OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt
    OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS recenttxt
WHERE atr.transaction_type != 2
      AND sess.session_id != @@spid
ORDER BY start_time ASC;

Identifizieren von Leistungsproblemen bei Wartevorgängen für Speicherzuweisung

Wenn der häufigste Wartetyp RESOURCE_SEMAHPORE ist und kein Problem mit einer hohen CPU-Auslastung vorliegt, besteht möglicherweise ein Problem bei Wartevorgängen für die Speicherzuweisung.

Ermitteln, ob ein `RESOURCE_SEMAHPORE`-Wartevorgang ein häufiger Wartevorgang ist

Verwenden Sie die folgende Abfrage, um zu ermitteln, ob ein RESOURCE_SEMAHPORE-Wartevorgang ein häufiger Wartevorgang ist.

SELECT wait_type,
       SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
    JOIN sys.dm_exec_sessions AS sess
        ON req.session_id = sess.session_id
WHERE is_user_process = 1
GROUP BY wait_type
ORDER BY SUM(wait_time) DESC;

Ermitteln von Anweisungen mit hohem Arbeitsspeicherverbrauch

Wenn Fehler wegen Arbeitsspeichermangel auftreten, lesen Sie sys.dm_os_out_of_memory_events.

Verwenden Sie die folgende Abfrage, um Anweisungen mit hohem Arbeitsspeicherverbrauch zu identifizieren:

SELECT IDENTITY(INT, 1, 1) rowId,
    CAST(query_plan AS XML) query_plan,
    p.query_id
INTO #tmp
FROM sys.query_store_plan AS p
    JOIN sys.query_store_runtime_stats AS r
        ON p.plan_id = r.plan_id
    JOIN sys.query_store_runtime_stats_interval AS i
        ON r.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE start_time > '2018-10-11 14:00:00.0000000'
      AND end_time < '2018-10-17 20:00:00.0000000';
GO
;WITH cte
AS (SELECT query_id,
        query_plan,
        m.c.value('@SerialDesiredMemory', 'INT') AS SerialDesiredMemory
    FROM #tmp AS t
        CROSS APPLY t.query_plan.nodes('//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]') AS m(c) )
SELECT TOP 50
    cte.query_id,
    t.query_sql_text,
    cte.query_plan,
    CAST(SerialDesiredMemory / 1024. AS DECIMAL(10, 2)) SerialDesiredMemory_MB
FROM cte
    JOIN sys.query_store_query AS q
        ON cte.query_id = q.query_id
    JOIN sys.query_store_query_text AS t
        ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;

Identifizieren der Speicherzuweisungen

Verwenden Sie die folgende Abfrage, um die 10 häufigsten aktiven Speicherzuweisungen zu identifizieren:

SELECT TOP 10
    CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime,
       r.session_id,
       r.blocking_session_id,
       r.cpu_time,
       r.total_elapsed_time,
       r.reads,
       r.writes,
       r.logical_reads,
       r.row_count,
       wait_time,
       wait_type,
       r.command,
       OBJECT_NAME(txt.objectid, txt.dbid) 'Object_Name',
       TRIM(REPLACE(
                REPLACE(
                            SUBSTRING(
                                        SUBSTRING(
                                                    text,
                                                    (r.statement_start_offset / 2) + 1,
                                                    ((CASE r.statement_end_offset
                                                            WHEN -1 THEN
                                                                DATALENGTH(text)
                                                            ELSE
                                                                r.statement_end_offset
                                                        END - r.statement_start_offset
                                                    ) / 2
                                                    ) + 1
                                                ),
                                        1,
                                        1000
                                    ),
                            CHAR(10),
                            ' '
                        ),
                CHAR(13),
                ' '
            )
            ) stmt_text,
       mg.dop,                                               --Degree of parallelism
       mg.request_time,                                      --Date and time when this query requested the memory grant.
       mg.grant_time,                                        --NULL means memory has not been granted
       mg.requested_memory_kb / 1024.0 requested_memory_mb,  --Total requested amount of memory in megabytes
       mg.granted_memory_kb / 1024.0 AS granted_memory_mb,   --Total amount of memory actually granted in megabytes. NULL if not granted
       mg.required_memory_kb / 1024.0 AS required_memory_mb, --Minimum memory required to run this query in megabytes.
       max_used_memory_kb / 1024.0 AS max_used_memory_mb,
       mg.query_cost,                                        --Estimated query cost.
       mg.timeout_sec,                                       --Time-out in seconds before this query gives up the memory grant request.
       mg.resource_semaphore_id,                             --Non-unique ID of the resource semaphore on which this query is waiting.
       mg.wait_time_ms,                                      --Wait time in milliseconds. NULL if the memory is already granted.
       CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
           WHEN 1 THEN
               'Yes'
           WHEN 0 THEN
               'No'
           ELSE
               'Memory has been granted'
       END AS 'Next Candidate for Memory Grant',
       qp.query_plan
FROM sys.dm_exec_requests AS r
    JOIN sys.dm_exec_query_memory_grants AS mg
        ON r.session_id = mg.session_id
           AND r.request_id = mg.request_id
    CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY mg.granted_memory_kb DESC;

Berechnen von Datenbank- und Objektgrößen

Die folgende Abfrage gibt die Größe Ihrer Datenbank (in Megabyte) zurück:

-- Calculates the size of the database.
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB
FROM sys.database_files
WHERE type_desc = 'ROWS';
GO

Die folgende Abfrage gibt die Größe der einzelnen Objekte in Ihrer Datenbank in Megabyte zurück:

-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;
GO

Überwachen von Verbindungen

Sie können mithilfe der Sicht sys.dm_exec_connections Informationen über Verbindungen mit einer bestimmten verwalteten Instanz abrufen und Details zu jeder Verbindungen anzeigen. Darüber hinaus ist die Sicht sys.dm_exec_sessions für das Abrufen von Informationen zu allen aktiven Benutzerverbindungen und internen Aufgaben nützlich.

Verwenden Sie die folgende Abfrage zum Abrufen der Informationen zur aktuellen Verbindung:

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

Überwachen der Ressourcennutzung

Sie können die Ressourcennutzung mithilfe des Abfragespeichers überwachen, genauso wie in SQL Server.

Sie können die Verwendung auch mithilfe von sys.dm_db_resource_stats und sys.server_resource_stats überwachen.

sys.dm_db_resource_stats

Sie können die Sicht sys.dm_db_resource_stats in jeder Datenbank verwenden. Die Sicht sys.dm_db_resource_stats enthält Daten zur Ressourcennutzung in der letzten Zeit relativ zur Dienstebene. Durchschnittliche Prozentsätze für CPU, Dateneingang/-ausgang, Protokollschreibvorgänge und Arbeitsspeicher werden alle 15 Sekunden aufgezeichnet und eine Stunde lang aufbewahrt.

Da diese Ansicht eine detailliertere Darstellung der Ressourcennutzung ist, sollten Sie für alle Analysen des aktuellen Zustands oder für die Problembehandlung zuerst sys.dm_db_resource_stats verwenden. Mit dieser Abfrage wird beispielsweise die durchschnittliche und maximale Ressourcennutzung für die aktuelle Datenbank in der letzten Stunde angezeigt:

SELECT  
    AVG(avg_cpu_percent) AS 'Average CPU use in percent',
    MAX(avg_cpu_percent) AS 'Maximum CPU use in percent',
    AVG(avg_data_io_percent) AS 'Average data IO in percent',
    MAX(avg_data_io_percent) AS 'Maximum data IO in percent',
    AVG(avg_log_write_percent) AS 'Average log write use in percent',
    MAX(avg_log_write_percent) AS 'Maximum log write use in percent',
    AVG(avg_memory_usage_percent) AS 'Average memory use in percent',
    MAX(avg_memory_usage_percent) AS 'Maximum memory use in percent'
FROM sys.dm_db_resource_stats;

Beispiele für andere Abfragen finden Sie unter sys.dm_db_resource_stats.

sys.server_resource_stats

Sie können sys.server_resource_stats verwenden, um CPU-Nutzung, E/A und Speicherdaten für eine Azure SQL Managed Instance-Instanz zurückzugeben. Die Daten werden in Intervallen von fünf Minuten gesammelt und aggregiert. Für jede 15-Sekunden-Berichterstattung ist eine Zeile vorhanden. Die zurückgegebenen Daten enthalten Informationen zu CPU-Nutzung, Speichergröße, E/A-Auslastung und SKU der verwalteten Instanz. Verlaufsdaten werden ungefähr 14 Tage lang beibehalten.

Die Beispiele zeigen verschiedene Möglichkeiten zur Verwendung der sys.server_resource_stats-Katalogsicht, um Informationen zur Ressourcennutzung durch Ihre Instanz abzurufen.

Das folgende Beispiel liefert die durchschnittliche CPU-Auslastung der letzten sieben Tage:

DECLARE @s datetime;  
DECLARE @e datetime;  
SET @s= DateAdd(d,-7,GetUTCDate());  
SET @e= GETUTCDATE();  
SELECT AVG(avg_cpu_percent) AS Average_Compute_Utilization   
FROM sys.server_resource_stats   
WHERE start_time BETWEEN @s AND @e;
GO

Das folgende Beispiel gibt den durchschnittlichen Speicherplatz zurück, den Ihre Instanz pro Tag verwendet, um eine Analyse der Wachstumstrends zu ermöglichen:

DECLARE @s datetime;  
DECLARE @e datetime;  
SET @s= DateAdd(d,-7,GetUTCDate());  
SET @e= GETUTCDATE();  
SELECT Day = convert(date, start_time), AVG(storage_space_used_mb) AS Average_Space_Used_mb
FROM sys.server_resource_stats   
WHERE start_time BETWEEN @s AND @e
GROUP BY convert(date, start_time)
ORDER BY convert(date, start_time);
GO

Maximale Anzahl von gleichzeitigen Anforderungen

Um die aktuelle Anzahl gleichzeitiger Anforderungen anzuzeigen, führen Sie diese Transact-SQL-Abfrage in Ihrer Datenbank aus:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R;

Um die Workload einer einzelnen Datenbank zu analysieren, ändern Sie diese Abfrage so ab, dass nach der zu analysierenden Datenbank gefiltert wird. Wenn Sie beispielsweise über eine Datenbank mit dem Namen MyDatabase verfügen, wird mit dieser Transact-SQL-Abfrage die Anzahl von gleichzeitigen Abfragen in dieser Datenbank zurückgegeben:

SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R
INNER JOIN sys.databases D ON D.database_id = R.database_id
AND D.name = 'MyDatabase';

Dies ist nur eine Momentaufnahme zu einem bestimmten Zeitpunkt. Um ein besseres Verständnis Ihrer Workload und der Anforderungen an gleichzeitige Anforderungen zu entwickeln, müssten Sie im Laufe der Zeit viele Beispiele sammeln.

Maximale Anzahl von gleichzeitigen Anmeldungen

Sie können Ihre Benutzer- und Anmeldungsmuster analysieren, um Informationen zur Häufigkeit der Anmeldungen zu erhalten. Sie können auch reale Auslastungen in einer Testumgebung ausführen, um sicherzustellen, dass diese oder andere in diesem Artikel beschriebene Grenzwerte nicht erreicht oder überschritten werden. Es gibt keine einzelne Abfrage oder dynamische Verwaltungssicht (DMV), mit der Sie die Anzahl von gleichzeitigen Anmeldungen oder den Verlauf anzeigen können.

Der Dienst authentifiziert jede Anmeldung, wenn mehrere Clients die gleiche Verbindungszeichenfolge verwenden. Wenn zehn Benutzer gleichzeitig mit dem gleichen Benutzernamen und Kennwort mit einer Datenbank verbunden sind, wären dies also zehn gleichzeitige Anmeldungen. Diese Obergrenze gilt nur für die Dauer der Anmeldung und Authentifizierung. Wenn sich die gleichen zehn Benutzer nacheinander an der Datenbank anmelden, wäre die Anzahl von gleichzeitigen Anmeldungen niemals höher als 1.

Maximale Anzahl von Sitzungen

Um die Anzahl aktueller aktiver Sitzungen anzuzeigen, führen Sie diese Transact-SQL-Abfrage in Ihrer Datenbank aus:

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;

Wenn Sie die SQL Server-Workload analysieren, ändern Sie die Abfrage für eine spezifische Datenbank ab. Diese Abfrage ist zum Ermitteln der möglichen Sitzungsanforderungen für die Datenbank hilfreich, wenn Sie eine Umstellung auf Azure erwägen.

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections C
INNER JOIN sys.dm_exec_sessions S ON (S.session_id = C.session_id)
INNER JOIN sys.databases D ON (D.database_id = S.database_id)
WHERE D.name = 'MyDatabase';

Diese Abfragen geben wieder eine Anzahl zu einem bestimmten Zeitpunkt zurück. Wenn Sie im Laufe der Zeit mehrere Beispielwerte sammeln, können Sie sich am besten über Ihre Sitzungsnutzung informieren.

Überwachen der Abfrageleistung

Langsame Abfragen oder Abfragen mit langen Ausführungszeiten können beträchtliche Systemressourcen beanspruchen. In diesem Abschnitt wird veranschaulicht, wie mit dynamischen Verwaltungssichten häufig auftretende Leistungsprobleme bei Abfragen ermittelt werden können.

Suchen der ersten N Abfragen

Das folgende Beispiel gibt Informationen über die „Top-Fünf“-Abfragen gemessen an durchschnittlicher CPU-Zeit zurück. In diesem Beispiel werden die Abfragen entsprechend ihrem Abfragenhash zusammengefasst, sodass logisch äquivalente Abfragen nach ihrem kumulativen Ressourcenverbrauch gruppiert werden.

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
     MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
        SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                WHEN -1 THEN DATALENGTH(ST.text)
                ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

Überwachen blockierter Abfragen

Langsame Abfragen oder Abfragen mit langer Laufzeit können zu einer übermäßigen Ressourcennutzung beitragen und auf blockierte Abfragen zurückzuführen sein. Ursache für das Blockieren kann ein mangelhafter Anwendungsentwurf, fehlerhafte Abfragepläne oder ein Mangel an nützlichen Indizes usw. sein. Verwenden Sie die Sicht „sys.dm_tran_locks“, um Informationen über die aktuellen Sperraktivitäten in der Datenbank abzurufen. Beispielcode finden Sie unter sys.dm_tran_locks. Weitere Informationen zur Problembehandlung beim Blockieren finden Sie unter Verstehen und Beheben von SQL Server-Blockierungsproblemen.

Überwachen von Deadlocks

In einigen Fällen können zwei oder mehr Abfragen sich gegenseitig blockieren, was zu einem Deadlock führt.

Sie können eine Ablaufverfolgung für erweiterte Ereignisse für eine Datenbank erstellen, um Deadlockereignisse zu erfassen, und dann verwandte Abfragen und ihre Ausführungspläne im Abfragespeicher suchen.

Informationen zu Azure SQL Managed Instance finden Sie im Leitfaden zu Deadlocks unter Deadlock tools (Deadlock-Tools).

Überwachen von Abfrageplänen

Auch ein ineffizienter Abfrageplan kann die CPU-Auslastung erhöhen. Im folgenden Beispiel wird die Sicht sys.dm_exec_query_stats verwendet, um festzustellen, welche Abfrage die CPU kumulativ am meisten auslastet.

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;

Andere Überwachungsoptionen

Überwachen mit dem Datenbank-Watcher (Vorschau)

Der Datenbankwatcher erfasst detaillierte Workload-Überwachungsdaten und vermittelt Ihnen detaillierte Einblicke in die Datenbankleistung, Konfiguration und Integrität. Dashboards im Azure-Portal vermitteln einen umfassenden Überblick über Ihre Azure SQL-Umgebung und Detailansichten aller überwachten Ressourcen. Die Daten werden in einem zentralen Datenspeicher in Ihrem Azure-Abonnement erfasst. Die erfassten Daten können Sie abfragen, analysieren, exportieren und visualisieren und in nachgelagerte Systeme integrieren.

Weitere Informationen zum Datenbank-Watcher finden Sie in den folgenden Artikeln:

Überwachung mit Azure Monitor

Azure Monitor bietet eine Vielzahl von Diagnosedatensammlungsgruppen, Metriken und Endpunkten zur Überwachung von Azure SQL Managed Instance. Weitere Informationen finden Sie unter Überwachen von Azure SQL Managed Instance mit Azure Monitor. Azure SQL-Analyse (Vorschauversion) ist eine Integration in Azure Monitor, bei der sich viele Überwachungslösungen nicht mehr in der aktiven Entwicklung befinden. Navigieren Sie zu Überwachung und Leistungsoptimierung in Azure SQL Managed Instance und Azure SQL-Datenbank, um mehr Überwachungsoptionen zu erhalten.

Teilen über

Überwachen der Leistung von Microsoft Azure SQL Managed Instance mithilfe von dynamischen Verwaltungssichten

Berechtigungen

Identifizieren von CPU-Leistungsproblemen

Das CPU-Problem tritt jetzt auf

Viele einzelne Abfragen, die zusammen zu einer hohen CPU-Auslastung führen

Abfragen mit langer Ausführungszeit, die CPU-Ressourcen verbrauchen, werden noch ausgeführt

Das CPU-Problem ist in der Vergangenheit aufgetreten

Identifizieren von Problemen mit der E/A-Leistung

Das E/A-Problem tritt jetzt auf

Anzeigen sämtlicher E/A-Vorgänge für WRITELOG-Wartevorgänge

Identifizieren von Problemen mit der `tempdb`-Leistung

Häufige Abfragen, die Tabellenvariablen und temporäre Tabellen verwenden

Identifizieren von Transaktionen mit langer Ausführungszeit

Identifizieren von Leistungsproblemen bei Wartevorgängen für Speicherzuweisung

Ermitteln, ob ein `RESOURCE_SEMAHPORE`-Wartevorgang ein häufiger Wartevorgang ist

Ermitteln von Anweisungen mit hohem Arbeitsspeicherverbrauch

Identifizieren der Speicherzuweisungen

Berechnen von Datenbank- und Objektgrößen

Überwachen von Verbindungen

Überwachen der Ressourcennutzung

sys.dm_db_resource_stats

sys.server_resource_stats

Maximale Anzahl von gleichzeitigen Anforderungen

Maximale Anzahl von gleichzeitigen Anmeldungen

Maximale Anzahl von Sitzungen

Überwachen der Abfrageleistung

Suchen der ersten N Abfragen

Überwachen blockierter Abfragen

Überwachen von Deadlocks

Überwachen von Abfrageplänen

Andere Überwachungsoptionen

Überwachen mit dem Datenbank-Watcher (Vorschau)

Überwachung mit Azure Monitor

Siehe auch

Nächste Schritte

Feedback

Zusätzliche Ressourcen

Teilen über

Überwachen der Leistung von Microsoft Azure SQL Managed Instance mithilfe von dynamischen Verwaltungssichten

Berechtigungen

Identifizieren von CPU-Leistungsproblemen

Das CPU-Problem tritt jetzt auf

Viele einzelne Abfragen, die zusammen zu einer hohen CPU-Auslastung führen

Abfragen mit langer Ausführungszeit, die CPU-Ressourcen verbrauchen, werden noch ausgeführt

Das CPU-Problem ist in der Vergangenheit aufgetreten

Identifizieren von Problemen mit der E/A-Leistung

Das E/A-Problem tritt jetzt auf

Anzeigen von pufferbezogenen E/A-Vorgängen, die den Abfragespeicher verwenden

Anzeigen sämtlicher E/A-Vorgänge für WRITELOG-Wartevorgänge

Identifizieren von Problemen mit der tempdb-Leistung

Häufige Abfragen, die Tabellenvariablen und temporäre Tabellen verwenden

Identifizieren von Transaktionen mit langer Ausführungszeit

Identifizieren von Leistungsproblemen bei Wartevorgängen für Speicherzuweisung

Ermitteln, ob ein RESOURCE_SEMAHPORE-Wartevorgang ein häufiger Wartevorgang ist

Ermitteln von Anweisungen mit hohem Arbeitsspeicherverbrauch

Identifizieren der Speicherzuweisungen

Berechnen von Datenbank- und Objektgrößen

Überwachen von Verbindungen

Überwachen der Ressourcennutzung

sys.dm_db_resource_stats

sys.server_resource_stats

Maximale Anzahl von gleichzeitigen Anforderungen

Maximale Anzahl von gleichzeitigen Anmeldungen

Maximale Anzahl von Sitzungen

Überwachen der Abfrageleistung

Suchen der ersten N Abfragen

Überwachen blockierter Abfragen

Überwachen von Deadlocks

Überwachen von Abfrageplänen

Andere Überwachungsoptionen

Überwachen mit dem Datenbank-Watcher (Vorschau)

Überwachung mit Azure Monitor

Siehe auch

Nächste Schritte

Feedback

Zusätzliche Ressourcen

Identifizieren von Problemen mit der `tempdb`-Leistung

Ermitteln, ob ein `RESOURCE_SEMAHPORE`-Wartevorgang ein häufiger Wartevorgang ist