lời tựa
Khá thường xuyên, người dùng, nhà phát triển và quản trị viên của MS SQL Server DBMS gặp phải các sự cố về hiệu suất của cơ sở dữ liệu hoặc toàn bộ DBMS, vì vậy việc giám sát MS SQL Server là rất phù hợp.
Bài viết này là một bổ sung cho bài viết và nó sẽ đề cập đến một số khía cạnh của việc giám sát MS SQL Server, cụ thể là: cách nhanh chóng xác định tài nguyên nào bị thiếu, cũng như các đề xuất để đặt cờ theo dõi.
Để các tập lệnh sau hoạt động, bạn cần tạo một lược đồ inf trong cơ sở dữ liệu mong muốn như sau:
Tạo một lược đồ inf
use <имя_БД>;
go
create schema inf;
Phương pháp phát hiện thiếu RAM
Chỉ báo đầu tiên về việc thiếu RAM là trường hợp một phiên bản MS SQL Server ngốn hết RAM được phân bổ cho nó.
Để làm điều này, chúng tôi sẽ tạo đại diện sau của inf.vRAM:
Tạo chế độ xem 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;
Sau đó, bạn có thể xác định rằng một phiên bản của MS SQL Server tiêu thụ tất cả bộ nhớ được phân bổ cho nó bằng truy vấn sau:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Nếu SQL_server_physical_memory_in_use_Mb luôn lớn hơn hoặc bằng SQL_server_commissed_target_Mb, thì nên kiểm tra số liệu thống kê về thời gian chờ.
Để xác định tình trạng thiếu RAM thông qua số liệu thống kê về thời gian chờ, hãy tạo chế độ xem inf.vWaits:
Tạo chế độ xem 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];
Trong trường hợp này, bạn có thể xác định tình trạng thiếu RAM bằng truy vấn sau:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Ở đây bạn cần chú ý đến các chỉ số Tỷ lệ phần trăm và AvgWait_S. Nếu chúng có ý nghĩa tổng thể, thì có khả năng rất cao là không có đủ RAM cho phiên bản MS SQL Server. Các giá trị đáng kể được xác định riêng cho từng hệ thống. Tuy nhiên, bạn có thể bắt đầu với những điều sau: Percentage>=1 và AvgWait_S>=0.005.
Để xuất các chỉ số sang một hệ thống giám sát (ví dụ: Zabbix), bạn có thể tạo hai truy vấn sau:
- RAM chiếm bao nhiêu loại thời gian chờ theo tỷ lệ phần trăm (tổng của tất cả các loại thời gian chờ đó):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - có bao nhiêu loại thời gian chờ RAM mất tính bằng mili giây (giá trị tối đa của tất cả độ trễ trung bình cho tất cả các loại thời gian chờ đó):
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' );
Dựa trên tính năng động của các giá trị thu được cho hai chỉ báo này, chúng tôi có thể kết luận liệu có đủ RAM cho một phiên bản MS SQL Server hay không.
Phương pháp phát hiện quá tải CPU
Để xác định việc thiếu thời gian của bộ xử lý, chỉ cần sử dụng chế độ xem hệ thống sys.dm_os_schedulers là đủ. Ở đây, nếu runnable_tasks_count liên tục lớn hơn 1, thì có khả năng cao là số lượng lõi không đủ cho phiên bản MS SQL Server.
Để xuất chỉ báo cho hệ thống giám sát (ví dụ: Zabbix), bạn có thể tạo truy vấn sau:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Dựa trên tính năng động của các giá trị thu được cho chỉ báo này, chúng tôi có thể kết luận liệu có đủ thời gian xử lý (số lượng lõi CPU) cho một phiên bản MS SQL Server hay không.
Tuy nhiên, điều quan trọng cần lưu ý là bản thân các yêu cầu có thể yêu cầu nhiều luồng cùng một lúc. Và đôi khi trình tối ưu hóa không thể ước tính chính xác độ phức tạp của truy vấn. Sau đó, yêu cầu có thể được phân bổ quá nhiều luồng không thể xử lý cùng một lúc tại thời điểm nhất định. Và điều này cũng gây ra một kiểu chờ đợi liên quan đến việc thiếu thời gian của bộ xử lý và sự gia tăng hàng đợi cho các bộ lập lịch sử dụng các lõi CPU cụ thể, tức là chỉ số runnable_tasks_count sẽ tăng lên trong các điều kiện như vậy.
Trong trường hợp này, trước khi tăng số lượng lõi CPU, cần định cấu hình chính xác các thuộc tính song song của chính phiên bản MS SQL Server và từ phiên bản 2016, hãy định cấu hình chính xác các thuộc tính song song của cơ sở dữ liệu cần thiết:
Ở đây bạn nên chú ý đến các tham số sau:
- Mức độ song song tối đa - đặt số luồng tối đa có thể được phân bổ cho mỗi yêu cầu (mặc định là 0 - chỉ giới hạn bởi chính hệ điều hành và phiên bản MS SQL Server)
- Ngưỡng chi phí cho tính song song - chi phí ước tính cho tính song song (mặc định là 5)
- Max DOP - đặt số luồng tối đa có thể được phân bổ cho mỗi truy vấn ở cấp cơ sở dữ liệu (nhưng không nhiều hơn giá trị của thuộc tính "Mức độ song song tối đa") (mặc định là 0 - chỉ bị giới hạn bởi chính hệ điều hành và phiên bản của MS SQL Server, cũng như hạn chế đối với thuộc tính "Mức độ song song tối đa" của toàn bộ phiên bản MS SQL Server)
Ở đây không thể đưa ra một công thức tốt như nhau cho mọi trường hợp, tức là bạn cần phân tích các truy vấn nặng.
Từ kinh nghiệm của bản thân, tôi khuyên dùng thuật toán hành động sau cho các hệ thống OLTP để thiết lập các thuộc tính xử lý song song:
- trước tiên hãy tắt tính năng song song bằng cách đặt Mức độ song song tối đa trên toàn phiên bản thành 1
- phân tích các yêu cầu nặng nhất và chọn số luồng tối ưu cho chúng
- đặt Mức độ song song tối đa thành số luồng tối ưu đã chọn thu được từ bước 2 và đối với các cơ sở dữ liệu cụ thể, đặt giá trị DOP tối đa thu được từ bước 2 cho mỗi cơ sở dữ liệu
- phân tích các yêu cầu nặng nhất và xác định tác động tiêu cực của đa luồng. Nếu đúng như vậy, thì hãy tăng Ngưỡng chi phí cho Tính song song.
Đối với các hệ thống như 1C, Microsoft CRM và Microsoft NAV, trong hầu hết các trường hợp, việc cấm đa luồng là phù hợp
Ngoài ra, nếu có phiên bản Tiêu chuẩn, thì trong hầu hết các trường hợp, việc cấm đa luồng là phù hợp do phiên bản này bị giới hạn về số lượng lõi CPU.
Đối với các hệ thống OLAP, thuật toán được mô tả ở trên không phù hợp.
Từ kinh nghiệm của bản thân, tôi khuyên dùng thuật toán hành động sau cho các hệ thống OLAP để thiết lập các thuộc tính xử lý song song:
- phân tích các yêu cầu nặng nhất và chọn số luồng tối ưu cho chúng
- đặt Mức độ song song tối đa thành số luồng tối ưu đã chọn thu được từ bước 1 và đối với các cơ sở dữ liệu cụ thể, đặt giá trị DOP tối đa thu được từ bước 1 cho mỗi cơ sở dữ liệu
- phân tích các truy vấn nặng nhất và xác định tác động tiêu cực của việc hạn chế đồng thời. Nếu đúng như vậy, thì hãy hạ thấp giá trị Ngưỡng chi phí cho tính song song hoặc lặp lại các bước 1-2 của thuật toán này
Nghĩa là, đối với các hệ thống OLTP, chúng tôi chuyển từ đơn luồng sang đa luồng và ngược lại, đối với các hệ thống OLAP, chúng tôi chuyển từ đa luồng sang đơn luồng. Do đó, bạn có thể chọn cài đặt song song tối ưu cho cả cơ sở dữ liệu cụ thể và toàn bộ phiên bản của MS SQL Server.
Cũng cần hiểu rằng các cài đặt của thuộc tính xử lý song song cần được thay đổi theo thời gian, dựa trên kết quả giám sát hiệu suất của MS SQL Server.
Hướng dẫn Đặt cờ theo dõi
Từ kinh nghiệm của bản thân và kinh nghiệm của các đồng nghiệp, để có hiệu suất tối ưu, tôi khuyên bạn nên đặt các cờ theo dõi sau ở cấp độ chạy của dịch vụ MS SQL Server cho các phiên bản 2008-2016:
- 610 - Giảm ghi nhật ký của phần chèn vào bảng được lập chỉ mục. Có thể trợ giúp chèn vào các bảng có nhiều bản ghi và nhiều giao dịch, với WRITELOG thường xuyên chờ đợi các thay đổi trong chỉ mục
- 1117 - Nếu một tệp trong nhóm tệp đáp ứng các yêu cầu ngưỡng tự động phát triển, tất cả các tệp trong nhóm tệp sẽ phát triển
- 1118 - Buộc tất cả các đối tượng được định vị ở các phạm vi khác nhau (cấm phạm vi hỗn hợp), giúp giảm thiểu nhu cầu quét trang SGAM, được sử dụng để theo dõi các phạm vi hỗn hợp
- 1224 - Vô hiệu hóa nâng cấp khóa dựa trên số lượng khóa. Tuy nhiên, việc sử dụng bộ nhớ quá mức có thể kích hoạt leo thang khóa
- 2371 - Thay đổi ngưỡng cập nhật thống kê tự động cố định thành ngưỡng cập nhật thống kê tự động động. Quan trọng để cập nhật kế hoạch truy vấn cho các bảng lớn, trong đó số lượng bản ghi không chính xác dẫn đến kế hoạch thực hiện sai
- 3226 - Chặn thông báo sao lưu thành công trong nhật ký lỗi
- 4199 - Bao gồm các thay đổi đối với trình tối ưu hóa truy vấn được phát hành trong CU và Gói Dịch vụ SQL Server
- 6532-6534 - Bao gồm các cải tiến hiệu suất cho các thao tác truy vấn trên các kiểu dữ liệu không gian
- 8048 - Chuyển đổi các đối tượng bộ nhớ được phân vùng NUMA thành các đối tượng được phân vùng CPU
- 8780 - Cho phép phân bổ thời gian bổ sung cho việc lập kế hoạch truy vấn. Một số yêu cầu không có cờ này có thể bị từ chối vì chúng không có kế hoạch truy vấn (lỗi rất hiếm gặp)
- 8780 - 9389 - Bật bộ đệm bộ nhớ cấp động bổ sung cho các câu lệnh chế độ hàng loạt, cho phép người vận hành chế độ hàng loạt yêu cầu bộ nhớ bổ sung và tránh di chuyển dữ liệu sang tempdb nếu có bộ nhớ bổ sung
Cũng trước năm 2016, sẽ rất hữu ích khi bật cờ theo dõi 2301, cho phép tối ưu hóa hỗ trợ quyết định nâng cao và do đó giúp chọn các gói truy vấn chính xác hơn. Tuy nhiên, kể từ phiên bản 2016, nó thường có tác động tiêu cực đến thời gian thực hiện truy vấn tổng thể khá lâu.
Ngoài ra, đối với các hệ thống có nhiều chỉ mục (ví dụ: đối với cơ sở dữ liệu 1C), tôi khuyên bạn nên bật cờ theo dõi 2330 để vô hiệu hóa việc thu thập sử dụng chỉ mục, thường có tác động tích cực đến hệ thống.
Để biết thêm thông tin về cờ theo dõi, hãy xem
Từ liên kết ở trên, điều quan trọng là phải xem xét các phiên bản và bản dựng của MS SQL Server, vì đối với các phiên bản mới hơn, một số cờ theo dõi được bật theo mặc định hoặc không có tác dụng.
Bạn có thể bật và tắt cờ theo dõi bằng các lệnh DBCC TRACEON và DBCC TRACEOFF tương ứng. Để biết thêm chi tiết xem
Bạn có thể nhận trạng thái của các cờ theo dõi bằng lệnh DBCC TRACESTATUS:
Để các cờ theo dõi được bao gồm trong tự động khởi động của dịch vụ MS SQL Server, bạn phải truy cập Trình quản lý cấu hình máy chủ SQL và thêm các cờ theo dõi này qua -T trong thuộc tính dịch vụ:
Kết quả
Trong bài viết này, một số khía cạnh của việc giám sát MS SQL Server đã được phân tích, nhờ đó bạn có thể nhanh chóng xác định tình trạng thiếu RAM và thời gian trống của CPU, cũng như một số vấn đề ít rõ ràng khác. Các cờ theo dõi được sử dụng phổ biến nhất đã được xem xét.
Nguồn:
»
»
»
»
»
»
»
Nguồn: www.habr.com