Müqəddimə
Çox vaxt MS SQL Server DBMS-nin istifadəçiləri, tərtibatçıları və idarəçiləri verilənlər bazasının və ya bütövlükdə DBMS-nin performans problemləri ilə qarşılaşırlar, buna görə də MS SQL Server monitorinqi çox aktualdır.
Bu məqalə məqaləyə əlavədir və o, MS SQL Serverin monitorinqinin bəzi aspektlərini əhatə edəcək, xüsusən: hansı resursların çatışmadığını tez bir zamanda necə müəyyənləşdirmək, eləcə də iz bayraqlarının qurulması üçün tövsiyələr.
Aşağıdakı skriptlərin işləməsi üçün istədiyiniz verilənlər bazasında aşağıdakı kimi inf sxemi yaratmalısınız:
İnf sxeminin yaradılması
use <имя_БД>;
go
create schema inf;
RAM çatışmazlığını aşkar etmək üsulu
RAM çatışmazlığının ilk göstəricisi MS SQL Server nümunəsinin ona ayrılmış bütün RAM-ı yeməsi halıdır.
Bunun üçün inf.vRAM-ın aşağıdakı təsvirini yaradacağıq:
inf.vRAM görünüşünün yaradılması
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;
Sonra MS SQL Server nümunəsinin ona ayrılmış bütün yaddaşı aşağıdakı sorğu ilə istehlak etdiyini müəyyən edə bilərsiniz:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Əgər SQL_server_physical_memory_in_use_Mb ardıcıl olaraq SQL_server_committed_target_Mb-dən böyük və ya bərabərdirsə, gözləmə statistikası yoxlanılmalıdır.
Gözləmə statistikası vasitəsilə RAM çatışmazlığını müəyyən etmək üçün inf.vWaits görünüşünü yaradaq:
inf.vWaits Görünüşünün yaradılması
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];
Bu halda, RAM çatışmazlığını aşağıdakı sorğu ilə müəyyən edə bilərsiniz:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Burada Faiz və AvgWait_S göstəricilərinə diqqət yetirmək lazımdır. Əgər onlar cəmində əhəmiyyətlidirlərsə, MS SQL Server nümunəsi üçün kifayət qədər RAM olma ehtimalı çox yüksəkdir. Əhəmiyyətli dəyərlər hər bir sistem üçün fərdi olaraq müəyyən edilir. Bununla belə, siz aşağıdakılarla başlaya bilərsiniz: Faiz>=1 və AvgWait_S>=0.005.
Monitorinq sisteminə (məsələn, Zabbix) göstəriciləri çıxarmaq üçün aşağıdakı iki sorğu yarada bilərsiniz:
- Faizlə RAM neçə növ gözləməni tutur (bütün bu cür gözləmələrin cəmi):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - millisaniyədə neçə RAM gözləmə növü (bütün belə gözləmə növləri üçün bütün orta gecikmələrin maksimum dəyəri):
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' );
Bu iki göstərici üçün əldə edilən dəyərlərin dinamikasına əsaslanaraq, MS SQL Server nümunəsi üçün kifayət qədər operativ yaddaşın olub olmadığı qənaətinə gələ bilərik.
CPU Həddindən artıq yüklənməsinin aşkarlanması metodu
Prosessor vaxtının çatışmazlığını müəyyən etmək üçün sys.dm_os_schedulers sistem görünüşündən istifadə etmək kifayətdir. Burada, əgər runnable_tasks_count daim 1-dən böyükdürsə, o zaman nüvələrin sayının MS SQL Server nümunəsi üçün kifayət etməməsi ehtimalı yüksəkdir.
Monitorinq sisteminə bir göstərici çıxarmaq üçün (məsələn, Zabbix) aşağıdakı sorğu yarada bilərsiniz:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Bu göstərici üçün əldə edilən dəyərlərin dinamikasına əsaslanaraq, MS SQL Server nümunəsi üçün kifayət qədər prosessor vaxtının (CPU nüvələrinin sayı) olub-olmaması barədə nəticə çıxara bilərik.
Bununla belə, sorğuların özləri eyni anda bir neçə mövzu tələb edə biləcəyini nəzərə almaq vacibdir. Və bəzən optimallaşdırıcı sorğunun özünün mürəkkəbliyini düzgün qiymətləndirə bilmir. Sonra sorğuya eyni vaxtda eyni vaxtda emal edilə bilməyən çoxlu mövzular ayrıla bilər. Və bu da prosessor vaxtının çatışmazlığı ilə əlaqəli bir növ gözləməyə səbəb olur və xüsusi CPU nüvələrindən istifadə edən planlaşdırıcılar üçün növbənin artmasına səbəb olur, yəni belə şəraitdə runnable_tasks_count göstəricisi artacaq.
Bu halda, CPU nüvələrinin sayını artırmazdan əvvəl, MS SQL Server nümunəsinin özünün paralellik xassələrini düzgün konfiqurasiya etmək və 2016 versiyasından tələb olunan verilənlər bazalarının paralellik xüsusiyyətlərini düzgün konfiqurasiya etmək lazımdır:
Burada aşağıdakı parametrlərə diqqət yetirməlisiniz:
- Maksimum Paralellik Dərəcəsi - hər bir sorğuya ayrıla bilən mövzuların maksimum sayını təyin edir (standart 0-dır - yalnız əməliyyat sisteminin özü və MS SQL Server nəşri ilə məhdudlaşır)
- Paralellik üçün xərc həddi - paralelliyin təxmini dəyəri (defolt olaraq 5-dir)
- Max DOP - verilənlər bazası səviyyəsində hər bir sorğuya ayrıla bilən mövzuların maksimum sayını təyin edir (lakin "Maks. Paralellik dərəcəsi" xüsusiyyətinin dəyərindən çox deyil) (defolt 0-dır - yalnız əməliyyat sisteminin özü və MS SQL Server nəşri, eləcə də MS SQL Serverin bütün nümunəsinin "Maksimum Paralellik Dərəcəsi" xassəsinə məhdudiyyət)
Burada bütün hallar üçün eyni dərəcədə yaxşı bir resept vermək mümkün deyil, yəni ağır sorğuları təhlil etməlisiniz.
Öz təcrübəmdən paralellik xüsusiyyətlərini qurmaq üçün OLTP sistemləri üçün aşağıdakı hərəkət alqoritmini tövsiyə edirəm:
- ilk olaraq misal üzrə Maks Paralellik Dərəcəsini 1-ə təyin etməklə paralelliyi söndürün
- ən ağır sorğuları təhlil edin və onlar üçün optimal iplik sayını seçin
- Paralelliyin Maks dərəcəsini 2-ci addımdan əldə edilən seçilmiş optimal iplik sayına təyin edin və xüsusi verilənlər bazaları üçün hər bir verilənlər bazası üçün 2-ci addımdan əldə edilən Maks DOP dəyərini təyin edin
- ən ağır sorğuları təhlil edin və multithreading mənfi təsirini müəyyən edin. Əgər belədirsə, Paralellik üçün Xərc Həddini artırın.
1C, Microsoft CRM və Microsoft NAV kimi sistemlər üçün əksər hallarda çox iş parçacığının qadağan edilməsi uyğun gəlir.
Həmçinin, Standart bir nəşr varsa, əksər hallarda bu nəşrin CPU nüvələrinin sayında məhdud olduğuna görə çox iş parçacığının qadağan edilməsi uyğun gəlir.
OLAP sistemləri üçün yuxarıda təsvir edilən alqoritm uyğun deyil.
Öz təcrübəmdən paralellik xüsusiyyətlərini qurmaq üçün OLAP sistemləri üçün aşağıdakı hərəkət alqoritmini tövsiyə edirəm:
- ən ağır sorğuları təhlil edin və onlar üçün optimal iplik sayını seçin
- Paralelliyin Maks dərəcəsini 1-ci addımdan əldə edilən seçilmiş optimal iplik sayına təyin edin və xüsusi verilənlər bazaları üçün hər bir verilənlər bazası üçün 1-ci addımdan əldə edilən Maks DOP dəyərini təyin edin
- ən ağır sorğuları təhlil edin və paralelliyin məhdudlaşdırılmasının mənfi təsirini müəyyənləşdirin. Əgər belədirsə, ya Paralellik dəyəri üçün Xərc həddini aşağı salın, ya da bu alqoritmin 1-2 addımlarını təkrarlayın.
Yəni, OLTP sistemləri üçün biz tək yivlilikdən çox yivliliyə, OLAP-sistemləri üçün isə əksinə, çox iş parçacığından tək yivliliyə keçirik. Beləliklə, həm konkret verilənlər bazası, həm də MS SQL Serverin bütün nümunəsi üçün optimal paralellik parametrlərini seçə bilərsiniz.
Həmçinin başa düşmək lazımdır ki, MS SQL Serverin işinə nəzarətin nəticələrinə əsasən paralellik xassələrinin parametrləri zamanla dəyişdirilməlidir.
İz bayraqlarının qurulması üçün təlimatlar
Öz təcrübəmdən və həmkarlarımın təcrübəsindən optimal performans üçün 2008-2016 versiyaları üçün MS SQL Server xidmətinin işləmə səviyyəsində aşağıdakı iz bayraqlarını qoymağı tövsiyə edirəm:
- 610 - İndekslənmiş cədvəllərə əlavələrin qeydinin azaldılması. Çoxlu qeydlər və çoxlu əməliyyatlar olan cədvəllərə əlavələr etməkdə kömək edə bilər, tez-tez uzun WRITELOG indekslərdə dəyişiklikləri gözləyir.
- 1117 - Əgər fayl qrupundakı fayl avtomatik artım həddi tələblərinə cavab verirsə, fayl qrupundakı bütün fayllar böyüyür
- 1118 - Bütün obyektləri müxtəlif ölçülərdə yerləşdirməyə məcbur edir (qarışıq genişliklərin qadağan edilməsi), bu, qarışıq genişlikləri izləmək üçün istifadə edilən SGAM səhifəsini skan etmək ehtiyacını minimuma endirir.
- 1224 - Kilidlərin sayına əsaslanaraq kilid eskalasiyasını söndürür. Bununla belə, həddindən artıq yaddaş istifadəsi kilidin artmasına səbəb ola bilər
- 2371 - Sabit avtomatik statistika yeniləmə həddini dinamik avtomatik statistika yeniləmə həddinə dəyişir. Qeydlərin səhv sayının səhv icra planları ilə nəticələndiyi böyük cədvəllər üçün sorğu planlarını yeniləmək üçün vacibdir.
- 3226 - Səhv jurnalında ehtiyat nüsxənin müvəffəqiyyəti mesajlarını gizlədir
- 4199 - CU-larda və SQL Server Xidmət Paketlərində buraxılmış sorğu optimallaşdırıcısına dəyişikliklər daxildir
- 6532-6534 - Məkan məlumat növləri üzrə sorğu əməliyyatları üçün performans təkmilləşdirmələri daxildir
- 8048 - NUMA hissələrə bölünmüş yaddaş obyektlərini CPU bölünmüş obyektlərə çevirir
- 8780 - Sorğunun planlaşdırılması üçün əlavə vaxtın ayrılmasını aktivləşdirir. Bu bayraq olmayan bəzi sorğular sorğu planı olmadığı üçün rədd edilə bilər (çox nadir səhv)
- 8780 - 9389 - Toplu rejim bəyanatları üçün əlavə dinamik qrant yaddaş buferini işə salır, bu, toplu rejim operatoruna əlavə yaddaş tələb etməyə və əlavə yaddaş mövcud olduqda məlumatı tempdb-yə köçürməkdən çəkinməyə imkan verir.
Həmçinin 2016-cı ildən əvvəl 2301 izləmə bayrağını aktivləşdirmək faydalıdır ki, bu da qərar dəstəyinin təkmilləşdirilmiş optimallaşdırılmasına imkan verir və bununla da daha düzgün sorğu planlarının seçilməsinə kömək edir. Bununla belə, 2016-cı il versiyasına görə, bu, çox vaxt kifayət qədər uzun ümumi sorğunun icra müddətinə mənfi təsir göstərir.
Həmçinin, çoxlu indeksləri olan sistemlər üçün (məsələn, 1C verilənlər bazası üçün) indeks istifadəsinin toplanmasına qadağa qoyan 2330 iz bayrağını aktiv etməyi məsləhət görürəm ki, bu da ümumiyyətlə sistemə müsbət təsir göstərir.
İz bayraqları haqqında ətraflı məlumat üçün baxın
Yuxarıdakı linkdən MS SQL Serverin versiyalarını və quruluşlarını nəzərə almaq da vacibdir, çünki daha yeni versiyalarda bəzi iz bayraqları defolt olaraq aktivdir və ya heç bir təsiri yoxdur.
Siz müvafiq olaraq DBCC TRACEON və DBCC TRACEOFF əmrləri ilə izləmə bayrağını yandırıb söndürə bilərsiniz. Ətraflı məlumat üçün baxın
DBCC TRACESTATUS əmrindən istifadə edərək iz bayraqlarının statusunu əldə edə bilərsiniz:
İz bayraqlarının MS SQL Server xidmətinin avtostartına daxil edilməsi üçün siz SQL Server Konfiqurasiya Menecerinə daxil olmalı və xidmət xüsusiyyətlərinə -T vasitəsilə bu iz bayraqlarını əlavə etməlisiniz:
Nəticələri
Bu məqalədə MS SQL Server monitorinqinin bəzi aspektləri təhlil edilmişdir, onların köməyi ilə siz operativ yaddaşın çatışmazlığını və boş CPU vaxtını, həmçinin bir sıra digər daha az aşkar problemləri tez bir zamanda müəyyən edə bilərsiniz. Ən çox istifadə olunan iz bayraqları nəzərdən keçirilmişdir.
Mənbə:
»
»
»
»
»
»
»
Mənbə: www.habr.com