🥇Beberapa aspek pemantauan MS SQL Server. Pengesyoran untuk menetapkan bendera surih

Perutusan

Selalunya, pengguna, pembangun dan pentadbir MS SQL Server DBMS berhadapan dengan masalah prestasi pangkalan data atau DBMS secara keseluruhan, jadi pemantauan MS SQL Server adalah sangat relevan.
Artikel ini adalah tambahan kepada artikel Menggunakan Zabbix untuk memantau pangkalan data MS SQL Server dan ia akan meneliti beberapa aspek pemantauan MS SQL Server, khususnya: cara cepat menentukan sumber yang tiada, serta cadangan untuk menyediakan bendera surih.
Untuk skrip berikut berfungsi, anda perlu mencipta skema inf dalam pangkalan data yang dikehendaki seperti berikut:
Mencipta skema inf

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

Kaedah untuk mengesan kekurangan RAM

Penunjuk pertama kekurangan RAM ialah apabila contoh MS SQL Server memakan semua RAM yang diperuntukkan kepadanya.
Untuk melakukan ini, buat perwakilan inf.vRAM berikut:
Mencipta paparan 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 boleh menentukan bahawa contoh MS SQL Server menggunakan semua memori yang diperuntukkan kepadanya menggunakan pertanyaan berikut:

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

Jika penunjuk SQL_server_physical_memory_in_use_Mb sentiasa tidak kurang daripada SQL_server_committed_target_Mb, maka anda perlu menyemak statistik tunggu.
Untuk menentukan kekurangan RAM melalui statistik tunggu, mari buat paparan inf.vWaits:
Mencipta paparan 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 kes ini, anda boleh menentukan kekurangan RAM menggunakan pertanyaan berikut:

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

Di sini anda perlu memberi perhatian kepada penunjuk Peratusan dan AvgWait_S. Jika ia penting dalam keseluruhannya, maka terdapat kebarangkalian yang sangat tinggi bahawa contoh MS SQL Server tidak mempunyai RAM yang mencukupi. Nilai penting ditentukan secara individu untuk setiap sistem. Walau bagaimanapun, anda boleh mulakan dengan penunjuk berikut: Peratusan>=1 dan AvgWait_S>=0.005.
Untuk mengeluarkan penunjuk kepada sistem pemantauan (contohnya, Zabbix), anda boleh membuat dua pertanyaan berikut:

Berapakah peratusan jenis menunggu untuk RAM (jumlah untuk semua jenis menunggu sedemikian):

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 menunggu RAM yang diambil dalam milisaat (nilai maksimum semua kelewatan purata untuk semua jenis menunggu 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 dinamik nilai yang diperolehi untuk kedua-dua penunjuk ini, kita boleh membuat kesimpulan sama ada terdapat RAM yang mencukupi untuk contoh MS SQL Server.

Kaedah untuk mengesan beban CPU yang berlebihan

Untuk mengenal pasti kekurangan masa CPU, hanya gunakan pandangan sistem sys.dm_os_schedulers. Di sini, jika penunjuk runnable_tasks_count sentiasa lebih besar daripada 1, maka terdapat kebarangkalian tinggi bahawa bilangan teras tidak mencukupi untuk contoh MS SQL Server.
Untuk memaparkan penunjuk dalam sistem pemantauan (contohnya, Zabbix), anda boleh membuat permintaan berikut:

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

Berdasarkan dinamik nilai yang diperolehi untuk penunjuk ini, kita boleh membuat kesimpulan sama ada terdapat masa pemproses yang mencukupi (bilangan teras CPU) untuk contoh MS SQL Server.
Walau bagaimanapun, adalah penting untuk mengingati fakta bahawa pertanyaan itu sendiri boleh menanyakan berbilang urutan sekaligus. Dan kadangkala pengoptimum tidak dapat menganggarkan dengan betul kerumitan pertanyaan itu sendiri. Kemudian permintaan itu mungkin diperuntukkan terlalu banyak utas, yang pada masa tertentu tidak dapat diproses secara serentak. Dan ini juga menyebabkan jenis menunggu yang dikaitkan dengan kekurangan masa pemproses, dan pertumbuhan baris gilir untuk penjadual yang menggunakan teras CPU tertentu, iaitu, penunjuk runnable_tasks_count akan meningkat dalam keadaan sedemikian.
Dalam kes ini, sebelum menambah bilangan teras CPU, anda perlu mengkonfigurasi sifat selari bagi contoh MS SQL Server itu sendiri dengan betul, dan dari versi 2016, mengkonfigurasi sifat selari pangkalan data yang dikehendaki dengan betul:

Di sini anda harus memberi perhatian kepada parameter berikut:

Tahap Keselarian Maks—menetapkan bilangan maksimum utas yang boleh diperuntukkan kepada setiap permintaan (lalai ialah 0—hanya dihadkan oleh sistem pengendalian itu sendiri dan edisi MS SQL Server)
Ambang Kos untuk Keselarian - anggaran kos keselarian (lalai ialah 5)
DOP Maks—menetapkan bilangan maksimum utas yang boleh diperuntukkan kepada setiap pertanyaan pada peringkat pangkalan data (tetapi tidak lebih daripada nilai sifat “Darah Keselarian Maks”) (secara lalai ia adalah 0—hanya terhad oleh sistem pengendalian sendiri dan edisi MS SQL Server, serta pengehadan pada sifat "Max Degree of Parallelism" bagi keseluruhan contoh MS SQL Server)

Tidak mustahil untuk memberikan resipi yang sama baik untuk semua kes, iaitu, anda perlu menganalisis pertanyaan yang sukar.
Berdasarkan pengalaman saya sendiri, saya mengesyorkan algoritma tindakan berikut untuk sistem OLTP untuk mengkonfigurasi sifat selari:

lumpuhkan paralelisme terlebih dahulu dengan menetapkan Darjah Keselarian Maks kepada 1 pada tahap keseluruhan kejadian
menganalisis pertanyaan yang paling berat dan pilih bilangan utas yang optimum untuknya
tetapkan Darjah Keselarian Maks kepada bilangan utas optimum terpilih yang diperoleh daripada langkah 2, dan juga untuk pangkalan data tertentu tetapkan nilai DOP Maks yang diperoleh daripada langkah 2 untuk setiap pangkalan data
menganalisis pertanyaan yang paling berat dan mengenal pasti kesan negatif multithreading. Jika ya, maka tingkatkan Ambang Kos untuk Paralelisme.
Untuk sistem seperti 1C, Microsoft CRM dan Microsoft NAV, dalam kebanyakan kes melarang multithreading adalah sesuai

Selain itu, jika anda mempunyai edisi Standard, maka dalam kebanyakan kes larangan pada multi-threading adalah sesuai kerana fakta bahawa edisi ini terhad dalam bilangan teras CPU.
Algoritma yang diterangkan di atas tidak sesuai untuk sistem OLAP.
Berdasarkan pengalaman saya sendiri, saya mengesyorkan algoritma tindakan berikut untuk sistem OLAP untuk mengkonfigurasi sifat selari:

menganalisis pertanyaan yang paling berat dan pilih bilangan utas yang optimum untuknya
tetapkan Darjah Keselarian Maks kepada bilangan utas optimum terpilih yang diperoleh daripada langkah 1, dan juga untuk pangkalan data tertentu tetapkan nilai DOP Maks yang diperoleh daripada langkah 1 untuk setiap pangkalan data
menganalisis pertanyaan yang paling berat dan mengenal pasti kesan negatif mengehadkan konkurensi. Jika ya, maka sama ada menurunkan Ambang Kos untuk nilai Paralelisme, atau ulangi langkah 1-2 algoritma ini

Iaitu, untuk sistem OLTP kita beralih daripada satu-benang kepada berbilang-benang, dan untuk sistem OLAP, sebaliknya, kita pergi daripada berbilang-benang kepada satu-benang. Dengan cara ini anda boleh memilih tetapan selari optimum untuk pangkalan data tertentu dan untuk keseluruhan contoh MS SQL Server.
Ia juga penting untuk memahami bahawa tetapan sifat konkurensi perlu diubah dari semasa ke semasa, berdasarkan hasil pemantauan prestasi MS SQL Server.

Pengesyoran untuk menetapkan bendera surih

Daripada pengalaman saya sendiri dan pengalaman rakan sekerja saya, untuk prestasi optimum, saya mengesyorkan menetapkan bendera surih berikut pada tahap larian perkhidmatan MS SQL Server untuk versi 2008-2016:

610 - Kurangkan pengelogan sisipan ke dalam jadual yang diindeks. Boleh membantu dengan sisipan ke dalam jadual dengan sejumlah besar rekod dan banyak transaksi, dengan WRITELOG yang kerap menunggu perubahan dalam indeks
1117 - Jika fail dalam kumpulan fail memenuhi ambang pertumbuhan automatik, semua fail dalam kumpulan fail akan dibesarkan
1118 - Memaksa semua objek untuk ditempatkan dalam takat yang berbeza (tidak membenarkan takat bercampur), yang meminimumkan keperluan untuk mengimbas halaman SGAM, yang digunakan untuk menjejak takat bercampur
1224 - Menyahdayakan peningkatan kunci berdasarkan kiraan kunci. Walau bagaimanapun, penggunaan memori yang berlebihan boleh mendayakan peningkatan kunci
2371 - Menukar ambang kemas kini statistik automatik tetap kepada ambang kemas kini statistik automatik dinamik. Penting untuk mengemas kini pelan pertanyaan pada jadual besar yang salah mentakrifkan bilangan rekod mengakibatkan rancangan pelaksanaan yang salah
3226 - Menindas mesej kejayaan sandaran dalam log ralat
4199 - Termasuk perubahan pada pengoptimum pertanyaan yang dikeluarkan dalam rollup kemas kini SQL Server dan pek perkhidmatan
6532-6534 - Termasuk peningkatan prestasi untuk pertanyaan dengan jenis data spatial
8048 - Menukar objek memori berpartition NUMA kepada objek berpartition CPU
8780 - Membolehkan peruntukan masa tambahan untuk perancangan pertanyaan. Sesetengah permintaan tanpa bendera ini mungkin ditolak kerana mereka tidak mempunyai rancangan pertanyaan (ralat yang sangat jarang berlaku)
8780 - 9389 - Mendayakan penimbal memori sementara dinamik tambahan untuk pengendali mod kelompok, membenarkan pengendali mod kelompok meminta memori tambahan dan mengelakkan pemindahan data ke tempdb jika memori tambahan tersedia

Ia juga berguna untuk mendayakan bendera surih 2016 sebelum versi 2301, yang membolehkan pengoptimuman sokongan keputusan lanjutan dan dengan itu membantu dalam memilih pelan pertanyaan yang lebih baik. Walau bagaimanapun, sejak versi 2016, ia sering memberi kesan negatif pada masa pelaksanaan pertanyaan keseluruhan yang agak panjang.
Selain itu, untuk sistem yang mempunyai banyak indeks (contohnya, untuk pangkalan data 1C), saya mengesyorkan mendayakan bendera surih 2330, yang melumpuhkan pengumpulan penggunaan indeks, yang secara amnya mempunyai kesan positif pada sistem.
Anda boleh mengetahui lebih lanjut tentang bendera surih di sini
Daripada pautan di atas, adalah penting juga untuk mempertimbangkan versi dan binaan MS SQL Server, seperti untuk versi yang lebih baharu, beberapa bendera surih didayakan secara lalai atau tiada kesan.
Anda boleh mendayakan atau melumpuhkan bendera surih menggunakan perintah DBCC TRACEON dan DBCC TRACEOFF, masing-masing. Lihat butiran lanjut di sini
Anda boleh mendapatkan status bendera surih menggunakan arahan DBCC TRACESTATUS: lebih
Agar bendera surih disertakan dalam autostart perkhidmatan MS SQL Server, anda perlu pergi ke Pengurus Konfigurasi Pelayan SQL dan tambahkan bendera surih ini melalui -T dalam sifat perkhidmatan:

Keputusan

Artikel ini mengkaji beberapa aspek pemantauan MS SQL Server, dengan bantuannya anda boleh mengenal pasti kekurangan RAM dan masa CPU percuma dengan cepat, serta beberapa masalah lain yang kurang jelas. Bendera surih yang paling biasa digunakan telah disemak.

Sumber:

» Statistik Tunggu Pelayan SQL
» Statistik tunggu SQL Server atau sila beritahu saya di mana ia menyakitkan
» Paparan sistem sys.dm_os_schedulers
» Menggunakan Zabbix untuk memantau pangkalan data MS SQL Server
» Gaya Hidup SQL
» Bendera jejak
» sql.ru

Sumber: www.habr.com

Beberapa aspek pemantauan MS SQL Server. Pengesyoran untuk menetapkan bendera surih