Bagikan melalui


Memantau performa Azure SQL Database menggunakan tampilan manajemen dinamis

Berlaku untuk: Azure SQL Database

Anda dapat menggunakan tampilan manajemen dinamis (DMV) untuk memantau performa beban kerja dan mendiagnosis masalah performa, yang mungkin disebabkan oleh kueri yang diblokir atau berjalan lama, penyempitan sumber daya, rencana kueri suboptimal, dan banyak lagi.

Artikel ini menyediakan informasi tentang cara mendeteksi masalah performa umum dengan mengkueri tampilan manajemen dinamis melalui T-SQL. Anda bisa menggunakan alat kueri apa pun, seperti:

Izin

Di Azure SQL Database, tergantung pada ukuran komputasi, opsi penyebaran, dan data di DMV, mengkueri DMV mungkin memerlukan VIEW DATABASE STATEizin , atau VIEW SERVER PERFORMANCE STATE, atau VIEW SERVER SECURITY STATE . Dua izin terakhir disertakan VIEW SERVER STATE dalam izin. Lihat izin status server diberikan melalui keanggotaan dalam peran server yang sesuai. Untuk menentukan izin mana yang diperlukan untuk mengkueri DMV tertentu, lihat Tampilan manajemen dinamis dan temukan artikel yang menjelaskan DMV.

Untuk memberikan VIEW DATABASE STATE izin kepada pengguna database, jalankan kueri berikut, ganti database_user dengan nama prinsipal pengguna dalam database:

GRANT VIEW DATABASE STATE TO [database_user];

Untuk memberikan keanggotaan dalam ##MS_ServerStateReader## peran server ke login bernama login_name di server logis, sambungkan master ke database, lalu jalankan kueri berikut sebagai contoh:

ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login_name];

Mungkin perlu waktu beberapa menit agar pemberian izin berlaku. Untuk informasi selengkapnya, lihat Batasan peran tingkat server.

Memantau penggunaan sumber daya

Anda dapat memantau penggunaan sumber daya di tingkat database menggunakan tampilan berikut. Tampilan ini berlaku untuk database mandiri, dan database dalam kumpulan elastis.

Anda dapat memantau penggunaan sumber daya di tingkat kumpulan elastis menggunakan tampilan berikut:

Anda dapat memantau penggunaan sumber daya di tingkat kueri dengan menggunakan Wawasan Performa Kueri SQL Database di portal Azure, atau melalui Penyimpanan Kueri.

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 batas ukuran komputasi. Persentase CPU, I/O data, penulisan log, utas pekerja, dan penggunaan memori terhadap batas dicatat untuk setiap interval 15 detik dan dipertahankan selama sekitar satu jam.

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

SELECT
    database_name = DB_NAME(),
    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',
    MAX(max_worker_percent) AS 'Maximum worker use in percent'
FROM sys.dm_db_resource_stats

Untuk kueri lain, lihat contoh dalam sys.dm_db_resource_stats.

sys.sumberdaya_stats

Tampilan sys.resource_stats di database master memiliki informasi tambahan yang dapat membantu Anda memantau performa database pada tingkat layanan dan ukuran komputasi spesifiknya. Data dikumpulkan setiap 5 menit dan dipertahankan selama sekitar 14 hari. Tampilan ini berguna untuk analisis historis jangka panjang tentang bagaimana database Anda menggunakan sumber daya.

Grafik berikut menunjukkan penggunaan sumber daya CPU untuk database Premium dengan ukuran komputasi P2 untuk setiap jam dalam seminggu. Grafik ini dimulai pada hari Senin, menunjukkan lima hari kerja, dan kemudian menunjukkan akhir pekan, ketika jauh lebih sedikit terjadi dalam aplikasi.

Cuplikan layar contoh grafik penggunaan sumber daya database.

Dari data tersebut, database ini saat ini memiliki beban CPU puncak hanya lebih dari 50 persen penggunaan CPU relatif terhadap ukuran komputasi P2 (tengah hari pada hari Selasa). Jika CPU adalah faktor dominan dalam profil sumber daya aplikasi, maka Anda dapat memutuskan bahwa P2 adalah ukuran komputasi yang tepat untuk menjamin bahwa beban kerja selalu pas. Jika Anda mengharapkan aplikasi tumbuh dari waktu ke waktu, ada baiknya Anda memiliki buffer sumber daya tambahan sehingga aplikasi tidak mencapai batas tingkat performa. Jika Anda meningkatkan ukuran komputasi, Anda dapat membantu menghindari kesalahan yang terlihat pelanggan yang mungkin terjadi ketika database tidak memiliki cukup daya untuk memproses permintaan secara efektif, terutama di lingkungan yang sensitif terhadap latensi.

Untuk jenis aplikasi lain, Anda mungkin menginterpretasikan grafik yang sama secara berbeda. Misalnya, jika aplikasi mencoba memproses data penggajian setiap hari dan memiliki bagan yang sama, model "pekerjaan batch" semacam ini mungkin bisa berfungsi dengan baik pada ukuran komputasi P1. Ukuran komputasi P1 memiliki 100 DTU dibandingkan dengan 200 DTU pada ukuran komputasi P2. Ukuran komputasi P1 memberikan setengah performa ukuran komputasi P2. Jadi, 50 persen penggunaan CPU pada P2 sama dengan penggunaan CPU 100 persen dalam P1. Jika aplikasi tidak memiliki waktu habis, mungkin tidak masalah jika pekerjaan membutuhkan waktu 2 jam atau 2,5 jam untuk menyelesaikannya, jika selesai hari ini. Aplikasi dalam kategori ini mungkin dapat menggunakan ukuran komputasi P1. Anda dapat memanfaatkan adanya periode waktu di siang hari ketika penggunaan sumber daya lebih rendah, sehingga setiap "puncak besar" mungkin meluap ke salah satu palung di kemudian hari. Ukuran komputasi P1 mungkin baik untuk aplikasi semacam itu (dan menghemat uang), selama pekerjaan dapat selesai tepat waktu setiap hari.

Mesin database mengekspos informasi sumber daya yang digunakan untuk setiap database aktif dalam sys.resource_stats tampilan master database di setiap server logis. Data dalam tampilan diagregasi oleh interval 5 menit. Dibutuhkan beberapa menit agar data ini muncul dalam tabel, jadi sys.resource_stats lebih berguna untuk analisis historis daripada analisis mendekati real-time. sys.resource_stats Kueri tampilan untuk melihat riwayat database terbaru dan memvalidasi apakah ukuran komputasi yang Anda pilih memberikan performa yang Anda inginkan saat diperlukan.

Catatan

Anda harus tersambung ke master database untuk mengkueri sys.resource_stats dalam contoh berikut.

Contoh ini menunjukkan data di sys.resource_stats:

SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'userdb1'
ORDER BY start_time DESC;

Contoh berikutnya memperlihatkan kepada Anda berbagai cara untuk menggunakan sys.resource_stats tampilan katalog untuk mendapatkan informasi tentang cara database Anda menggunakan sumber daya:

  1. Untuk melihat penggunaan sumber daya minggu lalu untuk database userdb1pengguna , Anda bisa menjalankan kueri ini, mengganti nama database Anda sendiri:

    SELECT *
    FROM sys.resource_stats
    WHERE database_name = 'userdb1' 
        AND start_time > DATEADD(day, -7, GETDATE())
    ORDER BY start_time DESC;
    
  2. Untuk mengevaluasi seberapa baik beban kerja Anda sesuai dengan ukuran komputasi, Anda perlu menelusuri paling detail setiap aspek metrik sumber daya: CPU, I/O data, penulisan log, jumlah pekerja, dan jumlah sesi. Berikut adalah kueri yang direvisi menggunakan sys.resource_stats untuk melaporkan nilai rata-rata dan maksimum metrik sumber daya ini, untuk setiap ukuran komputasi database telah disediakan untuk:

    SELECT rs.database_name
    ,    rs.sku
    ,    storage_mb                           = MAX(rs.storage_in_megabytes)
    ,    'Average CPU Utilization In %'       = AVG(rs.avg_cpu_percent)
    ,    'Maximum CPU Utilization In %'       = MAX(rs.avg_cpu_percent)
    ,    'Average Data IO In %'               = AVG(rs.avg_data_io_percent)
    ,    'Maximum Data IO In %'               = MAX(rs.avg_data_io_percent)
    ,    'Average Log Write Utilization In %' = AVG(rs.avg_log_write_percent)
    ,    'Maximum Log Write Utilization In %' = MAX(rs.avg_log_write_percent)
    ,    'Maximum Requests In %'              = MAX(rs.max_worker_percent)
    ,    'Maximum Sessions In %'              = MAX(rs.max_session_percent)
    FROM sys.resource_stats AS rs
    WHERE rs.database_name = 'userdb1' 
    AND rs.start_time > DATEADD(day, -7, GETDATE())
    GROUP BY rs.database_name, rs.sku;
    
  3. Dengan informasi ini tentang nilai rata-rata dan maksimum setiap metrik sumber daya, Anda dapat menilai seberapa baik beban kerja Anda sesuai dengan ukuran komputasi yang Anda pilih. Biasanya, nilai rata-rata dari sys.resource_stats memberi Anda garis besar yang baik untuk digunakan terhadap ukuran target.

    • Untuk database model pembelian DTU:

      Misalnya, Anda mungkin menggunakan tingkat layanan Standar dengan ukuran komputasi S2. Persentase penggunaan rata-rata untuk pembacaan dan penulisan CPU dan I/O di bawah 40 persen, jumlah rata-rata pekerja di bawah 50, dan jumlah rata-rata sesi di bawah 200. Beban kerja Anda mungkin sesuai dengan ukuran komputasi S1. Sangat mudah untuk melihat apakah database Anda cocok dengan batas pekerja dan sesi. Untuk melihat apakah database cocok dengan ukuran komputasi yang lebih rendah, bagi jumlah DTU dari ukuran komputasi yang lebih rendah dengan jumlah DTU ukuran komputasi Anda saat ini, lalu kalikan hasilnya dengan 100:

      S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40

      Hasilnya adalah perbedaan performa relatif antara dua ukuran komputasi dalam persentase. Jika penggunaan sumber daya Anda tidak melebihi persentase ini, beban kerja Anda mungkin sesuai dengan ukuran komputasi yang lebih rendah. Namun, Anda perlu melihat semua rentang nilai penggunaan sumber daya, dan menentukan, berdasarkan persentase, seberapa sering beban kerja database Anda akan masuk ke ukuran komputasi yang lebih rendah. Kueri berikut menghasilkan persentase kecocokan per dimensi sumber daya, berdasarkan ambang 40 persen yang kami hitung dalam contoh ini:

       SELECT database_name,
           100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent'
       FROM sys.resource_stats
       WHERE start_time > DATEADD(day, -7, GETDATE())
       AND database_name = 'sample' --remove to see all databases
       GROUP BY database_name;
      

      Berdasarkan tingkat layanan database, Anda dapat memutuskan apakah beban kerja Anda sesuai dengan ukuran komputasi yang lebih rendah. Jika tujuan beban kerja database Anda adalah 99,9 persen dan kueri sebelumnya mengembalikan nilai yang lebih besar dari 99,9 persen untuk ketiga dimensi sumber daya, beban kerja Anda kemungkinan cocok dengan ukuran komputasi yang lebih rendah.

      Melihat persentase kecocokan juga memberi Anda wawasan tentang apakah Anda harus pindah ke ukuran komputasi yang lebih tinggi berikutnya untuk memenuhi tujuan Anda. Misalnya, penggunaan CPU untuk database sampel selama seminggu terakhir:

      Persentase CPU rata-rata Persentase CPU maksimum
      24,5 100.00

      CPU rata-rata adalah sekitar seperempat dari batas ukuran komputasi, yang akan cocok dengan ukuran komputasi database.

    • Untuk model pembelian DTU dan database model pembelian vCore:

      Nilai maksimum menunjukkan bahwa database mencapai batas ukuran komputasi. Apakah Anda perlu pindah ke ukuran komputasi yang lebih tinggi berikutnya? Lihatlah berapa kali beban kerja Anda mencapai 100 persen, lalu bandingkan dengan tujuan beban kerja database Anda.

       SELECT database_name,
           100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent',
           100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent'
       FROM sys.resource_stats
       WHERE start_time > DATEADD(day, -7, GETDATE())
       AND database_name = 'sample'  --remove to see all databases
       GROUP BY database_name;
      

      Persentase ini adalah jumlah sampel yang sesuai dengan beban kerja Anda di bawah ukuran komputasi saat ini. Jika kueri ini mengembalikan nilai kurang dari 99,9 persen untuk salah satu dari tiga dimensi sumber daya, beban kerja rata-rata sampel Anda melebihi batas. Pertimbangkan untuk pindah ke ukuran komputasi yang lebih tinggi berikutnya atau gunakan teknik penyetelan aplikasi untuk mengurangi beban pada database.

sys.dm_elastic_pool_resource_stats

Demikian pula dengan sys.dm_db_resource_stats, sys.dm_elastic_pool_resource_stats menyediakan data penggunaan sumber daya terbaru dan terperinci untuk kumpulan elastis. Tampilan dapat dikueri dalam database apa pun dalam kumpulan elastis untuk menyediakan data penggunaan sumber daya untuk seluruh kumpulan, bukan database tertentu. Nilai persentase yang dilaporkan oleh DMV ini adalah terhadap batas kumpulan elastis, yang mungkin lebih tinggi dari batas untuk database di kumpulan.

Contoh ini menunjukkan data penggunaan sumber daya yang dirangkum untuk kumpulan elastis saat ini dalam 15 menit terakhir:

SELECT dso.elastic_pool_name,
       AVG(eprs.avg_cpu_percent) AS avg_cpu_percent,
       MAX(eprs.avg_cpu_percent) AS max_cpu_percent,
       AVG(eprs.avg_data_io_percent) AS avg_data_io_percent,
       MAX(eprs.avg_data_io_percent) AS max_data_io_percent,
       AVG(eprs.avg_log_write_percent) AS avg_log_write_percent,
       MAX(eprs.avg_log_write_percent) AS max_log_write_percent,
       MAX(eprs.max_worker_percent) AS max_worker_percent,
       MAX(eprs.used_storage_percent) AS max_used_storage_percent,
       MAX(eprs.allocated_storage_percent) AS max_allocated_storage_percent
FROM sys.dm_elastic_pool_resource_stats AS eprs
CROSS JOIN sys.database_service_objectives AS dso
WHERE eprs.end_time >= DATEADD(minute, -15, GETUTCDATE())
GROUP BY dso.elastic_pool_name;

Jika Anda menemukan bahwa penggunaan sumber daya mendekati 100% untuk jangka waktu yang signifikan, Anda mungkin perlu meninjau penggunaan sumber daya untuk database individual di kumpulan elastis yang sama untuk menentukan berapa banyak setiap database berkontribusi pada penggunaan sumber daya tingkat kumpulan.

sys.elastic_pool_resource_stats

Demikian sys.resource_statspula dengan , sys.elastic_pool_resource_stats dalam master database menyediakan data penggunaan sumber daya historis untuk semua kumpulan elastis di server logis. Anda dapat menggunakan sys.elastic_pool_resource_stats untuk pemantauan historis selama 14 hari terakhir, termasuk analisis tren penggunaan.

Contoh ini menunjukkan data penggunaan sumber daya yang dirangkum dalam tujuh hari terakhir untuk semua kumpulan elastis di server logis saat ini. Jalankan kueri dalam master database.

SELECT elastic_pool_name,
       AVG(avg_cpu_percent) AS avg_cpu_percent,
       MAX(avg_cpu_percent) AS max_cpu_percent,
       AVG(avg_data_io_percent) AS avg_data_io_percent,
       MAX(avg_data_io_percent) AS max_data_io_percent,
       AVG(avg_log_write_percent) AS avg_log_write_percent,
       MAX(avg_log_write_percent) AS max_log_write_percent,
       MAX(max_worker_percent) AS max_worker_percent,
       AVG(avg_storage_percent) AS avg_used_storage_percent,
       MAX(avg_storage_percent) AS max_used_storage_percent,
       AVG(avg_allocated_storage_percent) AS avg_allocated_storage_percent,
       MAX(avg_allocated_storage_percent) AS max_allocated_storage_percent
FROM sys.elastic_pool_resource_stats
WHERE start_time >= DATEADD(day, -7, GETUTCDATE())
GROUP BY elastic_pool_name
ORDER BY elastic_pool_name ASC;

Permintaan bersamaan

Untuk melihat jumlah permintaan bersamaan saat ini, jalankan kueri ini di database pengguna Anda:

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

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.

Tingkat permintaan rata-rata

Contoh ini menunjukkan cara menemukan tingkat permintaan rata-rata untuk database, atau untuk database dalam kumpulan elastis, selama periode waktu tertentu. Dalam contoh ini, periode waktu diatur ke 30 detik. Anda dapat menyesuaikannya dengan memodifikasi WAITFOR DELAY pernyataan. Jalankan kueri ini di database pengguna Anda. Jika database berada dalam kumpulan elastis dan jika Anda memiliki izin yang memadai, hasilnya menyertakan database lain dalam kumpulan elastis.

DECLARE @DbRequestSnapshot TABLE (
                                 database_name sysname PRIMARY KEY,
                                 total_request_count bigint NOT NULL,
                                 snapshot_time datetime2 NOT NULL DEFAULT (SYSDATETIME())
                                 );

INSERT INTO @DbRequestSnapshot
(
database_name,
total_request_count
)
SELECT rg.database_name,
       wg.total_request_count
FROM sys.dm_resource_governor_workload_groups AS wg
INNER JOIN sys.dm_user_db_resource_governance AS rg
ON wg.name = CONCAT('UserPrimaryGroup.DBId', rg.database_id);

WAITFOR DELAY '00:00:30';

SELECT rg.database_name,
       (wg.total_request_count - drs.total_request_count) / DATEDIFF(second, drs.snapshot_time, SYSDATETIME()) AS requests_per_second
FROM sys.dm_resource_governor_workload_groups AS wg
INNER JOIN sys.dm_user_db_resource_governance AS rg
ON wg.name = CONCAT('UserPrimaryGroup.DBId', rg.database_id)
INNER JOIN @DbRequestSnapshot AS drs
ON rg.database_name = drs.database_name;

Sesi saat ini

Untuk melihat jumlah sesi aktif saat ini, jalankan kueri ini di database Anda:

SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

Kueri ini mengembalikan jumlah titik waktu. Jika Anda mengumpulkan beberapa sampel dari waktu ke waktu, Anda akan memiliki pemahaman terbaik tentang penggunaan sesi Anda.

Riwayat permintaan, sesi, dan pekerja terbaru

Contoh ini mengembalikan penggunaan historis permintaan, sesi, dan utas pekerja baru-baru ini untuk database, atau untuk database dalam kumpulan elastis. Setiap baris mewakili rekam jepret penggunaan sumber daya pada titik waktu untuk database. Kolom requests_per_second adalah tingkat permintaan rata-rata selama interval waktu yang berakhir pada snapshot_time. Jika database berada dalam kumpulan elastis dan jika Anda memiliki izin yang memadai, hasilnya menyertakan database lain dalam kumpulan elastis.

SELECT rg.database_name,
       wg.snapshot_time,
       wg.active_request_count,
       wg.active_worker_count,
       wg.active_session_count,
       CAST(wg.delta_request_count AS decimal) / duration_ms * 1000 AS requests_per_second
FROM sys.dm_resource_governor_workload_groups_history_ex AS wg
INNER JOIN sys.dm_user_db_resource_governance AS rg
ON wg.name = CONCAT('UserPrimaryGroup.DBId', rg.database_id)
ORDER BY snapshot_time DESC;

Menghitung ukuran database dan objek

Kueri berikut mengembalikan ukuran data dalam database Anda (dalam megabyte):

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

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

-- Calculates the size of individual database objects.
SELECT o.name, SUM(ps.reserved_page_count) * 8.0 / 1024 AS size_mb
FROM sys.dm_db_partition_stats AS ps 
    INNER JOIN sys.objects AS o 
        ON ps.object_id = o.object_id
GROUP BY o.name
ORDER BY size_mb DESC;

Mengidentifikasi masalah performa CPU

Bagian ini membantu Anda mengidentifikasi kueri individual yang merupakan konsumen CPU teratas.

Jika konsumsi CPU di atas 80% untuk jangka waktu yang lama, pertimbangkan langkah-langkah pemecahan masalah berikut apakah masalah CPU terjadi sekarang atau telah terjadi di masa lalu. Anda juga dapat mengikuti langkah-langkah di bagian ini untuk secara proaktif mengidentifikasi kueri penggunaan CPU teratas dan menyetelnya. Dalam beberapa kasus, mengurangi konsumsi CPU mungkin memungkinkan Anda menurunkan skala database dan kumpulan elastis dan menghemat biaya.

Langkah-langkah pemecahan masalah sama untuk database dan database mandiri di kumpulan elastis. Jalankan semua kueri dalam database pengguna.

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 kueri teratas menurut hash kueri:

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 Query Store. Pengguna dengan akses database bisa menggunakan T-SQL untuk mengkueri data Query Store. Secara default, Penyimpanan Kueri mengambil statistik kueri agregat untuk interval satu jam.

  1. 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() untuk melihat periode waktu selain dua jam terakhir:

    -- Top 15 CPU consuming queries by query hash
    -- Note that a query hash can have many query ids if not parameterized or not parameterized properly
    WITH AggregatedCPU
    AS (
        SELECT q.query_hash
            ,SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms
            ,SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms
            ,MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms
            ,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
            INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
            INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
            INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
            INNER 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_ms
            ,avg_cpu_ms
            ,max_cpu_ms
            ,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_ms DESC
                    ,query_hash ASC
                ) AS query_hash_row_number
        FROM AggregatedCPU
        )
    SELECT OD.query_hash
        ,OD.total_cpu_ms
        ,OD.avg_cpu_ms
        ,OD.max_cpu_ms
        ,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.query_hash_row_number
    FROM OrderedCPU AS OD
    WHERE OD.query_hash_row_number <= 15 --get top 15 rows by total_cpu_ms
    ORDER BY total_cpu_ms DESC;
    
  2. Setelah Anda mengidentifikasi kueri yang bermasalah, saatnya untuk menyetel kueri tersebut untuk mengurangi pemanfaatan CPU. Atau, Anda dapat memilih untuk meningkatkan ukuran komputasi database atau kumpulan elastis untuk mengatasi masalah ini.

Untuk informasi selengkapnya tentang menangani masalah performa CPU di Azure SQL Database, lihat Mendiagnosis dan memecahkan masalah CPU tinggi di Azure SQL Database.

Mengidentifikasi masalah performa I/O

Saat mengidentifikasi masalah performa input/output penyimpanan (I/O), jenis tunggu teratas adalah:

  • PAGEIOLATCH_*

    Untuk masalah I/O file data (termasuk PAGEIOLATCH_SH, , PAGEIOLATCH_UPPAGEIOLATCH_EX). Jika nama jenis tunggu memiliki IO di dalamnya, itu menunjuk ke masalah I/O. Jika tidak ada IO di nama tunggu kait halaman, itu menunjuk ke jenis masalah yang berbeda yang tidak terkait dengan performa penyimpanan (misalnya, tempdb pertikaian).

  • WRITE_LOG

    Untuk masalah I/O log transaksi.

Jika masalah I/O terjadi sekarang

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

Mengidentifikasi data dan penggunaan I/O log

Gunakan kueri berikut untuk mengidentifikasi data dan penggunaan I/O log.

SELECT
    database_name = DB_NAME()
,   UTC_time = end_time
,   'Data IO In % of Limit'                   = rs.avg_data_io_percent
,   'Log Write Utilization In % of Limit'     = rs.avg_log_write_percent
FROM sys.dm_db_resource_stats AS rs  --past hour only
ORDER BY  rs.end_time DESC;

Untuk contoh selengkapnya menggunakan sys.dm_db_resource_stats, lihat bagian Memantau penggunaan sumber daya nanti di artikel ini.

Jika batas I/O telah tercapai, Anda memiliki dua opsi:

  • Meningkatkan ukuran komputasi atau tingkat layanan
  • Identifikasi dan sesuaikan kueri yang paling banyak menggunakan I/O.

Untuk mengidentifikasi kueri teratas berdasarkan tunggu terkait I/O, Anda bisa menggunakan kueri Penyimpanan Kueri berikut 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
                         INNER JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
                         INNER JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
                         INNER JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
                         INNER 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 query_hash_row_number
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.query_hash_row_number
FROM Ordered AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_wait_time_ms
ORDER BY total_wait_time_ms DESC;
GO

Anda juga dapat menggunakan tampilan sys.query_store_runtime_stats , berfokus pada kueri dengan nilai besar di avg_physical_io_reads kolom dan avg_num_physical_io_reads .

Lihat total log I/O untuk writelog menunggu

Jika jenis tunggu adalah WRITELOG, gunakan kueri berikut untuk menampilkan total I/O log 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_ms,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms,
           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
        INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
        INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
        INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
        INNER 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 query_hash_row_number
    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.query_hash_row_number
FROM OrderedLogUsed AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_log_bytes_used
ORDER BY total_log_bytes_used DESC;
GO

Mengidentifikasi masalah performa tempdb

Jenis tunggu umum yang terkait dengan tempdb masalah 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 mengonfirmasi tempdb ketidakcocokan lebih lanjut, gunakan sys.dm_exec_requests untuk mengonfirmasi bahwa wait_resource nilai dimulai dengan 2:x:y di mana 2 adalah tempdb ID database, x adalah ID file, dan y merupakan 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
  • Kueri yang memiliki rencana kueri yang menggunakan pengurutan, gabungan hash, dan tampungan

Untuk informasi selengkapnya, lihat tempdb di Azure SQL.

Semua database dalam kumpulan elastis berbagi database yang sama tempdb . Pemanfaatan ruang tinggi tempdb oleh satu database mungkin memengaruhi database lain dalam kumpulan elastis yang sama.

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)') AS 'Database'
, stmt.stmt_details.value('@Schema', 'varchar(max)') AS 'Schema'
, stmt.stmt_details.value('@Table', 'varchar(max)') AS '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
        INNER JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;
GO
DROP TABLE #tmpPlan
DROP TABLE #tmp2

Mengidentifikasi transaksi yang berjalan lama

Gunakan kueri berikut untuk mengidentifikasi transaksi yang berjalan lama. Transaksi yang berjalan lama mencegah pembersihan penyimpanan versi persisten (PVS). Untuk informasi selengkapnya, lihat Memecahkan masalah pemulihan database yang dipercepat.

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 adalah RESOURCE_SEMAPHORE, Anda mungkin memiliki masalah tunggu pemberian memori di mana kueri tidak dapat mulai dijalankan sampai mereka mendapatkan pemberian memori yang cukup besar.

Menentukan apakah tunggu RESOURCE_SEMAPHORE adalah tunggu atas

Gunakan kueri berikut untuk menentukan apakah RESOURCE_SEMAPHORE tunggu adalah tunggu teratas. Juga indikasi akan menjadi naiknya pangkat RESOURCE_SEMAPHORE waktu tunggu dalam sejarah baru-baru ini. Untuk informasi selengkapnya tentang pemecahan masalah tunggu pemberian memori, lihat Memecahkan masalah performa lambat atau memori rendah yang disebabkan oleh pemberian memori di SQL Server.

SELECT wait_type,
       SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
    INNER 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 kehabisan memori di Azure SQL Database, tinjau sys.dm_os_out_of_memory_events. Untuk informasi selengkapnya, lihat Memecahkan masalah kesalahan kehabisan memori dengan Azure SQL Database.

Pertama, ubah skrip berikut untuk memperbarui nilai yang relevan dari start_time dan end_time. Kemudian, jalankan kueri berikut untuk mengidentifikasi pernyataan yang memakan memori 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
    INNER JOIN sys.query_store_runtime_stats AS r
        ON p.plan_id = r.plan_id
    INNER 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';

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
    INNER JOIN sys.query_store_query AS q
        ON cte.query_id = q.query_id
    INNER JOIN sys.query_store_query_text AS t
        ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;

Mengidentifikasi 10 peruntukan memori aktif teratas

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), ' ')) AS 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
    INNER 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;

Memantau koneksi

Anda dapat menggunakan tampilan sys.dm_exec_connections untuk mengambil informasi tentang koneksi yang dibuat ke database tertentu dan detail setiap koneksi. Jika database berada dalam kumpulan elastis dan Anda memiliki izin yang memadai, tampilan mengembalikan kumpulan koneksi untuk semua database di kumpulan elastis. Selain itu, tampilan sys.dm_exec_sessions sangat membantu saat mengambil informasi tentang semua koneksi pengguna aktif dan tugas internal.

Lihat sesi saat ini

Kueri berikut mengambil informasi untuk koneksi dan sesi Anda saat ini. Untuk melihat semua koneksi dan sesi, hapus WHERE klausa.

Anda melihat semua sesi eksekusi pada database hanya jika Anda memiliki VIEW DATABASE STATE izin pada database saat menjalankan sys.dm_exec_requests tampilan dan sys.dm_exec_sessions . Jika tidak, Anda hanya melihat sesi 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
    INNER JOIN sys.dm_exec_sessions AS s
        ON c.session_id = s.session_id
WHERE c.session_id = @@SPID; --Remove to view all sessions, if permissions allow

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 menggunakan tampilan manajemen dinamis sys.dm_exec_query_stats . Tampilan berisi satu baris per pernyataan kueri dalam paket yang di-cache, dan masa pakai baris terikat dengan rencana itu sendiri. Saat paket dihapus dari cache, baris terkait dihilangkan dari tampilan ini. Jika kueri tidak memiliki paket cache, misalnya karena OPTION (RECOMPILE) digunakan, kueri tidak ada dalam hasil dari tampilan ini.

Menemukan kueri teratas menurut waktu CPU

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

SELECT TOP 15 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 Avg_CPU_Time DESC;

Memantau rencana kueri untuk waktu CPU kumulatif

Rencana kueri yang tidak efisien juga dapat meningkatkan penggunaan CPU. Contoh berikut menentukan kueri mana yang menggunakan CPU paling kumulatif dalam riwayat terbaru.

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 15
            qs.plan_handle,
            qs.total_worker_time
        FROM
            sys.dm_exec_query_stats AS 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;

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 sys.dm_tran_locks tampilan untuk mendapatkan informasi tentang aktivitas penguncian saat ini dalam database. Untuk contoh 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 mungkin memblokir satu dengan yang lain, yang mengakibatkan kebuntuan.

Anda dapat membuat jejak Kejadian yang Diperluas untuk menangkap peristiwa kebuntuan, lalu menemukan kueri terkait dan rencana eksekusinya di Penyimpanan Kueri. Pelajari selengkapnya di Menganalisis dan mencegah kebuntuan di Azure SQL Database, termasuk lab untuk Menyebabkan kebuntuan di AdventureWorksLT. Pelajari selengkapnya tentang jenis sumber daya yang dapat membuat kebuntuan.