🥇Beberapa aspek pemantauan MS SQL Server. Pedoman Setting Trace Flag

kata pengantar

Cukup sering, pengguna, pengembang, dan administrator DBMS MS SQL Server menghadapi masalah kinerja database atau DBMS secara keseluruhan, sehingga pemantauan MS SQL Server sangat relevan.
Artikel ini merupakan tambahan dari artikel tersebut Menggunakan Zabbix untuk Memantau Basis Data MS SQL Server dan itu akan mencakup beberapa aspek pemantauan MS SQL Server, khususnya: cara cepat menentukan sumber daya mana yang hilang, serta rekomendasi untuk menyetel bendera pelacakan.
Agar skrip berikut berfungsi, Anda perlu membuat skema inf di database yang diinginkan sebagai berikut:
Membuat skema inf

use <имя_БД>;
go
create schema inf;

Metode untuk mendeteksi kekurangan RAM

Indikator pertama dari kurangnya RAM adalah kasus ketika instance MS SQL Server memakan semua RAM yang dialokasikan untuknya.
Untuk melakukan ini, kami akan membuat representasi inf.vRAM berikut:
Membuat tampilan inf.vRAM

CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb]						--сколько свободно ОЗУ на сервере в МБ
		 , a.[RAM_Avail_Percent]					--процент свободного ОЗУ на сервере
		 , a.[Server_physical_memory_Mb]				--сколько всего ОЗУ на сервере в МБ
		 , a.[SQL_server_committed_target_Mb]			--сколько всего ОЗУ выделено под MS SQL Server в МБ
		 , a.[SQL_server_physical_memory_in_use_Mb] 		--сколько всего ОЗУ потребляет MS SQL Server в данный момент времени в МБ
		 , a.[SQL_RAM_Avail_Percent]				--поцент свободного ОЗУ для MS SQL Server относительно всего выделенного ОЗУ для MS SQL Server
		 , a.[StateMemorySQL]						--достаточно ли ОЗУ для MS SQL Server
		 , a.[SQL_RAM_Reserve_Percent]				--процент выделенной ОЗУ для MS SQL Server относительно всего ОЗУ сервера
		 --достаточно ли ОЗУ для сервера
		, (case when a.[RAM_Avail_Percent]<10 and a.[RAM_Avail_Percent]>5 and a.[TotalAvailOSRam_Mb]<8192 then 'Warning' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Danger' else 'Normal' end) as [StateMemoryServer]
	from
	(
		select cast(a0.available_physical_memory_kb/1024.0 as int) as TotalAvailOSRam_Mb
			 , cast((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [RAM_Avail_Percent]
			 , a0.system_low_memory_signal_state
			 , ceiling(b.physical_memory_kb/1024.0) as [Server_physical_memory_Mb]
			 , ceiling(b.committed_target_kb/1024.0) as [SQL_server_committed_target_Mb]
			 , ceiling(a.physical_memory_in_use_kb/1024.0) as [SQL_server_physical_memory_in_use_Mb]
			 , cast(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Avail_Percent]
			 , cast((b.committed_target_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Reserve_Percent]
			 , (case when (ceiling(b.committed_target_kb/1024.0)-1024)<ceiling(a.physical_memory_in_use_kb/1024.0) then 'Warning' else 'Normal' end) as [StateMemorySQL]
		from sys.dm_os_sys_memory as a0
		cross join sys.dm_os_process_memory as a
		cross join sys.dm_os_sys_info as b
		cross join sys.dm_os_sys_memory as v
	) as a;

Kemudian Anda dapat menentukan bahwa instance MS SQL Server menggunakan semua memori yang dialokasikan untuknya dengan kueri berikut:

select  SQL_server_physical_memory_in_use_Mb,  SQL_server_committed_target_Mb
from [inf].[vRAM];

Jika SQL_server_physical_memory_in_use_Mb secara konsisten lebih besar dari atau sama dengan SQL_server_committed_target_Mb, maka statistik tunggu harus diperiksa.
Untuk menentukan kekurangan RAM melalui statistik tunggu, mari buat tampilan inf.vWaits:
Membuat Tampilan inf.vWaits

CREATE view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], --имя типа ожидания
        [wait_time_ms] / 1000.0 AS [WaitS],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
        [waiting_tasks_count] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [waiting_tasks_count]>0
		and [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
, ress as (
	SELECT
	    [W1].[wait_type] AS [WaitType],
	    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
	    [W1].[WaitCount] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
	    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
	    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
	    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
	    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
	FROM [Waits] AS [W1]
	INNER JOIN [Waits] AS [W2]
	    ON [W2].[RowNum] <= [W1].[RowNum]
	GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
	    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
	HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold
)
SELECT [WaitType]
      ,MAX([Wait_S]) as [Wait_S]
      ,MAX([Resource_S]) as [Resource_S]
      ,MAX([Signal_S]) as [Signal_S]
      ,MAX([WaitCount]) as [WaitCount]
      ,MAX([Percentage]) as [Percentage]
      ,MAX([AvgWait_S]) as [AvgWait_S]
      ,MAX([AvgRes_S]) as [AvgRes_S]
      ,MAX([AvgSig_S]) as [AvgSig_S]
  FROM ress
  group by [WaitType];

Dalam hal ini, Anda dapat menentukan kekurangan RAM dengan kueri berikut:

SELECT [Percentage]
      ,[AvgWait_S]
  FROM [inf].[vWaits]
  where [WaitType] in (
    'PAGEIOLATCH_XX',
    'RESOURCE_SEMAPHORE',
    'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Di sini Anda perlu memperhatikan indikator Persentase dan AvgWait_S. Jika totalitasnya signifikan, maka kemungkinan besar tidak ada cukup RAM untuk instance MS SQL Server. Nilai signifikan ditentukan secara individual untuk setiap sistem. Namun, Anda dapat memulai dengan yang berikut: Persentase>=1 dan AvgWait_S>=0.005.
Untuk menampilkan indikator ke sistem pemantauan (misalnya, Zabbix), Anda dapat membuat dua kueri berikut:

berapa banyak jenis penantian yang ditempati oleh RAM dalam persentase (jumlah dari semua jenis penantian tersebut):

select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

berapa banyak jenis tunggu RAM yang diambil dalam milidetik (nilai maksimum semua penundaan rata-rata untuk semua jenis tunggu tersebut):

select coalesce(max([AvgWait_S])*1000, 0.00) as [AvgWait_MS]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Berdasarkan dinamika nilai yang diperoleh untuk kedua indikator ini, kita dapat menyimpulkan apakah RAM yang ada cukup untuk instance MS SQL Server.

Metode Deteksi Overload CPU

Untuk mengidentifikasi kurangnya waktu prosesor, cukup menggunakan tampilan sistem sys.dm_os_schedulers. Di sini, jika runnable_tasks_count terus-menerus lebih besar dari 1, maka kemungkinan besar jumlah inti tidak cukup untuk instance MS SQL Server.
Untuk menampilkan indikator ke sistem pemantauan (misalnya, Zabbix), Anda dapat membuat kueri berikut:

select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;

Berdasarkan dinamika nilai yang diperoleh untuk indikator ini, kita dapat menyimpulkan apakah waktu prosesor (jumlah inti CPU) cukup untuk instance MS SQL Server.
Namun, penting untuk diingat fakta bahwa permintaan itu sendiri dapat meminta banyak utas sekaligus. Dan terkadang pengoptimal tidak dapat memperkirakan kompleksitas kueri itu sendiri dengan benar. Maka permintaan tersebut mungkin dialokasikan terlalu banyak utas yang tidak dapat diproses pada waktu yang sama pada waktu yang ditentukan. Dan ini juga menyebabkan jenis menunggu yang terkait dengan kurangnya waktu prosesor, dan pertumbuhan antrian untuk penjadwal yang menggunakan inti CPU tertentu, yaitu indikator runnable_tasks_count akan bertambah dalam kondisi seperti itu.
Dalam hal ini, sebelum menambah jumlah inti CPU, properti paralelisme dari instance MS SQL Server itu sendiri harus dikonfigurasi dengan benar, dan dari versi 2016, konfigurasikan dengan benar properti paralelisme dari database yang diperlukan:

Di sini Anda harus memperhatikan parameter berikut:

Tingkat Paralelisme Maks - menetapkan jumlah utas maksimum yang dapat dialokasikan untuk setiap permintaan (defaultnya adalah 0 - hanya dibatasi oleh sistem operasi itu sendiri dan edisi MS SQL Server)
Ambang Batas Biaya untuk Paralelisme - perkiraan biaya paralelisme (standarnya adalah 5)
Max DOP - menetapkan jumlah utas maksimum yang dapat dialokasikan untuk setiap kueri di tingkat basis data (tetapi tidak lebih dari nilai properti "Derajat Paralelisme Maks") (defaultnya adalah 0 - hanya dibatasi oleh sistem operasi itu sendiri dan edisi MS SQL Server, serta pembatasan pada properti "Max Degree of Parallelism" dari seluruh instance MS SQL Server)

Di sini tidak mungkin memberikan resep yang sama baiknya untuk semua kasus, mis. Anda perlu menganalisis kueri yang berat.
Dari pengalaman saya sendiri, saya merekomendasikan algoritme tindakan berikut untuk sistem OLTP untuk menyiapkan properti paralelisme:

pertama-tama nonaktifkan paralelisme dengan menyetel Tingkat Paralelisme Maks lebar-instans ke 1
menganalisis permintaan terberat dan memilih jumlah utas yang optimal untuknya
atur Tingkat Paralelisme Maks ke jumlah utas optimal yang dipilih yang diperoleh dari langkah 2, dan untuk basis data tertentu atur nilai DOP Maks yang diperoleh dari langkah 2 untuk setiap basis data
menganalisis permintaan terberat dan mengidentifikasi efek negatif dari multithreading. Jika ya, tingkatkan Ambang Biaya untuk Paralelisme.
Untuk sistem seperti 1C, Microsoft CRM dan Microsoft NAV, dalam banyak kasus, pelarangan multithreading cocok

Selain itu, jika ada edisi Standar, maka dalam banyak kasus larangan multithreading cocok karena edisi ini terbatas pada jumlah inti CPU.
Untuk sistem OLAP, algoritme yang dijelaskan di atas tidak sesuai.
Dari pengalaman saya sendiri, saya merekomendasikan algoritme tindakan berikut untuk sistem OLAP untuk menyetel properti paralelisme:

menganalisis permintaan terberat dan memilih jumlah utas yang optimal untuknya
atur Tingkat Paralelisme Maks ke jumlah utas optimal yang dipilih yang diperoleh dari langkah 1, dan untuk basis data tertentu atur nilai DOP Maks yang diperoleh dari langkah 1 untuk setiap basis data
menganalisis kueri terberat dan mengidentifikasi efek negatif dari pembatasan konkurensi. Jika ya, turunkan nilai Ambang Biaya untuk Paralelisme, atau ulangi langkah 1-2 dari algoritme ini

Artinya, untuk sistem OLTP kita beralih dari single-threading ke multi-threading, dan untuk sistem OLAP, sebaliknya, kita beralih dari multi-threading ke single-threading. Dengan demikian, Anda dapat memilih pengaturan paralelisme optimal untuk database tertentu dan seluruh instance MS SQL Server.
Penting juga untuk dipahami bahwa pengaturan properti paralelisme perlu diubah seiring waktu, berdasarkan hasil pemantauan kinerja MS SQL Server.

Pedoman untuk Menetapkan Tanda Jejak

Dari pengalaman saya sendiri dan pengalaman rekan-rekan saya, untuk kinerja yang optimal, saya sarankan untuk menyetel bendera pelacakan berikut pada tingkat proses layanan MS SQL Server untuk versi 2008-2016:

610 - Pengurangan logging sisipan ke dalam tabel yang diindeks. Dapat membantu memasukkan ke dalam tabel dengan banyak catatan dan banyak transaksi, dengan seringnya WRITELOG lama menunggu perubahan dalam indeks
1117 - Jika file dalam filegroup memenuhi persyaratan ambang pertumbuhan otomatis, semua file dalam filegroup tumbuh
1118 - Memaksa semua objek berada di luasan yang berbeda (larangan luasan campuran), yang meminimalkan kebutuhan untuk memindai halaman SGAM, yang digunakan untuk melacak luasan campuran
1224 - Menonaktifkan eskalasi kunci berdasarkan jumlah kunci. Namun, penggunaan memori yang berlebihan dapat memicu eskalasi penguncian
2371 - Mengubah ambang pembaruan statistik otomatis tetap menjadi ambang pembaruan statistik otomatis dinamis. Penting untuk memperbarui rencana kueri untuk tabel besar, di mana hitungan rekaman yang salah menghasilkan rencana eksekusi yang salah
3226 - Menekan pesan keberhasilan pencadangan di log kesalahan
4199 - Menyertakan perubahan pada pengoptimal kueri yang dirilis di CU dan Paket Layanan SQL Server
6532-6534 - Menyertakan peningkatan kinerja untuk operasi kueri pada tipe data spasial
8048 - Mengubah objek memori yang dipartisi NUMA menjadi objek yang dipartisi CPU
8780 - Memungkinkan alokasi waktu tambahan untuk perencanaan kueri. Beberapa permintaan tanpa tanda ini mungkin ditolak karena tidak memiliki rencana kueri (bug yang sangat langka)
8780 - 9389 - Mengaktifkan buffer memori hibah dinamis tambahan untuk pernyataan mode batch, yang memungkinkan operator mode batch untuk meminta memori tambahan dan menghindari pemindahan data ke tempdb jika memori tambahan tersedia

Juga sebelum tahun 2016, akan berguna untuk mengaktifkan bendera pelacakan 2301, yang memungkinkan peningkatan pengoptimalan dukungan keputusan dan dengan demikian membantu dalam memilih rencana kueri yang lebih tepat. Namun, pada versi 2016, ini sering kali berdampak negatif pada waktu eksekusi kueri keseluruhan yang cukup lama.
Selain itu, untuk sistem dengan banyak indeks (misalnya, untuk database 1C), saya sarankan untuk mengaktifkan tanda pelacakan 2330, yang menonaktifkan pengumpulan penggunaan indeks, yang umumnya berdampak positif pada sistem.
Untuk informasi selengkapnya tentang bendera pelacakan, lihat di sini
Dari tautan di atas, penting juga untuk mempertimbangkan versi dan build MS SQL Server, karena untuk versi yang lebih baru, beberapa tanda pelacakan diaktifkan secara default atau tidak berpengaruh.
Anda dapat mengaktifkan dan menonaktifkan bendera pelacakan dengan perintah DBCC TRACEON dan DBCC TRACEOFF. Untuk lebih jelasnya lihat di sini
Anda bisa mendapatkan status bendera pelacakan menggunakan perintah DBCC TRACESTATUS: lebih
Agar bendera pelacakan disertakan dalam autostart layanan MS SQL Server, Anda harus pergi ke Manajer Konfigurasi Server SQL dan menambahkan bendera pelacakan ini melalui -T di properti layanan:

Hasil

Pada artikel ini, beberapa aspek pemantauan MS SQL Server dianalisis, yang dengannya Anda dapat dengan cepat mengidentifikasi kekurangan RAM dan waktu CPU kosong, serta sejumlah masalah lain yang kurang jelas. Bendera pelacakan yang paling umum digunakan telah ditinjau.

Sumber:

» Statistik menunggu SQL Server
» Statistik tunggu SQL Server atau tolong beri tahu saya di mana sakitnya
» Tampilan sistem sys.dm_os_schedulers
» Menggunakan Zabbix untuk Memantau Basis Data MS SQL Server
» Gaya Hidup SQL
» Melacak Bendera
» sql.ru

Sumber: www.habr.com

Beberapa aspek pemantauan MS SQL Server. Pedoman untuk Menetapkan Tanda Jejak