Friday, June 30, 2017

xSQL Profiler Use case: Custom event definitions

SQL Traces can be very effective for gathering information about the operation of a SQL Server provided you have a good utility to manage them, and a tool like xSQL Profiler can prove to be essential for this purpose. Among a large number of problems, xSQL Profiler provides solutions for, it can help drastically reduce the amount of expert manpower needed to monitor SQL Servers. How does it achieve this? 3 words: Custom event definitions.
One might think that the task of choosing what events and what columns to include is relatively easy. However, if you look at the MSDN documentation for traces you'll see that there are hundreds of different SQL events and even more event columns to choose from. Keeping in mind that a trace needs to be as efficient as possible, you can see how this task can become very complicated, very quickly. Which is why any person handling the monitoring of SQL Servers needs to have extensive knowledge of SQL Traces. If there are only a few SQL Servers to monitor one person should be enough to handle this task but if there are more than that, there should be more than one person, which could get quite expensive, because experts don't come cheap.
By using custom event definitions you only need one expert to create the custom trace events and then other people can take care of starting/stopping traces and preparing reports on the data these traces collect. If you have all the right knowledge and experience with SQL Traces, creating custom event definitions in xSQL Profiler if fairly easy.
You can either click on Trace > Add event definition or right click on the list of event definitions and click 'Add Event Definition'. The following dialog shows up.

First, you choose the list of SQL events that will be included in the event definition and then click 'Get Columns' to get the all the columns that are supported by these SQL Events.
After this, you can select only the columns that you want to be included in this trace to make it as efficient as possible while getting all the information needed. After the event definition is created, anyone can create a trace with this event definition and have all of its columns selected without having to worry which columns need to be selected and which do not.


Post a Comment