Facal-toisich
Gu math tric, bidh luchd-cleachdaidh, luchd-leasachaidh agus luchd-rianachd an MS SQL Server DBMS a’ tighinn tarsainn air duilgheadasan coileanaidh an stòr-dàta no an DBMS gu h-iomlan, agus mar sin tha sgrùdadh MS SQL Server gu math buntainneach.
Tha an artaigil seo a bharrachd air an artaigil agus còmhdaichidh e cuid de thaobhan de sgrùdadh MS SQL Server, gu sònraichte: mar a nì thu co-dhùnadh gu sgiobalta dè na goireasan a tha a dhìth, a bharrachd air molaidhean airson brataichean lorg a shuidheachadh.
Airson na sgriobtaichean a leanas a bhith ag obair, feumaidh tu inf schema a chruthachadh anns an stòr-dàta a tha thu ag iarraidh mar a leanas:
A’ cruthachadh sgeama inf
use <имя_БД>;
go
create schema inf;
Dòigh airson dìth RAM a lorg
Is e a’ chiad chomharra air dìth RAM a tha fìor nuair a bhios eisimpleir de MS SQL Server ag ithe suas an RAM gu lèir a chaidh a thoirt dha.
Gus seo a dhèanamh, cruthaichidh sinn an riochdachadh a leanas de inf.vRAM:
A' cruthachadh an t-seallaidh 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;
An uairsin faodaidh tu dearbhadh gu bheil eisimpleir de MS SQL Server ag ithe a h-uile cuimhne a chaidh a thoirt dha leis a’ cheist a leanas:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Ma tha SQL_server_physical_memory_in_use_Mb gu cunbhalach nas motha na no co-ionann ri SQL_server_committed_target_Mb, bu chòir na staitistig feitheimh a sgrùdadh.
Gus faighinn a-mach dè an ìre de RAM a tha ann tro staitistig feitheimh, cruthaichidh sinn an sealladh inf.vWaits:
A' cruthachadh an sealladh 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];
Anns a 'chùis seo, faodaidh tu dearbhadh dè an dìth RAM leis a' cheist a leanas:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
An seo feumaidh tu aire a thoirt do na comharran Percentage agus AvgWait_S. Ma tha iad cudromach gu h-iomlan, tha coltas gu math àrd ann nach eil RAM gu leòr ann airson eisimpleir MS SQL Server. Tha luachan cudromach air an co-dhùnadh leotha fhèin airson gach siostam. Ge-tà, faodaidh tu tòiseachadh leis na leanas: Percentage>=1 agus AvgWait_S>=0.005.
Gus comharran toraidh a chuir gu siostam sgrùdaidh (mar eisimpleir, Zabbix), faodaidh tu an dà cheist a leanas a chruthachadh:
- cia mheud seòrsa feitheamh a tha RAM ann an ceudad (suim gach seòrsa feitheamh):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - cia mheud seòrsa feitheimh RAM a bhios a’ toirt a-steach milliseconds (an luach as àirde airson gach dàil cuibheasach airson a h-uile seòrsa feitheimh):
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' );
Stèidhichte air dinamics nan luachan a fhuaireadh airson an dà thaisbeanair seo, is urrainn dhuinn co-dhùnadh a bheil RAM gu leòr ann airson eisimpleir de MS SQL Server.
Dòigh lorgaidh cus cuideim CPU
Gus dìth ùine pròiseasar a chomharrachadh, tha e gu leòr an sealladh siostam sys.dm_os_schedulers a chleachdadh. An seo, ma tha an runnable_tasks_count an-còmhnaidh nas àirde na 1, tha coltas ann nach eil an àireamh de choraichean gu leòr airson eisimpleir MS SQL Server.
Gus comharradh a chuir a-mach gu siostam sgrùdaidh (mar eisimpleir, Zabbix), faodaidh tu a’ cheist a leanas a chruthachadh:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Stèidhichte air daineamaigs nan luachan a fhuaireadh airson a’ chomharra seo, is urrainn dhuinn co-dhùnadh a bheil ùine pròiseasar gu leòr (an àireamh de choraichean CPU) airson eisimpleir de MS SQL Server.
Ach, tha e cudromach cuimhneachadh gum faod iarrtasan iad fhèin iomadh snàithlean iarraidh aig an aon àm. Agus uaireannan chan urrainn don optimizer tuairmse ceart a dhèanamh air iom-fhillteachd na ceiste fhèin. An uairsin faodar cus snàithleanan a thoirt don iarrtas nach gabh a phròiseasadh aig an aon àm aig an àm ainmichte. Agus tha seo cuideachd ag adhbhrachadh seòrsa de dh’fheitheamh co-cheangailte ri dìth ùine pròiseasar, agus fàs a’ chiudha airson luchd-clàraidh a bhios a’ cleachdadh coraichean CPU sònraichte, ie fàsaidh an comharra runnable_tasks_count ann an leithid de shuidheachaidhean.
Anns a ’chùis seo, mus àrdaich thu an àireamh de choraichean CPU, feumar feartan co-shìnteachd eisimpleir MS SQL Server fhèin a rèiteachadh gu ceart, agus bhon dreach 2016, rèiteachadh ceart a dhèanamh air feartan co-shìnteachd nan stòran-dàta a tha a dhìth:
An seo feumaidh tu aire a thoirt do na paramadairean a leanas:
- Ìre as àirde de cho-shìnteachd - a’ suidheachadh an àireamh as motha de shnàithleanan a dh’ fhaodar a riarachadh airson gach iarrtas (is e 0 an rud bunaiteach - cuingealaichte a-mhàin leis an t-siostam obrachaidh fhèin agus deasachadh MS SQL Server)
- Strì cosgais airson Co-shìnteachd - tuairmse air cosgais co-shìnte (is e 5 an àbhaist)
- Max DOP - a’ suidheachadh an àireamh as motha de shnàithleanan a dh’ fhaodar a riarachadh do gach ceist aig ìre an stòr-dàta (ach gun a bhith nas motha na luach an t-seilbh “An ìre as àirde de cho-shìnteachd”) (is e 0 an ìre àbhaisteach - cuingealaichte a-mhàin leis an t-siostam obrachaidh fhèin agus an deasachadh de MS SQL Server, a bharrachd air a’ chuingealachadh air an t-seilbh “Max Degree of Parallelism” den eisimpleir gu lèir de MS SQL Server)
An seo tha e do-dhèanta reasabaidh a cheart cho math a thoirt seachad airson a h-uile cùis, i.e. feumaidh tu ceistean trom a sgrùdadh.
Bhon eòlas agam fhìn, tha mi a’ moladh an algairim gnìomh a leanas airson siostaman OLTP airson togalaichean co-shìnteachd a stèidheachadh:
- cuir à comas co-shìnteachd an toiseach le bhith a’ suidheachadh an ìre as àirde de cho-shìnteachd gu 1
- dèan mion-sgrùdadh air na h-iarrtasan as truime agus tagh an àireamh snàithlean as fheàrr dhaibh
- suidhich an Ìre as àirde de cho-shìnteachd ris an àireamh as fheàrr de snàithleanan a gheibhear bho cheum 2, agus airson stòran-dàta sònraichte suidhich an luach Max DOP a gheibhear bho cheum 2 airson gach stòr-dàta
- mion-sgrùdadh a dhèanamh air na h-iarrtasan as truime agus comharraich a’ bhuaidh àicheil a th’ aig multithreading. Ma tha, an uairsin àrdaich an Cothrom Cosgais airson Co-shìnteachd.
Airson siostaman leithid 1C, Microsoft CRM agus Microsoft NAV, sa mhòr-chuid de chùisean, tha casg air multithreading freagarrach
Cuideachd, ma tha deasachadh àbhaisteach ann, sa mhòr-chuid de chùisean tha an casg air ioma-snàithlean iomchaidh leis gu bheil an deasachadh seo cuibhrichte anns an àireamh de choraichean CPU.
Airson siostaman OLAP, chan eil an algairim air a mhìneachadh gu h-àrd freagarrach.
Bhon eòlas agam fhìn, tha mi a’ moladh an algairim gnìomh a leanas airson siostaman OLAP airson togalaichean co-shìnteachd a stèidheachadh:
- dèan mion-sgrùdadh air na h-iarrtasan as truime agus tagh an àireamh snàithlean as fheàrr dhaibh
- suidhich an Ìre as àirde de cho-shìnteachd ris an àireamh as fheàrr de snàithleanan a gheibhear bho cheum 1, agus airson stòran-dàta sònraichte suidhich an luach Max DOP a gheibhear bho cheum 1 airson gach stòr-dàta
- mion-sgrùdadh a dhèanamh air na ceistean as truime agus comharraich an droch bhuaidh a tha aig cuingealachadh concurrency. Ma tha, an uairsin lughdaich an Luach Cothromach airson Co-shìnteachd, no cuir a-rithist ceumannan 1-2 den algairim seo
Is e sin, airson siostaman OLTP bidh sinn a’ dol bho aon-snàthainn gu ioma-snàithlean, agus airson siostaman OLAP, air an làimh eile, bidh sinn a’ dol bho ioma-snàthainn gu snàithlean singilte. Mar sin, faodaidh tu na roghainnean co-shìnteachd as fheàrr a thaghadh airson gach cuid stòr-dàta sònraichte agus an eisimpleir iomlan de MS SQL Server.
Tha e cuideachd cudromach tuigsinn gum feumar suidheachaidhean nan togalaichean co-shìnte atharrachadh thar ùine, stèidhichte air toraidhean sgrùdadh air coileanadh MS SQL Server.
Stiùireadh airson a bhith a’ suidheachadh brataichean trace
Bhon eòlas agam fhìn agus eòlas mo cho-obraichean, airson an coileanadh as fheàrr, tha mi a’ moladh na brataichean lorg a leanas a shuidheachadh aig ìre ruith seirbheis MS SQL Server airson dreachan 2008-2016:
- 610 - Lùghdachadh air logadh a-steach do chlàran clàraichte. Is urrainn dha cuideachadh le cuir a-steach do chlàran le mòran chlàran agus mòran ghnothaichean, le tric fada WRAICHTE a’ feitheamh ri atharrachaidhean ann an clàran-amais
- 1117 - Ma choinnicheas faidhle ann am buidheann fhaidhlichean ri riatanasan stairsneach fèin-fhàs, bidh a h-uile faidhle sa bhuidheann fhaidhlichean a’ fàs
- 1118 - A’ toirt air a h-uile nì a bhith suidhichte ann an diofar ìrean (toirmeasg air ìrean measgaichte), a lùghdaicheas an fheum air duilleag SGAM a sganadh, a thathas a’ cleachdadh gus sùil a chumail air ìrean measgaichte
- 1224 - Cuir à comas àrdachadh glasan stèidhichte air an àireamh de ghlasan. Ach, faodaidh cus cleachdadh cuimhne àrdachadh glasaidh a bhrosnachadh
- 2371 - Ag atharrachadh stairsneach ùrachadh staitistig fèin-ghluasadach stèidhichte gu stairsneach ùrachadh staitistig fèin-ghluasadach fiùghantach. Cudromach airson planaichean ceist ùrachadh airson clàran mòra, far a bheil cunntas ceàrr de chlàran a’ ciallachadh gu bheil planaichean cur an gnìomh mearachdach
- 3226 - A’ cuir stad air teachdaireachdan soirbheachais cùl-taic sa loga mhearachd
- 4199 - A’ toirt a-steach atharrachaidhean air an optimizer ceist a chaidh fhoillseachadh ann an CUn agus Pacaidean Seirbheis SQL Server
- 6532-6534 - A’ toirt a-steach leasachaidhean coileanaidh airson gnìomhachd ceist air seòrsachan dàta spàsail
- 8048 - Ag atharrachadh nithean cuimhne sgaraichte NUMA gu feadhainn le sgaradh CPU
- 8780 - A’ comasachadh ùine a bharrachd a riarachadh airson dealbhadh cheistean. Dh’ fhaodadh cuid de dh’ iarrtasan às aonais a’ bhratach seo a bhith air an diùltadh leis nach eil plana ceist aca (bug glè ainneamh)
- 8780 - 9389 - A’ comasachadh bufair cuimhne tabhartais fiùghantach a bharrachd airson aithrisean modh baidse, a leigeas le gnìomhaiche modh baidse cuimhne a bharrachd iarraidh agus gluasad dàta gu tempdb a sheachnadh ma tha cuimhne a bharrachd ri fhaighinn
Cuideachd ro 2016, tha e feumail lorg bratach 2301 a chomasachadh, a bheir comas do optimachadh taic co-dhùnaidh nas fheàrr agus mar sin a’ cuideachadh le bhith a’ taghadh planaichean ceist nas ceart. Ach, a thaobh dreach 2016, bidh e gu tric a’ toirt droch bhuaidh air amannan cur an gnìomh ceist gu math fada.
Cuideachd, airson siostaman le mòran chlàran-amais (mar eisimpleir, airson stòran-dàta 1C), tha mi a 'moladh a bhith a' comasachadh bratach lorg 2330, a tha a 'cur casg air cruinneachadh cleachdadh clàr-amais, a bheir buaidh mhath air an t-siostam mar as trice.
Airson tuilleadh fiosrachaidh mu bhrataichean lorg, faic
Bhon cheangal gu h-àrd, tha e cudromach cuideachd beachdachadh air dreachan agus toglaichean de MS SQL Server, oir airson tionndaidhean nas ùire, tha cuid de bhrataichean lorg air an comasachadh gu bunaiteach no chan eil buaidh sam bith aca.
Faodaidh tu a’ bhratach lorg a thionndadh air agus dheth leis na h-òrdughan DBCC TRACEON agus DBCC TRACEOFF, fa leth. Airson tuilleadh fiosrachaidh faic
Gheibh thu inbhe nam brataichean lorg a’ cleachdadh an àithne DBCC TRACESTATUS:
Gus am bi brataichean lorg air an toirt a-steach do autostart seirbheis MS SQL Server, feumaidh tu a dhol gu SQL Server Configuration Manager agus cuir na brataichean lorg sin tro -T anns na feartan seirbheis:
Builean
San artaigil seo, chaidh mion-sgrùdadh a dhèanamh air cuid de thaobhan de sgrùdadh MS SQL Server, le cuideachadh bhon urrainn dhut gu sgiobalta comharrachadh dìth RAM agus ùine CPU an-asgaidh, a bharrachd air grunn dhuilgheadasan eile nach eil cho follaiseach. Chaidh ath-sgrùdadh a dhèanamh air na brataichean lorg as cumanta.
Stòran:
»
»
»
»
»
»
»
Source: www.habr.com