img

استفاده Extended Event و Event Notificationهای SQL Server به شکل فعال برای مسائل مربوط به عملکرد

/
/
/

مانیتور کردن فرآیندهای بلوکه شده با extended event
همانند بن‌بست‌ها، فرایندهای بلوکه شده را می‌توان با extended eventها مانیتور کرد، گرچه فرایندهای بلوکه شده همانند مانیتورینگ بن‌بست، از قبل پیکربندی نمی‌شوند. برای انجام این مانیتورینگ، نیاز به ایجاد یک جلسه extended event دارید.
دو رویداد هستند که با extended eventها استفاده می‌شوند:
– sqlserver.locks_lock_timeouts : مقدار زمانی که SQL Server منتظر row lock می‌ماند.
– sqlserver.locks_lock_waits : مقدار زمان کل به میلی ثانیه‌ که SQL Server منتظر row lock مانده است
جلسه extended event زیر را می‌توان برای ضبط بلوکه کردن یا blocking ساخت. این جلسه هر دو رویداد را همراه عمل‌های sql_text و tsql_stack ضبط می‌کند.

CREATE EVENT SESSION BlockingTransactions ON SERVER
ADD EVENT sqlserver. locks_lock_timeouts (
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
)
,ADD EVENT sqlserver. locks_lock_waits (
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
)
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

ALTER EVENT SESSION BlockingTransactions ON SERVER STATE = START;

GO

وقتی بلوکه کردن رخ می‌دهد، اطلاعات را می‌توان از جلسه با استفاده از پرس و جوی زیر استخراج کرد:

WITH BlockingTransactions
AS (
SELECT CAST(target_data AS xml) AS SessionXML
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = ‹BlockingTransactions›
)
SELECT
block.value(‹@timestamp›, ‹datetime›) AS event_timestamp
,block.value(‹@name›, ‹nvarchar(128)›) AS event_name
,block.value(‹(data/value)[1]›, ‹nvarchar(128)›) AS event_count
,block.value(‹(data/value)[1]›, ‹nvarchar(128)›) AS increment
,mv.map_value AS lock_type
,block.value(‹(action/value)[1]›, ‹nvarchar(max)›) AS sql_text
,block.value(‹(action/value)[2]›, ‹nvarchar(255)›) AS tsql_stack
FROM BlockingTransactions b
CROSS APPLY SessionXML.nodes (‹//RingBufferTarget/event›) AS t(block)
INNER JOIN sys.dm_xe_map_values mv ON block.value(‹(data/value)[3]›, ‹nvarchar(128)›) =
mv.map_key AND name = ‹lock_mode›
WHERE block.value(‹@name›, ‹nvarchar(128)›) = ‹locks_lock_waits›
UNION ALL
SELECT
block.value(‹@timestamp›, ‹datetime›) AS event_timestamp
,block.value(‹@name›, ‹nvarchar(128)›) AS event_name
,block.value(‹(data/value)[1]›, ‹nvarchar(128)›) AS event_count
,NULL
,mv.map_value AS lock_type
,block.value(‹(action/value)[1]›, ‹nvarchar(max)›) AS sql_text
,block.value(‹(action/value)[2]›, ‹nvarchar(255)›) AS tsql_stack
FROM BlockingTransactions b
CROSS APPLY SessionXML.nodes (‹//RingBufferTarget/event›) AS t(block)
INNER JOIN sys.dm_xe_map_values mv ON block.value(‹(data/value)[2]›, ‹nvarchar(128)›) =
mv.map_key AND name = ‹lock_mode›
WHERE block.value(‹@name›, ‹nvarchar(128)›) = ‹locks_lock_timeouts›;

در خروجی نمونه بالا، یک لاک اتفاق افتاده است. این رویداد تقریباً ۱۸هزار میلی ثانیه زمان انتظار داشت. علاوه بر آن عمل‌ها مربوط به Sql_text و tsql_stackهستند. Sql_text اطلاعات مربوط به SQL را تهیه می‌کند که در تراکنش ارائه می‌گردد. سپس tsql_stack پشته‌ای از فراخوانی‌ها را تهیه می‌کند که منجر به دستوری می‌گردد که با بلوکه شدن مواجه می‌شود.
این اطلاعات احتمالاً به روش‌های دیگر جمع‌آوری و گزارش می‌شوند. آنچه باعث این تفاوت نسبت به استفاده از event notification ها می‌گردد این است که کنترل بیشتری روی جزییات بازگردانده شده ارائه می‌دهد. جلسه extended event را می‌توان فیلتر کرد تا تنها نگاهی به پایگاه‌های اطلاعاتی، سرورها، کاربرهای معین وغیره بیاندازید. بعد از چند رویداد مشخص شده، مجموعه رویداد را می‌توان پوشش داد. اگر عمل‌ها اضافه شده کافی نباشند، جزییات بیشتر را می‌توان اضافه کرد، مثل idجلسه، نام میزبان و نام اپلیکیشن و غیره.
مزیت دیگر استفاده از extended event ها فرصتی برای استفاده از اهداف چندگانه و جمع‌آوری اطلاعات با گذشت زمان است. برای مثال، به جای هدف ring_buffer، جلسه‌ها را می‌توان برای استفاده asynchronous_bucketizer یا synchronous_buketizer استفاده کرد. با این هدف‌ها، تراکنش‌هایی را که موجب بلوکه شدن می‌گردند را می‌توان با یکدیگر گروهبندی کرد و الگوهای بلوکه کننده را در دستورات خاص دید. این انعطاف‌پذیری امکان عیب‌یابی عمیق و معنادار را با تلاش حداقلی در بخش DBA فراهم می‌سازد.

مانیتور کردن خطاها

یک چالش کلیدی که مدیران پایگاه اطلاعاتی دارند، یافتن زمان کافی برای مانتیور کردن خطاهای SQL Server و گزارشات خطا در محیط‌هاست. در جهان ایده‌آل، یک مدیر پایگاه اطلاعاتی، در راس تمام خطاها در یک محیط خواهد بود و با مرور کردن گزارشات خطا به صورت روزانه، اطمینان حاصل می‌کنند که هیچ اتفاق غیر منتظره‌ای در آن‌ ظاهر نمی‌شود. متاسفانه، مانیتور کردن گزارشات خطا به خوبی مقیاس‌بندی نمی‌شود. همانطور که مدیر پایگاه اطلاعاتی مسئول نمونه‌های بیشتر و بیشتری می‌شود، تعداد گزارشاتی که باید بازبینی شوند، افزایش می‌یابند. مدیر پایگاه اطلاعاتی پس از بررسی چند نمونه باید بین خواند گزارشات خطا یا انجام اقداماتی در رابطه با آن‌ها یکی را انتخاب کند.
بخش دیگر این چالش آن است که خواندن گزارشات خطا در روز بعد از رخ دادن آن‌ها، زمان اشتباهی برای پرداخت به آنهاست. چه اطلاعات موجود در گزارش خطا مربوط به خرابی باشند یا مربوط به بسته شدن اکانت (account lockout)، مدیران پایگاه اطلاعاتی باید مسائلی که اتفاق می‌افتند را در زمان واقعی بدانند. اگر زمان برای بررسی تمام گزارشات خطا به صورت روزانه نباشد، یقیناً زمان برای مانتیور کردن مداوم گزارشات خطا در سراسر روز و جستجوی برای رویدادهای جدید وجود ندارد.
چالش دیگری که در راس هر دوی این چالش‌ها قرار دارد، مقدار درهم ریختگی است که در گزارش خطا ظاهر می‌شود. ما متوجه بکاپ‌های کامل شده، پیام‌های DBCC ، خطاهای کاربر، خطاهای اپلیکیشن‌ها و غیره شدیم. برخی‌ها خطاها فوریت بسیار بالای دارند و لازم است خیلی زود به آن‌ها پرداخته شود. برخی دیگر اطلاعاتی هستند و باید «دیده شوند نه این که شنیده شوند». وقتی نگاهی به گزارش خطا می‌اندازیم، باید برخی آمارهایی را نادیده بگیریم تا مقدار واقعی را بیابیم- در غیر این صورت ریسک از دست دادن پیام‌هایی که مهم و حتی بحرانی هستند را داریم.

مانیتورینگ خطاها با event notification

خوشبختانه، event notification، راهکاری برای مسئله دشوار خواندن از طریق پیام خطا را ارائه می‌دهد. یکی از رویدادهای ارائه شده در event notification، رویداد ERRORLOG است. با event notification ، پیام‌هایی که به گزارش خطا ارسال می‌شوند می‌توانند برای پردازش بیشتر به صف سرویس بروکرهم فرستاده شوند.
Event notification گزارش خطا را می‌توان از طریق اسکریپت زیر تنظیم کرد:

USE AdventureWorks
GO

CREATE QUEUE ErrorLogNotificationQueue ;
GO

CREATE SERVICE ErrorLogNotificationService
ON QUEUE ErrorLogNotificationQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) ;
GO

CREATE ROUTE ErrorLogNotificationRoute
WITH SERVICE_NAME = ‹ErrorLogNotificationService›,
ADDRESS = ‹LOCAL›;
GO

CREATE EVENT NOTIFICATION ErrorLogNotification
ON SERVER
WITH FAN_IN
FOR ERRORLOG
TO SERVICE ‹ErrorLogNotificationService›,›current database›;
GO

ALTER EVENT SESSION exErrors ON SERVER STATE = START;
GO

همانند even notification های دیگر لازم است یک رویه ذخیره ‌شده بسازیم تا برای پردازش پیام‌های گزارش خطا به صف سرویس بروکر اضافه کنیم. یک ایراد این event notification آن است که تمام پیام‌های فرستاده شده به گزارش خطا، به صف سرویس- بروکر گزارش خطا هم می‌رود. تمام پیام‌ها لازم نیستند، در غیر این صورت، event notification گزارش خطا، خاصیتش را به عنوان روش جایگزین برای انتخاب از طریق گزارش خطا را از دست می‌دهد.
برای ساده شدن کار، رویه ذخیره شده تمام پیام‌های خطای گزارش را با شدت ۱۹ یا بالاتر حفظ می‌کند.همچنین تمام پیام‌های خطایی که برای خطای ۸۲۵ گزارش شده‌اند، گزارش می‌شوند.
رویه ذخیره شده گزارش خطا مراحل زیر را دارد:
۱- پیام‌های گزارش خطا را برای آن مقایر فیلتر می‌کند
۲- پیام گزارش خطا را به تیم DBA ایمیل کنید
رویه ذخیره شده چیزی شبیه این است:

USE AdventureWorks
GO

IF OBJECT_ID(‹[dbo].[ProcessErrorLog]›) IS NOT NULL
DROP PROCEDURE [dbo].[ProcessErrorLog]
GO

CREATE PROCEDURE [dbo].[ProcessErrorLog]
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON

DECLARE @message_body XML
,@message_type INT
,@dialog UNIQUEIDENTIFIER
,@subject VARCHAR(MAX)
,@body VARCHAR(MAX)

WHILE (1 = 1)
BEGIN
BEGIN
BEGIN TRANSACTION

— Receive the next available message from the queue
WAITFOR (
RECEIVE TOP(1) — just handle one message at a time
@message_type=message_type_id, –the type of message received
@message_body=CAST(message_body AS XML), — the message contents
@dialog = conversation_handle — the identifier of the dialog this
message was received on
FROM dbo.ErrorLogNotificationQueue
), TIMEOUT 1000 — if the queue is empty for one second, give UPDATE and go away

— If we didn›t get anything, bail out
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF (
(@message_body.value(‹(/EVENT_INSTANCE/Severity)[1]›,›int›) > 10) — Error is not
Informational
OR (@message_body.value(‹(/EVENT_INSTANCE/Error)[1]›,›int›) = ۸۲۵) — Indicates
possible failing drives
)
AND (@message_body.value(‹(/EVENT_INSTANCE/Error)[1]›,›int›) <> 18456) — Ignore
Logon failures
AND (@message_body.value(‹(/EVENT_INSTANCE/Error)[1]›,›int›) <> 18452) — Ignore
Logon failures
BEGIN

SET @subject = @@SERVERNAME + ‹ – SQL Server Error Log›

SET @body = ‹The following ErrorLog was logged in the SQL Server ErrorLog:›
+ CHAR(10) + ‹<P /><B>PostTime:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/PostTime)[1]›,
‹varchar(128)›)
+ ‹<BR><B>Error:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/Error)[1]›,
‹varchar(20)›)
+ ‹<BR><B>Severity:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/Severity)[1]›,
‹varchar(20)›)
+ ‹<BR><B>HostName:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/HostName)[1]›,
‹varchar(128)›)
+ ‹<BR><B>ApplicationName:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/ApplicationName)[1]›,
‹varchar(128)›)
+ ‹<BR><B>DatabaseName:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/DatabaseName)[1]›,
‹varchar(128)›)
+ ‹<BR><B>SessionLoginName:</B> ‹ +
@message_body.value(‹(/EVENT_INSTANCE/SessionLoginName)[1]›,
‹varchar(128)›)
+ ‹<BR><B>TextData:</B><BR> ‹ +
REPLACE(@message_body.value(‹(/EVENT_INSTANCE/TextData)[1]›,
‹varchar(4000)›),
CHAR(10),›<BR>›)
+ ‹<P><I>!! Automatically generated by [Monitor].[ProcessErrorLog]
!!</I></P>›

EXEC msdb.dbo.sp_send_dbmail @recipients = ‹production_dba@your_company› —
your email
,@subject = @subject — Subject defined above
,@body = @body — Body defined above
,@body_format = ‹HTML› ;
END
END

— Commit the transaction. At any point before this, we could roll
— back – the received message would be back on the queue AND the response
— wouldn›t be sent.
COMMIT TRANSACTION
END;
GO

ADD SIGNATURE TO OBJECT::[dbo].[ProcessErrorLog]
BY CERTIFICATE [DBMailCertificate]
WITH PASSWORD = ‹P@$$w0rd›;
GO

با ساخته شدن این رویه ذخیره شده، می‌توان آن را به صف سرویس- بروکر گزارش خطا اضافه کرد. اضافه کردن را می‌توان با دستور SQL زیر اضافه نمود:

USE AdventureWorks
GO

ALTER QUEUE ErrorLogNotificationQueue
WITH
ACTIVATION
(STATUS=ON,
PROCEDURE_NAME = [dbo].[ProcessErrorLog],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
GO

در این لحظه، SQL Server شروع به ارسال ایمیل‌ها در زمان بروز این خطاها در محیط شما می‌کند، و دیگر لازم نیست برای یافتن خطاها، گزارشات خطا را دائماً بازبینی کنید. ایمیل شامل اطلاعات اولیه در مورد خطا است تا بتوانید بازبینی این مشکل را آغاز نمایید.

مانتیورینگ خطا با extended events

خطاهایی که در گزارش خطا ظاهر می‌شوند تنها خطاهایی نیستند که به عنوان مدیر پایگاه اطلاعاتی برایتان مهم هستند. گرچه ما همیشه جزو کسانی نیستیم که در محیط شان اپلیکیشن می‌نویسند، اما مسئولیم بدانیم چه زمانی اپلیکیشن‌ها خراب می‌شوند و چرا. به دلیل همین مسئولیت‌ها، پلتفرم extended event انعطاف‌پذیری و عمق لازم برای جمع‌آوری اطلاعات در مورد خطاها در SQL Server را ارائه می‌دهد. در زمینه خطاها، ما در مورد خطاهایی صحبت می‌کنیم که کاربران اغلب می‌بینند، اما مثل پیام خطای « نمی‌تواند رویه ذخیره شده را بیابد»، به گزارش خطا ختم نمی‌شوند.

Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure ‘asdfasdf’.

وقتی این خطاهای مشابه بخشی از اپلیکیشن هستند که SQL Server استفاده می‌کند، برخی اپلیکیشن‌ها فاقد هندلینگ یا کنترل‌پذیری لازم برای مرتبط ساختن درست موضوع با کاربر نهایی یا توسعه دهنده می‌باشند. گاهی اوقات وقتی خطاها ظاهر می‌شوند، مفهوم پیام خطا همراه با جزییات مهم مربوط به خطا از دست می‌رود.
Extended eventها می‌توانند یک پلتفرم ردیابی سبک را ارائه دهند تا خطاها را زمانی که رخ می‌دهند، جمع‌آوری کرده و این شانس را به مدیر پایگاه اطلاعاتی بدهند تا پیام‌های خطای جدید را بازبینی کند و به توسعه‌دهندگان در مورد مسائل عیب‌یابی کمک می‌کند. این یک روش برای حفظ امواج مسائلی است که ممکن است در آینده بزرگتر شوند.
برای مثال، می‌خواهیم یک جلسه extended event بسازیم که سه پیام خطا را ضبط می‌کند. خطاها این ها هستند: ۲۰۸،۲۸۱۲،۴۱۲۱٫ که این طور ترجمه می‌شوند: نام شی‌ء نامعتبر است، نمی‌تواند رویه ذخیره شده را بیابد و نمی‌تواند به ترتیب ستون یا تابع تعریف شده توسط کاربر یا تابع حاصل جمع را بیابد. این خطاها نمی‌توانند توسط event sqlserver.erroe_repoted ( رویدادی است که هنگام مواجه شدن با خطا تریگر می‌شود) ضبط شوند. همراه این رویداد، جلسه extended event جزییات زیر را ضبط می‌کند:

– sqlever.session_id
– sqlserver.sql_text
– sqlserver.client_app_name
– sqlserver.client_hostame
– sqlserver.database_id
– sqlserver.username

این جزییات اطلاعات مورد نیاز برای در نظر گرفتن یک خطا و ردیابی آن تا یک اپلیکیشن را ارائه می‌دهند تا مسائل مربوط به آن حل شوند. جلسه extended event مربوط به این کار را می‌توان از طریق اسکریپت زیر ایجاد کرد:

CREATE EVENT SESSION exErrors ON SERVER — Session Name
ADD EVENT sqlserver.error_reported — Event we want to capture
(
ACTION — What contents to capture
(
sqlserver.session_id
,sqlserver.sql_text
,sqlserver.client_app_name
,sqlserver.client_hostname
,sqlserver.database_id
,sqlserver.username
)
— Some predicate or filter (here it is object not found error number)
WHERE (error = 208
OR error = 2812
OR error = 4121
)
)
ADD TARGET package0.ring_buffer
WITH (max_dispatch_latency = 5 seconds); — The target
GO

ALTER EVENT SESSION exErrors ON SERVER STATE = START
GO

وقتی خطاهایی رخ می‌دهند که جلسه رویداد ضبط می‌کند، این خطاها می‌توانند توسط پرس و جویی شبیه این خوانده شوند:

WITH exErrors
AS (
SELECT CAST(target_data AS xml) AS SessionData
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = ‹exErrors›
)
SELECT
error.value(‹(@timestamp)[1]›, ‹datetime›) as event_timestamp
,error.value(‹(data/value)[5]›, ‹varchar(max)›) as [error_message]
,error.value(‹(data/value)[1]›, ‹int›) as error
,error.value(‹(action/value)[3]›, ‹nvarchar(255)›) AS client_app_name
,error.value(‹(action/value)[4]›, ‹nvarchar(255)›) as client_hostname
,DB_NAME(error.value(‹(action/value)[5]›, ‹int›)) AS database_name
,error.value(‹(action/value)[6]›, ‹nvarchar(128)›) AS username
,error.value(‹(action/value)[2]›, ‹varchar(max)›) as sql_text
,error.value(‹(action/value)[1]›, ‹int›) as session_id
,error.value(‹(data/value)[4]›, ‹bit›) as user_defined
FROM exErrors d
CROSS APPLY SessionData.nodes (‹//RingBufferTarget/event›) AS t(error)
WHERE error.value(‹@name›, ‹nvarchar(128)›) = ‹error_reported›;

این پرس و جو مجموعه نتایجی شبیه به تصویر صفحه قبل را ارائه می‌دهند.
از همینجا، یک مدیر پایگاه اطلاعات می‌تواند به راحتی شروع به پیگیری مسائل کند و توسعه دهنده را آگاه نماید که خطاها در کجا رخ داده‌اند، تناوب زمانی که این خطاها رخ داده اند و کدام دستورات T-SQL موجب بروز آن‌ها شده‌اند. این نمونه تنها شامل چند پیام خطا است اما می‌تواند براحتی آن را برای پوشش دادن به بسیاری از خطاها تعمیم داد، که در واقع به حل پیام‌های خطایی که ممکن است با آن‌ها مواجه شوید همچون خطاهای برشی (truncation) کمک نماید.

نتیجه گیری
در این مقاله به سراغ راهکارهایی رفته‌ایم که مسائل مدیران پایگاه اطلاعاتی را مخاطب قرار می‌دادند که روزانه با آن‌ها مواجه می‌شوند. هر کدام از این مسائل چالش‌های خودشان را دارند. وقتی مسائل بروز می‌کنند، مدیران پایگاه اطلاعاتی نیاز به دسترسی به اطلاعات دقیق و به موقع دارند تا بتواند کارهایی را در آن خصوص انجام دهند، نه اطلاعاتی که مستلزم بررسی و جستجوهای بیشتر باشند.

بن بست‌ها:
با event notification‌ها می‌توانیم گراف‌های بن‌بست را به اشخاص مناسب این کار ارسال کنیم. در extended event ها، مروری داشتیم بر چگونه ضبط کردن بن‌بست‌ها و ذخیره کردن آن‌ها در جلسه system_health extended event که توسط SQL Server صورت می‌گیرد.

بلوکه کردن
به جای آن که صبر کنیم تا کاربران در مورد عملکرد شکایت کنند، از طریق event notification‌ها، فرایندهای بلوکه شده را مانتیور کردیم و توانستیم ایمیل‌هایی را در زمان بروز رویدادها ارسال نماییم. سپس با extended eventها نگاهی داشتیم به نحوه جمع‌آوری اطلاعات مربوط به بلوکه کردن آنگونه که شما دوست دارید، و ضبط آن در فرمتی که برای پرس و جو کردن و اقدام کردن آسان باشد.

پیام‌های خطا
در آخرین سناریو، گفتیم که چگونه event notification‌ها می‌توانند مدیران پایگاه اطلاعاتی را از مانیتور کردن گزارشات خطای و دریافت اطلاعات مربوط به مسائل رها نمایند. نمونه نشان داد که چگونه می‌توان از extended eventها برای مانتیور کردن خطای کاربرکه مدیران پایگاه اطلاعاتی اغلب با آن‌ها مواجه نمی‌شوند، استفاده کرد.
برای استفاده از extended eventها و event notificationها محدودیت‌هایی وجود دارند که باید قبل از استفاده از آن‌ها مطلع باشید. اگر از SQL Server2005 یا جدیدتر استفاده می‌کنید، ویژگی extended eventها برای شما موجود است که می‌توانید از همین امروز شروع به استفاده از آن‌ها نمایید. در مورد event notification ها نیاز به حداقل SQL Server2008 دارید. خوشبختانه، هیچ محدودیت ویرایشی در مورد هر کدام از این تکنولوژی‌ها وجود ندارد.

نظر بدهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

It is main inner container footer text