مشاهده و تحلیل دادههای Extended Events
یادداشت حقوقی: کاربر حق ترجمه و بازنشر این اثر را برای پروژه تأیید کرده است.PAGE-722ساخت Event Session با T-SQL
CREATE EVENT SESSION [Chapter19Session] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION(sqlserver.sql_text,sqlserver.session_id)
WHERE (sqlserver.database_id = DB_ID('Chapter19'))
)
ADD TARGET package0.event_file
(SET filename=N'D:\XEvents\Chapter19.xel')
WITH (STARTUP_STATE=ON);
GO
ALTER EVENT SESSION [Chapter19Session] ON SERVER STATE=START;
STARTUP_STATE=ON باعث شروع Session پس از Restart میشود. Live Data Viewer برای event_file مناسب است؛ ring_buffer و ETW محدودیت View متفاوت دارند.
PAGE-724با INSERT داده در Customers Event تولید میشود. Payload شامل Statement و Actionهای انتخابی است و در فایل XEL نوشته میشود.
PAGE-725با INSERT داده در Customers Event تولید میشود. Payload شامل Statement و Actionهای انتخابی است و در فایل XEL نوشته میشود.
PAGE-726Data Viewer
SSMS Data Viewer Event File را باز و Columnهای Event/Action را Grid نمایش میدهد. Timestamp، Name، Duration، CPU، Reads و sql_text بسته به Session قابل مشاهدهاند.
Figure 19-8 — شکل/تصویر منبع، صفحه PDF 726PAGE-727Toolbar اجازه Filter، Sort، Group و Choose Columns را میدهد. Columnهای XML Payload را میتوان به Grid Promote کرد تا تحلیل سادهتر شود.
Figure 19-9 — شکل/تصویر منبع، صفحه PDF 727Figure 19-8 — شکل/تصویر منبع، صفحه PDF 727PAGE-728Aggregation
Viewer میتواند Eventها را بر اساس Field مانند query_hash، object_name یا wait_type Aggregate کند. Aggregation سریع برای کشف Top Pattern مفید است، ولی تحلیل عمیق بهتر است با T-SQL/PowerShell انجام شود.
Figure 19-10 — شکل/تصویر منبع، صفحه PDF 728PAGE-729Grid فیلترشده میتواند Export شود. Filter در Viewer فقط Presentation را تغییر میدهد و Overhead Capture را کم نمیکند؛ Predicate باید در Session تعریف شود.
Figure 19-11 — شکل/تصویر منبع، صفحه PDF 729Table 19-5 — بازنمایی متن فنی جدول منبع--- PDF PAGE 729 ---
723
Analyzing Data with T-SQL
If you require more complex analysis of the data, then you can achieve this via
T-SQL. The sys.fn_xe_file_target_read_file function makes this possible by reading
the target file and returning one row per event in XML format. The sys.fn_xe_file_
target_read_file accepts the parameters detailed in Table 19-5.
Figure 19-11. The data viewer grid
In the data viewer grid, we are now able to see a SUM of the duration column for the
wait_type, and if we expand this group, it displays the granular details, as shown in
Figure 19-11.
Chapter 19 Extended Events
--- PDF PAGE 730 ---
724
The sys.fn_xe_file_target_read_file procedure returns the columns detailed in
Table 19-6.
Table 19-5. sys.fn_xe_ file_target_read_ file Parameters
Parameter
Description
path
The file path and file name of the .XEL file. This can contain the *
wildcard so that rollover files can be included.
mdpath
The file path and name of the metadata file. This is not required for SQL
Server 2012 and above but is for backward compatibility only, so you
should always pass NULL.
initial_file_name
The first file in the path to read. If this parameter is not NULL, then you
must also specify initial_offset.
initial_offset
Specifies the last offset that was read so that all events prior are skipped.
If specified, then you must also specify initial_file_name.
Table 19-6. sys.fn_xe_ file_target_read_ file Results
Column
Description
module_guid
The GUID of the module that contains the package
package_guid
The GUID of the package that contains the event
object_name
The name of the event
event_data
The event data, in XML format
file_name
The name of the XEL file that contains the event
file_offset
The offset of the block within the file that contains the event
Because the event data is returned in XML format, we need to use XQuery to shred
the nodes into relational data. A full description of XQuery is beyond the scope of
this book, but Microsoft provides an XQuery language reference on https://msdn.
microsoft.com.
Chapter 19 Extended Events
|
sys.fn_xe_file_target_read_file
این Function فایل XEL و Metadata File را میخواند و event_data را XML برمیگرداند. پارامتر Path Wildcard و Initial File/Offset امکان ادامه خواندن Incremental را میدهد.
Table 19-6 — بازنمایی متن فنی جدول منبع--- PDF PAGE 730 ---
724
The sys.fn_xe_file_target_read_file procedure returns the columns detailed in
Table 19-6.
Table 19-5. sys.fn_xe_ file_target_read_ file Parameters
Parameter
Description
path
The file path and file name of the .XEL file. This can contain the *
wildcard so that rollover files can be included.
mdpath
The file path and name of the metadata file. This is not required for SQL
Server 2012 and above but is for backward compatibility only, so you
should always pass NULL.
initial_file_name
The first file in the path to read. If this parameter is not NULL, then you
must also specify initial_offset.
initial_offset
Specifies the last offset that was read so that all events prior are skipped.
If specified, then you must also specify initial_file_name.
Table 19-6. sys.fn_xe_ file_target_read_ file Results
Column
Description
module_guid
The GUID of the module that contains the package
package_guid
The GUID of the package that contains the event
object_name
The name of the event
event_data
The event data, in XML format
file_name
The name of the XEL file that contains the event
file_offset
The offset of the block within the file that contains the event
Because the event data is returned in XML format, we need to use XQuery to shred
the nodes into relational data. A full description of XQuery is beyond the scope of
this book, but Microsoft provides an XQuery language reference on https://msdn.
microsoft.com.
Chapter 19 Extended Events
|
SELECT CAST(event_data AS xml) AS event_xml
FROM sys.fn_xe_file_target_read_file(
'D:\XEvents\Chapter19*.xel', NULL, NULL, NULL);
Path باید متناسب با Environment تنظیم شود.
PAGE-732WITH X AS
(
SELECT CAST(event_data AS xml) AS x
FROM sys.fn_xe_file_target_read_file('D:\XEvents\Chapter19*.xel',NULL,NULL,NULL)
)
SELECT
x.value('(/event/@name)[1]','sysname') AS EventName,
x.value('(/event/@timestamp)[1]','datetime2') AS EventTime,
x.value('(/event/action[@name="sql_text"]/value)[1]','nvarchar(max)') AS SqlText
FROM X;
XQuery هر Field/Action را استخراج میکند و داده XEvent را برای Reporting یا Correlation آماده میسازد.