ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Qee yam ntawm MS SQL Server saib xyuas. Cov Lus Qhia rau Kev Teeb Tsa Cov chij

Qee yam ntawm MS SQL Server saib xyuas. Cov Lus Qhia rau Kev Teeb Tsa Cov chij

Lus Qhia Tshab

Feem ntau, cov neeg siv, cov tsim tawm thiab cov thawj coj ntawm MS SQL Server DBMS ntsib teeb meem kev ua haujlwm ntawm cov ntaub ntawv lossis DBMS tag nrho, yog li MS SQL Server kev saib xyuas yog qhov tseem ceeb heev.
Kab lus no yog ib qho ntxiv rau tsab xov xwm Siv Zabbix los saib xyuas MS SQL Server Database thiab nws yuav npog qee yam ntawm kev saib xyuas MS SQL Server, tshwj xeeb: yuav ua li cas txiav txim siab sai npaum li cas cov peev txheej uas ploj lawm, nrog rau cov lus pom zoo rau kev teeb tsa kab cim.
Rau cov ntawv sau hauv qab no ua haujlwm, koj yuav tsum tsim ib qho inf schema hauv cov ntaub ntawv xav tau raws li hauv qab no:
Tsim ib qho inf schema

use <имя_Π‘Π”>;
go
create schema inf;

Txoj kev los txheeb xyuas qhov tsis muaj RAM

Thawj qhov qhia txog qhov tsis muaj RAM yog qhov xwm txheej thaum piv txwv ntawm MS SQL Server noj tag nrho cov RAM faib rau nws.
Txhawm rau ua qhov no, peb yuav tsim cov qauv hauv qab no ntawm inf.vRAM:
Tsim qhov inf.vRAM saib

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;

Tom qab ntawd koj tuaj yeem txiav txim siab tias qhov piv txwv ntawm MS SQL Server siv tag nrho cov cim xeeb faib rau nws los ntawm cov lus nug hauv qab no:

select  SQL_server_physical_memory_in_use_Mb,  SQL_server_committed_target_Mb
from [inf].[vRAM];

Yog tias SQL_server_physical_memory_in_use_Mb tsis tu ncua ntau dua lossis sib npaug rau SQL_server_committed_target_Mb, ces cov txheeb cais tos yuav tsum tau kuaj xyuas.
Txhawm rau txiav txim siab qhov tsis muaj RAM los ntawm kev tos cov txheeb cais, cia peb tsim qhov inf.vWaits saib:
Tsim cov 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];

Hauv qhov no, koj tuaj yeem txiav txim siab qhov tsis muaj RAM nrog cov lus nug hauv qab no:

SELECT [Percentage]
      ,[AvgWait_S]
  FROM [inf].[vWaits]
  where [WaitType] in (
    'PAGEIOLATCH_XX',
    'RESOURCE_SEMAPHORE',
    'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Ntawm no koj yuav tsum tau them sai sai rau qhov ntsuas feem pua ​​thiab AvgWait_S. Yog tias lawv tseem ceeb hauv lawv qhov tag nrho, ces muaj qhov tshwm sim siab heev uas tsis muaj RAM txaus rau MS SQL Server piv txwv. Cov txiaj ntsig tseem ceeb yog txiav txim siab tus kheej rau txhua qhov system. Txawm li cas los xij, koj tuaj yeem pib nrog cov hauv qab no: Feem pua>=1 thiab AvgWait_S>=0.005.
Txhawm rau tso tawm cov ntsuas mus rau kev saib xyuas (piv txwv li, Zabbix), koj tuaj yeem tsim ob cov lus nug hauv qab no:

  1. Muaj pes tsawg hom tos yog nyob ntawm RAM hauv feem pua ​​(tus lej ntawm txhua hom kev tos): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. Ntau npaum li cas RAM tos hom siv nyob rau hauv milliseconds (tus nqi siab tshaj plaws ntawm txhua qhov nruab nrab ncua sij hawm rau txhua hom tos): 
    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'
  );

Raws li lub zog ntawm qhov tau txais txiaj ntsig rau ob qhov ntsuas no, peb tuaj yeem xaus seb puas muaj RAM txaus rau qhov piv txwv ntawm MS SQL Server.

CPU Overload Detection Method

Txhawm rau txheeb xyuas qhov tsis muaj lub sijhawm processor, nws txaus los siv sys.dm_os_schedulers system saib. Ntawm no, yog tias runnable_tasks_count tas li ntau dua 1, ces muaj qhov tshwm sim siab tias tus lej ntawm cov cores tsis txaus rau MS SQL Server piv txwv.
Txhawm rau tso tawm qhov ntsuas mus rau qhov kev saib xyuas (piv txwv li, Zabbix), koj tuaj yeem tsim cov lus nug hauv qab no:

select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;

Raws li qhov kev hloov pauv ntawm qhov tau txais txiaj ntsig rau qhov ntsuas no, peb tuaj yeem txiav txim siab seb puas muaj lub sijhawm ua haujlwm txaus (tus naj npawb ntawm CPU cores) piv txwv li MS SQL Server.
Txawm li cas los xij, nws yog ib qho tseem ceeb uas yuav tsum nco ntsoov qhov tseeb tias kev thov lawv tus kheej tuaj yeem thov ntau cov xov ib zaug. Thiab qee zaum tus optimizer tsis tuaj yeem kwv yees qhov nyuaj ntawm cov lus nug nws tus kheej. Tom qab ntawd qhov kev thov yuav raug faib ntau cov xov uas tsis tuaj yeem ua tiav tib lub sijhawm ntawm lub sijhawm. Thiab qhov no kuj ua rau hom kev tos cuam tshuam nrog lub sijhawm tsis muaj lub sijhawm ua haujlwm, thiab kev loj hlob ntawm cov kab rau cov sijhawm teem sijhawm uas siv cov CPU cores tshwj xeeb, piv txwv li qhov ntsuas runnable_tasks_count yuav loj hlob hauv cov xwm txheej zoo li no.
Nyob rau hauv rooj plaub no, ua ntej nce tus naj npawb ntawm CPU cores, nws yog ib qho tsim nyog yuav tsum tau teeb tsa cov khoom sib luag ntawm MS SQL Server piv txwv nws tus kheej, thiab los ntawm 2016 version, kho kom raug cov khoom sib luag ntawm cov ntaub ntawv xav tau:
Qee yam ntawm MS SQL Server saib xyuas. Cov Lus Qhia rau Kev Teeb Tsa Cov chij

Qee yam ntawm MS SQL Server saib xyuas. Cov Lus Qhia rau Kev Teeb Tsa Cov chij
Ntawm no koj yuav tsum xyuam xim rau cov nram qab no tsis:

  1. Max Degree ntawm Parallelism - teeb tsa qhov siab tshaj plaws ntawm cov xov uas tuaj yeem faib rau txhua qhov kev thov (lub neej ntawd yog 0 - txwv tsuas yog los ntawm kev ua haujlwm ntawm nws tus kheej thiab ib tsab ntawm MS SQL Server)
  2. Tus nqi pib rau Parallelism - kwv yees tus nqi ntawm parallelism (default yog 5)
  3. Max DOP - teeb tsa qhov siab tshaj plaws ntawm cov xov uas tuaj yeem faib rau txhua qhov lus nug ntawm qib database (tab sis tsis ntau tshaj tus nqi ntawm "Max Degree of Parallelism" khoom) (default yog 0 - txwv tsuas yog los ntawm kev khiav hauj lwm nws tus kheej thiab ib tsab ntawm MS SQL Server, nrog rau kev txwv ntawm "Max Degree of Parallelism" cov cuab yeej ntawm tag nrho cov piv txwv ntawm MS SQL Server)

Ntawm no nws tsis yooj yim sua kom muab ib daim ntawv qhia zoo sib npaug rau txhua kis, piv txwv li koj yuav tsum tau tshuaj xyuas cov lus nug hnyav.
Los ntawm kuv tus kheej qhov kev paub dhau los, kuv pom zoo cov txheej txheem hauv qab no ntawm kev ua rau OLTP systems rau kev teeb tsa cov khoom sib luag:

  1. thawj lov tes taw parallelism los ntawm kev teeb tsa qhov dav dav Max Degree ntawm Parallelism rau 1
  2. txheeb xyuas qhov kev thov hnyav tshaj plaws thiab xaiv cov xov tooj zoo tshaj plaws rau lawv
  3. teeb tsa Max Degree ntawm Parallelism rau qhov xaiv qhov zoo tshaj plaws ntawm cov xov tau los ntawm kauj ruam 2, thiab rau cov ntaub ntawv tshwj xeeb teeb tsa Max DOP tus nqi tau los ntawm kauj ruam 2 rau txhua qhov database
  4. txheeb xyuas qhov kev thov hnyav tshaj plaws thiab txheeb xyuas qhov tsis zoo ntawm multithreading. Yog hais tias nws yog, ces nce tus nqi pib rau Parallelism.
    Rau cov tshuab xws li 1C, Microsoft CRM thiab Microsoft NAV, feem ntau, txwv tsis pub sib txuas lus yog tsim nyog.

Tsis tas li ntawd, yog tias muaj ib tsab ntawv Standard, ces feem ntau qhov kev txwv tsis pub muaj ntau txoj xov zoo yog qhov tsim nyog vim qhov tseeb tias tsab ntawv no txwv tsis pub muaj cov CPU cores.
Rau OLAP systems, algorithm tau piav saum toj no tsis haum.
Los ntawm kuv tus kheej kev paub dhau los, kuv pom zoo kom ua raws li cov txheej txheem hauv qab no ntawm kev ua rau OLAP cov txheej txheem rau kev teeb tsa cov khoom sib luag:

  1. txheeb xyuas qhov kev thov hnyav tshaj plaws thiab xaiv cov xov tooj zoo tshaj plaws rau lawv
  2. teeb tsa Max Degree ntawm Parallelism rau qhov xaiv qhov zoo tshaj plaws ntawm cov xov tau los ntawm kauj ruam 1, thiab rau cov ntaub ntawv tshwj xeeb teeb tsa Max DOP tus nqi tau los ntawm kauj ruam 1 rau txhua qhov database
  3. txheeb xyuas cov lus nug hnyav tshaj plaws thiab txheeb xyuas qhov cuam tshuam tsis zoo ntawm kev txwv qhov sib xws. Yog tias nws yog, ces tus nqi qis dua rau Parallelism tus nqi, lossis rov ua cov kauj ruam 1-2 ntawm no algorithm

Ntawd yog, rau OLTP systems peb mus los ntawm ib leeg-xov mus rau ntau txoj xov, thiab rau OLAP-systems, ntawm qhov tsis sib xws, peb mus ntawm ntau txoj xov mus rau ib txoj xov. Yog li, koj tuaj yeem xaiv qhov zoo tshaj plaws parallelism nqis rau ob qho tib si database tshwj xeeb thiab tag nrho cov piv txwv ntawm MS SQL Server.
Nws tseem yog ib qho tseem ceeb kom nkag siab tias cov kev teeb tsa ntawm cov khoom sib luag yuav tsum tau hloov pauv lub sijhawm, raws li cov txiaj ntsig ntawm kev saib xyuas kev ua haujlwm ntawm MS SQL Server.

Cov Lus Qhia rau Kev Teeb Tsa Cov chij

Los ntawm kuv tus kheej kev paub thiab kev paub dhau los ntawm kuv cov npoj yaig, kom ua tau zoo, Kuv pom zoo kom teeb tsa cov kab cim hauv qab no ntawm qib khiav ntawm MS SQL Server kev pabcuam rau versions 2008-2016:

  1. 610 - Txo kev nkag ntawm cov ntawv ntxig rau hauv cov rooj indexed. tuaj yeem pab nrog ntxig rau hauv cov ntxhuav nrog ntau cov ntaub ntawv thiab ntau qhov kev lag luam, nrog rau ntev ntev WRITELOG tos rau kev hloov pauv hauv indexes
  2. 1117 - Yog hais tias ib cov ntaub ntawv nyob rau hauv ib pab pawg neeg ua tau raws li qhov yuav tsum tau autogrowth threshold, tag nrho cov ntaub ntawv nyob rau hauv lub filegroup loj hlob
  3. 1118 - quab yuam txhua yam khoom nyob rau hauv ntau qhov sib txawv (kev txwv tsis pub sib xyaw), uas txo qis qhov yuav tsum tau luam theej duab SGAM nplooj ntawv, uas yog siv los taug qab cov sib xyaw ua ke
  4. 1224 - Disables xauv escalation raws li tus naj npawb ntawm cov xauv. Txawm li cas los xij, kev siv lub cim xeeb ntau dhau tuaj yeem ua rau lub xauv nrawm nrawm
  5. 2371 - Hloov kho qhov tsis siv neeg txheeb cais hloov tshiab pib mus rau qhov hloov tshiab tsis siv neeg txheeb cais hloov tshiab. Ib qho tseem ceeb rau kev hloov kho cov lus nug rau cov rooj loj, qhov twg cov ntaub ntawv suav tsis raug ua rau cov phiaj xwm ua tsis raug
  6. 3226 - Tshem tawm cov ntaub ntawv ua tiav hauv kev ua yuam kev
  7. 4199 - suav nrog kev hloov pauv rau cov lus nug optimizer tso tawm hauv CUs thiab SQL Server Service Packs
  8. 6532-6534 - suav nrog kev txhim kho kev ua haujlwm rau cov lus nug ua haujlwm ntawm cov ntaub ntawv spatial
  9. 8048 - Hloov NUMA partitioned nco khoom rau CPU muab faib sawv daws yuav
  10. 8780 - Ua kom muaj kev faib sijhawm ntxiv rau kev npaj nug. Qee qhov kev thov yam tsis muaj tus chij no yuav raug tsis lees paub vim tias lawv tsis muaj cov lus nug (cov kab mob tsawg heev)
  11. 8780 - 9389 - Ua kom muaj peev xwm ntxiv dynamic grant memory buffer rau batch hom nqe lus, uas tso cai rau tus neeg teb xov tooj batch hom thov kev nco ntxiv thiab tsis txhob txav cov ntaub ntawv mus rau tempdb yog tias muaj kev nco ntxiv

Tsis tas li ntawd ua ntej xyoo 2016, nws yog ib qho tseem ceeb los pab kom cov kab cim 2301, uas ua rau muaj kev txhawb nqa kev txiav txim siab zoo thiab yog li pab xaiv cov lus nug kom raug. Txawm li cas los xij, raws li version 2016, nws feem ntau muaj qhov cuam tshuam tsis zoo rau tag nrho cov lus nug ntev ntev.
Tsis tas li ntawd, rau cov tshuab uas muaj ntau qhov ntsuas (piv txwv li, rau 1C databases), Kuv pom zoo kom ua kom cov kab cim 2330, uas cuam tshuam kev sau cov kev ntsuas kev siv, uas feem ntau muaj txiaj ntsig zoo rau lub cev.
Yog xav paub ntxiv txog tus chij kab, saib no
Los ntawm qhov txuas saum toj no, nws tseem ceeb heev uas yuav tsum xav txog cov qauv thiab tsim ntawm MS SQL Server, raws li cov tshiab versions, qee cov kab cim tau qhib los ntawm lub neej ntawd lossis tsis muaj txiaj ntsig.
Koj tuaj yeem tig tus chij kab rau thiab tawm nrog DBCC TRACEON thiab DBCC TRACEOFF cov lus txib, feem. Yog xav paub ntxiv mus saib no
Koj tuaj yeem tau txais cov xwm txheej ntawm cov cim cim siv DBCC TRACESTATUS hais kom ua: ntau info
Txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau suav nrog autostart ntawm MS SQL Server kev pabcuam, koj yuav tsum mus rau SQL Server Configuration Manager thiab ntxiv cov kab cim ntawm -T hauv cov khoom pabcuam:
Qee yam ntawm MS SQL Server saib xyuas. Cov Lus Qhia rau Kev Teeb Tsa Cov chij

Cov txiaj ntsim tau los

Hauv tsab xov xwm no, qee yam ntawm kev saib xyuas MS SQL Server tau txheeb xyuas, nrog kev pab cuam uas koj tuaj yeem txheeb xyuas qhov tsis muaj RAM thiab lub sijhawm pub dawb CPU, nrog rau ntau yam teeb meem tsis tshua pom tseeb. Cov kab uas siv ntau tshaj plaws tau raug tshuaj xyuas.

Qhov chaw:

Β» SQL Server tos cov txheeb cais
Β» SQL Server tos cov txheeb cais lossis thov qhia kuv qhov twg nws mob
Β» System saib sys.dm_os_schedulers
Β» Siv Zabbix los saib xyuas MS SQL Server Database
Β» SQL Lub neej
Β» Trace Chiv
Β» sql ua

Tau qhov twg los: www.hab.com

Ntxiv ib saib