ืืึทืืืึธืืข
ืืึทื ืฅ ืึธืคื, ื ืืฆืขืจืก, ืืขืืืขืืึธืคึผืขืจืก ืืื ืึทืืืื ืืกืืจืึทืืึธืจืก ืคืื MS SQL Server DBMS ืืขื ืขื ืคืืืกื ืืื ืคืึธืจืฉืืขืืื ื ืคึผืจืึธืืืขืืก ืคืื ืื ืืึทืืึทืืืืก ืึธืืขืจ ืื DBMS ืืื ืึท ืืึทื ืฅ, ืึทืืื ืืึธื ืืืึธืจืื ื MS SQL Server ืืื ืืืืขืจ ืืึทืืืึทืืืง.
ืืขืจ ืึทืจืืืงื ืืื ืึทื ืืขืจืฆื ืฆื ืืขื ืึทืจืืืงื ืืื ืขืก ืืืขื ืื ืืขืจืืืื ืขืืืขืืข ืึทืกืคึผืขืงืฅ ืคืื ืืึธื ืืืึธืจืื ื MS SQL Server, ืืื ืืึทืืื ืืขืจ: ืืื ืฆื ืืขืฉืืืื ื ืืึทืฉืืืกื ืืืึธืก ืจืขืกืืจืกื ืืขื ืขื ืคืขืื ืืืง, ืืื ืืขืืื ื ืืื ืจืขืงืึทืืึทื ืืืืฉืึทื ื ืคึฟืึทืจ ืืึทืฉืืขืืืงื ืึทืจืืืฃ ืฉืคึผืืจ ืคืืึทืืก.
ืคึฟืึทืจ ืื ืคืืืืขื ืืข ืกืงืจืืคึผืก ืฆื ืึทืจืืขืื, ืืืจ ืืึทืจืคึฟื ืฆื ืฉืึทืคึฟื ืึท ืื ืค ืกืืฉืขืืึท ืืื ืืขืจ ืืขืืืืื ืืึทืืึทืืืืก ืืื ืืืื:
ืงืจืืืืืื ื ืื ืค ืกืืฉืขืืึท
use <ะธะผั_ะะ>;
go
create schema inf;
ืืืคึฟื ืคึฟืึทืจ ืืืืขืงืืื ื ืคืขืื ืคืื ืืึทืจืึทื
ืืขืจ ืขืจืฉืืขืจ ืืจืืื ืคืื ืึท ืคืขืื ืคืื ืืึทืจืึทื ืืื ืืืขื ืึท ืืืึทืฉืคึผืื ืคืื MS SQL Server ืขืกื ืึทืืข ืื ืืึทืจืึทื ืึทืืึทืงืืืืื ืฆื ืขืก.
ืฆื ืืึธื ืืึธืก, ืฉืึทืคึฟื ืื ืคืืืืขื ืืข ืคืึทืจืืจืขืืื ื 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;
ืืขืจื ืึธื ืืืจ ืงืขื ืขื ืืึทืฉืืืืขื ืึทื ืึท ืืืึทืฉืคึผืื ืคืื MS SQL Server ืงืึทื ืกืืืื ื ืึทืืข ืื ืืืงืึธืจื ืึทืืึทืงืืืืื ืฆื ืขืก ืืื ืื ืคืืืืขื ืืข ืึธื ืคึฟืจืขื:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
ืืืื ืื SQL_server_physical_memory_in_use_Mb ืืจืืื ืืื ืงืขืกืืืืขืจ ื ืื ืืืืื ืืงืขืจ ืืื SQL_server_committed_target_Mb, ืืืจ ืืึทืจืคึฟื ืฆื ืงืึธื ืืจืึธืืืจื ืื ืืืึทืจืื ืกืืึทืืืกืืืง.
ืฆื ืืึทืฉืืืกื ืื ืคืขืื ืคืื ืืึทืจืึทื ืืืจื ืืืึทืจืื ืกืืึทืืืกืืืง, ืืึธืื ืืื ืื ืืึทืื ืึท ืืืื ืื ื 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];
ืืื ืืขื ืคืึทื, ืืืจ ืงืขื ืขื ืืึทืฉืืืกื ืื ืคืขืื ืคืื ืืึทืจืึทื ืืื ืื ืคืืืืขื ืืข ืึธื ืคึฟืจืขื:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
ืืึธ ืืืจ ืืึทืจืคึฟื ืฆื ืืึทืฆืึธืื ืืคืืขืจืงืืึทืืงืืื ืฆื ืื ืื ืืืงืึทืืึธืจืก ืคืื ืคึผืจืึธืฆืขื ื ืืื AvgWait_S. ืืืื ืืื ืืขื ืขื ืืึทืืืืืืง ืืื ืืืืขืจ ืืึทื ืฅ, ืขืก ืืื ืึท ืืืืขืจ ืืืื ืืึทืฉืืึธืขืก ืึทื ืื MS SQL Server ืืืึทืฉืคึผืื ืืื ื ืืฉื ืืขื ืื ืืึทืจืึทื. ืืงืขืจืืืง ืืืึทืืืขืก ืืขื ืขื ืืืฉืืืกื ืื ืืืืืืืืฉืืึทืื ืคึฟืึทืจ ืืขืืขืจ ืกืืกืืขื. ืึธืืขืจ, ืืืจ ืงืขื ืขื ืึธื ืืืืื ืืื ืื ืคืืืืขื ืืข ืืจืืื: ืคึผืจืึธืฆืขื ื>=1 ืืื AvgWait_S>=0.005.
ืฆื ืจืขืืืืืึทื ืื ืืึทืงืืืืขืจื ืฆื ืึท ืืึธื ืืืึธืจืื ื ืกืืกืืขื (ืืืฉื, Zabbix), ืืืจ ืงืขื ืขื ืืึทืื ืื ืคืืืืขื ืืข ืฆืืืื ืคึฟืจืืื:
- ืืืึธืก ืืื ืืขืจ ืคึผืจืึธืฆืขื ื ืคืื ืืืึทืจืื ืืืืคึผืก ืคึฟืึทืจ ืืึทืจืึทื (ืกืึทืืึทืงื ืคึฟืึทืจ ืึทืืข ืึทืืึท ืืืึทืจืื ืืืืคึผืก):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - ืืื ืคืืืข ืืึทืจืึทื ืืืึทืจืื ืืืืคึผืก ื ืขืืขื ืืื ืืืืืกืขืงืึทื ืื (ืื ืืึทืงืกืืืื ืืืขืจื ืคืื ืึทืืข ืืืจืืฉื ืืืืขื ืืืืืื ืคึฟืึทืจ ืึทืืข ืึทืืึท ืืืึทืจืื ืืืืคึผืก):
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' );
ืืึทืืืจื ืืืืฃ ืื ืืื ืึทืืืง ืคืื ืื ืืืงืืืขื ืืืึทืืืขืก ืคึฟืึทืจ ืื ืฆืืืื ืื ืืึทืงืืืืขืจื, ืืืจ ืงืขื ืขื ืคืึทืจืขื ืืืงื ืฆื ืขืก ืืื ืืขื ืื ืืึทืจืึทื ืคึฟืึทืจ ืื MS SQL Server ืืืึทืฉืคึผืื.
ืืืคึฟื ืคึฟืึทืจ ืืืืขืงืืื ื ืืืขืจืืง ืงืคึผื ืืึทืกืข
ืฆื ืืืขื ืืืคืืฆืืจื ืึท ืคืขืื ืคืื ืงืคึผื ืฆืืื, ื ืึธืจ ื ืืฆื ืื sys.dm_os_schedulers ืกืืกืืขื ืืืื ืื ื. ืืึธ, ืืืื ืื runnable_tasks_count ืืจืืื ืืื ืงืขืกืืืืขืจ ืืจืขืกืขืจ ืืื 1, ืขืก ืืื ืึท ืืืื ืืึทืฉืืึธืขืก ืึทื ืื ื ืืืขืจ ืคืื ืงืึธืจืขืก ืืื ื ืืฉื ืืขื ืื ืคึฟืึทืจ ืื MS SQL Server ืืืึทืฉืคึผืื.
ืฆื ืืืืึทืื ืึท ืืจืืื ืืื ืึท ืืึธื ืืืึธืจืื ื ืกืืกืืขื (ืืืฉื, Zabbix), ืืืจ ืงืขื ืขื ืืึทืื ืื ืคืืืืขื ืืข ืืขืื:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
ืืึทืืืจื ืืืืฃ ืื ืืื ืึทืืืง ืคืื ืื ืืืงืืืขื ืืืึทืืืขืก ืคึฟืึทืจ ืืขื ืืจืืื, ืืืจ ืงืขื ืขื ืคืึทืจืขื ืืืงื ืฆื ืขืก ืืื ืืขื ืื ืคึผืจืึทืกืขืกืขืจ ืฆืืื (ื ืืืขืจ ืคืื ืงืคึผื ืงืึธืจืขืก) ืคึฟืึทืจ ืื MS SQL ืกืขืจืืืืจืขืจ ืืืึทืฉืคึผืื.
ืึธืืขืจ, ืขืก ืืื ืืืืืืืง ืฆื ืืขืืขื ืงืขื ืื ืคืึทืงื ืึทื ืื ืงืืืืจืื ืืื ืงืขื ืขื ืึธื ืคึฟืจืขื ืงืืืคื ืคึฟืขืืขื ืืื ืึทืืึธื. ืืื ืืื ืื ืึธืคึผืืืืืืขืจ ืงืขื ืขื ื ืืฉื ืจืืืืืง ืึธืคึผืฉืึทืฆื ืื ืงืึทืืคึผืืขืงืกืืื ืคืื ืื ืึธื ืคึฟืจืขื ืืื. ืืขืจื ืึธื ืื ืืงืฉื ืงืขื ืืืื ืึทืืึทืงืืืืื ืฆื ืคืืืข ืคึฟืขืืขื, ืืืึธืก ืืื ืึท ืืขืืขืื ืฆืืื ืงืขื ืขื ื ืื ืืืื ืคึผืจืึทืกืขืกื ืกืืืืึทืืืืื ืืึทืกืื. ืืื ืืึธืก ืืืื ื ืึท ืืืคึผ ืคืื ืืืึทืจืื ืคึฟืึทืจืืื ืื ืืื ืึท ืคืขืื ืคืื ืคึผืจืึทืกืขืกืขืจ ืฆืืื, ืืื ืืขืจ ืืืึผืงืก ืคืื ืืขืจ ืจืื ืคึฟืึทืจ ืกืงืขืืืฉืืืขืจื ืืืึธืก ื ืืฆื ืกืคึผืขืฆืืคืืฉ ืงืคึผื ืงืึธืจืขืก, ืืึธืก ืืื, ืื ืจืื ื ืึทืืืข_ืืึทืกืงืก_ืงืึธืื ื ืื ืืืงืึทืืึธืจ ืืืขื ืคืึทืจืืจืขืกืขืจื ืืื ืึทืืึท ืื ืึธืื.
ืืื ืืขื ืคืึทื, ืืืืืขืจ ืืืจ ืคืึทืจืืจืขืกืขืจื ืื ื ืืืขืจ ืคืื ืงืคึผื ืงืึธืจืขืก, ืืืจ ืืึทืจืคึฟื ืฆื ืจืืืืืง ืงืึทื ืคืืืืขืจ ืื ืคึผืึทืจืึทืืขืืืกื ืคึผืจืึธืคึผืขืจืืืขืก ืคืื ืื MS SQL Server ืืืืฉืคืืื ืืื, ืืื ืคึฟืื ืืืขืจืกืืข 2016, ืจืืืืืง ืงืึทื ืคืืืืขืจ ืื ืคึผืึทืจืึทืืขืืืืกื ืคึผืจืึธืคึผืขืจืืืขืก ืคืื ืื ืคืืจืืื ืื ืืึทืืึทืืืืกืื:
ืืึธ ืืืจ ืืึธื ืืึทืฆืึธืื ืืคืืขืจืงืืึทืืงืืึทื ืฆื ืื ืคืืืืขื ืืข ืคึผืึทืจืึทืืขืืขืจืก:
- ืืึทืงืก ืืจืึทื ืคืื ืคึผืึทืจืึทืืขืืืื - ืฉืืขืื ืื ืืึทืงืกืืืื ื ืืืขืจ ืคืื ืคึฟืขืืขื ืืืึธืก ืงืขื ืขื ืืืื ืึทืืึทืงืืืืื ืฆื ืืขืืขืจ ืืงืฉื (ืื ืคืขืืืงืืึทื ืืื 0 - ืืืืืืขื ืืืืื ืืืจื ืื ืึธืคึผืขืจืืืืื ื ืกืืกืืขื ืืื ืืื ืื ืึทืืืฉืึทื ืคืื MS SQL Server)
- ืคึผืจืืึทื ืฉืืืขื ืคึฟืึทืจ ืคึผืึทืจืึทืืขืืืื - ืขืกืืืืึทืืขื ืงืึธืก ืคืื ืคึผืึทืจืึทืืขืืืืกื (ืคืขืืืงืืึทื ืืื 5)
- ืืึทืงืก ืืึธืคึผ - ืฉืืขืื ืื ืืึทืงืกืืืื ื ืืืขืจ ืคืื ืคึฟืขืืขื ืืืึธืก ืงืขื ืขื ืืืื ืึทืืึทืงืืืืื ืฆื ืืขืืขืจ ืึธื ืคึฟืจืขื ืืื ืื ืืึทืืึทืืืืก ืืืจืื (ืึธืืขืจ ื ืื ืืขืจ ืืื ืื ืืืขืจื ืคืื ืื "ืืึทืงืก ืืืืจื ืคืื ืคึผืึทืจืึทืืขืืืื" ืคืึทืจืืึธื) (ืืืจื ืคืขืืืงืืึทื ืขืก ืืื 0 - ืืืืืืขื ืืืืื ืืืจื ืื ืึธืคึผืขืจืืืืื ื ืกืืกืืขื ืืื ืืื ืื ืึทืืืฉืึทื ืคืื MS SQL Server, ืืื ืืขืืื ื ืืื ืึท ืืึทืืจืขื ืขืฆืื ื ืืืืฃ ืื "ืืึทืงืก ืืืืจื ืคืื ืคึผืึทืจืึทืืขืืืืกื" ืคืึทืจืืึธื ืคืื ืื ืืื ืฆืข MS SQL ืกืขืจืืืืจืขืจ ืืืึทืฉืคึผืื)
ืขืก ืืื ืืืืืขืืืขื ืฆื ืืขืื ืึทื ืืืืึทื ืืื ืจืขืฆืขืคึผื ืคึฟืึทืจ ืึทืืข ืงืึทืกืขืก, ืืึธืก ืืื, ืืืจ ืืึทืจืคึฟื ืฆื ืคืื ืึทื ืืขืจืงืืืึทืื ืฉืืืขืจ ืคึฟืจืืื.
ืืึทืืืจื ืืืืฃ ืืืื ืืืืืขื ืข ืืขืจืคืึทืจืื ื, ืืื ืจืขืงืึธืืขื ืืืจื ืื ืคืืืืขื ืืข ืึทืืืขืจืืืึทื ืคืื ืึทืงืฉืึทื ื ืคึฟืึทืจ OLTP ืกืืกืืขืืขื ืฆื ืงืึทื ืคืืืืขืจ ืคึผืึทืจืึทืืขืืืืกื ืคึผืจืึธืคึผืขืจืืืขืก:
- ืขืจืฉืืขืจ ืืืกืืืืึทื ืคึผืึทืจืึทืืขืืืืกื ืืืจื ืืึทืฉืืขืืืงื ืืึทืงืก ืืจืึทื ืคืื ืคึผืึทืจืึทืืขืืืืกื ืฆื 1 ืืืืฃ ืืขืจ ืืืจืื ืคืื ืื ืืื ืฆืข ืืืึทืฉืคึผืื
- ืคืื ืึทื ืืขืจืงืืืึทืื ืื ืืขืืืืึทืกื ืคึฟืจืืื ืืื ืกืขืืขืงืืืจื ืืขื ืึธืคึผืืืืึทื ื ืืืขืจ ืคืื ืคึฟืขืืขื ืคึฟืึทืจ ืืื
- ืฉืืขืื ืื ืืึทืงืก ืืจืึทื ืคืื ืคึผืึทืจืึทืืขืืืื ืฆื ืื ืืืืกืืขืงืืืื ืึธืคึผืืืืึทื ื ืืืขืจ ืคืื ืคึฟืขืืขื ืืืงืืืขื ืคืื ืฉืจืื 2, ืืื ืืืื ืคึฟืึทืจ ืกืคึผืขืฆืืคืืฉ ืืึทืืึทืืืืกืื ืฉืืขืื ืื ืืึทืงืก DOP ืืืขืจื ืืืงืืืขื ืคืื ืฉืจืื 2 ืคึฟืึทืจ ืืขืืขืจ ืืึทืืึทืืืืก
- ืคืื ืึทื ืืขืจืงืืืึทืื ืื ืืขืืืืึทืกื ืคึฟืจืืื ืืื ืืืขื ืืืคืืฆืืจื ืื ื ืขืืึทืืืื ืืืืจืงืื ื ืคืื ืืืืืืืืจืขืึทืืื ื. ืืืื ืืึธืก ืืื, ืคืึทืจืืจืขืกืขืจื ืื ืคึผืจืืึทื ืฉืืืขื ืคึฟืึทืจ ืคึผืึทืจืึทืืขืืืืึทื.
ืคึฟืึทืจ ืกืืกืืขืืขื ืึทืืึท ืืื 1C, Microsoft CRM ืืื Microsoft NAV, ืืื ืจืืึฟ ืงืึทืกืขืก, ืคึผืจืึธืืืืืึทืืื ื ืืืืืืืืจืขืึทืืื ื ืืื ืคึผืึทืกืืง
ืืืื, ืืืื ืืืจ ืืึธืื ืื ืกืืึทื ืืึทืจื ืึทืืืฉืึทื, ืืื ืจืืึฟ ืคืืื, ืึท ืคืึทืจืืึธื ืืืืฃ ืืืืื-ืืจืขืืื ื ืืื ืฆืื ืขืืขื ืจืขืื ืฆื ืืขื ืคืึทืงื ืึทื ืื ืึทืืืฉืึทื ืืื ืืืืืืขื ืืื ืื ื ืืืขืจ ืคืื ืงืคึผื ืงืึธืจืขืก.
ืืขืจ ืึทืืืขืจืืืึทื ืืืกืงืจืืืื ืืืืื ืืื ื ืืฉื ืคึผืึทืกืืง ืคึฟืึทืจ OLAP ืกืืกืืขืืขื.
ืืึทืืืจื ืืืืฃ ืืืื ืืืืืขื ืข ืืขืจืคืึทืจืื ื, ืืื ืจืขืงืึธืืขื ืืืจื ืื ืคืืืืขื ืืข ืึทืืืขืจืืืึทื ืคืื ืึทืงืฉืึทื ื ืคึฟืึทืจ OLAP ืกืืกืืขืืขื ืฆื ืงืึทื ืคืืืืขืจ ืคึผืึทืจืึทืืขืืืืกื ืคึผืจืึธืคึผืขืจืืืขืก:
- ืคืื ืึทื ืืขืจืงืืืึทืื ืื ืืขืืืืึทืกื ืคึฟืจืืื ืืื ืกืขืืขืงืืืจื ืืขื ืึธืคึผืืืืึทื ื ืืืขืจ ืคืื ืคึฟืขืืขื ืคึฟืึทืจ ืืื
- ืฉืืขืื ืื ืืึทืงืก ืืจืึทื ืคืื ืคึผืึทืจืึทืืขืืืื ืฆื ืื ืืืืกืืขืงืืืื ืึธืคึผืืืืึทื ื ืืืขืจ ืคืื ืคึฟืขืืขื ืืืงืืืขื ืคืื ืฉืจืื 1, ืืื ืืืื ืคึฟืึทืจ ืกืคึผืขืฆืืคืืฉ ืืึทืืึทืืืืกืื ืฉืืขืื ืื ืืึทืงืก DOP ืืืขืจื ืืืงืืืขื ืคืื ืฉืจืื 1 ืคึฟืึทืจ ืืขืืขืจ ืืึทืืึทืืืืก
- ืคืื ืึทื ืืขืจืงืืืึทืื ืื ืืขืืืืึทืกื ืงืืืืจืื ืืื ืืืขื ืืืคืืฆืืจื ืื ื ืขืืึทืืืื ืืืืจืงืื ื ืคืื ืืืืึทืืื ื ืงืึทื ืงืขืจืึทื ืกื. ืืืื ืืึธืก ืืื, ืึธืืขืจ ื ืืืขืจืืงืขืจ ืืขืจ ืคึผืจืืึทื ืฉืืืขื ืคึฟืึทืจ ืคึผืึทืจืึทืืขืืืื ืืืขืจื, ืึธืืขืจ ืืืืขืจืืืจื ืกืืขืคึผืก 1-2 ืคืื ืืขื ืึทืืืขืจืืืึทื
ืืึธืก ืืื, ืคึฟืึทืจ OLTP ืกืืกืืขืืขื ืืืจ ืืืื ืคืื ืืืื-ืืจืขืืื ื ืฆื ืืืืื-ืืจืขืืื ื, ืืื ืคึฟืึทืจ OLAP ืกืืกืืขืืขื, ืืืืฃ ืื ืคืึทืจืงืขืจื, ืืืจ ืืืื ืคืื ืืืืื-ืืจืขืืื ื ืฆื ืืืื-ืคึฟืขืืขื. ืืขื ืืืขื ืืืจ ืงืขื ืขื ืืืืกืงืืืึทืื ืื ืึธืคึผืืืืึทื ืคึผืึทืจืึทืืืขืืืกื ืกืขืืืื ืืก ืืืืืข ืคึฟืึทืจ ืึท ืกืคึผืขืฆืืคืืฉ ืืึทืืึทืืืืก ืืื ืคึฟืึทืจ ืื ืืื ืฆืข MS SQL ืกืขืจืืืืจืขืจ ืืืึทืฉืคึผืื.
ืขืก ืืื ืืืื ืืืืืืืง ืฆื ืคึฟืึทืจืฉืืืื ืึทื ืื ืกืขืืืื ืืก ืคืื ืงืึทื ืงืขืจืึทื ืกื ืคึผืจืึธืคึผืขืจืืืขืก ืืืื ืืืื ืืฉืืื ืืืฉื ืืืืขืจ ืฆืืื, ืืืืืจื ืืืืฃ ืื ืจืขืืืืืึทืื ืคืื ืืึธื ืืืึธืจืื ื ืื ืคืึธืจืฉืืขืืื ื ืคืื MS SQL Server.
ืจืขืงืึทืืึทื ืืืืฉืึทื ื ืคึฟืึทืจ ืืึทืฉืืขืืืงื ืฉืคึผืืจ ืคืืึทืืก
ืคึฟืื ืืืื ืืืืืขื ืข ืืขืจืคืึทืจืื ื ืืื ืืขืจ ืืขืจืคืึทืจืื ื ืคืื ืืืื ืืืจืื, ืคึฟืึทืจ ืึธืคึผืืืืึทื ืคืึธืจืฉืืขืืื ื, ืืื ืจืขืงืึธืืขื ืืืจื ืืึทืฉืืขืืืงื ืื ืคืืืืขื ืืข ืฉืคึผืืจ ืคืืึทืืก ืืืืฃ ืื MS SQL Server ืืืืคื ืืืจืื ืคึฟืึทืจ 2008-2016 ืืืขืจืกืืขืก:
- 610 - ืจืขืืืฆืืจื ืืึธืืื ื ืคืื ืื ืกืขืจืฅ ืืื ืื ืืขืงืกื ืืืฉื. ืงืขื ืขื ืืขืืคึฟื ืืื ืื ืกืขืจืฅ ืืื ืืืฉื ืืื ืึท ืืจืืืก ื ืืืขืจ ืคืื ืจืขืงืึธืจืืก ืืื ืคืืืข ืืจืึทื ืืึทืงืฉืึทื ื, ืืื ืึธืคื ืืึทื ื WRITELOG ืืืืืฅ ืคึฟืึทืจ ืขื ืืขืจืื ืืขื ืืื ืื ืืขืงืกืื
- 1117 - ืืืื ืึท ืืขืงืข ืืื ืึท ืคืืืขืืจืึธืืคึผ ืืืฅ ืื ืึทืืืึธ-ืืืึทืงืกื ืฉืืืขื, ืึทืืข ืืขืงืขืก ืืื ืืขืจ ืคืืืขืืจืืคึผ ืืขื ืขื ืืขืจืืืึทืงืกื
- 1118 - ืคืึธืจืกืขืก ืึทืืข ืึทืืืืฉืขืงืฅ ืฆื ืืืื ืืืื ืืื ืคืึทืจืฉืืืขื ืข ืืึธืก (ืืืกืึทืืึธืื ืืขืืืฉื ืืึธืก), ืืืึธืก ืืื ืึทืืืืืึทื ืื ื ืืื ืฆื ืืืขืจืงืืงื ืื SGAM ืืืึทื, ืืืึธืก ืืื ืืขื ืืฆื ืฆื ืฉืคึผืืจ ืืขืืืฉื ืืึธืก.
- 1224 - ืืืกืืืืึทืื ืฉืืึธืก ืขืกืงืึทืืืจืื ื ืืืืืจื ืืืืฃ ืฉืืึธืก ืฆืืืื. ืึธืืขืจ, ืืืขืจืืง ืืืงืึธืจื ืืึทื ืืฅ ืงืขื ืขื ืืขืื ืฉืืึธืก ืขืกืงืึทืืืจืื ื
- 2371 - ืขื ืืขืจืื ืืขื ืื ืคืึทืจืคืขืกืืืงื ืึธืืึทืืึทืืืง ืืขืจืืืึทื ืืืงื ืฉืืืขื ืคืื ืกืืึทืืืกืืืง ืฆื ืื ืืื ืึทืืืฉ ืึธืืึทืืึทืืืง ืืขืจืืืึทื ืืืงื ืฉืืืขื ืคืื ืกืืึทืืืกืืืง. ืืืืืืืง ืคึฟืึทืจ ืึทืคึผืืืืืื ื ืึธื ืคึฟืจืขื ืคึผืืึทื ื ืืืืฃ ืืจืืืก ืืืฉื, ืืื ืื ืงืขืจืขืงืืื ืืืคืืื ืื ื ืื ื ืืืขืจ ืคืื ืจืขืงืึธืจืืก ืจืขืืืืืึทืื ืืื ืขืจืึธืื ืืึทืก ืืืจืืคืืจืื ื ืคึผืืึทื ื
- 3226 - ืกืึทืคึผืจืขืกืื ืืึทืงืึทืคึผ ืืฆืืื ืึทืจืืืงืืขื ืืื ืื ืืขืืช ืงืืึธืฅ
- 4199 - ืื ืงืืืื ืขื ืืขืจืื ืืขื ืฆื ืื ืึธื ืคึฟืจืขื ืึธืคึผืืืืืืขืจ ืจืขืืขืึทืกืขื ืืื SQL Server ืืขืจืืืึทื ืืืงื ืจืึธืืืึทืคึผืก ืืื ืกืขืจืืืืก ืคึผืึทืงืก
- 6532-6534 - ืื ืงืืืื ืคืึธืจืฉืืขืืื ื ืืืคึผืจืืืืืึทื ืฅ ืคึฟืึทืจ ืงืืืืจืื ืืื ืกืคึผืืืฉืึทื ืืึทืื ืืืืคึผืก
- 8048 - ืงืึทื ืืืขืจืฅ NUMA-ืคึผืึทืจืืืืฉืึทื ื ืืืงืึธืจื ืึทืืืืฉืขืงืฅ ืฆื ืงืคึผื-ืคึผืึทืจืืืืฉืึทื ื ืึธื ืขืก
- 8780 - ืื ืืืืึทืื ื ืึธื ืฆืืื ืึทืืึทืงืืืฉืึทื ืคึฟืึทืจ ืึธื ืคึฟืจืขื ืคึผืืึทื ืืจืื ื. ืขืืืขืืข ืจืืงืืืขืก ืึธื ืืขื ืคืึธื ืงืขื ืืืื ืคืืจืืืืจืคื ืืืืึทื ืืื ืืึธื ื ืื ืืึธืื ืึท ืึธื ืคึฟืจืขื ืคึผืืึทื (ืึท ืืืืขืจ ืืขืืื ืืขืืช)
- 8780 - 9389 - ืื ืืืืึทืื ืึทื ื ืึธื ืืื ืึทืืืฉ ืฆืืึทืืืืืึทืืืง ืืึผืจืื ืืึทืคืขืจ ืคึฟืึทืจ ืคึผืขืงื ืืึธืืข ืึธืคึผืขืจืืืืขืจื, ืึทืืึทืืื ื ืื ืคึผืขืงื ืืึธืืข ืึธืคึผืขืจืึทืืึธืจ ืฆื ืืขืื ื ืึธื ืืึผืจืื ืืื ืืืกืืืืื ืืจืึทื ืกืคืขืจืื ื ืืึทืื ืฆื tempdb ืืืื ื ืึธื ืืึผืจืื ืืื ืื ืืืฆื
ืขืก ืืื ืืืื ื ืืฆืืง ืฆื ืืขืื ืฉืคึผืืจ ืคืึธื 2016 ืืืืืขืจ ืืืขืจืกืืข 2301, ืืืึธืก ืื ืืืืึทืื ืึทืืืึทื ืกืืจืืข ืืึทืฉืืืก ืฉืืืฆื ืึทืคึผืืึทืืึทืืืืฉืึทื ืืื ืึทืืื ืืขืืคึผืก ืฆื ืงืืืึทืื ืืขืกืขืจ ืึธื ืคึฟืจืขื ืคึผืืึทื ื. ืึธืืขืจ, ืืื ื ืืืขืจืกืืข 2016, ืขืก ืึธืคื ืืื ืึท ื ืขืืึทืืืื ืืืืจืงืื ื ืืืืฃ ืืึทื ืฅ ืืึทื ื ืงืืืืขืืืืง ืึธื ืคึฟืจืขื ืืืจืืคืืจืื ื ืฆืืื.
ืืืื, ืคึฟืึทืจ ืกืืกืืขืืขื ืืื ืึท ืคึผืืึทืฅ ืคืื ืื ืืขืงืกืื (ืืืฉื ืคึฟืึทืจ 1C ืืึทืืึทืืืืกืื), ืืื ืจืขืงืึธืืขื ืืืจื ืฆื ืืขืื ืฉืคึผืืจ ืคืึธื 2330, ืืืึธืก ืืืกืืืืึทืื ืืึทืืืื ื ืคืื ืืื ืืขืงืก ืืึทื ืืฅ, ืืืึธืก ืืืื ืืื ืึท positive ืืืืจืงืื ื ืืืืฃ ืื ืกืืกืืขื.
ืืืจ ืงืขื ืขื ืืขืจื ืขื ืืขืจ ืืืขืื ืคืืึทืืก ืืืืฃ ืฉืคึผืืจ
ืคึฟืื ืืขื ืืื ืง ืืืืื, ืขืก ืืื ืืืื ืืืืืืืง ืฆื ืืึทืืจืึทืืื ืื ืืืขืจืกืืขืก ืืื ืืืืขื ืคืื MS SQL Server, ืืื ืคึฟืึทืจ ื ืืึทืขืจ ืืืขืจืกืืขืก, ืขืืืขืืข ืฉืคึผืืจ ืคืืึทืืก ืืขื ืขื ืขื ืืืืึทืื ืืืจื ืคืขืืืงืืึทื ืึธืืขืจ ืืึธืื ืงืืื ืืืืจืงืื ื.
ืืืจ ืงืขื ืขื ืืขืื ืึธืืขืจ ืืืกืืืืึทื ืื ืฉืคึผืืจ ืคืึธื ืืื ืื DBCC TRACEON ืืื DBCC TRACEOFF ืงืึทืืึทื ืื ืจืืกืคึผืขืงืืืืืื. ืืขื ืืขืจ ืืขืืึทืืืก
ืืืจ ืงืขื ืขื ืืึทืงืืืขื ืื ืกืืึทืืืก ืคืื ืฉืคึผืืจ ืคืืึทืืก ื ืืฆื ืื DBCC TRACESTATUS ืืึทืคึฟืขื:
ืืื ืกืืจ ืคึฟืึทืจ ืฉืคึผืืจ ืคืืึทืืก ืฆื ืืืื ืึทืจืืึทื ืืขืจืขืื ื ืืื ืื ืึทืืืึธืกืืึทืจื ืคืื ืื MS SQL Server ืืื ืกื, ืืืจ ืืึทืจืคึฟื ืฆื ืืืื ืฆื SQL Server Configuration Manager ืืื ืืืืื ืื ืฉืคึผืืจ ืคืืึทืืก ืืืจื -T ืืื ืื ืกืขืจืืืืก ืคึผืจืึธืคึผืขืจืืืขืก:
ืจืขืืืืืึทืื ืคืื
ืืขืจ ืึทืจืืืงื ืืืืึทืืึทื ื ืขืืืขืืข ืึทืกืคึผืขืงืฅ ืคืื ืืึธื ืืืึธืจืื ื MS SQL Server, ืืื ืื ืืืืฃ ืคืื ืืืึธืก ืืืจ ืงืขื ืขื ืืขืฉืืืื ื ืืืขื ืืืคืืฆืืจื ืึท ืคืขืื ืคืื ืืึทืจืึทื ืืื ืคืจืื ืงืคึผื ืฆืืื, ืืื ืืขืืื ื ืืื ืึท ื ืืืขืจ ืคืื ืื ืืขืจืข ืืืืื ืืงืขืจ ืงืืึธืจ ืืื ืืขืจ ืืึธื ืคึผืจืึธืืืขืืก. ืื ืืขืจืกื ืึธืคื ืืขื ืืฆื ืฉืคึผืืจ ืคืืึทืืก ืืขื ืขื ืจืืืืืื.
ืงืืืืื:
ยป
ยป
ยป
ยป
ยป
ยป
ยป
ืืงืืจ: www.habr.com