Monitorowanie wydajności usługi Microsoft Azure SQL Managed Instance przy użyciu dynamicznych widoków zarządzania

Artykuł
10/16/2024

Usługa Microsoft Azure SQL Managed Instance umożliwia podzestaw dynamicznych widoków zarządzania (DMV) do diagnozowania problemów z wydajnością, które mogą być spowodowane przez zablokowane lub długotrwałe zapytania, wąskie gardła zasobów, słabe plany zapytań itd. Ten artykuł zawiera informacje na temat wykrywania typowych problemów z wydajnością przy użyciu dynamicznych widoków zarządzania.

Ten artykuł dotyczy usługi Azure SQL Managed Instance. Zobacz również Monitorowanie wydajności usługi Microsoft Azure SQL Database przy użyciu dynamicznych widoków zarządzania.

Uprawnienia

W usłudze Azure SQL Managed Instance wykonywanie zapytań w widoku dynamicznego zarządzania wymaga uprawnień WYŚWIETL STAN SERWERA .

GRANT VIEW SERVER STATE TO database_user;

W wystąpieniu programu SQL Server i w usłudze Azure SQL Managed Instance dynamiczne widoki zarządzania zwracają informacje o stanie serwera.

Identyfikowanie problemów z wydajnością procesora CPU

Jeśli użycie procesora CPU przekracza 80% przez dłuższy czas, rozważ następujące kroki rozwiązywania problemów:

Problem z procesorem CPU występuje teraz

Jeśli problem występuje teraz, istnieją dwa możliwe scenariusze:

Wiele pojedynczych zapytań, które zbiorczo zużywają wysokie użycie procesora CPU

Użyj następującego zapytania, aby zidentyfikować najważniejsze skróty zapytań:

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;

Długotrwałe zapytania, które zużywają procesor CPU, są nadal uruchomione

Użyj następującego zapytania, aby zidentyfikować te zapytania:

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

Problem z procesorem CPU wystąpił w przeszłości

Jeśli problem wystąpił w przeszłości i chcesz przeprowadzić analizę głównej przyczyny, użyj magazynu zapytań. Użytkownicy z dostępem do bazy danych mogą używać języka T-SQL do wykonywania zapytań dotyczących danych magazynu zapytań. Domyślne konfiguracje magazynu zapytań używają stopnia szczegółowości 1 godziny. Użyj następującego zapytania, aby przyjrzeć się aktywności w przypadku zapytań zużywających wysokie użycie procesora CPU. To zapytanie zwraca 15 najważniejszych zapytań zużywających procesor CPU. Pamiętaj, aby zmienić :rsi.start_time >= DATEADD(hour, -2, GETUTCDATE()

-- 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;

Po zidentyfikowaniu problematycznych zapytań nadszedł czas, aby dostosować te zapytania w celu zmniejszenia wykorzystania procesora CPU. Jeśli nie masz czasu na dostosowanie zapytań, możesz również zdecydować się na uaktualnienie celu slo wystąpienia zarządzanego, aby obejść ten problem.

Identyfikowanie problemów z wydajnością operacji we/wy

Podczas identyfikowania problemów z wydajnością operacji we/wy najczęstsze typy oczekiwania skojarzone z problemami operacji we/wy są następujące:

PAGEIOLATCH_*

W przypadku problemów z operacjami we/wy pliku danych (w tym PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Jeśli nazwa typu oczekiwania zawiera we/wy, wskazuje problem z we/wy. Jeśli w nazwie oczekiwania na zatrzask strony nie ma operacji we/wy, wskazuje na inny typ problemu (na przykład tempdb rywalizację).
WRITE_LOG

W przypadku problemów z we/wy dziennika transakcji.

Jeśli problem z operacjami we/wy występuje teraz

Użyj sys.dm_exec_requests lub sys.dm_os_waiting_tasks, aby wyświetlić element wait_type i wait_time.

W przypadku opcji 2 można użyć następującego zapytania względem magazynu zapytań dla operacji we/wy związanych z buforem, aby wyświetlić ostatnie dwie godziny śledzonej aktywności:

-- 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

Wyświetlanie całkowitej operacji we/wy dziennika na potrzeby oczekiwania writeLOG

Jeśli typ oczekiwania to WRITELOG, użyj następującego zapytania, aby wyświetlić łączną liczbę operacji we/wy dziennika według instrukcji:

-- 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

Identyfikowanie `tempdb` problemów z wydajnością

Podczas identyfikowania problemów z wydajnością operacji we/wy najważniejsze typy oczekiwania skojarzone z problemami tempdb to PAGELATCH_* (nie PAGEIOLATCH_*). Jednak oczekiwania nie zawsze oznaczają, PAGELATCH_* że masz tempdb rywalizację. To oczekiwanie może również oznaczać występowanie rywalizacji o stronę danych obiektu użytkownika z powodu współbieżnych żądań przeznaczonych dla tej samej strony danych. Aby jeszcze bardziej potwierdzić tempdb rywalizację, użyj sys.dm_exec_requests , aby potwierdzić, że wartość wait_resource zaczyna się od 2:x:y miejsca, gdzie 2 jest tempdb identyfikatorem bazy danych, x jest identyfikatorem pliku i y jest identyfikatorem strony.

W przypadku tempdb rywalizacji typową metodą jest zmniejszenie lub przepisanie kodu aplikacji, który opiera się na metodzie tempdb. Typowe tempdb obszary użycia obejmują:

Tabele tymczasowe
Zmienne tabeli
Parametry z wartościami przechowywanymi w tabeli
Użycie magazynu wersji (skojarzone z długotrwałymi transakcjami)
Zapytania z planami, które używają sortowania, sprzężeń skrótów i buforów

Najważniejsze zapytania korzystające ze zmiennych tabeli i tabel tymczasowych

Użyj następującego zapytania, aby zidentyfikować najważniejsze zapytania korzystające ze zmiennych tabeli i tabel tymczasowych:

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;

Identyfikowanie długotrwałych transakcji

Użyj następującego zapytania, aby zidentyfikować długotrwałe transakcje. Długotrwałe transakcje uniemożliwiają czyszczenie magazynu wersji.

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;

Identyfikowanie problemów z wydajnością oczekiwania na udzielanie pamięci

Jeśli twój typ oczekiwania jest RESOURCE_SEMAHPORE najwyższy i nie masz problemu z wysokim użyciem procesora CPU, może wystąpić problem z oczekiwaniem na udzielenie pamięci.

Ustal, czy oczekiwanie `RESOURCE_SEMAHPORE` jest oczekiwaniem na górę

Użyj następującego zapytania, aby określić, czy RESOURCE_SEMAHPORE oczekiwanie jest najwięcej

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;

Identyfikowanie instrukcji zużywających dużą ilość pamięci

Jeśli wystąpią błędy dotyczące braku pamięci, przejrzyj widok sys.dm_os_out_of_memory_events.

Użyj następującego zapytania, aby zidentyfikować instrukcje zużywające dużą ilość pamięci:

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;

Identyfikowanie przydziałów pamięci

Użyj następującego zapytania, aby zidentyfikować 10 aktywnych przydziałów pamięci:

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;

Obliczanie rozmiaru bazy danych i obiektów

Następujące zapytanie zwraca rozmiar bazy danych (w megabajtach):

-- 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

Następujące zapytanie zwraca rozmiar pojedynczych obiektów (w megabajtach) w bazie danych:

-- 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

Monitorowanie połączeń

Możesz użyć widoku sys.dm_exec_connections , aby pobrać informacje o połączeniach ustanowionych z określonym wystąpieniem zarządzanym oraz szczegóły poszczególnych połączeń. Ponadto widok sys.dm_exec_sessions jest przydatny podczas pobierania informacji o wszystkich aktywnych połączeniach użytkowników i zadaniach wewnętrznych.

Następujące zapytanie pobiera informacje dotyczące bieżącego połączenia:

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;

Monitorowanie użycia zasobów

Użycie zasobów można monitorować przy użyciu magazynu zapytań, tak jak w programie SQL Server.

Możesz również monitorować użycie przy użyciu sys.dm_db_resource_stats i sys.server_resource_stats.

sys.dm_db_resource_stats

Widok sys.dm_db_resource_stats można używać w każdej bazie danych. Widok sys.dm_db_resource_stats pokazuje ostatnie dane użycia zasobów względem warstwy usługi. Średnie wartości procentowe procesora CPU, operacji we/wy danych, zapisów dzienników i pamięci są rejestrowane co 15 sekund i są utrzymywane przez 1 godzinę.

Ponieważ ten widok zapewnia bardziej szczegółowe spojrzenie na użycie zasobów, należy najpierw użyć sys.dm_db_resource_stats funkcji analizy bieżącego stanu lub rozwiązywania problemów. Na przykład to zapytanie pokazuje średnie i maksymalne użycie zasobów dla bieżącej bazy danych w ciągu ostatniej godziny:

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;

Inne zapytania można znaleźć w przykładach w sys.dm_db_resource_stats.

sys.server_resource_stats

Możesz użyć sys.server_resource_stats , aby zwrócić dane użycia procesora CPU, operacji we/wy i magazynu dla usługi Azure SQL Managed Instance. Dane są zbierane i agregowane w pięciu minutach. Istnieje jeden wiersz co 15 sekund raportowania. Zwrócone dane obejmują użycie procesora CPU, rozmiar magazynu, wykorzystanie operacji we/wy i jednostkę SKU wystąpienia zarządzanego. Dane historyczne są przechowywane przez około 14 dni.

W przykładach pokazano różne sposoby używania sys.server_resource_stats widoku wykazu w celu uzyskania informacji o sposobie używania zasobów przez wystąpienie.

Poniższy przykład zwraca średnie użycie procesora CPU w ciągu ostatnich siedmiu dni:

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

Poniższy przykład zwraca średnią ilość miejsca do magazynowania używanego przez wystąpienie dziennie, aby umożliwić analizę trendów wzrostu:

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

Maksymalna liczba współbieżnych żądań

Aby wyświetlić bieżącą liczbę równoczesnych żądań, uruchom to zapytanie Języka Transact-SQL w bazie danych:

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

Aby przeanalizować obciążenie pojedynczej bazy danych, zmodyfikuj to zapytanie, aby filtrować określoną bazę danych, którą chcesz przeanalizować. Jeśli na przykład masz bazę danych o nazwie MyDatabase, to zapytanie Języka Transact-SQL zwraca liczbę współbieżnych żądań w tej bazie danych:

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';

Jest to tylko migawka w jednym punkcie w czasie. Aby lepiej zrozumieć wymagania dotyczące obciążenia i żądań współbieżnych, należy zebrać wiele przykładów w czasie.

Maksymalna liczba jednoczesnych logowań

Możesz analizować wzorce użytkowników i aplikacji, aby zrozumieć częstotliwość logowania. Możesz również uruchamiać rzeczywiste obciążenia w środowisku testowym, aby upewnić się, że nie osiągasz tego lub innych limitów omówimy w tym artykule. Nie ma pojedynczego zapytania ani dynamicznego widoku zarządzania (DMV), który może pokazywać współbieżne liczby logowań lub historię.

Jeśli wielu klientów używa tego samego parametry połączenia, usługa uwierzytelnia każde logowanie. Jeśli 10 użytkowników łączy się jednocześnie z bazą danych przy użyciu tej samej nazwy użytkownika i hasła, będzie istnieć 10 jednoczesnych logowań. Ten limit dotyczy tylko czasu logowania i uwierzytelniania. Jeśli ten sam 10 użytkowników łączy się z bazą danych sekwencyjnie, liczba równoczesnych logowań nigdy nie będzie większa niż 1.

Maksymalna liczba sesji

Aby wyświetlić liczbę bieżących aktywnych sesji, uruchom to zapytanie Języka Transact-SQL w bazie danych:

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

Jeśli analizujesz obciążenie programu SQL Server, zmodyfikuj zapytanie, aby skoncentrować się na określonej bazie danych. To zapytanie pomaga określić możliwe potrzeby sesji dla bazy danych, jeśli rozważasz przeniesienie jej na platformę Azure.

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';

Ponownie te zapytania zwracają liczbę punktów w czasie. Jeśli z czasem zbierzesz wiele przykładów, będziesz mieć najlepszą wiedzę na temat użycia sesji.

Monitorowanie wydajności zapytań

Wolne lub długotrwałe zapytania mogą zużywać znaczne zasoby systemowe. W tej sekcji pokazano, jak używać dynamicznych widoków zarządzania do wykrywania kilku typowych problemów z wydajnością zapytań.

Znajdowanie pierwszych N zapytań

Poniższy przykład zwraca informacje o pięciu pierwszych zapytaniach sklasyfikowanych według średniego czasu procesora CPU. W tym przykładzie zapytania są agregowane zgodnie z ich skrótem zapytania, dzięki czemu zapytania równoważne logicznie są grupowane według skumulowanego użycia zasobów.

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;

Monitorowanie zablokowanych zapytań

Powolne lub długotrwałe zapytania mogą przyczynić się do nadmiernego użycia zasobów i być konsekwencją zablokowanych zapytań. Przyczyną blokowania może być słaba konstrukcja aplikacji, złe plany zapytań, brak przydatnych indeksów itd. Możesz użyć widoku sys.dm_tran_locks, aby uzyskać informacje o bieżącym działaniu blokowania w bazie danych. Na przykład kod zobacz sys.dm_tran_locks. Aby uzyskać więcej informacji na temat rozwiązywania problemów z blokowaniem, zobacz Omówienie i rozwiązywanie problemów z blokowaniem usługi Azure SQL.

Monitorowanie zakleszczeń

W niektórych przypadkach co najmniej dwa zapytania mogą wzajemnie się blokować, co powoduje zakleszczenie.

Możesz utworzyć bazę danych śledzenia zdarzeń rozszerzonych w celu przechwycenia zdarzeń zakleszczenia, a następnie znaleźć powiązane zapytania i ich plany wykonywania w magazynie zapytań.

W przypadku usługi Azure SQL Managed Instance zapoznaj się z narzędziami zakleszczenia w przewodniku Zakleszczenia.

Monitorowanie planów zapytań

Nieefektywny plan zapytania może również zwiększyć użycie procesora CPU. W poniższym przykładzie użyto widoku sys.dm_exec_query_stats , aby określić, które zapytanie używa najbardziej skumulowanego procesora CPU.

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;

Inne opcje monitorowania

Monitorowanie za pomocą obserwatora bazy danych (wersja zapoznawcza)

Obserwator bazy danych zbiera szczegółowe dane monitorowania obciążenia, aby uzyskać szczegółowy widok wydajności, konfiguracji i kondycji bazy danych. Pulpity nawigacyjne w witrynie Azure Portal zapewniają widok z jednym okienkiem szkła dla majątku usługi Azure SQL i szczegółowy widok każdego monitorowanego zasobu. Dane są zbierane w centralnym magazynie danych w ramach subskrypcji platformy Azure. Możesz wykonywać zapytania, analizować, eksportować, wizualizować zebrane dane i integrować je z systemami podrzędnymi.

Aby uzyskać więcej informacji na temat obserwatora bazy danych, zobacz następujące artykuły:

Monitorowanie za pomocą usługi Azure Monitor

Usługa Azure Monitor udostępnia różne grupy, metryki i punkty końcowe zbierania danych diagnostycznych na potrzeby monitorowania usługi Azure SQL Managed Instance. Aby uzyskać więcej informacji, zobacz Monitorowanie usługi Azure SQL Managed Instance za pomocą usługi Azure Monitor. Usługa Azure SQL Analytics (wersja zapoznawcza) to integracja z usługą Azure Monitor, w której wiele rozwiązań do monitorowania nie jest już aktywnie opracowywanych. Aby uzyskać więcej opcji monitorowania, zobacz Monitorowanie i dostrajanie wydajności w usłudze Azure SQL Managed Instance i usłudze Azure SQL Database.

Udostępnij za pośrednictwem

Monitorowanie wydajności usługi Microsoft Azure SQL Managed Instance przy użyciu dynamicznych widoków zarządzania

Uprawnienia

Identyfikowanie problemów z wydajnością procesora CPU

Problem z procesorem CPU występuje teraz

Wiele pojedynczych zapytań, które zbiorczo zużywają wysokie użycie procesora CPU

Długotrwałe zapytania, które zużywają procesor CPU, są nadal uruchomione

Problem z procesorem CPU wystąpił w przeszłości

Identyfikowanie problemów z wydajnością operacji we/wy

Jeśli problem z operacjami we/wy występuje teraz

Wyświetlanie całkowitej operacji we/wy dziennika na potrzeby oczekiwania writeLOG

Identyfikowanie `tempdb` problemów z wydajnością

Najważniejsze zapytania korzystające ze zmiennych tabeli i tabel tymczasowych

Identyfikowanie długotrwałych transakcji

Identyfikowanie problemów z wydajnością oczekiwania na udzielanie pamięci

Ustal, czy oczekiwanie `RESOURCE_SEMAHPORE` jest oczekiwaniem na górę

Identyfikowanie instrukcji zużywających dużą ilość pamięci

Identyfikowanie przydziałów pamięci

Obliczanie rozmiaru bazy danych i obiektów

Monitorowanie połączeń

Monitorowanie użycia zasobów

sys.dm_db_resource_stats

sys.server_resource_stats

Maksymalna liczba współbieżnych żądań

Maksymalna liczba jednoczesnych logowań

Maksymalna liczba sesji

Monitorowanie wydajności zapytań

Znajdowanie pierwszych N zapytań

Monitorowanie zablokowanych zapytań

Monitorowanie zakleszczeń

Monitorowanie planów zapytań

Inne opcje monitorowania

Monitorowanie za pomocą obserwatora bazy danych (wersja zapoznawcza)

Monitorowanie za pomocą usługi Azure Monitor

Zobacz też

Następne kroki

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

Monitorowanie wydajności usługi Microsoft Azure SQL Managed Instance przy użyciu dynamicznych widoków zarządzania

Uprawnienia

Identyfikowanie problemów z wydajnością procesora CPU

Problem z procesorem CPU występuje teraz

Wiele pojedynczych zapytań, które zbiorczo zużywają wysokie użycie procesora CPU

Długotrwałe zapytania, które zużywają procesor CPU, są nadal uruchomione

Problem z procesorem CPU wystąpił w przeszłości

Identyfikowanie problemów z wydajnością operacji we/wy

Jeśli problem z operacjami we/wy występuje teraz

Wyświetlanie operacji we/wy związanych z buforem przy użyciu magazynu zapytań

Wyświetlanie całkowitej operacji we/wy dziennika na potrzeby oczekiwania writeLOG

Identyfikowanie tempdb problemów z wydajnością

Najważniejsze zapytania korzystające ze zmiennych tabeli i tabel tymczasowych

Identyfikowanie długotrwałych transakcji

Identyfikowanie problemów z wydajnością oczekiwania na udzielanie pamięci

Ustal, czy oczekiwanie RESOURCE_SEMAHPORE jest oczekiwaniem na górę

Identyfikowanie instrukcji zużywających dużą ilość pamięci

Identyfikowanie przydziałów pamięci

Obliczanie rozmiaru bazy danych i obiektów

Monitorowanie połączeń

Monitorowanie użycia zasobów

sys.dm_db_resource_stats

sys.server_resource_stats

Maksymalna liczba współbieżnych żądań

Maksymalna liczba jednoczesnych logowań

Maksymalna liczba sesji

Monitorowanie wydajności zapytań

Znajdowanie pierwszych N zapytań

Monitorowanie zablokowanych zapytań

Monitorowanie zakleszczeń

Monitorowanie planów zapytań

Inne opcje monitorowania

Monitorowanie za pomocą obserwatora bazy danych (wersja zapoznawcza)

Monitorowanie za pomocą usługi Azure Monitor

Zobacz też

Następne kroki

Opinia

Dodatkowe zasoby

Identyfikowanie `tempdb` problemów z wydajnością

Ustal, czy oczekiwanie `RESOURCE_SEMAHPORE` jest oczekiwaniem na górę