You also have to enter values for the output table (the name of the table that will hold the data in the MDW database), an optional filter to futher limiting the rows you want to save to the database and the comma separated list of the event session fields you want to add to the destination table. ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS This is the session I am using:ĬREATE EVENT SESSION ON SERVER ADD EVENT sqlserver.blocked_process_report The most important part of this step is providing a definition for the Extended Events session that captures blocked processes. Click the “Parameters” button and fill the dialog with the information requested. Now enter the information for the first collection item (Blocked Processes)Ħ. Right click the Collection Set “Blocking and Deadlocking” and select “Add”. Fill the information about your Collection Set and hit “Save”ĥ. Right click the “Collection Sets” node and select “Add”Ĥ. If the custom collector types provided by Extended T-SQL Collector are not installed, install themģ. Start the Collectionset Manager and connect it to your instance.Ģ. With Extended T-SQL Collector you can create a collection set to capture blocked processes and deadlocks using the GUI.ġ. If you do, be aware that you’re in a very small minority. This part of the Data Collector is missing a Graphical User Interface and requires messing with XML. Nobody likes XML. If you ever had the chance to play with Data Collector and tried to create a custom collection set, you probably have noticed that it’s not as easy as it should. All you have to do is download your copy of Extended T-SQL Collector, install it on your server and then set up a collection set for blocking and deadlocking. The good news is that a tool that can do that already exists and even better news is that it’s free and open source! Its name is Extended T-SQL Collector.Įxtended T-SQL Collector allows you to combine Extended Events and Data Collector, with the addition of alerting for interesting events. It would be great if there was a way to capture events with an XE session, store them to a table for later analysis and alert as soon as the event occurs. With SQL Trace we had Event Notifications that did exactly this, using a Service Broker queue. However, as far as alerting is concerned, they are still lacking a piece of the puzzle. In fact Extended Events don’t provide a built-in way to fire an alert whenever an event is captured. In this post I will show you an easy way to combine monitoring and alerting using the cutting edge monitoring technology available in SQL Server: Extended Events.Įxtended Events are cool: they can capture events not available otherwise and they impact the monitored server much less than other capture technologies (e.g. For instance, capturing blocked process reports with an Extended Events session is very easy, but firing an alert when the event is captured is not. Some other methods are very useful for capturing all the information we need, but don’t provide a way to fire an alert when the event is captured. For instance, a simple SQLAgent alert for “deadlocks/sec > 0” is enough to shoot an email to the DBAs whenever a deadlock happens, but it doesn’t provide a simple way to capture the deadlock graph and include it in the email or save it to a destination table. Some are very useful for alerting, but don’t provide a simple way to capture additional information about the event. Sometimes, the tools available in SQL Server are not as friendly as we would like them to be. Monitoring blocking and deadlocking hasn’t always been easy.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |