Magana
Sau da yawa, masu amfani, masu haɓakawa da masu gudanarwa na MS SQL Server DBMS suna fuskantar matsalolin aiki na ma'ajin bayanai ko DBMS gaba ɗaya, don haka saka idanu na MS SQL Server yana da dacewa sosai.
Wannan labarin kari ne ga labarin kuma zai shafi wasu bangarori na sa ido kan MS SQL Server, musamman: yadda ake saurin tantance albarkatun da suka ɓace, da kuma shawarwarin saita tutoci.
Domin waɗannan rubutun su yi aiki, kuna buƙatar ƙirƙirar tsarin inf a cikin bayanan da ake so kamar haka:
Ƙirƙirar tsarin inf
use <имя_БД>;
go
create schema inf;
Hanyar gano rashin RAM
Alamar farko ta rashin RAM shine yanayin lokacin da misalin MS SQL Server ya cinye duk RAM ɗin da aka ware masa.
Don yin wannan, za mu ƙirƙiri mai zuwa wakilci na inf.vRAM:
Ƙirƙirar kallon 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;
Sannan zaku iya tantance cewa misalin MS SQL Server yana cinye duk memorin da aka ware masa ta tambaya mai zuwa:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Idan SQL_server_physical_memory_in_use_Mb ya fi girma ko daidai da SQL_server_committed_target_Mb, to ya kamata a duba kididdigar jira.
Don tantance rashin RAM ta hanyar kididdigar jira, bari mu ƙirƙiri ra'ayin inf.vWaits:
Ƙirƙirar 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];
A wannan yanayin, zaku iya ƙayyade rashin RAM tare da tambaya mai zuwa:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Anan kuna buƙatar kula da alamomin Kashi da AvgWait_S. Idan suna da mahimmanci a cikin jimlar su, to akwai yuwuwar cewa babu isasshen RAM don misalin MS SQL Server. Ana ƙididdige ƙima mai mahimmanci ga kowane tsarin. Koyaya, zaku iya farawa da masu zuwa: Kashi>=1 da AvgWait_S>=0.005.
Don fitar da alamomi zuwa tsarin kulawa (misali, Zabbix), zaku iya ƙirƙirar tambayoyin guda biyu masu zuwa:
- Nawa nau'ikan jirage nawa ne ke shagaltar da RAM a cikin kashi (jimilar duk ire-iren waɗannan jirage):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - Nawa nau'in jira na RAM nawa ke ɗauka a cikin millise seconds (mafi girman ƙimar duk matsakaicin jinkiri ga duk irin waɗannan nau'ikan jirage):
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' );
Dangane da ƙarfin ƙimar da aka samu don waɗannan alamomi guda biyu, zamu iya yanke shawarar ko akwai isasshen RAM don misalin MS SQL Server.
Hanyar Ganewar CPU Overload
Don gano rashin lokacin sarrafawa, ya isa a yi amfani da tsarin tsarin sys.dm_os_schedulers. Anan, idan runnable_tasks_count koyaushe yana girma fiye da 1, to akwai yuwuwar yawan adadin ba su isa ga MS SQL Server misali ba.
Don fitar da mai nuna alama zuwa tsarin kulawa (misali, Zabbix), zaku iya ƙirƙirar tambaya mai zuwa:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Dangane da ƙarfin ƙimar da aka samu don wannan mai nuna alama, zamu iya yanke shawarar ko akwai isasshen lokacin sarrafawa (yawan adadin abubuwan CPU) don misalin MS SQL Server.
Koyaya, yana da mahimmanci a tuna cewa buƙatun kansu na iya buƙatar zaren da yawa a lokaci ɗaya. Kuma wani lokacin na'urar ingantawa ba zai iya kimanta sarkar tambayar da kanta ba. Sa'an nan bukatar za a iya kasaftawa da yawa zaren da ba za a iya sarrafa a lokaci guda a lokacin da aka bayar. Kuma wannan yana haifar da nau'in jira da ke da alaƙa da rashin lokacin sarrafawa, da haɓakar jerin gwano don masu tsara shirye-shiryen da ke amfani da takamaiman CPU, watau mai nuna runnable_tasks_count zai girma a cikin irin wannan yanayi.
A wannan yanayin, kafin haɓaka adadin cores na CPU, ya zama dole a daidaita daidaitattun kaddarorin na MS SQL Server misalin kanta, kuma daga sigar 2016, daidaita daidaitattun kaddarorin bayanan bayanan da ake buƙata:
Anan ya kamata ku kula da sigogi masu zuwa:
- Max Degree na Parallelism - yana saita matsakaicin adadin zaren da za a iya kasaftawa ga kowane buƙatu (tsoho shine 0 - iyakance kawai ta tsarin aiki da kanta da bugu na MS SQL Server)
- Matsakaicin farashi don daidaitawa - kiyasin farashin daidaici (tsoho shine 5)
- Max DOP - yana saita matsakaicin adadin zaren da za a iya kasaftawa ga kowane tambaya a matakin bayanan (amma bai wuce ƙimar "Max Degree of Parallelism" dukiya ba) (tsoho shine 0 - iyakance kawai ta tsarin aiki da kanta kuma edition na MS SQL Server, kazalika da ƙuntatawa a kan "Max Degree of Parallelism" dukiya na duk misalin MS SQL Server)
Anan ba shi yiwuwa a ba da girke-girke mai kyau daidai ga duk lokuta, watau kuna buƙatar bincika tambayoyi masu nauyi.
Daga gwaninta na, Ina ba da shawarar algorithm mai zuwa na ayyuka don tsarin OLTP don kafa kaddarorin daidaitawa:
- na farko musaki daidaito ta hanyar saita babban misali-fadi Max Degree na Parallelism zuwa 1
- bincika mafi nauyi buƙatun kuma zaɓi mafi kyawun adadin zaren don su
- saita Max Degree na Parallelism zuwa zaɓi mafi kyawun adadin zaren da aka samu daga mataki na 2, kuma don takamaiman bayanan bayanai saita ƙimar Max DOP da aka samu daga mataki na 2 don kowane bayanan bayanai.
- bincika buƙatun mafi nauyi kuma gano mummunan tasirin multithreading. Idan haka ne, to ƙara Ƙofar Kuɗi don Daidaitawa.
Don tsarin kamar 1C, Microsoft CRM da Microsoft NAV, a mafi yawan lokuta, haramta multithreading ya dace.
Har ila yau, idan akwai Standard edition, to, a mafi yawan lokuta haramcin multithreading ya dace saboda gaskiyar cewa wannan bugu yana iyakance a cikin adadin CPU.
Don tsarin OLAP, algorithm da aka kwatanta a sama bai dace ba.
Daga gwaninta na, Ina ba da shawarar algorithm mai zuwa na ayyuka don tsarin OLAP don kafa kaddarorin daidaitawa:
- bincika mafi nauyi buƙatun kuma zaɓi mafi kyawun adadin zaren don su
- saita Max Degree na Parallelism zuwa zaɓi mafi kyawun adadin zaren da aka samu daga mataki na 1, kuma don takamaiman bayanan bayanai saita ƙimar Max DOP da aka samu daga mataki na 1 don kowane bayanan bayanai.
- bincika mafi nauyi tambayoyin kuma gano mummunan tasirin iyakance concurrency. Idan haka ne, to ko dai rage Ƙofar Ƙimar don ƙimar daidaici, ko maimaita matakai 1-2 na wannan algorithm.
Wato, don tsarin OLTP muna tafiya daga zaren guda ɗaya zuwa multithreading, kuma ga tsarin OLAP, akasin haka, muna tafiya daga multithreading zuwa zaren guda ɗaya. Don haka, zaku iya zaɓar saitunan daidaitattun daidaitattun duka biyu takamaiman bayanai da duk misalin MS SQL Server.
Har ila yau, yana da mahimmanci a fahimci cewa ana buƙatar canza saitunan daidaitattun kaddarorin na tsawon lokaci, dangane da sakamakon sa ido kan aikin MS SQL Server.
Jagorori don Saita Tutoci
Daga gwaninta da kuma kwarewar abokan aiki na, don kyakkyawan aiki, Ina ba da shawarar saita tutoci masu zuwa a matakin gudu na sabis na MS SQL Server don nau'ikan 2008-2016:
- 610 - Rage shigar da abubuwan da aka saka a cikin allunan da aka lissafta. Zai iya taimakawa tare da sakawa cikin tebur tare da bayanai da yawa da ma'amaloli da yawa, tare da yawan dogon WRITELOG yana jiran canje-canje a cikin fihirisa.
- 1117 - Idan fayil a cikin rukunin fayil ɗin ya cika buƙatun ƙa'idodin haɓakar haɓakawa, duk fayilolin da ke cikin rukunin fayil suna girma.
- 1118 - Tilastawa dukkan abubuwa su kasance a wurare daban-daban (haramta gaurayawan iyaka), wanda ke rage buƙatar bincika shafin SGAM, wanda ake amfani da shi don bin diddigin gauraye.
- 1224 - Yana hana haɓakar kullewa bisa adadin makullai. Koyaya, yawan amfani da ƙwaƙwalwar ajiya na iya haifar da haɓakar kullewa
- 2371 - Yana canza ƙayyadaddun ƙayyadaddun ƙididdiga ta atomatik zuwa ƙarshen sabunta ƙididdiga ta atomatik. Mahimmanci don sabunta tsare-tsaren tambaya don manyan tebura, inda ƙidayar bayanan da ba daidai ba ta haifar da kuskuren tsare-tsaren aiwatarwa.
- 3226 - Yana danne saƙonnin nasara na madadin a cikin log ɗin kuskure
- 4199 - Ya haɗa da canje-canje ga mai inganta tambaya da aka fitar a cikin CUs da fakitin Sabis na SQL
- 6532-6534 - Ya haɗa da haɓaka aiki don ayyukan tambaya akan nau'ikan bayanan sarari
- 8048 - Yana Canza NUMA abubuwan ƙwaƙwalwar ajiya zuwa ɓangarori na CPU
- 8780 - Yana ba da damar ƙarin keɓe lokaci don tsara tambaya. Wasu buƙatun ba tare da wannan tuta ba za a iya ƙi su saboda ba su da shirin tambaya (mafi ƙarancin bug)
- 8780 - 9389 - Yana ba da damar ƙarin ƙaƙƙarfan buffer ƙwaƙwalwar ajiyar kyauta don bayanan yanayin tsari, wanda ke ba da damar ma'aikacin yanayin tsari don buƙatar ƙarin ƙwaƙwalwar ajiya kuma guje wa motsi bayanai zuwa tempdb idan akwai ƙarin ƙwaƙwalwar ajiya.
Hakanan kafin 2016, yana da amfani don ba da damar gano tuta 2301, wanda ke ba da damar inganta ingantaccen tallafi na yanke shawara kuma don haka yana taimakawa wajen zaɓar ƙarin shirye-shiryen tambaya daidai. Koyaya, kamar na sigar 2016, sau da yawa yana da mummunan tasiri akan tsawon lokacin aiwatar da tambayoyin gabaɗaya.
Hakanan, don tsarin da ke da fihirisa da yawa (misali, don bayanan bayanan 1C), Ina ba da shawarar kunna alamar alama 2330, wanda ke hana tarin amfani da fihirisa, wanda gabaɗaya yana da tasiri mai kyau akan tsarin.
Don ƙarin bayani game da tutoci, duba
Daga hanyar haɗin da ke sama, yana da mahimmanci kuma a yi la'akari da nau'i da kuma ginawa na MS SQL Server, kamar yadda na sababbin nau'o'in, wasu tutocin suna kunna ta tsohuwa ko kuma ba su da wani tasiri.
Kuna iya kunna tuta da kashewa tare da umarnin DBCC TRACEON da DBCC TRACEOFF, bi da bi. Don ƙarin bayani duba
Kuna iya samun matsayin alamar tutocin ta amfani da umarnin DBCC TRACESTATUS:
Domin a haɗa tutoci a cikin autostart na sabis na SQL na MS SQL, dole ne ku je zuwa Manajan Kanfigareshan Sabar SQL kuma ƙara waɗannan tutocin ta hanyar -T a cikin kaddarorin sabis:
Sakamakon
A cikin wannan labarin, an yi nazarin wasu fannoni na saka idanu na MS SQL Server, tare da taimakonsu za ku iya gano rashin RAM da lokacin CPU kyauta, da kuma wasu matsalolin da ba a bayyana su ba. An sake duba tutocin da aka fi amfani da su.
Sources:
»
»
»
»
»
»
»
source: www.habr.com