Extended Event: סקריפט בסיסי

01/07/2015

אין תגובות

מכירים את הקטע הזה שאחרי שטחנו לכם שצריך לעבור ל-Extended Events אתם מנסים לעבוד איתו, לא מוצאים את הידיים והרגליים, מסתבכים עם ה-Wizard ועם כל מיני סקריפטים מהאינטרנט, לא מקבלים את מה שרוצים, לא מצליחים בכלל להבין מה קיבלנו, מקללים את כל העולם ואת גיסו, ולבסוף חוזרים ל-Profiler הישן והטוב ומבצעים את העבודה בהצלחה?
גם אם אתם לא, אני כן; ולהלן כל מה שאתם צריכים.
רגע- מה הכוונה להכל? בואו נדייק: מדובר בתסריט טיפוסי בו רוצים לראות מי הריץ מה. נניח- מי פנה לטבלה מסויימת או הריץ פרוצדורה מסויימת. כבר כדאי להבהיר כאן שבמקרה כזה יתכן והכלי המתאים אינו Extended Events ואינו Profiler, אל Audits; אבל כשנתקעים עם בעייה בכלי אחד ושוקלים מרוב תסכול לחזור  לכלי הקודם, הדבר האחרון שאנחנו צריכים זה איזה חוכמולוג שיפנה אותנו לכלי שלישי.. בקיצור- סקריפט לניטור פקודות שכוללות מחרוזת מסויימת.

המצרכים: יש צורך בשרת, מחיצה אליה נפנה את הקובץ שיווצר עם הנתונים, ומצב רוח טוב.
נתחיל מהמחיצה- כדי למצוא היכן למקם אותה (או לחפש אחת שכבר קיימת ושניתן להשתמש בה) צריך לקבל קודם כל את רשימת הדיסקים של השרת-

Exec xp_fixeddrives;

נניח שהתברר שיש לנו דיסק C (תמיד יש אך לרוב עדיף להשתמש באחד אחר שעליו לא מותקנת מערכת ההפעלה) ובו נרצה ליצור מחיצה בשם Trace. במקום להתחבר ב-RDC לשרת, נשתמש ב-xp_cmdshell להרצת פקודות מתאימות. אם ה-xp_cmdshell אינו מאופשר – נאפשר אותו כך:

Exec sp_configure 'show advanced options', 1

Reconfigure

Exec sp_configure 'xp_cmdshell', 1

Reconfigure

למען הסדר הטוב- יש המון סיכונים בשימוש ב-xp_cmdshell ולכן הוא אינו מאופשר כברירת מחדל. ניתן לאפשר אותו באופן סלקטיבי ובטוח יותר בעזרת סרטיפיקאטים (ואז הוא מופעל בהרשאות כתיבה/קריאה מתאימות לכל משתמש בנפרד), אבל מטעמי נוחות מאפשרים לא פעם שימוש בלתי מוגבל (כלומר- לא כל אחד יכול להשתמש, אבל מי שכן – ללא הגבלה). עד כאן כסת”ח מטעם המערכת, ונמשיך הלאה.
בעזרת הנ”ל ניצור מחיצה מתאימה לקבצים:

Exec xp_cmdshell 'Md C:\Trace\';

ומכאן למנה העיקרית- אני מעוניין לעקוב אחר פקודות שפנו לאובייקט T_Customers:

Create Event Session XE20150701 On Server 

Add Event sp_statement_completed(

    Action(client_app_name,client_connection_id,client_hostname,client_pid,context_info,database_id,database_name,is_system,nt_username,plan_handle,query_hash,query_plan_hash,request_id,server_instance_name,server_principal_name,server_principal_sid,session_id,session_nt_username,session_resource_group_id,session_resource_pool_id,session_server_principal_name,sql_text,transaction_id,transaction_sequence,tsql_frame,username,tsql_stack)

    Where ([statement] Like N'%T[_]Customers%' Or [sqlserver].[sql_text] Like N'%T[_]Customers%')

    ),

Add Event module_end(

    Action(client_app_name,client_connection_id,client_hostname,client_pid,context_info,database_id,database_name,is_system,nt_username,plan_handle,query_hash,query_plan_hash,request_id,server_instance_name,server_principal_name,server_principal_sid,session_id,session_nt_username,session_resource_group_id,session_resource_pool_id,session_server_principal_name,sql_text,transaction_id,transaction_sequence,tsql_frame,username,tsql_stack)

    Where ([sqlserver].[sql_text] Like N'%T[_]Customers%')

    ),

Add Event rpc_completed(

    Action(client_app_name,client_connection_id,client_hostname,client_pid,context_info,database_id,database_name,is_system,nt_username,plan_handle,query_hash,query_plan_hash,request_id,server_instance_name,server_principal_name,server_principal_sid,session_id,session_nt_username,session_resource_group_id,session_resource_pool_id,session_server_principal_name,sql_text,transaction_id,transaction_sequence,tsql_frame,username,tsql_stack)

    Where ([statement] Like N'%T[_]Customers%' Or [sqlserver].[sql_text] Like N'%T[_]Customers%')

    ),

Add Event sql_batch_completed(

    Action(client_app_name,client_connection_id,client_hostname,client_pid,context_info,database_id,database_name,is_system,nt_username,plan_handle,query_hash,query_plan_hash,request_id,server_instance_name,server_principal_name,server_principal_sid,session_id,session_nt_username,session_resource_group_id,session_resource_pool_id,session_server_principal_name,sql_text,transaction_id,transaction_sequence,tsql_frame,username,tsql_stack)

    Where ([batch_text] Like N'%T[_]Customers%' Or [sqlserver].[sql_text] Like N'%T[_]Customers%')

    ),

Add Event sql_statement_completed(

    Action(client_app_name,client_connection_id,client_hostname,client_pid,context_info,database_id,database_name,is_system,nt_username,plan_handle,query_hash,query_plan_hash,request_id,server_instance_name,server_principal_name,server_principal_sid,session_id,session_nt_username,session_resource_group_id,session_resource_pool_id,session_server_principal_name,sql_text,transaction_id,transaction_sequence,tsql_frame,username,tsql_stack)

    Where ([statement] Like N'%T[_]Customers%' Or [sqlserver].[sql_text] Like N'%T[_]Customers%')

    )

Add Target package0.event_file(Set filename=N'C:\Trace\XE20150701')

GO

 

Alter Event Session XE20150701 On Server State=start;

Go

כל כך הרבה וכל כך ארוך?

  • הסקריפט כולל את ה-Events הבאים: sp_statement_completed, module_end, rpc_completed, sql_batch_completed,sql_statement_completed. אפשר לוותר על חלק, אבל אני מתחיל עם יותר.
  • לאחר ה-Action מופיעות בסוגריים כל העמודות שנקבל. אפשר להוסיף ולגרוע, וגם כאן – אני כולל את כל מה שזז.
  • כתוב בתנאי T[_]Customers כי התו _ מסמל בפעולות השוואה (Like) תו כלשהו, ואילו כאן אנחנו מתכוונים לתו _ עצמו.
  • יש כמה כפילויות ומידע מיותר. את כל זה אפשר לגרוע מהסקריפטים ולהסתפק רק במה שצריך.

כדי לקבל רשימה של כל ה-Events וה-Actions ניתן לתחקר את טבלת המערכת sys.dm_xe_objects.
כעת ניתן לבצע Select מהטבלה הנ”ל, להפעיל פרוצדורה שפונה אליה וכו’. כדאי להדגיש שפנייה לטבלה בעזרת View לא תיקלט כנראה על ה-Extended Events ולכן ציינתי בהתחלה שהכלי המתאים הוא Audits.
בכל מקרה, תוך כדי ריצה, כבר ניתן ל”להציץ” בקובץ (XML) שנוצר ולבדוק מה נכנס:

With T As

(Select    xml_event_data,

        X.value('(./@name)','Varchar(Max)') As event_name,

        X.value('(./@timestamp)','DateTime') As timestamp,

        X.value('(./data[@name="duration"]/value)[1]','bigint') As duration,

        X.value('(./data[@name="cpu_time"]/value)[1]','bigint') As cpu_time,

        X.value('(./data[@name="writes"]/value)[1]','bigint') As writes,

        X.value('(./data[@name="physical_reads"]/value)[1]','bigint') As physical_reads,

        X.value('(./data[@name="logical_reads"]/value)[1]','bigint') As logical_reads,

        X.value('(./data[@name="statement"]/value)[1]','Varchar(Max)') As statement,

        X.value('(./action[@name="sql_text"]/value)[1]','Varchar(Max)') As sql_text,

        X.value('(./data[@name="batch_text"]/value)[1]','Varchar(Max)') As batch_text,

        X.value('(./data[@name="row_count"]/value)[1]','Varchar(Max)') As row_count,

        X.value('(./data[@name="line_number"]/value)[1]','Varchar(Max)') As line_number,

        X.value('(./data[@name="offset"]/value)[1]','Varchar(Max)') As offset,

        X.value('(./data[@name="offset_end"]/value)[1]','Varchar(Max)') As offset_end,

        X.value('(./action[@name="client_connection_id"]/value)[1]','Varchar(Max)') As client_connection_id,

        X.value('(./action[@name="client_pid"]/value)[1]','Varchar(Max)') As client_pid,

        X.value('(./action[@name="context_info"]/value)[1]','Varchar(Max)') As context_info,

        X.value('(./action[@name="database_id"]/value)[1]','Int') As database_id,

        X.value('(./action[@name="database_name"]/value)[1]','Varchar(Max)') As Database_Name,

        X.value('(./action[@name="session_id"]/value)[1]','Int') As session_id,

        X.value('(./action[@name="username"]/value)[1]','Varchar(Max)') As username,

        X.value('(./action[@name="client_hostname"]/value)[1]','Varchar(Max)') As client_hostname,

        X.value('(./action[@name="client_app_name"]/value)[1]','Varchar(Max)') As client_app_name,

        X.value('(./action[@name="is_system"]/value)[1]','Varchar(Max)') As is_system,

        X.value('(./action[@name="nt_username"]/value)[1]','Varchar(Max)') As nt_username,

        Concat('0x',X.value('(./action[@name="plan_handle"]/value)[1]','Varchar(Max)')) As plan_handle,

        X.value('(./action[@name="query_hash"]/value)[1]','Varchar(Max)') As query_hash,

        X.value('(./action[@name="query_plan_hash"]/value)[1]','Varchar(Max)') As query_plan_hash,

        X.value('(./action[@name="request_id"]/value)[1]','Int') As request_id,

        X.value('(./action[@name="server_instance_name"]/value)[1]','Varchar(Max)') As server_instance_name,

        X.value('(./action[@name="server_principal_name"]/value)[1]','Varchar(Max)') As server_principal_name,

        X.value('(./action[@name="server_principal_sid"]/value)[1]','Varchar(Max)') As server_principal_sid,

        X.value('(./action[@name="session_nt_username"]/value)[1]','Varchar(Max)') As session_nt_username,

        X.value('(./action[@name="session_resource_group_id"]/value)[1]','Int') As session_resource_group_id,

        X.value('(./action[@name="session_resource_pool_id"]/value)[1]','Int') As session_resource_pool_id,

        X.value('(./action[@name="session_server_principal_name"]/value)[1]','Varchar(Max)') As session_server_principal_name,

        X.value('(./action[@name="transaction_id"]/value)[1]','BigInt') As transaction_id,

        X.value('(./action[@name="transaction_sequence"]/value)[1]','BigInt') As transaction_sequence,

        X.value('(/event/action[@name=''tsql_frame'']/value/frame/@handle)[1]','Varchar(Max)') AS tsql_frame_handle,

        X.value('(/event/action[@name=''tsql_frame'']/value/frame/@offsetStart)[1]','BigInt') AS tsql_frame_offsetStart,

        X.value('(/event/action[@name=''tsql_frame'']/value/frame/@offsetEnd)[1]','BigInt') AS tsql_frame_offsetEnd,

        X.value('(/event/action[@name=''tsql_stack'']/value/frames/frame/@handle)[1]','Varchar(Max)') AS tsql_stack_handle,

        X.value('(/event/action[@name=''tsql_stack'']/value/frames/frame/@offsetStart)[1]','BigInt') AS tsql_stack_offsetStart,

        X.value('(/event/action[@name=''tsql_stack'']/value/frames/frame/@offsetEnd)[1]','BigInt') AS tsql_stack_offsetEnd

From    (Select Cast(event_data As XML) xml_event_data 

        From sys.fn_xe_file_target_read_file('C:\Tmp\XE20150701*.xel',Null,Null,Null)) As XE_File

        Outer Apply xml_event_data.nodes('//event') (X))

Select    *

From    T

Order By timestamp Desc;

לוקח למערכת לפעמים כמה שניות לעדכן את הקובץ, ולכן כדאי להמתין כדקה מהרצת הפקודות לנסיון, ועד שמנסים לראות אם הן נקלטו.
אזהרה: הסקריפט הוא Case Sensitive בכל מה שקשור ל-XML, ולכן אין להפוך event ל-Event וכו’; ואם נעלמים נתונים מעמודות – לבדוק קודם כל בכיוון הזה..
את עמודת plan_handle שכוללת מידע מסוג Varbinary ניתן לפענח כך (כמובן – עם מה שמופיע אצלכם..):

Select * From sys.dm_exec_sql_text(0x0300070009639409D8259000C8A4000001000000000000000000000000000000000000000000000000000000);

לא הצלחתי לשלב את זה בשליפה עצמה מהקובץ, ומי שיצליח – שיעדכן אותי!
בשלב הזה נותנים למערכת לרוץ כמה שצריך, וכשבאה העת לעצור, עושים זאת כך-

Alter Event Session XE20150701 On Server State=stop;

Go

אם רוצים למחוק את ה-Session לגמרי ולא רק לעצור, אזי כך:

Drop Event Session XE20150701 On Server;

GO

את הקובץ ניתן להמשיך ולתחקר גם כשה-Session נעצר או נמחק.
לבסוף, ניתן למחוק את הקובץ עצמו לאחר שאין בו עוד צורך:

Exec xp_cmdshell 'Del/q C:\Trace\*.xel';

אפשר כמובן להמיר חלק מהפעולות כאן בשימוש בממשק הגרפי של ה-SSMS: שם השרת => Management => Extended Events.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *