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 when you have a large server farm things can escalate quickly and the associated costs can get out of control.

Custom event definitions make it possible for a single individual to handle practically unlimited number of servers. If you have all the right knowledge and experience with SQL Traces, creating custom event definitions in xSQL Profiler if very 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. Next, you 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.

Furthermore, xSQL Profiler allows you to complete automate the process by collecting trace data from target servers at pre-determined time intervals, enabling the DBAs to focus on analyzing the trace data instead of managing the traces. 

Download xSQL Profiler now an try it. xSQL Profiler supports all editions of SQL Server, including MSDE and SQL Server Express and it is completely free for a single SQL Express instance. 


Post a Comment