Last modified by Billie D on 2021/03/05 13:55
From version 3.1
edited by Billie D
on 2021/02/16 22:55
To version 4.1
edited by Billie D
on 2021/02/16 23:02
Change comment: There is no comment for this version

Summary

Details

Icon Page properties
Content
... ... @@ -66,12 +66,99 @@
66 66  
67 67  === Packages ===
68 68  
69 -A package is a container for the objects used within Extended Events. Here are the four
70 -types of SQL Server package:
69 +A package is a container for the objects used within Extended Events. Here are the four types of SQL Server package:
71 71  
72 -* Package0 – The default package, used for Extended Events system objects.
73 -* Sqlserver – Used for SQL Server–related objects.
74 -* Sqlos – Used for SQLOS-related objects.
75 -* SecAudit – Used by SQL Audit; however, its objects are not exposed.
71 +* **Package0** – The default package, used for Extended Events system objects.
72 +* **Sqlserver** – Used for SQL Server–related objects.
73 +* **Sqlos** – Used for SQLOS-related objects.
74 +* **SecAudit** – Used by SQL Audit; however, its objects are not exposed.
76 76  
76 +=== Events ===
77 +
78 +An **event** is an occurrence of interest that you can trace. It may be a SQL batch completing, a cache miss, or a page split, or virtually anything else that can happen within the Database Engine, depending on the nature of the trace that you are configuring. Each event is categorized by **channel** and **keyword** (also known as category). A //**channel**// is a __high-level categorization__, and all **//events//** in SQL Server 2016 fall into one of the channels
79 +
80 +* **Admin** - __Well-known events__ with well-known resolutions. For example, //deadlocks//, //server starts//, //CPU thresholds// being exceeded, and the use of deprecated features.
81 +* **Operational** - Used for __troubleshooting__ issues. For example, //bad memory// being detected an AlwaysOn Availability Group replica changing its state, and a //long IO// being detected are all events that fall within the Operational channel.
82 +* **Analytic** - __High-volume__ events that you can use for troubleshooting issues such as //performance//. For example, //a transaction beginning//, a //lock// being acquired, and a //file read completing// are all events that fall within the Analytic channel.
83 +* **Debug** - Used by //developers to diagnose issues// by returning __internal data__. The events in the Debug channel are __subject to change in future__ versions of SQL Server, so you should avoid them when possible.
84 +
85 +SQL Server exposes __122 events__ relating to AlwaysOn.
86 +
87 +=== Targets ===
88 +
89 +A target is the __consumer of the events__; essentially, it is the device to which the __trace data will be written.__
90 +
91 +[[image:SQLAG0013.JPG||height="686" width="968"]]
92 +
93 +=== Actions ===
94 +
95 +Also known as Global Fields, Actions are commands that allow additional information to
96 +be captured when an event fires. An action is fired synchronously when an event occurs
97 +and the event is unaware of the action. There are 50 actions available that allow you to
98 +capture a rich array of information, including the statement that caused the event to fire,
99 +the security context under which the statement ran, the transaction ID, the CPU ID, and
100 +the call stack.
101 +
102 +=== Predicates ===
103 +
104 +Predicates are filter conditions that you can apply before the system sends events to the
105 +target. It is possible to create simple predicates, such as filtering statements completing
106 +based on a database ID, but you can also create more complex predicates, such as only
107 +capturing the role change of an AlwaysOn Availability Group replica if it happens more
108 +than twice.
109 +
110 +Predicates also fully support short-circuiting. This means that if you use multiple
111 +conditions within a predicate, then the order of predicates is important, because if the
112 +evaluation of the first predicate fails, the second predicate will not be evaluated. Because
113 +predicates are evaluated synchronously, this can have an impact on performance.
114 +Therefore, it is sensible to design your predicates, so that predicates which are least likely
115 +to evaluate to true are placed before predicates that are very likely to evaluate to true.
116 +For example, imagine that you are planning to filter on a specific database (with a
117 +database ID of 6), but this database accounts for a high percentage of the activity on
118 +the instance. You also plan to filter on a specific user ID (UserA), which is responsible
119 +for a low percentage of the activity. In this scenario, you would use the WHERE
120 +(([sqlserver].[username]='UserA') AND ([sqlserver].[database_id]=(6~)~)~)
121 +predicate to first filter out activity that does not relate to UserA, before then filtering out
122 +activity that does not relate to database ID 6.
123 +Types
124 +All objects within a package are assigned a type. This type is used to interpret the data
125 +stored within the byte collection of an object. Objects are assigned one of the following
126 +types:
127 +• Action
128 +• Event
129 +• Pred_compare (retrieve data from events)
130 +• Pred_source (compare data types)
131 +• Target
132 +• Type
133 +
134 +=== Maps ===
135 +
136 +A map is a dictionary that maps internal ID vales to strings that DBAs can understand.
137 +Map keys are only unique within their context and are repeated between contexts. For
138 +example, within the statement_recompile_cause context, a map_key of 1 relates to a
139 +map_value of Schema Changed. Within the context of a database_sql_statement type,
140 +however, a map_key of 1 relates to a map_value of CREATE DATABASE. You can find a
141 +complete list of mappings by using the sys.dm_xe_map_values DMV.
142 +
143 +=== Sessions ===
144 +
145 +A session is essentially a trace. It can contain events from multiple packages, actions,
146 +targets, and predicates. When you start or stop a session, you are turning the trace on
147 +or off. When a session starts, events are written to memory buffers and have predicates
148 +applied before they are sent to the target. Therefore, when creating a session, you need to
149 +configure properties, such as how much memory the session can use for buffering, what
150 +events can be dropped if the session experiences memory pressure, and the maximum
151 +latency before the events are sent to the target.
152 +
153 +== Creating an Event Session to Monitor Availability Group ==
154 +
155 +You can create an event session using either the New Session Wizard, the New Session
156 +Dialog Box, or via T-SQL. To create a session using the New Session Wizard, drill through
157 +Management ➤ Extended Events in Object Explorer, and select New Session Wizard
158 +from the context menu of Sessions. This will cause the Introduction page of the New
159 +Session Wizard to be displayed.
160 +After passing through the Introduction page, you will find the Set Session Properties
161 +page, as displayed in Figure 9-5. Here, you can configure a name for the Session and also
162 +specify if the Session should automatically be started on creation.
163 +
77 77