Dasar-dasar pemantauan kinerja dan pemecahan masalah dalam SQL Server

Pada artikel ini, kita akan melihat alat-alat populer, pertanyaan T-SQL dan skrip untuk mendeteksi dan memecahkan berbagai masalah yang mungkin terjadi dengan kinerja SQL Server. Artikel ini akan membantu Anda mengetahui kapan SQL Server Anda memiliki sumber daya yang tidak mencukupi (memori, CPU, TIO disk), menemukan kunci, dan mendeteksi permintaan lambat. Mari kita lihat apa saja alat bawaan dan skrip dan utilitas pihak ketiga gratis untuk menganalisis keadaan Microsoft SQL Server.

Konten:

  • Alat Diagnostik SQL Server
  • Mendeteksi dan menyelesaikan masalah kinerja SQL Server

Alat Diagnostik SQL Server

Jika Anda mendiagnosis masalah dengan benar, maka setengah dari pekerjaan sudah dilakukan. Pertimbangkan alat apa yang biasanya digunakan oleh administrator sistem untuk mendiagnosis berbagai masalah dalam SQL Server:

  • T-sql - Alat yang paling kuat, sederhana, dan sangat diperlukan untuk pemecahan masalah dan menganalisis kinerja SQL Server. Hampir semua alat lain untuk bekerja dengan SQL Server menggunakan T-SQL. Tidak ada yang tidak dapat Anda lakukan dengan T-SQL.
  • Studio Manajemen SQL Server - Tanpa SSMS, hampir tidak mungkin untuk bekerja dengan SQL Server. Menggunakan SSMS, Anda dapat melihat Monitor aktivitas, menganalisis rencana kueri, melihat parameter server atau database, dan banyak hal lainnya.
  • SQL Server dan Log Kesalahan Windows - jika terjadi kesalahan, log kesalahan adalah tempat pertama yang dilihat administrator sistem. Log kesalahan SQL Server dapat dilihat melalui SSMS. Log Windows dapat dilihat melalui snap-in eventvwr.msc.
  • Monitor Sumber Daya Windows - resmon.exe adalah alat Windows yang sangat diperlukan untuk dengan cepat menilai status sumber daya server. Penggunaan RAM dan prosesor dapat dilihat melalui Task Manager, tetapi penggunaan rinci jaringan dan hard drive hanya dapat dilihat melalui resmon dan perfmon.
  • Monitor Sistem Windows (Monitor Kinerja) - Perfmon.exe adalah alat utama untuk memantau Windows, ini berisi berbagai "penghitung", baik metrik sistem dan aplikasi, termasuk SQL Server. Biasanya, penghitung perfmon diproses menggunakan sistem pemantauan lain, misalnya, Zabbix, karena dalam perfmon tidak nyaman untuk menyimpan dan melihat data untuk waktu yang lalu..
  • Aplikasi pihak ke-3 - Ada banyak aplikasi pemantauan SQL Server berbayar dan gratis. Sebagai contoh, salah satu aplikasi gratis adalah dbForge Monitor dari perusahaan Devart. Aplikasi ini diinstal sebagai tambahan untuk SSMS dan memungkinkan Anda untuk menampilkan dasbor yang sangat nyaman untuk menampilkan status SQL Server Anda saat ini (informasi tentang penggunaan memori, CPU, beban, kunci, proses, informasi tentang cadangan, "query SQL yang berat", kinerja subsistem disk, dll. .).
  • Script Brentozar - Ini adalah solusi populer untuk mendiagnosis pengaturan dan kesehatan SQL Server. Brentozar memiliki banyak skrip untuk berbagai tugas, tetapi untuk diagnostik kami tertarik pada "sp_blitz". Anda dapat mengunduhnya secara gratis dari situs web resmi https://www.brentozar.com/blitz/. Jalankan sp_Blitz.sql untuk menginstal prosedur yang diperlukan dan menjalankannya. exec sp_blitz untuk diagnosis. Alat ini gratis dan dikelola oleh komunitas SQL Server. Sp_blitz akan mengidentifikasi semua masalah populer dengan server Anda dan menyarankan cara menyelesaikannya..
  • Kumpulan skrip T-SQL - lebih mudah untuk memiliki koleksi langsung dari berbagai pertanyaan T-SQL untuk diagnosa SQL Server, karena tidak selalu ada waktu untuk menulis pertanyaan Anda sendiri, lebih baik mempersenjatai diri Anda terlebih dahulu. Berikut ini adalah tautan ke kueri T-SQL / PowerShell yang berguna, yang sering saya gunakan saat mendiagnosis dan menyetel MS SQL:
    • https://github.com/SQLadmin/AwesomeSQLServer - sekumpulan pertanyaan untuk memantau CPU / RAM / Disk IO dan parameter lainnya.
    • https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server - banyak skrip yang bermanfaat dan peretas kehidupan
    • https://github.com/ktaranov/sqlserver-kit - Skrip dan informasi berguna. Banyak contoh bekerja dengan SQL Server melalui PowerShell

Mendeteksi dan menyelesaikan masalah kinerja SQL Server

Masalah paling umum yang dihadapi oleh administrator sistem yang bekerja dengan SQL Server adalah keluhan pengguna tentang kinerja kueri dan server itu sendiri: "melambat", "kueri membutuhkan waktu lama", dan sebagainya.

Pertama-tama, Anda perlu memastikan bahwa server memiliki sumber daya yang cukup. Mari kita lihat bagaimana cara cepat menganalisis penggunaan memori, CPU, disk, dan kunci dalam SQL Server..

Analisis Penggunaan RAM SQL Server

Pertama, Anda perlu menentukan berapa banyak memori yang tersedia di SQL Server. Untuk melakukan ini, mulai SSMS (SQL Server Management Studio), pergi ke server dan pergi ke properti server (RMB dengan nama server di Object Browser).

Kapasitas RAM yang tersedia saja tidak akan memberi tahu Anda apa pun. Anda perlu membandingkan nomor ini dengan memori yang digunakan di Task Manager dan mesin SQL Server itu sendiri menggunakan DMV.

Di Task Manager, pada tab Details, cari sqlservr.exe dan lihat berapa banyak RAM yang digunakan proses ini.

  • Jika server, misalnya, memiliki 128 GB RAM, dan proses sqlservr.exe menggunakan 60 GB dan SQL Server tidak memiliki batas RAM, maka Anda memiliki cukup RAM.
  • Jika SQL Server menggunakan 80-90% dari yang ditetapkan atau RAM maksimum, maka Anda perlu menonton DMV. Perlu diingat bahwa sqlservr.exe tidak akan dapat menggunakan semua RAM. Jika server memiliki 128 GB, maka sqlservr.exe hanya dapat menggunakan 80-90% (100-110 GB), karena sisa memori dicadangkan untuk sistem operasi.

Perlu diingat bahwa proses SQL Server tidak mengirim RAM kembali ke sistem. Misalnya, SQL Server Anda biasanya menggunakan memori 20 GB, tetapi dengan laporan bulanan meningkatkan konsumsi hingga 100 GB, dan bahkan ketika perhitungan laporan selesai dan server beroperasi dalam mode sebelumnya, proses SQL Server masih akan menggunakan 100 GB hingga layanan dimulai kembali.

Bahkan jika Anda yakin bahwa server memiliki cukup RAM, tidak akan berlebihan untuk mengetahui persis jumlah RAM yang dikonsumsi..

Anda dapat mengetahui penggunaan nyata dari penggunaan RAM Pandangan manajemen yang dinamis. DMV adalah pemirsa administratif. Dengan DMV, Anda dapat mendiagnosis hampir semua masalah di SQL Server.

Mari kita lihat sys.dm_os_sys_memory, untuk kenyamanan kami menggunakan permintaan:

SELECT total_physical_memory_kb / 1024 AS [Total Memory Fisik], available_physical_memory_kb / 1024 AS [Memory Fisik Tersedia], total_page_file_kb / 1024 AS [Total File Halaman (MB)], available_page_file_kb / 1024 AS [File Halaman Tersedia (MB), [File Halaman Tersedia (MB), 100) 100 * Cast (available_physical_memory_kb AS DECIMAL (18, 3)) / Cast (total_physical_memory_kb AS DECIMAL (18, 3)) SEBAGAI 'Persentase Digunakan', system_memory_state_desc AS [Status Memori] FROM sys.dm_os_sys_memory;

Pertimbangkan setiap parameter output:

  1. [Memori fisik total] - jumlah RAM yang tersedia di sistem operasi. Pada beberapa server, ini mungkin menunjukkan sedikit lebih banyak daripada yang sebenarnya diinstal.
  2. [Memori Fisik Yang Tersedia] - jumlah RAM yang tersedia untuk SQL Server, tidak termasuk SQL Server yang sudah ditangkap.
  3. [Total Halaman File (MB)] - Volume "batas komit". Batas Komit = RAM + semua file swap. Artinya, jika Anda memiliki 32 GB RAM dan 16 paging file pada server, batas komit akan 48 GB.
  4. [File Halaman yang Tersedia (MB)] - Ukuran file paging.
  5. Persentase yang digunakan - persentase RAM yang digunakan. Tidak ada parameter seperti itu di sys.dm_os_sys_memory itu sendiri, tetapi dihitung menggunakan rumus available_physical_memory_kb / total_physical_memory_kb
  6. [Keadaan memori] - Status RAM. Bidang system_memory_state_desc berisi keadaan konsumsi RAM dalam bentuk teks. Nilai bidang ini dianggap berdasarkan pada dua lainnya: system_low_memory_signal_state dan system_high_memory_signal_state. Anda dapat memilihnya secara langsung jika Anda memerlukan format data Boolean / bit. Untuk semua bidang sys.dm_os_sys_memory, lihat dokumentasi https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact -sql? view = sql-server-ver15

Semua data ini berguna jika Anda ingin menentukan dengan tepat berapa banyak SQL Server Anda mengkonsumsi RAM. Ini paling sering digunakan jika ada kecurigaan bahwa terlalu banyak RAM dialokasikan untuk instance..

Jika Anda perlu memastikan bahwa server memiliki RAM yang cukup, Anda hanya dapat melihat bidang system_low_memory_signal_state, system_high_memory_signal_state dan system_memory_state_desc. Jika system_low_memory_signal_state = 1, maka server jelas tidak memiliki cukup RAM.

Penggunaan CPU dalam SQL Server

Beban pada prosesor lebih mudah untuk ditentukan, karena dapat dilakukan di Task Manager. Untuk mengetahui beban saat ini pada prosesor, temukan proses sqlservr.exe di Task Manager

Jika Anda ingin mengetahui beban untuk waktu yang lalu, Anda dapat menggunakan kueri:

Jangan lupa untuk mengubah @astnMin ke nomor yang Anda butuhkan dalam hitungan menit.
DECLARE @ts BIGINT; DECLARE @lastNmin TINYINT; SET @lastNmin = 30; SELECT @ts = (SELECT cpu_ticks / (cpu_ticks / ms_ticks) DARI sys.dm_os_sys_info); SELECT TOP (@lastNmin) Dateadd (ms, -1 * (@ts - [timestamp]), Getdate ()) SEBAGAI [EventTime], sqlprocessutilization AS [SQL Server Utilization], 100 - systemidle - sqlprocessutilization AS [Proses Lainnya CPU_Utilization] , systemidle AS [System Idle] DARI (SELECT record.value ('(./ Record / @ id) [1]', 'int') AS record_id, record.value ('(./ Record / SchedulerMonitorEvent / SystemHealth / SystemIdle ) [1] ',' int ') AS [SystemIdle], record.value (' (./ Record / SchedulerMonitorEvent / SystemHealth / ProcessUtilization) [1] ',' int ') AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp], CONVERT (XML, record) SEBAGAI [record] DARI sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR 'Dan catat LIKE' %% ') AS x) SEBAGAI ORDER DENGAN record_id DESC;

Hasilnya, kami mendapatkan statistik per menit tentang penggunaan prosesor.

Analisis Beban Disk SQL Server

Mari kita lihat memuat disk di sistem operasi. Untuk melakukan ini, jalankan resmon.exe.

Kami membutuhkan tab Disk. Di bagian Aktivitas disk file yang sedang diakses ditampilkan, dan kecepatan baca / tulisnya saat ini. Saring bagian ini dengan Total (klik Total). Di bagian paling atas akan ada file yang saat ini membuat sebagian besar drive. Dalam kasus SQL Server, ini dapat berguna untuk menentukan basis data mana yang paling banyak memuat disk..

Bagian Penyimpanan menampilkan semua drive dalam sistem. Di bagian ini, kita membutuhkan 2 parameter - Waktu Aktif dan Antrian Disk. Waktu Aktif sebagai persentase menampilkan beban pada disk, yaitu, jika Anda melihat pada disk C: \ Active Time sama dengan 90, ini berarti bahwa sumber daya baca / tulis disk saat ini digunakan pada 90%. Kolom Antrian Disk menampilkan antrian akses disk, dan jika nilai antrian tidak nol, maka disk tersebut 100% dimuat dan tidak dapat menangani beban. Juga, jika Waktu Aktif mendekati 100, maka disk tersebut digunakan hampir pada batas kemampuannya dalam kecepatan.

Lihat kunci dalam SQL Server

Setelah kami memastikan bahwa server memiliki sumber daya yang cukup, kami dapat melanjutkan untuk melihat kunci.

Kunci dapat dilihat melalui Monitor Aktivitas di SSMS, tetapi kami akan menggunakan T-SQL, karena opsi ini lebih nyaman dan visual. Kami memenuhi permintaan:

SET NOCOUNT ON GO SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR (10), "), CHAR (13),") SEBAGAI BATCH KE # T DARI Sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text (R. SQL_HANDLE) PERGI DENGAN BLOCKER (SPID, BLOCKED, LEVEL, BATCH) SEBAGAI (PILIH SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) SEBAGAI TINGKAT, BATCH DARI #TR WHERE (BLOCKED = 0 ATAU BLOCKED = SPID) DAN ADA (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID DAN R2.BLOCKED R2.SPID) UNION ALL SELECT R.SPID, R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH FROM #T AS R INNER BERGABUNG BLOKER DI R. BLOCKED = BLOCKERS.SPID WHERE R. BLOCKED> 0 AND R. BLOCKED R.SPID) PILIH N "+ REPLICATE (N '|', LEN (LEVEL) / 4 - 1) + KASUS KAPAN ( LEN (LEVEL) / 4 - 1) = 0 LALU 'HEAD -' ELSE '| ------' AKHIR + CAST (SPID AS NVARCHAR (10)) + N "+ BATCH SEBAGAI BLOCKING_TREE DARI BLOCKER ORDER BY LEVEL ASC GO DROP TABLE #T GO

Kueri ini mengembalikan daftar kunci dalam bentuk pohon. Ini nyaman dalam operasi, karena biasanya, jika satu kunci terjadi, itu memprovokasi orang lain. Demikian pula, di Monitor Aktivitas atau dalam output sp_who2, Anda dapat melihat bidang "Diblokir Oleh".

Jika permintaan tidak mengembalikan apa pun, maka tidak ada kunci.

Jika permintaan mengembalikan beberapa data, maka Anda perlu menganalisis rantai.

KEPALA berarti bahwa permintaan ini adalah alasan untuk semua kunci lainnya di bawah pohon. 64 adalah pengidentifikasi proses (SPID). Setelah itu, badan permintaan ditulis, yang menyebabkan kunci. Jika Anda memiliki sumber daya server yang cukup, maka kemungkinan besar masalahnya adalah dalam permintaan itu sendiri dan dalam daya tarik bersama untuk beberapa objek. Agar lebih tepat, Anda perlu menganalisis permintaan khusus yang menyebabkan kunci.

Kebijakan SQL Server

Bahkan ketika semuanya bekerja dengan baik untuk Anda dan tidak ada keluhan, sebenarnya ada banyak masalah yang akan muncul nanti. SQL Server memiliki kebijakan untuk ini..

Kebijakan dalam SQL Server, secara umum, memeriksa aturan untuk kepatuhan dengan nilai yang diberikan. Misalnya, menggunakan kebijakan, Anda dapat memastikan bahwa Auto Shrink dimatikan pada semua database di server. Pertimbangkan contoh impor dan pelaksanaan kebijakan

Di SSMS, sambungkan ke server tempat Anda ingin menjalankan kebijakan (Manajemen -> bagian Manajemen Kebijakan).

Impor file Database Auto Shrink.xml. Klik Evaluasilah

Ada dua database pada instance node1, test1 dan test2. Autoshrink diaktifkan pada test2. Mari kita lihat detailnya.

Kebijakan menentukan parameter AutoShrink yang disertakan, dalam penjelasan deskripsi untuk aturan biasanya ditulis. Dalam hal ini, penjelasan diberikan mengapa penyusutan otomatis lebih baik..

Kebijakan dapat dijalankan sesuai jadwal atau sesuai permintaan (satu kali). Hasil eksekusi kebijakan dapat ditemukan di log kebijakan..

Saat memasang SQL Server, Anda hanya perlu memilih komponen DBMS yang digunakan dan menentukan pengaturan sesuai dengan konfigurasi perangkat keras server Anda. Selalu pastikan bahwa server memiliki sumber daya yang cukup dan tidak ada kunci di server

Alat diagnostik SQL Server yang paling kuat adalah T-SQL dan DMV. Anda juga disarankan untuk membangun pemantauan SQL Server sepanjang waktu dan infrastrukturnya untuk mendeteksi semua masalah yang mungkin terjadi..

Kategori