Upu Tomua
O le tele o taimi, o tagata fa'aoga, atina'e ma pule o le MS SQL Server DBMS e feagai ma fa'afitauli fa'atinoga o fa'amaumauga po'o le DBMS atoa, o lea e matua'i talafeagai ai le mata'ituina o le MS SQL Server.
O lenei tusiga o se faʻaopoopoga i le tusiga ma o le a aofia ai nisi o vaega o le mataʻituina o le MS SQL Server, aemaise lava: faʻafefea ona vave iloa poʻo fea punaoa o loʻo misi, faʻapea foʻi ma fautuaga mo le faʻatulagaina o fuʻa.
Mo tusitusiga nei e galue, e tatau ona e fatuina se inf schema i le database manaʻomia e pei ona taua i lalo:
Fausia o se inf schema
use <имя_БД>;
go
create schema inf;
Metotia e iloa ai le leai o se RAM
O le faʻailoga muamua o le leai o se RAM o le tulaga lea pe a 'ai e se faʻataʻitaʻiga o le MS SQL Server uma le RAM na tuʻuina atu i ai.
Ina ia faia lenei mea, matou te fatuina le faʻatusa o le inf.vRAM:
Fausia le vaaiga 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;
Ona mafai lea ona e fuafuaina o se faʻataʻitaʻiga o le MS SQL Server e faʻaaogaina uma mafaufauga na tuʻuina atu i ai e ala i le fesili lenei:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Afai ole SQL_server_physical_memory_in_use_Mb e sili atu pe tutusa ma SQL_server_committed_target_Mb, e tatau ona siaki fuainumera faʻatali.
Ina ia iloa le leai o le RAM e ala i fuainumera faʻatali, seʻi o tatou fatuina le inf.vWaits view:
Fausia le inf.vWaits View
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];
I lenei tulaga, e mafai ona e fuafuaina le leai o se RAM i le fesili lenei:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
O iinei e tatau ona e gauai atu i faailoga o le pasene ma le AvgWait_S. Afai latou te taua i lo latou aofaʻi, o loʻo i ai se tulaga maualuga tele e le lava le RAM mo le MS SQL Server faʻataʻitaʻiga. O taua taua e faʻamoemoeina taʻitoʻatasi mo faiga taʻitasi. Ae ui i lea, e mafai ona e amata i mea nei: Pasene>=1 ma AvgWait_S>=0.005.
I fa'ailoga fa'ailoga i se faiga mata'ituina (mo se fa'ata'ita'iga, Zabbix), e mafai ona e faia fesili nei e lua:
- e fia ituaiga o fa'atali o lo'o nofoia e le RAM i le pasene (le aofa'i o ituaiga uma o fa'atali):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - e fia ituaiga fa'atalitali o le RAM e ave ile milliseconds (le tau aupito maualuga o fa'atuai uma mo ia ituaiga fa'atalitali uma):
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' );
Faʻavae i luga o le malosi o tau maua mai mo nei faʻailoga e lua, e mafai ona tatou faʻamaonia pe lava le RAM mo se faʻataʻitaʻiga o le MS SQL Server.
Metotia Su'esu'eina ova Ova le CPU
Ina ia iloa le leai o se taimi o le gaosiga, ua lava le faʻaogaina o le sys.dm_os_schedulers system view. O iinei, afai o le runnable_tasks_count e sili atu nai lo le 1, o loʻo i ai se avanoa maualuga e le lava le numera o cores mo le MS SQL Server faʻataʻitaʻiga.
Ina ia faʻaalia se faʻailoga i se faiga mataʻituina (mo se faʻataʻitaʻiga, Zabbix), e mafai ona e fatuina le fesili lenei:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Faʻavae i luga o le malosi o tau maua mai mo lenei faʻailoga, e mafai ona tatou faʻaiʻuina pe lava le taimi o le gaosiga (le numera o CPU cores) mo se faʻataʻitaʻiga o le MS SQL Server.
Ae ui i lea, e taua le manatua o le mea moni o talosaga latou te mafai ona talosagaina ni filo se tele i le taimi e tasi. Ma o nisi taimi e le mafai e le optimizer ona fa'atatau sa'o le lavelave o le fesili lava ia. Ona mafai lea ona tu'uina atu le talosaga i le tele o filo e le mafai ona fa'agasolo i le taimi e tasi i le taimi ua tu'uina atu. Ma o lenei mea e mafua ai se ituaiga o faʻatali e fesoʻotaʻi ma le le lava o le taimi faʻagaioiga, ma le tuputupu aʻe o le faʻasologa mo tagata faʻatulagaina e faʻaogaina ai le CPU faʻapitoa, o lona uiga o le runnable_tasks_count indicator o le a tupu i ia tulaga.
I lenei tulaga, aʻo leʻi faʻateleina le numera o le CPU cores, e tatau ona saʻo le faʻatulagaina o mea tutusa o le MS SQL Server faʻataʻitaʻiga lava ia, ma mai le 2016 version, faʻapipiʻi saʻo mea tutusa o faʻamaumauga manaʻomia:
O iinei e tatau ona e gauai atu i vaega nei:
- Max Degree of Parallelism - faʻatulagaina le numera maualuga o filo e mafai ona tuʻufaʻatasia i talosaga taʻitasi (o le faaletonu o le 0 - faʻatapulaʻa e le faiga faʻaogaina lava ia ma le lomiga o le MS SQL Server)
- Fa'atauga o le Fa'atutusa - tau fa'atatau o le fa'atusa (fa'aletonu e 5)
- Max DOP - faʻatulagaina le numera maualuga o filo e mafai ona tuʻufaʻatasia i fesili taʻitasi i le tulaga o faʻamaumauga (ae le sili atu nai lo le tau o le "Max Degree of Parallelism" meatotino) (default o le 0 - faʻatapulaʻa naʻo le faiga faʻaoga lava ia ma le lomiga o le MS SQL Server, faʻapea foʻi ma le faʻatapulaʻaina o le "Max Degree of Parallelism" meatotino o le faʻataʻitaʻiga atoa o le MS SQL Server)
O iinei e le mafai ona tuʻuina atu se fua tutusa lelei mo mataupu uma, o lona uiga e te manaʻomia le suʻeina o fesili mamafa.
Mai loʻu lava poto masani, ou te fautuaina le algorithm o gaioiga mo faiga OLTP mo le faʻatulagaina o mea tutusa:
- muamua fa'agata le tutusa e ala i le setiina o le fa'ata'ita'iga lautele Max Degree of Parallelism i le 1
- iloilo le sili ona mamafa talosaga ma filifili le numera sili ona lelei o filo mo latou
- seti le Max Degree of Parallelism i le numera sili ona lelei filifilia o filo na maua mai i le Laasaga 2, ma mo faʻamaumauga patino seti le Max DOP tau maua mai le laasaga 2 mo faʻamaumauga taʻitasi
- au'ili'ili talosaga e sili ona mamafa ma fa'ailoa le a'afiaga leaga ole fa'aulutele. Afai o lea, ona fa'aopoopoina lea o le Tau o le Fa'atutusa.
Mo faiga e pei o le 1C, Microsoft CRM ma Microsoft NAV, i le tele o tulaga, fa'asaina le tele o filo e talafeagai.
E le gata i lea, afai o loʻo i ai se lomiga faʻasalalau, o le tele o tulaga o le faʻasaina o le multithreading e talafeagai ona o le mea moni o lenei lomiga e faʻatapulaʻaina i le numera o pusa CPU.
Mo faiga OLAP, o le algorithm o loʻo faʻamatalaina i luga e le talafeagai.
Mai loʻu lava poto masani, ou te fautuaina le algorithm o gaioiga mo faiga OLAP mo le faʻatulagaina o mea tutusa:
- iloilo le sili ona mamafa talosaga ma filifili le numera sili ona lelei o filo mo latou
- seti le Max Degree of Parallelism i le numera sili ona lelei filifilia o filo na maua mai i le Laasaga 1, ma mo faʻamaumauga patino seti le Max DOP tau maua mai le laasaga 1 mo faʻamaumauga taʻitasi
- au'ili'ili fesili sili ona mamafa ma fa'ailoa le a'afiaga o le fa'atapula'aina o le fa'atasi. Afai o lea, ona faʻaititia lea o le tau o le tau mo le Parallelism, pe toe fai laasaga 1-2 o lenei algorithm.
O lona uiga, mo faiga OLTP tatou te alu atu mai le tasi-threading i le tele-threading, ma mo OLAP-systems, i se isi itu, tatou te o mai le tele-threading i le tasi-threading. O le mea lea, e mafai ona e filifilia le faʻatulagaina tutusa lelei mo se faʻamaumauga faʻapitoa ma le faʻataʻitaʻiga atoa o le MS SQL Server.
E taua foi le malamalama o tulaga o mea tutusa tutusa e tatau ona suia i le taimi, e faavae i luga o taunuuga o le mataituina o le faatinoga o le MS SQL Server.
Ta'iala mo le Fa'atulagaina o Fu'a
Mai loʻu lava poto masani ma le poto masani a aʻu uo, mo le faʻatinoga sili ona lelei, ou te fautuaina le setiina o fuʻa o loʻo i lalo i le maualuga o le MS SQL Server service mo versions 2008-2016:
- 610 - Fa'aitiitia le fa'amauina o fa'aofi i totonu o laulau fa'asino. E mafai ona fesoasoani i faʻaofi i totonu o laulau ma le tele o faʻamaumauga ma le tele o fefaʻatauaiga, faʻatasi ai ma le WRITELOG umi e faʻatali mo suiga i faʻamatalaga.
- 1117 - Afai o se faila i totonu o le faila faila e fetaui ma le autogrowth threshold manaʻomia, o faila uma i le faila faila e tupu aʻe.
- 1118 - Faʻamalosia mea uma e tuʻu i tulaga eseese (faʻasaina o faʻalavelave fefiloi), lea e faʻaitiitia ai le manaʻoga e suʻe le itulau SGAM, lea e faʻaaogaina e siaki ai faʻafefiloi lautele.
- 1224 - Fa'ate'aina loka fa'ateleina e fa'atatau i le numera o loka. Ae ui i lea, o le tele o le fa'aoga manatua e mafai ona fa'aoso ai le fa'ateleina o loka
- 2371 - Suia le fa'afou fa'afou fa'amaumauga otometi fa'amau i le fa'afou fa'afou fa'amaumauga otometi. Taua mo le fa'afouina o fuafuaga fa'atatau mo laulau lapopo'a, pe a le sa'o le faitau aofa'i o fa'amaumauga e mafua ai ona sese fuafuaga fa'atino.
- 3226 - Taofi savali manuia faaleoleo i totonu o le log error
- 4199 - E aofia ai suiga i le su'esu'ega optimizer fa'amatu'u i CUs ma SQL Server Service Packs
- 6532-6534 - E aofia ai le faʻaleleia atili o faʻatinoga mo faʻatonuga o faʻamatalaga i luga o faʻamatalaga faʻafanua
- 8048 - Fa'aliliu mea manatua vaeluaga o le NUMA i mea fa'a-PPU
- 8780 - Fa'ataga fa'aopoopo taimi fa'asoa mo fuafuaga fa'atatau. O nisi talosaga e aunoa ma lenei fu'a e ono te'ena ona e leai se latou fuafuaga fa'atatau (sesese bug)
- 8780 - 9389 - Fa'aagaaga fa'aopoopo fa'ameaalofa fa'apolopolo manatuaga mo fa'amatalaga tu'ufa'atasiga, lea e fa'ataga ai le fa'atonu fa'aputuga o lo'o fa'atonuina le tele o manatua ma aloese mai le fa'anofoina o fa'amatalaga i le tempdb pe a maua le tele o manatuaga.
A'o le'i o'o i le 2016, e aoga le fa'aagaaga o le su'eina o le fu'a 2301, lea e mafai ai ona fa'aleleia atili filifiliga lagolago ma fesoasoani i le filifilia atili o fuafuaga fa'atatau sa'o. Ae ui i lea, e amata mai i le version 2016, e masani ona i ai se aafiaga le lelei i le umi o le taimi o le faʻatinoina o fesili.
E le gata i lea, mo faiga e tele fa'asinomaga (mo se fa'ata'ita'iga, mo 1C fa'amaumauga), ou te fautuaina le fa'agaoioia o le fu'a 2330, lea e fa'agata ai le aoina o fa'aoga fa'asino, lea e masani ona i ai se aafiaga lelei i le faiga.
Mo nisi fa'amatalaga e uiga i fa'ailoga fu'a, va'ai
Mai le feso'ota'iga i luga, e taua fo'i le iloiloina o fa'aliliuga ma fau o le MS SQL Server, ae mo fa'amatalaga fou, o nisi o fu'a e mafai ona fa'agasolo pe leai se aoga.
E mafai ona e ki ma tape le fu'a su'esu'e i le tulafono DBCC TRACEON ma le DBCC TRACEOFF. Mo nisi fa'amatalaga va'ai
E mafai ona e mauaina le tulaga o fuʻa faʻailoga e faʻaaoga ai le DBCC TRACESTATUS poloaiga:
Ina ia mafai ona faʻapipiʻi fuʻa i totonu o le autostart o le MS SQL Server service, e tatau ona e alu i le SQL Server Configuration Manager ma faʻaopopo nei faʻailoga fuʻa e ala i le -T i mea tau auaunaga:
O taunuʻuga
I lenei tusiga, o nisi o vaega o le mataʻituina o le MS SQL Server na suʻesuʻeina, faʻatasi ai ma le fesoasoani e mafai ai ona e vave iloa le leai o se RAM ma le taimi ole PPU, faʻapea foʻi ma le tele o isi faʻafitauli e le o manino. O fu'a e masani ona fa'aaogaina ua toe iloiloina.
Punaoa:
»
»
»
»
»
»
»
puna: www.habr.com