Przedmowa
Dość często użytkownicy, programiści i administratorzy MS SQL Server DBMS napotykają problemy z wydajnością bazy danych lub DBMS jako całości, dlatego monitorowanie MS SQL Server jest bardzo istotne.
Ten artykuł jest dodatkiem do artykułu i obejmie niektóre aspekty monitorowania MS SQL Server, w szczególności: jak szybko określić, których zasobów brakuje, a także zalecenia dotyczące ustawiania flag śledzenia.
Aby poniższe skrypty działały, musisz utworzyć schemat inf w żądanej bazie danych w następujący sposób:
Tworzenie schematu inf
use <имя_БД>;
go
create schema inf;
Metoda wykrywania braku pamięci RAM
Pierwszym wskaźnikiem braku pamięci RAM jest przypadek, gdy instancja MS SQL Server zjada całą przydzieloną jej pamięć RAM.
Aby to zrobić, utworzymy następującą reprezentację inf.vRAM:
Tworzenie widoku 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;
Następnie możesz określić, że instancja MS SQL Server zużywa całą przydzieloną jej pamięć za pomocą następującego zapytania:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Jeśli SQL_server_physical_memory_in_use_Mb jest stale większy lub równy SQL_server_committed_target_Mb, należy sprawdzić statystyki oczekiwania.
Aby określić brak pamięci RAM za pomocą statystyk oczekiwania, utwórzmy widok inf.vWaits:
Tworzenie widoku 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];
W takim przypadku możesz określić brak pamięci RAM za pomocą następującego zapytania:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Tutaj musisz zwrócić uwagę na wskaźniki Procent i AvgWait_S. Jeśli są one znaczące w całości, to istnieje bardzo duże prawdopodobieństwo, że nie ma wystarczającej ilości pamięci RAM dla instancji MS SQL Server. Istotne wartości są ustalane indywidualnie dla każdego systemu. Możesz jednak zacząć od następujących wartości: Procent>=1 i AvgWait_S>=0.005.
Aby wyprowadzić wskaźniki do systemu monitorowania (na przykład Zabbix), możesz utworzyć następujące dwa zapytania:
- ile typów waitów zajmuje pamięć RAM w procentach (suma wszystkich typów waitów):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - ile typów oczekiwania RAM trwa w milisekundach (maksymalna wartość wszystkich średnich opóźnień dla wszystkich takich typów oczekiwania):
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' );
Na podstawie dynamiki uzyskanych wartości tych dwóch wskaźników możemy stwierdzić, czy jest wystarczająca ilość pamięci RAM dla instancji MS SQL Server.
Metoda wykrywania przeciążenia procesora
Aby zidentyfikować brak czasu procesora wystarczy skorzystać z widoku systemowego sys.dm_os_schedulers. Tutaj, jeśli runnable_tasks_count jest stale większe niż 1, istnieje duże prawdopodobieństwo, że liczba rdzeni nie jest wystarczająca dla instancji MS SQL Server.
Aby wyprowadzić wskaźnik do systemu monitorowania (na przykład Zabbix), możesz utworzyć następujące zapytanie:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Na podstawie dynamiki uzyskanych wartości tego wskaźnika możemy stwierdzić, czy jest wystarczająca ilość czasu procesora (liczba rdzeni procesora) dla instancji MS SQL Server.
Należy jednak pamiętać, że same żądania mogą żądać wielu wątków jednocześnie. Czasami optymalizator nie może poprawnie oszacować złożoności samego zapytania. Wtedy żądaniu może zostać przydzielonych zbyt wiele wątków, które nie mogą być przetwarzane jednocześnie w danym czasie. A to też powoduje rodzaj oczekiwania związanego z brakiem czasu procesora i wzrostem kolejki dla harmonogramów wykorzystujących określone rdzenie procesora, czyli wskaźnik runnable_tasks_count będzie rósł w takich warunkach.
W takim przypadku przed zwiększeniem liczby rdzeni procesora należy poprawnie skonfigurować właściwości równoległości samej instancji MS SQL Server, a od wersji 2016 poprawnie skonfigurować właściwości równoległości wymaganych baz danych:
Tutaj należy zwrócić uwagę na następujące parametry:
- Max Degree of Parallelism - ustawia maksymalną liczbę wątków, które można przydzielić do każdego żądania (domyślnie 0 - ograniczone tylko przez sam system operacyjny i edycję MS SQL Server)
- Próg kosztu równoległości — szacowany koszt równoległości (wartość domyślna to 5)
- Max DOP - ustawia maksymalną liczbę wątków, które można przydzielić do każdego zapytania na poziomie bazy danych (ale nie więcej niż wartość właściwości "Max Degree of Parallelism") (domyślnie 0 - ograniczone tylko przez sam system operacyjny i edycji MS SQL Server, a także ograniczenie właściwości „Max Degree of Parallelism” całej instancji MS SQL Server)
Tutaj nie da się podać równie dobrego przepisu na wszystkie przypadki, czyli trzeba przeanalizować ciężkie zapytania.
Z własnego doświadczenia polecam następujący algorytm działań dla systemów OLTP do ustawiania właściwości równoległości:
- najpierw wyłącz równoległość, ustawiając maksymalny stopień równoległości całej instancji na 1
- przeanalizuj najcięższe żądania i wybierz dla nich optymalną liczbę wątków
- ustaw Max Degree of Parallelism na wybraną optymalną liczbę wątków uzyskaną z kroku 2, a dla określonych baz danych ustaw wartość Max DOP uzyskaną z kroku 2 dla każdej bazy danych
- analizuj najcięższe żądania i identyfikuj negatywne skutki wielowątkowości. Jeśli tak, zwiększ próg kosztu dla równoległości.
W przypadku systemów takich jak 1C, Microsoft CRM i Microsoft NAV w większości przypadków odpowiednie jest zakazanie wielowątkowości
Ponadto, jeśli istnieje edycja Standard, w większości przypadków zakaz wielowątkowości jest odpowiedni ze względu na fakt, że ta edycja jest ograniczona liczbą rdzeni procesora.
W przypadku systemów OLAP opisany powyżej algorytm nie jest odpowiedni.
Z własnego doświadczenia polecam następujący algorytm działań dla systemów OLAP do ustawiania właściwości równoległości:
- przeanalizuj najcięższe żądania i wybierz dla nich optymalną liczbę wątków
- ustaw Max Degree of Parallelism na wybraną optymalną liczbę wątków uzyskaną z kroku 1, a dla określonych baz danych ustaw wartość Max DOP uzyskaną z kroku 1 dla każdej bazy danych
- przeanalizuj najcięższe zapytania i zidentyfikuj negatywny efekt ograniczenia współbieżności. Jeśli tak, to albo obniż wartość Próg kosztu dla równoległości, albo powtórz kroki 1-2 tego algorytmu
Oznacza to, że w przypadku systemów OLTP przechodzimy od jednowątkowego do wielowątkowego, aw przypadku systemów OLAP, przeciwnie, przechodzimy od wielowątkowego do jednowątkowego. Dzięki temu można wybrać optymalne ustawienia równoległości zarówno dla konkretnej bazy danych, jak i całej instancji MS SQL Server.
Ważne jest również, aby zrozumieć, że ustawienia właściwości równoległości muszą być zmieniane w czasie, w oparciu o wyniki monitorowania wydajności MS SQL Server.
Wytyczne dotyczące ustawiania flag śledzenia
Z własnego doświadczenia i doświadczenia moich kolegów, dla optymalnej wydajności, zalecam ustawienie następujących flag śledzenia na poziomie uruchamiania usługi MS SQL Server dla wersji 2008-2016:
- 610 — Zredukowano rejestrowanie wstawek w tabelach indeksowanych. Może pomóc przy wstawianiu do tabel z wieloma rekordami i wieloma transakcjami, przy częstych długich oczekiwaniach WRITELOG na zmiany w indeksach
- 1117 — Jeśli plik w grupie plików spełnia wymagania dotyczące progu automatycznego wzrostu, wszystkie pliki w grupie plików rosną
- 1118 — Wymusza umieszczenie wszystkich obiektów w różnych zakresach (zakaz mieszania zakresów), co minimalizuje potrzebę skanowania strony SGAM, która służy do śledzenia mieszanych zakresów
- 1224 — Wyłącza eskalację blokad na podstawie liczby blokad. Jednak nadmierne użycie pamięci może spowodować eskalację blokady
- 2371 — Zmienia stały próg automatycznej aktualizacji statystyk na dynamiczny próg automatycznej aktualizacji statystyk. Ważne przy aktualizowaniu planów kwerend dla dużych tabel, gdzie niepoprawna liczba rekordów skutkuje błędnymi planami wykonania
- 3226 — Pomija komunikaty o powodzeniu tworzenia kopii zapasowej w dzienniku błędów
- 4199 — obejmuje zmiany w optymalizatorze zapytań wydanym w CU i dodatkach Service Pack dla programu SQL Server
- 6532-6534 — zawiera ulepszenia wydajności operacji zapytań dotyczących typów danych przestrzennych
- 8048 — Konwertuje obiekty pamięci partycjonowanej NUMA na obiekty partycjonowane przez procesor
- 8780 — Włącza dodatkową alokację czasu na planowanie zapytań. Niektóre żądania bez tej flagi mogą zostać odrzucone, ponieważ nie mają planu zapytań (bardzo rzadki błąd)
- 8780-9389 — Włącza dodatkowy dynamiczny bufor pamięci grantu dla instrukcji trybu wsadowego, co pozwala operatorowi trybu wsadowego zażądać dodatkowej pamięci i uniknąć przenoszenia danych do bazy danych tempdb, jeśli dostępna jest dodatkowa pamięć
Również przed 2016 przydatne jest włączenie flagi śledzenia 2301, która umożliwia ulepszone optymalizacje wspomagania decyzji, a tym samym pomaga w wyborze bardziej poprawnych planów zapytań. Jednak od wersji 2016 często ma to negatywny wpływ na dość długi ogólny czas wykonywania zapytań.
Również w przypadku systemów z dużą ilością indeksów (na przykład dla baz danych 1C) zalecam włączenie flagi śledzenia 2330, która wyłącza gromadzenie użycia indeksu, co generalnie ma pozytywny wpływ na system.
Aby uzyskać więcej informacji na temat flag śledzenia, zobacz
Z powyższego łącza należy również wziąć pod uwagę wersje i kompilacje MS SQL Server, ponieważ w przypadku nowszych wersji niektóre flagi śledzenia są domyślnie włączone lub nie mają żadnego efektu.
Flagę śledzenia można włączać i wyłączać odpowiednio za pomocą poleceń DBCC TRACEON i DBCC TRACEOFF. Aby uzyskać więcej informacji, patrz
Możesz uzyskać status flag śledzenia za pomocą polecenia DBCC TRACESTATUS:
Aby flagi śledzenia zostały uwzględnione podczas autostartu usługi MS SQL Server, musisz przejść do SQL Server Configuration Manager i dodać te flagi śledzenia za pomocą opcji -T we właściwościach usługi:
Wyniki
W tym artykule przeanalizowano niektóre aspekty monitorowania MS SQL Server, za pomocą których można szybko zidentyfikować brak pamięci RAM i wolnego czasu procesora, a także szereg innych mniej oczywistych problemów. Dokonano przeglądu najczęściej używanych flag śledzenia.
Źródła:
»
»
»
»
»
»
»
Źródło: www.habr.com