Memantau performa Microsoft Azure SQL Managed Instance menggunakan tampilan manajemen dinamis

  • Artikel

Berlaku untuk:Azure SQL Managed Instance

Microsoft Azure SQL Managed Instance memungkinkan subset tampilan manajemen dinamis (DMV) untuk mendiagnosis masalah performa, yang mungkin disebabkan oleh kueri yang diblokir atau berjalan lama, penyempitan sumber daya, rencana kueri yang buruk, dan sebagainya. Artikel ini memberi informasi tentang cara mendeteksi masalah performa umum dengan menggunakan tampilan manajemen dinamis.

Artikel ini berisi tentang Azure SQL Managed Instance, lihat juga Memantau performa Microsoft Azure SQL Database menggunakan tampilan manajemen dinamis.

Izin

Di Azure SQL Managed Instance, mengkueri tampilan manajemen dinamis memerlukan izin TAMPILKAN STATUS DATABASE.

GRANT VIEW SERVER STATE TO database_user;

Dalam instans SQL Server dan di Azure SQL Managed Instance, tampilan manajemen dinamis mengembalikan informasi status server.

Mengidentifikasi masalah performa CPU

Jika penggunaan CPU di atas 80% untuk jangka waktu yang lama, pertimbangkan langkah-langkah pemecahan masalah berikut:

Masalah CPU sedang terjadi sekarang

Jika masalah terjadi saat ini, ada dua skenario yang mungkin:

Banyak kueri individu yang secara kumulatif penggunaan CPUnya tinggi

Gunakan kueri berikut untuk mengidentifikasi hash kueri teratas:

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;

Kueri yang berjalan lama yang menggunakan CPU masih berjalan

Gunakan kueri berikut untuk mengidentifikasi kueri ini:

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

Jika masalah CPU terjadi di masa lalu

Jika masalah terjadi di masa lalu dan Anda ingin melakukan analisis akar penyebab, gunakan Penyimpanan Kueri. Pengguna dengan akses database bisa menggunakan T-SQL untuk mengkueri data Query Store. Konfigurasi default Query Store menggunakan granularitas 1 jam. Gunakan kueri berikut untuk melihat aktivitas untuk kueri penggunaan CPUnya tinggi. Kueri ini mengembalikan 15 kueri teratas yang memakan CPU. Ingatlah untuk mengubah 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;

Setelah Anda mengidentifikasi kueri yang bermasalah, saatnya untuk menyetel kueri tersebut untuk mengurangi pemanfaatan CPU. Jika Anda tidak punya waktu untuk menyetel kueri, Anda juga dapat memilih untuk meningkatkan SLO instans terkelola untuk mengatasi masalah tersebut.

Mengidentifikasi masalah performa IO

Saat mengidentifikasi masalah kinerja IO, jenis tunggu terbanyak yang terkait dengan masalah IO adalah:

  • PAGEIOLATCH_*

    Untuk masalah IO file data (termasuk PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGEIOLATCH_UP). Jika nama jenis tunggu memiliki IO di dalamnya, itu menunjukkan masalah IO. Jika tidak ada IO di nama tunggu kait halaman, itu menunjuk ke jenis masalah yang berbeda (misalnya, tempdb ketidakcocokan).

  • WRITE_LOG

    Untuk masalah log IO transaksi.

Jika masalah IO terjadi sekarang

Gunakan sys.dm_exec_requests atau sys.dm_os_waiting_tasks untuk melihat wait_type dan wait_time.

Untuk opsi 2, Anda bisa menggunakan kueri berikut ini terhadap Query Store untuk IO terkait buffer untuk menampilkan dua jam terakhir aktivitas terlacak:

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

Lihat total log IO untuk WRITELOG tunggu

Jika jenis tunggu adalah WRITELOG, gunakan kueri berikut ini untuk menampilkan total log IO menurut pernyataan:

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

Mengidentifikasi masalah performa tempdb

Saat mengidentifikasi masalah performa IO, jenis tunggu teratas yang terkait dengan masalah tempdb adalah PAGELATCH_* (bukan PAGEIOLATCH_*). Namun, PAGELATCH_* menunggu tidak selalu berarti Anda memiliki ketidakcocokan tempdb. Waktu tunggu ini juga dapat berarti bahwa Anda memiliki ketidakcocokan halaman data objek pengguna karena permintaan bersamaan yang menargetkan halaman data yang sama. Untuk mengkonfirmasi lebih lanjut ketidakcocokan tempdb, gunakan sys.dm_exec_requests untuk mengonfirmasi bahwa nilai wait_resource dimulai dengan 2:x:y di mana 2 adalah tempdb merupakan ID database, x adalah ID file, dan y adalah ID halaman.

Untuk tempdb ketidakcocokan, metode umum adalah mengurangi atau menulis ulang kode aplikasi yang bergantung pada tempdb. Area penggunaan tempdb umum meliputi:

  • Tabel sementara
  • Variabel tabel
  • Parameter bernilai tabel
  • Penggunaan penyimpanan versi (terkait dengan transaksi yang berjalan lama)
  • Kueri yang memiliki rencana kueri yang menggunakan pengurutan, gabungan hash, dan tampungan

Kueri teratas yang menggunakan variabel tabel dan tabel sementara

Anda dapat menggunakan kueri berikut untuk mengidentifikasi kueri teratas yang menggunakan variabel tabel dan tabel sementara:

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;

Mengidentifikasi transaksi yang berjalan lama

Gunakan kueri berikut untuk mengidentifikasi transaksi yang berjalan lama. Transaksi yang berjalan lama mencegah penghapusan penyimpanan versi.

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;

Mengidentifikasi masalah performa tunggu peruntukan memori

Jika jenis tunggu teratas Anda RESOURCE_SEMAHPORE adalah dan Anda tidak memiliki masalah penggunaan CPU yang tinggi, Anda mungkin memiliki masalah tunggu peruntukan memori.

Menentukan apakah RESOURCE_SEMAHPORE tunggu adalah tunggu teratas

Menggunakan kueri berikut untuk menentukan apakah RESOURCE_SEMAHPORE tunggu adalah tunggu teratas

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;

Mengidentifikasi pernyataan yang penggunaan memorinya tinggi

Jika Anda menemukan kesalahan memori habis, tinjau sys.dm_os_out_of_memory_events.

Gunakan kueri berikut untuk mengidentifikasi pernyataan yang penggunaan memorinya tinggi:

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;

Mengidentifikasi hibah memori

Gunakan kueri berikut untuk mengidentifikasi 10 hibah memori aktif teratas:

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;

Menghitung ukuran database dan objek

Kueri berikut mengembalikan ukuran database Anda (dalam megabyte):

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

Kueri berikut mengembalikan ukuran objek individual (dalam megabyte) di database Anda:

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

Memantau koneksi

Anda dapat menggunakan tampilan sys.dm_exec_connections untuk mengambil informasi tentang koneksi yang dibuat ke instans terkelola tertentu dan detail setiap koneksi. Selain itu, tampilan sys.dm_exec_sessions sangat membantu saat mengambil informasi tentang semua koneksi pengguna aktif dan tugas internal.

Kueri berikut ini mengambil informasi tentang koneksi saat ini:

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;

Memantau penggunaan sumber daya

Anda dapat memantau penggunaan sumber daya menggunakan Penyimpanan Kueri, seperti yang Anda lakukan di SQL Server.

Anda juga dapat memantau penggunaan menggunakan sys.dm_db_resource_stats dan sys.server_resource_stats.

sys.dm_db_sumberdaya_stats

Anda bisa menggunakan tampilan sys.dm_db_resource_stats di setiap database. sys.dm_db_resource_stats Tampilan menunjukkan data penggunaan sumber daya terbaru relatif terhadap tingkat layanan. Persentase rata-rata untuk CPU, IO data, tulis log, dan memori direkam setiap 15 detik dan dipertahankan selama 1 jam.

Karena tampilan ini memberikan tampilan yang lebih terperinci tentang penggunaan sumber daya, gunakan sys.dm_db_resource_stats terlebih dahulu untuk analisis atau pemecahan masalah saat ini. Misalnya, kueri ini memperlihatkan penggunaan sumber daya rata-rata dan maksimum untuk database saat ini selama satu jam terakhir:

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;

Untuk kueri lain, lihat contoh dalam sys.dm_db_resource_stats.

sys.server_resource_stats

Anda dapat menggunakan sys.server_resource_stats untuk mengembalikan penggunaan CPU, IO, dan data penyimpanan untuk Azure SQL Managed Instance. Data dikumpulkan dan diagregasi dalam interval lima menit. Ada satu baris untuk setiap 15 detik pelaporan. Data yang dikembalikan mencakup penggunaan CPU, ukuran penyimpanan, pemanfaatan IO, dan SKU instans terkelola. Data historis disimpan selama sekitar 14 hari.

Contoh menunjukkan berbagai cara agar Anda dapat menggunakan sys.server_resource_stats tampilan katalog untuk mendapatkan informasi tentang cara instans Anda menggunakan sumber daya.

  1. Contoh berikut mengembalikan penggunaan CPU rata-rata selama tujuh hari terakhir:

    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

  2. Contoh berikut mengembalikan ruang penyimpanan rata-rata yang digunakan oleh instans Anda per hari, untuk memungkinkan analisis tren pertumbuhan:

    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

Permintaan bersamaan maksimum

Untuk melihat jumlah permintaan bersamaan saat ini, jalankan kueri Transact-SQL ini pada database Anda:

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

Untuk menganalisis beban kerja database individual, ubah kueri ini untuk memfilter pada database tertentu yang ingin Anda analisis. Misalnya, jika Anda memiliki database bernama MyDatabase, kueri Transact-SQL ini mengembalikan jumlah permintaan bersamaan dalam database tersebut:

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

Ini hanya rekam jepret pada satu titik waktu. Untuk lebih memahami tentang beban kerja dan persyaratan permintaan bersamaan, Anda harus mengumpulkan banyak sampel dari waktu ke waktu.

Masuk bersamaan maksimum

Anda dapat menganalisis pola pengguna dan aplikasi Anda untuk mendapatkan gambaran frekuensi login. Anda juga dapat menjalankan beban dunia nyata di lingkungan pengujian untuk memastikan bahwa Anda tidak mencapai batas ini atau batas lain yang kami bahas di artikel ini. Tidak ada satu kueri atau tampilan manajemen dinamis (DMV) yang dapat memperlihatkan jumlah atau riwayat login bersamaan.

Jika beberapa klien menggunakan string koneksi yang sama, layanan akan mengautentikasi setiap login. Jika 10 pengguna secara bersamaan terhubung ke database dengan menggunakan nama pengguna dan kata sandi yang sama, akan ada 10 login bersamaan. Batas ini hanya berlaku untuk durasi login dan autentikasi. Jika 10 pengguna yang sama tersambung ke database secara berurutan, jumlah login bersamaan tidak akan pernah lebih besar dari 1.

Sesi maksimum

Untuk melihat jumlah permintaan bersamaan, jalankan kueri T-SQL ini di database Anda:

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

Jika Anda menganalisis beban kerja SQL Server, ubah kueri untuk fokus pada database tertentu. Kueri ini membantu Anda menentukan kemungkinan kebutuhan sesi untuk database jika Anda mempertimbangkan untuk memindahkannya ke 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';

Sekali lagi, kueri ini mengembalikan jumlah point-in-time. Jika Anda mengumpulkan beberapa sampel dari waktu ke waktu, Anda akan memiliki pemahaman terbaik tentang penggunaan sesi Anda.

Memantau performa kueri

Kueri yang berjalan lambat atau lama penggunaan sumber daya sistemnya bersifat signifikan. Bagian ini menunjukkan cara menggunakan tampilan manajemen dinamis untuk mendeteksi beberapa masalah performa kueri umum.

Menemukan kueri N teratas

Contoh berikut mengembalikan informasi tentang lima kueri teratas yang diberi pangkat berdasarkan waktu CPU rata-rata. Contoh ini mengagregasi kueri sesuai dengan hash kuerinya, sehingga kueri yang setara secara logis dikelompokkan menurut penggunaan sumber daya kumulatifnya.

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;

Memantau kueri yang diblokir

Kueri yang berjalan lambat atau lama dapat menyebabkan penggunaan sumber daya yang berlebihan dan berakibat kueri diblokir. Penyebab pemblokiran bisa karena desain aplikasi yang buruk, rencana kueri yang buruk, kurangnya indeks yang berguna, dan sebagainya. Anda bisa menggunakan tampilan sys.dm_tran_locks untuk mendapatkan informasi tentang aktivitas penguncian saat ini dalam database. Misalnya kode, lihat sys.dm_tran_locks. Untuk informasi selengkapnya tentang pemblokiran pemecahan masalah, lihat Memahami dan mengatasi masalah pemblokiran Azure SQL.

Memantau kebuntuan

Dalam beberapa kasus, dua atau beberapa kueri dapat saling memblokir satu sama lain, mengakibatkan kebuntuan.

Anda dapat membuat Extended Events melacak database untuk menangkap peristiwa kebuntuan, lalu menemukan kueri terkait dan rencana eksekusinya di Penyimpanan Kueri.

Untuk Azure SQL Managed Instance, lihat alat Kebuntuan di panduan Kebuntuan.

Memantau rencana kueri

Rencana kueri yang tidak efisien juga dapat meningkatkan penggunaan CPU. Contoh berikut menggunakan tampilan sys.dm_exec_query_stats untuk menentukan kueri mana yang menggunakan CPU paling kumulatif.

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;

Opsi pemantauan lainnya

Memantau dengan Wawasan SQL (pratinjau)

Azure Monitor SQL Insights (pratinjau) adalah alat untuk memantau instans Azure SQL Managed Instance, database di Azure SQL Database, dan SQL Server di Azure SQL VM. Layanan ini menggunakan agen jarak jauh untuk mengambil data dari tampilan manajemen dinamis (DMV) dan merutekan data ke Azure Log Analytics, yang dapat dipantau dan dianalisis. Anda dapat melihat data ini dari Azure Monitor dalam tampilan yang tersedia, atau mengakses data Log secara langsung untuk menjalankan kueri dan menganalisis tren. Untuk mulai menggunakan Wawasan SQL Azure Monitor (pratinjau), lihat Mengaktifkan Wawasan SQL (pratinjau).

Pemantauan dengan log Azure Monitor

Azure Monitor menyediakan berbagai grup pengumpulan data diagnostik, metrik, dan titik akhir untuk memantau Azure SQL Managed Instance. Untuk informasi selengkapnya, lihat Memantau Azure SQL Managed Instance dengan Azure Monitor. Azure SQL Analytics (pratinjau) adalah integrasi dengan Azure Monitor, tempat banyak solusi pemantauan tidak lagi dalam pengembangan aktif. Untuk opsi pemantauan lainnya, lihat Pemantauan dan penyetelan performa di Azure SQL Managed Instance dan Azure SQL Database.

Baca juga

Langkah berikutnya