Server health and extended events - SQL Server Tutorial

- [Instructor] Extended Events is a SQL Server feature that allows you to create a customizable monitoring arrangement where system events trigger actions on a target consumer. The process starts with the Extended Events engine. It monitors the server instance for occurrences of an event that you want to keep an eye out for. This could be a deadlock situation that arose, or an index rebuild was initiated, or an error was generated. When an event on the watchlist is encountered, it's passed to the target. Targets can write information to a file, such as an error log, count up the number of times an event occurred by bucketing them, or watch for pairs of events, such as a lock being taken on a table, and then later released. If the paired item doesn't occur, then the target can flag that activity. In Management Studio, you can find Extended Events underneath the Management folder here in Object Explorer. We'll open up the Extended Events folder, and then finally open up the Sessions folder. Notice that there are already a couple of sessions created on the server. AlwaysOn_health, which is currently stopped, and you can see that based off of the icon here with the red square. We also have system_health and telemetry_xevents, or telemetry extended events. Both of those are currently running, and you can see that with the green triangle. To see what each of these events track, go ahead and right click on one of them, go down to Script Session as, CREATE To, and then open it up in a New Query Editor Window. Here, we can see the events that are added to the system_health extended events session. We have on event down here on line 24 that'll report errors where it has a severity of greater than or equal to 20, or, if the error number matches one of these numbers here, which all relate to memory errors. On line nine, we can see an event where a process is killed. On line 11, we see an event that gets triggered when a deadlock is encountered, and we have lots of events when a non-yielding scheduler problem occurs. You can scroll through this list and see all the different events that are created for the system_health session. Let's go ahead and close this tab, and we'll right click on telemetry_xevents, and do the same thing. Go to Script Session as, CREATE To, New Query Editor Window. This session has lots of events relating to columnstore indexes. So, for instance, it creates an index, or it builds the index, or it deletes a buffer. We also have events triggered by masking behavior. So, masking_ddl_dolumn_definition, or masking_traffic was encountered. We have an event that's watching for databases that are created and databases that are dropped on lines 18 and 19. You can go ahead and scroll through this to see all the different events that are created for the telemetry_xevents or the telemetry extended event session. Let's go ahead and close this tab. You can also find out information about extended events on your system with some T-SQL queries against some catalog views. Let's go ahead and select everything from a catalog view called sys.server_event_sessions. Here, we get three records back that indicate the three different event sessions that we already looked at. AlwaysOn_health is session ID 65537, system_health is session ID 65536, and telemetry_xevents on my computer is 66305. If you want to see what these events are tracking, we can select everything from sys.server_event_session_events. Let's go ahead and run line nine. This will show me all the individual events that are included in sessions on my machine. We can see the event session IDs down here on the left, the event IDs, and the name, and so on. We can filter this table to just a specific event. So for instance, if I only want to see the events that are included in the system health extended event, I can filter the query results to only the records where the event session ID is equal to 65536. Let's go ahead and run lines 11 through 13. Now we can see the same information that we got by scripting the event. We can see that we're looking for errors that are being reported, deadlocks that are encountered, wait info, and lots of other events. If we only want to see the events that are triggered by the telemetry extended event, let's go ahead and select lines 16 and 17 to filter those results. And it looks like I don't get any results. Let's go ahead and double check that event_session_id number, sometimes it changes on you. Let's go ahead and run line six here, and I'm looking for event ID 66305, and it looks like I do have a typo there. So 66305, let's go ahead and change that and run it again. Now I can see all of the events that are being watched for by the telemetry_xevents session. We're looking for servers starts and stops, error_reporteds, missing_column_statistics, and a whole host of other things. Finally, when each of these events is encountered, SQL Server is going to record some information. So, for instance, when event ID number one, which is a server_start_stop, is encountered, let's go ahead and see what gets tracked. Looks like everything from sys.server_event_session_actions, and again, we'll filter it to just the actions that are occurring in the system_health session. So go ahead and execute that and look for event ID number one. These first five records all relate to event ID number one, so when a server is started or stopped, SQL Server is going to record information based off of the callstack, the session_id, the database_id, any sql_text and any tsql_stack information. So these are the three built in sessions. To create your own extended events, just go ahead and right click on the Sessions folder and choose New Session. That'll allow us to give the session a new name. I'll just call it TempSession, and then we get to choose a template, which is basically a collection of pre-made events. Let's take a look at the standard template. We can see that the standard template captures all stored procedures and Transact-SQL batches that are run. This will be useful for monitoring general database server activity. We can change the template to something else. How about TSQL_Locks? This session will capture all of the Transact-SQL statements that are submitted to SQL Server by clients along with exceptional lock events. This one's useful for troubleshooting deadlocks, lock time-outs, and lock escalation events. If you want to customize the events that are being monitored, just switch over to the events page by clicking events on the left. Here, we can see an entire library of events that we can monitor from. Just scroll through the list, find something you want to track, click on it, and then press this arrow to add it over here into the selected events section. If you want to take that out, just click this arrow to move it back. Once you're done customizing the events, go ahead and press OK, and that'll create your new event. We can see it right down here in the Object Explorer. To start it, just right-click on it and choose Start Session. When you're done recording information, right-click on it and choose Stop Session. Let's actually right-click on it and choose delete. I'll go ahead and press OK and it'll remove it from my server. Finally, to see the information is being recorded by an extended event, you need to expand the session. For instance, I'll expand the system_health session, right-click on the event file, and choose View Target Data. That'll show you all of the events that have been captured, and you can click on each one to get additional details down below. These are all of the events that have been previously captured. If I want to see live data, though, I can right-click on the session name and choose Watch Live Data. This will bring up a window where I can watch events come through as they occur. Now this live data isn't going to be terribly exciting on my test machine, but on a live production server, you'll see events appear here as they happen. With extended events, either the built-in defaults or your own custom creation, you can monitor SQL Server for a host of problems, actions, and other triggering events.