αα»ααααααΆ
ααΆααΉαααΆαα α’αααααααΎααααΆαα α’αααα’αα·αααααα αα·αα’αααααααααααα MS SQL Server DBMS ααααααα»αααΉααααα αΆααααΎαααΆαααααΌαααααΆααα·αααααα α¬ DBMS ααΆααααΌα ααΌα
ααααααΆααααα½ααα·αα·ααα MS SQL Server ααΊααΆαααααααααααΆααααΆααα
α’ααααααααααΊααΆααΆαααααααααΎα’ααααα
ααΎααααΈα±ααααααααΈαααΆααααααααααΎαααΆα α’αααααααΌααααααΎααααααααΆααα inf αα
αααα»αααΌαααααΆααα·αααααααααα
ααααΆαααΌα
ααΆααααααα
ααΆααααααΎααααααααΆααα inf
use <ΠΈΠΌΡ_ΠΠ>;
go
create schema inf;
αα·ααΈααΆααααααααα»αααΆαααααΎαααααα RAM
ααΌα
ααΆααααααΌαααααααα RAM ααΊαα
ααααααα§ααΆα ααααα MS SQL Server αααΈ RAM ααΆααα’αααααααΆαααααα»ααα»ααααααΆααααΆα
ααΎααααΈααααΎααΌα
ααααααααΎαααααΆαααΆαααααα inf.vRAMα
ααΆααααααΎααα·αααααΆα 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 ααΆαα·α
αα
ααα α’αααααααΌααα·αα·αααααΎααααα·αα·αααα
αΆαα
ααΎααααΈαααααααΆαααααααΆααα RAM ααΆαααααααα·αα·αααα
αΆα α
αΌαααΎααααααΎααα·αααααΆα inf.vWaitsα
ααΆααααααΎααα·αααααΆα 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];
αααα»αααααΈαααα’αααα’αΆα αααααααααα RAM αααααααΎαααα½αααΆααααααα
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
αα
ααΈαααα’αααααααΌαααα
α·ααααα»αααΆααααΎααΌα
ααΆααααΆααα αα·α AvgWait_S α ααααα·αααΎαα½αααΆααΆαααΆααααααΆαααα
αααα»αα
ααα½αααα»ααααααα½ααα αααααΆαααααΌααΆαααΈααΈαααααααααΆαααααααΆ MS SQL Server instance αα·αααΆα RAM αααααααααΆααα αααααααααΆαααααααΌαααΆααααααααΆαααααααα»αααααααααΆααααααααααααΈαα½ααα ααααααΆαααΆααααα α’αααα’αΆα
α
αΆααααααΎαααΆαα½αααΉαααΌα
ααΆααααΆααααααα ααΆααα>=1 αα·α AvgWait_S>=0.005 α
ααΎααααΈαααα
ααααΌα
ααΆαααα
αααααααααααα½ααα·αα·ααα (α§ααΆα ααα Zabbix) α’αααα’αΆα
αααααΎααααα½αααΈαααΆααααααα
- ααΎα’αααΈαα
ααΆααΆααααααααααααααα
αΆααααααΆαα RAM (ααααΌααααααΆαααααααααααα
αΆαααΆααα’αα)α
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' );
- ααΎαααααααααα
αΆα RAM ααα»ααααΆαααααΌαα
αααΆαααααα·αααΆαα·ααααΈαα·ααΆααΈ (αααααα’αα·ααααΆααααΆααααααΆαααααΆαααααααΆααα’αααααααΆαααααααααααα
αΆαααΆααα’αα)α
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' );
αααααα’ααααΎαααααΆαα»ααααααααααααααα½αααΆααααααΆααααΌα ααΆααααΆααααΈαααα ααΎαα’αΆα ααααα·ααααΆαααΆααΎααΆα RAM αααααααααΆαααααααΆαα 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 Server αααα¬ααα
αααααΆαααΆαααΆααααα ααΆααΆααΏαααααΆαααααα»αααΆαα
αα
αΆαααΌαααΆααα·ααααααΆ αααα½ααααα½αα―αα’αΆα
αα½ααααααααα‘αΆαααΆα
αααΎααααα»αααααααα½αα α αΎαααααααααααααα·ααΈαααααΎαααααα·αααααΆααα·αα’αΆα
αααΆααααααΆαααΆαααααΉαααααΌαααΌαααΆααααα»αααααΆααααααα½αααααααα½αα―ααααααα αααααΆααααααααΎα’αΆα
ααααΌαααΆααααα
αααααααααα‘αΆαα
αααΎαααα ααααα
αααααααααα·αα’αΆα
ααααΎαααΆααααα»ααααααααΆαααααΆααΆαααα α αΎαααααααααααΆαα±ααααααααααααΆααααα
αΆααααααααΌαααΆαααααΆαααααΆααααΆαα½αααΉαααΆαααααααΆααααααααΆααααΎαααΆα α αΎαααΆαααΎαα‘αΎααααα½ααααααΆααα’ααααααααααααααααααΎααααΌααααΈααΈααΌααΆααααΆαα αααααΊααΆααΌα
ααΆαα runnable_tasks_count ααΉαααΎαα‘αΎααα
αααα»αααααααααααααααα
αααα»αααααΈααα αα»αααΉααααααΎαα
ααα½α CPU cores α’αααααααΌαααααααα
ααΆαααααααα parallelism αααΆαααααΉαααααΌααα instance MS SQL Server αααα½αααΆ α αΎαααΈ version 2016 ααααααα
ααΆαααααααα parallelism αααΆαααααΉαααααΌααα databases αααααααΌαααΆαα
αα
ααΈαααα’ααααα½αααα
α·ααααα»αααΆααααΎαααΆαααΆααααααααΌα
ααΆαααααα:
- Max Degree of Parallelismβαααααα ααα½αα’αα·ααα·ααΆαααααααααα‘αΆααααα’αΆα ααααΌαααΆααααα αααα ααΆαααααΎααΈαα½αα (ααααΆαααΎαααΊ 0βααααααααααααααααααααα·ααααα·ααΆαααααΆαα αα·αααΆαααααα»ααααααα MS SQL Server)
- ααααα·αα αααΆααααααΆααααΆαααααααααΆ - ααααααααΆααααααΆαααααΆαααααααααΆ (ααααΆαααΎαααΊ 5)
- Max DOP - αααααα ααα½αα’αα·ααααΆαααααααααα‘αΆααααα’αΆα ααααΌαααΆαααααα»ααα»ααααααΆαααααα½αααΈαα½αααα ααααα·αααΌαααααΆααα·αααααα (ααα»αααααα·αααΎαααΈααααααααααααααααααααα· "αααααΆααααα’αα·ααααΆααααΆαααααααααΆ") (ααΆαααααΆαααΎαααΆααΊ 0 - ααααααααααααααααααααα·ααααα·ααΆαααααα»αααααα αααα½αααΆααααΆαα αα·αααΆαααααα»ααααααα MS SQL Server ααααΌα ααΆααΆααααααααΎαααααααααααααα· "ααααα·αα’αα·ααααΆααααΆαααααααααΆ" αα MS SQL Server ααΆααααΌα)
ααΆαα·αα’αΆα
αα
αα½α
αααααα»αααΆααααααααΌαααΌαααααααααα’ααααΎααααΆαααααΆαααααααααααΈααΆααα’αα αααααΊα’αααααααΌααα·ααΆααααα½ααα·ααΆααα
αααααα’ααααΎαααα·αααααααααΆαααααα½ααααααααα»α αααα»αααΌαααααΆααααα½ααααααααΆαααΆαααααααααααααααΆααααααΆαααααααααα OLTP ααΎααααΈααααααα
ααΆααααααααααΆαααααααααΆα
- ααΈαα½ααα·αααΆαααααααααΆαααααααα Max Degree of Parallelism αα 1 αα ααααα·αααα§ααΆα αααααΆααααΌα
- αα·ααΆαβαααα½αβαααβαααααβαααα»α α αΎαβααααΎαβα ααα½αβααααβαααα‘αΆαβααβααα’βαααα»αβαααααΆααβαα½αααα
- ααααααααα·αα’αα·ααα·ααΆααααΆαααααααααΆαα ααΉαα ααα½ααααααααα‘αΆαααα’αααα»ααααααΆαααααΎαααΎαααΈααα αΆαααΈ 2 α αΎααααααΆααααΌαααααΆααα·ααααααααΆααααΆαααααααααααα DOP α’αα·ααααΆαααααα½αααΆαααΈααα αΆαααΈ 2 αααααΆααααΌαααααΆααα·ααααααααΈαα½ααα
- αα·ααΆααααα½ααααααααααααα»α αα·ααααααααΈα₯αααα·ααα’αα·αααααΆααα multithreading α ααααα·αααΎααΆααΊ αααααααΌααααααΎαααααα·αα
αααΆααααααΆααααΆαααααααααΆα
αααααΆααααααααααααΌα ααΆ 1C, Microsoft CRM αα·α Microsoft NAV αααα»αααααΈααΆαα αααΎαααΆαα αΆαααΆααααΆααααα αΌααα α»ααααααΊαααααα
ααΌα
ααααΆααααααααααααα·αααΎα’αααααΆα Standard edition ααααααα»αααααΈααΆαα
αααΎαααΆαα αΆαααααΆαααΎ multi-threading ααΊαααααααααααΆαααααΆααα·ααααααΆααΆαααααα»ααααααααααΌαααΆαααααααααα»αα
ααα½α CPU cores α
αααα½ααααααααΆααααααΆααα·αααααΆααΆαααΎααΊαα·ααααααααααααΆαααααααααα OLAP ααα
αααααα’ααααΎαααα·αααααααααΆαααααα½ααααααααα»α αααα»αααΌαααααΆααααα½ααααααααΆαααΆαααααααααααααααΆααααααΆαααααααααα OLAP ααΎααααΈααααααα
ααΆααααααααααΆαααααααααΆα
- αα·ααΆαβαααα½αβαααβαααααβαααα»α α αΎαβααααΎαβα ααα½αβααααβαααα‘αΆαβααβααα’βαααα»αβαααααΆααβαα½αααα
- ααααααααα·αα’αα·ααα·ααΆααααΆαααααααααΆαα ααΉαα ααα½ααααααααα‘αΆαααα’αααα»ααααααΆαααααΎαααΎαααΈααα αΆαααΈ 1 α αΎααααααΆααααΌαααααΆααα·ααααααααΆααααΆαααααααααααα DOP α’αα·ααααΆαααααα½αααΆαααΈααα αΆαααΈ 1 αααααΆααααΌαααααΆααα·ααααααααΈαα½ααα
- αα·ααΆααααα½ααααααααααααα»α αα·ααααααααΈα₯αααα·ααα’αα·αααααΆαααααΆααααααααΌαα·ααααααα ααααα·αααΎααΆααΊ ααααααααααααα·αα αααΆααααααΆααααααααααΆαααΆα‘αα α¬ααααΎαααααααααα αΆα 1-2 αααααα½ααααααααΆαααα
αααααΊαααααΆαααααααααα OLTP ααΎααα
ααΈαααααααα½ααα
αα α»αααα α αΎααααααΆαααααααααα OLAP αααα»ααα
αα·α ααΎααα
ααΈαααααα α»αααααα
αααααααα½αα αα·ααΈαααα’αααα’αΆα
ααααΎαααΎαααΆααααααααΆαααααααααΆααααα’αααααΎαααΆαααααααΆααααΌαααααΆααα·ααααααααΆααααΆαα αα·ααααααΆααα§ααΆα ααα MS SQL Server ααΆααααΌαα
ααΆααααααΆααααααααααα»αααΆααααααΆααΆααααααααααααααααααα·ααααααααΆααααΌαααααΆααααααΌαααΆααααααααΆ αααααα’ααααΎααααααααααΆααααα½ααα·αα·αααααααΎαααΆααααα MS SQL Server α
ααΆαααααΆααααααΆααααααααααααΆα
ααΈαααα·αααααααααΆαααααα½ααααααααα»α αα·ααααα·ααααααααααα αααα·ααααααααα»α αααααΆααααααΎαααΆαααααα’αααααΎα αααα»αααΌαααααΆαα±ααααααααααααΆαααΆαααααααα ααααα·αααααΎαααΆαααααΆαααα MS SQL Server αααααΆαααααα 2008-2016α
- 610 - ααΆαααααααααΆααααααααΆααΆααααα αΌααα αααα»αααΆααΆααααααΆαααααΎαα·αα·ααααα α’αΆα αα½ααααα»αααΆααααα αΌααα αααα»αααΆααΆαααΆαα½αααΉαα ααα½αααα αααΎααααααααααααΆ αα·αααααα·ααααα·ααΆαααΆα αααΎα ααΆαα½αααΉα WRITELOG ααΌαααΆααΉαααΆαααααα αΆαααΆαααααΆααααααΌααα αααα»ααα·αα·αααα
- 1117 - ααααα·αααΎα―αααΆααα αααα»ααααα»αα―αααΆαααααΌαααΉαααααα·αααΌαααΆαααααααααααααααααα· α―αααΆαααΆααα’αααα αααα»ααααα»αα―αααΆαααααΌαααΆααααααΈα
- 1118 - αααααα±ααααααα»ααΆααα’ααααΆαααΈααΆαααα αααα»ααα·ααΆαααΆαααααααααααΆ (αα·αα’αα»ααααΆααα·ααΆαααΆαα αααα»α) αααααΆααααααααααααΌαααΆααααα»αααΆααααααααααα SGAM αααααααΌαααΆαααααΎααΎααααΈααΆαααΆααα·ααΆαααΆαα αααα»α
- 1224 - αα·αααααΎαααΆααααααΎαα‘αΎααααααα’ααααΎα ααα½αααα αααααΆαααΆαααΆααααα ααΆαααααΎααααΆααα’αααα αα αΆαα αααΎααααα’αΆα ααΎαααααΎαααΆαα αΆαααα
- 2371 - ααααΆααααααΌαααααα·ααααααααΆαααααΎαα αα α»ααααααααΆααααα·αα·ααααααααααααα·ααααα ααααα·ααααααααΆαααααΎαα αα α»ααααααααΆααααα·αα·ααααααααααααα·ααΆαααααα ααααΆαααααααΆααααΆαααααΎαα αα α»ααααααααΆααααααΆααααα½ααα ααΎααΆααΆααα αααααΆααααααα ααα½ααααααααααΆαα·αααααΉαααααΌαααΆαα±αααααααΆαααααα·ααααα·αα»α
- 3226 - ααΆααΆααααΆαααααααααααΆαααααα»ααα»ααα αααα»ααααααα ααα»ααα α»α
- 4199 - αα½ααααα αΌαααΆαααααΆααααααΌααα ααΎαααααα·ααΈαααααΎαααααα·αααααΆααααα½ααααααΆαα ααααααΆααα αααα»α SQL Server rollups αα·ααααα ααααααΆαααα
- 6532-6534 - αα½ααααα αΌαααΆααααααα’ααΆαα’αα»αααααααααΆαααααα½ααααααΆααααααααα·ααααααααα α
- 8048 - αααααααααααα»α’αααα αα αΆααααααΆααααα αα NUMA αα ααΆααααα»αααααΆαααΆααααα αααααΈααΈααΌ
- 8780 - ααΎαααΆααααα αααααααααΆαααααααααααΆααααΆαααααΎαααααΆααααα½αα ααααΎαα½αα ααα½ααααααααΆαααααααα’αΆα ααααΌαααΆαα αααΆαα αα αααααΆααα½ααααα·αααΆααααααααααα½α (ααΆααα α»ααααααααα½α)
- 8780 - 9389 - ααΎαααααΎαααΆαα’αααα αα αΆαααααααα’αΆααααααααααααΆααααααααααΆααααααα·ααααα·ααααααααΆα α αααα’αα»ααααΆαα±ααααααα·ααααα·ααααααααΆα αααααΎαα»αα’αααα αα αΆααααααα αα·ααααααΆαααΆαααααααα·αααααααα tempdb ααααα·αααΎααΆαα’αααα αα αΆααααααα
ααΆααααΆαααααααααααααααααα»αααΆαααΎα trace flag 2016 αα»αααααααα 2301 αααα’αΆα
α±ααααΆααααααΎαααααα·αααααΆαααΆααααααΆααααααα
α
α·αααααααα·αααααα α αΎαααΌα
ααααααΆαα½ααααα»αααΆαααααΎαααΎααααααΆααααα½ααααααΎαααΆααα»αα ααααααΆαααΆααααα α
αΆααααΆααααΈαααα 2016 αα ααΆααΏααααΆααα₯αααα·ααα’αα·αααααΆααα
ααΎαααααααΆααααΎαααΆααααα½αααΆααααΌαα
ααΌα
ααααΆαααααααα αααααΆαααααααααααααααΆααα·αα·ααααα
αααΎα (α§ααΆα ααα αααααΆααααΌαααααΆααα·αααααα 1C) αααα»αααΌαααααΆαα±ααααΎαααααΎαααΆα trace flag 2330 ααααα·αααΆααααααΌαααααΆαααααΎααααΆαααα·αα·αααα αααααΆααΌαα
ααΆαα₯αααα·αααα·αααααΆαααΎααααααααα
α’αααα’αΆα
ααααααααααααααα’αααΈαααααΆα
ααΈαααααΆαααΎ ααΆααααααΆααααααααααα»αααΆααα·α
αΆαααΆααΎαααα αα·αααΆααααααΎααααα MS SQL Server ααΌα
ααΆαααααΆααααααααααΈααΆαααα αααααΆααα½αα
ααα½αααααΌαααΆαααΎαααΆαααααΆαααΎα α¬ααααΆαααααα·αααααΆαα
α’αααα’αΆα
ααΎα α¬αα·ααααααΆααααααααΎααΆααααααααΆ DBCC TRACEON αα·α DBCC TRACEOFF αααααααΆα ααΎαααααααΆααααα’α·ααααααα
α’αααα’αΆα
ααα½αααΆαααααΆαααΆααααααααΆααααααααΎααΆααααααααΆ DBCC TRACESTATUSα
ααΎααααΈα±αααααααΆαααααΌαααΆαααΆαααααα
αΌααααα»αααΆαα
αΆααααααΎαααααααααααααα·ααααααΆαααα MS SQL Server α’αααααααΌαα
αΌααα
ααΆαα SQL Server Configuration Manager α αΎααααααααααααΆαααΆαααααααΆαααα -T αα
αααα»ααααααααααααααα·ααααΆααααα
αααααα
α’ααααααααααΆααα·αα·ααααα·αααααΆααα½αα ααα½αααααΆααααα½ααα·αα·ααα MS SQL Server αααααΆααααα½ααααα’αααα’αΆα αααααα’ααααααααΆαααααα RAM αα·ααααααααΆαααΈααΈααΌαααα₯ααα·ααααα ααααααΆαααααα αΆαα½αα ααα½αααααααααααααα·αααΌαα αααΆααα αααααΆααααααααΎααΆααΌαα αααα»αααααΌαααΆααα·αα·αααα
ααααα:
Β»
Β»
Β»
Β»
Β»
Β»
Β»
ααααα: www.habr.com