Önsöz
Oldukça sık olarak, MS SQL Server DBMS kullanıcıları, geliştiricileri ve yöneticileri, veritabanının veya bir bütün olarak DBMS'nin performans sorunlarıyla karşılaşır, bu nedenle MS SQL Server izlemesi çok önemlidir.
Bu makale, makaleye bir ektir ve özellikle MS SQL Server'ı izlemenin bazı yönlerini kapsayacaktır: hangi kaynakların eksik olduğunu hızlı bir şekilde belirlemenin yanı sıra izleme bayraklarını ayarlamak için öneriler.
Aşağıdaki betiklerin çalışması için istediğiniz veritabanında aşağıdaki gibi bir inf şeması oluşturmanız gerekir:
inf şeması oluşturma
use <имя_БД>;
go
create schema inf;
RAM eksikliğini tespit etme yöntemi
RAM eksikliğinin ilk göstergesi, bir MS SQL Server örneğinin kendisine tahsis edilen tüm RAM'i tüketmesidir.
Bunu yapmak için, aşağıdaki inf.vRAM gösterimini oluşturacağız:
inf.vRAM görünümünü oluşturma
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;
Ardından, bir MS SQL Server örneğinin kendisine ayrılan tüm belleği aşağıdaki sorguyla tükettiğini belirleyebilirsiniz:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
SQL_server_physical_memory_in_use_Mb sürekli olarak SQL_server_commited_target_Mb'den büyük veya eşitse, bekleme istatistikleri kontrol edilmelidir.
Bekleme istatistikleri aracılığıyla RAM eksikliğini belirlemek için inf.vWaits görünümünü oluşturalım:
inf.vWaits Görünümü Oluşturma
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 durumda, aşağıdaki sorgu ile RAM eksikliğini belirleyebilirsiniz:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Burada Yüzde ve AvgWait_S göstergelerine dikkat etmeniz gerekir. Toplamda önemliyse, MS SQL Server örneği için yeterli RAM bulunmama olasılığı çok yüksektir. Önemli değerler her sistem için ayrı ayrı belirlenir. Ancak, aşağıdakilerle başlayabilirsiniz: Yüzde>=1 ve AvgWait_S>=0.005.
Göstergeleri bir izleme sistemine (örneğin, Zabbix) çıkarmak için aşağıdaki iki sorguyu oluşturabilirsiniz:
- RAM tarafından yüzde olarak kaç bekleme türü kullanılıyor (tüm bu tür bekleme türlerinin toplamı):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - milisaniye cinsinden kaç RAM bekleme türü alır (tüm bu tür bekleme türleri için tüm ortalama gecikmelerin maksimum değeri):
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österge için elde edilen değerlerin dinamiklerine dayanarak, bir MS SQL Server örneği için yeterli RAM olup olmadığı sonucuna varabiliriz.
CPU Aşırı Yük Algılama Yöntemi
İşlemci süresinin eksikliğini belirlemek için sys.dm_os_schedulers sistem görünümünü kullanmak yeterlidir. Burada runnable_tasks_count sürekli olarak 1'den büyükse, MS SQL Server örneği için çekirdek sayısının yeterli olmama olasılığı yüksektir.
Bir izleme sistemine (örneğin, Zabbix) bir gösterge çıktısı almak için aşağıdaki sorguyu oluşturabilirsiniz:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Bu gösterge için elde edilen değerlerin dinamiklerine dayanarak, bir MS SQL Server örneği için yeterli işlemci süresi (CPU çekirdeği sayısı) olup olmadığı sonucuna varabiliriz.
Ancak, isteklerin kendilerinin aynı anda birden fazla iş parçacığı talep edebileceği gerçeğini akılda tutmak önemlidir. Ve bazen optimize edici, sorgunun kendisinin karmaşıklığını doğru bir şekilde tahmin edemez. Daha sonra istek, verilen zamanda aynı anda işlenemeyen çok fazla iş parçacığı tahsis edilebilir. Bu ayrıca, işlemci süresi eksikliği ile ilişkili bir tür beklemeye ve belirli CPU çekirdeklerini kullanan planlayıcılar için kuyruğun büyümesine neden olur, yani bu tür koşullarda runnable_tasks_count göstergesi büyüyecektir.
Bu durumda, CPU çekirdeği sayısını artırmadan önce, MS SQL Server örneğinin paralellik özelliklerini doğru şekilde yapılandırmak ve 2016 sürümünden itibaren gerekli veritabanlarının paralellik özelliklerini doğru şekilde yapılandırmak gerekir:
Burada aşağıdaki parametrelere dikkat etmelisiniz:
- Maksimum Paralellik Derecesi - her isteğe tahsis edilebilecek maksimum iş parçacığı sayısını ayarlar (varsayılan değer 0'dır - yalnızca işletim sisteminin kendisi ve MS SQL Server sürümü ile sınırlıdır)
- Paralellik için Maliyet Eşiği - tahmini paralellik maliyeti (varsayılan 5'tir)
- Maks DOP - veritabanı düzeyinde her bir sorguya tahsis edilebilecek maksimum iş parçacığı sayısını ayarlar (ancak "Maksimum Paralellik Derecesi" özelliğinin değerinden fazla olamaz) (varsayılan 0'dır - yalnızca işletim sisteminin kendisi tarafından sınırlıdır ve MS SQL Server sürümünün yanı sıra tüm MS SQL Server örneğinin "Maksimum Paralellik Derecesi" özelliği üzerindeki kısıtlama)
Burada tüm durumlar için eşit derecede iyi bir tarif vermek imkansızdır, yani ağır sorguları analiz etmeniz gerekir.
Kendi deneyimlerime göre, paralellik özelliklerini ayarlamak için OLTP sistemleri için aşağıdaki eylem algoritmasını öneriyorum:
- önce örnek genelindeki Maksimum Paralellik Derecesini 1 olarak ayarlayarak paralelliği devre dışı bırakın
- en ağır istekleri analiz edin ve onlar için en uygun iş parçacığı sayısını seçin
- Maksimum Paralellik Derecesini 2. adımdan elde edilen seçilen en uygun iş parçacığı sayısına ayarlayın ve belirli veritabanları için her veritabanı için 2. adımdan elde edilen Maks DOP değerini ayarlayın
- en ağır istekleri analiz edin ve çoklu iş parçacığının olumsuz etkisini belirleyin. Öyleyse, Paralellik için Maliyet Eşiğini artırın.
1C, Microsoft CRM ve Microsoft NAV gibi sistemler için çoğu durumda çoklu iş parçacığını yasaklamak uygundur
Ayrıca, bir Standart sürüm varsa, bu sürümün CPU çekirdeği sayısıyla sınırlı olması nedeniyle çoğu durumda çoklu kullanım yasağı uygundur.
OLAP sistemleri için yukarıda açıklanan algoritma uygun değildir.
Kendi deneyimlerime göre, paralellik özelliklerini ayarlamak için OLAP sistemleri için aşağıdaki eylem algoritmasını öneriyorum:
- en ağır istekleri analiz edin ve onlar için en uygun iş parçacığı sayısını seçin
- Maksimum Paralellik Derecesini 1. adımdan elde edilen seçilen en uygun iş parçacığı sayısına ayarlayın ve belirli veritabanları için her veritabanı için 1. adımdan elde edilen Maks DOP değerini ayarlayın
- en ağır sorguları analiz edin ve eşzamanlılığı sınırlamanın olumsuz etkisini belirleyin. Öyleyse, Paralellik için Maliyet Eşiği değerini düşürün veya bu algoritmanın 1-2 adımlarını tekrarlayın
Yani, OLTP sistemleri için tek iş parçacığından çoklu iş parçacığına geçiyoruz ve OLAP sistemleri için bunun tersine, çoklu iş parçacığından tek iş parçacığına geçiyoruz. Böylece, hem belirli bir veritabanı hem de tüm MS SQL Server örneği için en uygun paralellik ayarlarını seçebilirsiniz.
MS SQL Server'ın performansını izlemenin sonuçlarına bağlı olarak, paralellik özelliklerinin ayarlarının zaman içinde değiştirilmesi gerektiğini anlamak da önemlidir.
İzleme İşaretlerini Ayarlama Yönergeleri
Kendi deneyimlerime ve iş arkadaşlarımın deneyimlerine dayanarak, optimum performans için, 2008-2016 sürümleri için MS SQL Server hizmetinin çalışma düzeyinde aşağıdaki izleme bayraklarının ayarlanmasını öneriyorum:
- 610 - İndeksli tablolara girişlerin daha az günlüğe kaydedilmesi. Dizinlerdeki değişiklikler için sık sık uzun WRITELOG beklemeleri ile birçok kayıt ve birçok işlem içeren tablolara eklemelere yardımcı olabilir
- 1117 - Bir dosya grubundaki bir dosya, otomatik büyüme eşiği gereksinimlerini karşılıyorsa, dosya grubundaki tüm dosyalar büyür
- 1118 - Karışık kapsamları izlemek için kullanılan SGAM sayfasının taranması ihtiyacını en aza indiren tüm nesneleri farklı kapsamlarda bulunmaya zorlar (karışık kapsamların yasaklanması)
- 1224 - Kilit sayısına bağlı olarak kilit yükseltmeyi devre dışı bırakır. Ancak, aşırı bellek kullanımı kilit yükseltmesini tetikleyebilir
- 2371 - Sabit otomatik istatistik güncelleme eşiğini dinamik otomatik istatistik güncelleme eşiğine değiştirir. Yanlış kayıt sayısının hatalı yürütme planlarına neden olduğu büyük tablolar için sorgu planlarını güncellemek için önemlidir.
- 3226 - Hata günlüğündeki yedekleme başarı mesajlarını gizler
- 4199 - CU'larda ve SQL Server Hizmet Paketlerinde yayınlanan sorgu iyileştiricide yapılan değişiklikleri içerir
- 6532-6534 - Uzamsal veri türlerinde sorgulama işlemleri için performans iyileştirmeleri içerir
- 8048 - NUMA bölümlenmiş bellek nesnelerini CPU bölümlenmiş olanlara dönüştürür
- 8780 - Sorgu planlaması için ek zaman ayırmayı etkinleştirir. Bu işarete sahip olmayan bazı istekler, bir sorgu planı olmadığı için reddedilebilir (çok nadir hata)
- 8780 - 9389 - Toplu iş modu operatörünün daha fazla bellek talep etmesine ve daha fazla bellek varsa verileri tempdb'ye taşımaktan kaçınmasına izin veren toplu iş modu ifadeleri için ek dinamik tahsisli bellek arabelleğini etkinleştirir
Ayrıca 2016'dan önce, gelişmiş karar desteği optimizasyonları sağlayan ve böylece daha doğru sorgu planlarının seçilmesine yardımcı olan izleme bayrağı 2301'in etkinleştirilmesi yararlıdır. Ancak, 2016 sürümünden itibaren, genellikle oldukça uzun genel sorgu yürütme süreleri üzerinde olumsuz bir etkiye sahiptir.
Ayrıca, çok fazla dizine sahip sistemler için (örneğin, 1C veritabanları için), genellikle sistem üzerinde olumlu bir etkisi olan dizin kullanımının toplanmasını devre dışı bırakan izleme bayrağı 2330'u etkinleştirmenizi öneririm.
İzleme bayrakları hakkında daha fazla bilgi için bkz.
Yukarıdaki bağlantıdan, MS SQL Server'ın sürümlerini ve yapılarını dikkate almak da önemlidir, çünkü daha yeni sürümler için bazı izleme bayrakları varsayılan olarak etkindir veya etkisizdir.
İzleme bayrağını sırasıyla DBCC TRACEON ve DBCC TRACEOFF komutları ile açıp kapatabilirsiniz. Daha fazla ayrıntı için bkz.
DBCC TRACESTATUS komutunu kullanarak izleme bayraklarının durumunu alabilirsiniz:
İzleme bayraklarının MS SQL Server hizmetinin otomatik başlatılmasına dahil edilmesi için, SQL Server Yapılandırma Yöneticisine gitmeli ve bu izleme bayraklarını -T aracılığıyla hizmet özelliklerinde eklemelisiniz:
sonuçlar
Bu makalede, MS SQL Server'ı izlemenin bazı yönleri analiz edildi; bu sayede, RAM eksikliğini ve boş CPU zamanını ve ayrıca daha az belirgin olan diğer sorunları hızlı bir şekilde tanımlayabilirsiniz. En sık kullanılan izleme bayrakları gözden geçirildi.
Kaynaklar:
»
»
»
»
»
»
»
Kaynak: habr.com