One of the most powerful features SQL Server has, is it's ability to monitor just about every aspect of it's operation with mechanisms such as SQL traces. A SQL Trace is an internal SQL Server mechanism for capturing events which are raised basically every time something "happens" on SQL Server. All versions of SQL Server support traces which can be created using a set of system stored procedures designed specifically for this task. However, anyone that has worked with these stored procedures before can tell you that they are pretty confusing because you have to specify every low level detail. For example, to start a trace, you have to specify the events it will trace, and for every event the properties or columns for which the trace will register data. These data will be saved in a trace file which, again needs to be specified as a parameter to the stored procedure, and then you use another stored procedure to read the data from the file. To make things even more complicated, the different events and event columns are not specified by their name (even though it is unique), but by their IDs. Taking into consideration the fact that there are hundreds of different events with tens of columns each, you can easily see how using T-SQL code to manipulate traces can be very difficult and time consuming. Well, Microsoft has seen this as well and constructed SQL Server Profiler which provides a UI that makes managing traces quite easy. Unfortunately, this is not available on Express versions of SQL Server. These versions do support traces, however, you have to do everything by hand.
This is where we come in. Our xSQL Profiler provides a user interface that hides the low level details of starting an analyzing trace data on SQL Server and, best of all, you can use it to monitor SQL Server Express instances free of charge. You can get the setup from this link and for a detailed explanation of the installation process check out our previous article on "Installing the new xSQL Profiler 2.1". Apart from offering every functionality that is offered by Microsoft's SQL Server Profiler, our xSQL Profiler also offers a few higher level features that simplify monitoring SQL Server Express instances even more. Lets have a look at some of the most important higher level features, offered for SQL Server Express:
This is where we come in. Our xSQL Profiler provides a user interface that hides the low level details of starting an analyzing trace data on SQL Server and, best of all, you can use it to monitor SQL Server Express instances free of charge. You can get the setup from this link and for a detailed explanation of the installation process check out our previous article on "Installing the new xSQL Profiler 2.1". Apart from offering every functionality that is offered by Microsoft's SQL Server Profiler, our xSQL Profiler also offers a few higher level features that simplify monitoring SQL Server Express instances even more. Lets have a look at some of the most important higher level features, offered for SQL Server Express:
Automatically persist trace data
xSQL Profiler automatically saves all trace data in a structured format in the database that's created during the installation process. This means that the DBA can view the trace information at any time through xSQL Profiler's UI. Also, because this data is persisted on a SQL Server database, you can always easily extend xSQL Profiler's monitoring capabilities. For example, you can place xSQL Profiler's database on a web server and build a web application on top of that database that allows users that may not have access to the machine where xSQL Profiler is installed to still be able to access and analyse real time trace data from the monitored servers.
Advanced filtering and reporting
xSQL Profiler offers the ability to add filters directly to your traces, to make them register only a portion of the data, reducing the load that running traces might impose on your server. Apart from this, xSQL Profiler also enables you to further filter the data that are saved in the database when you are generating reports. If the wide range of built in filters still does not meet your requirements, xSQL Profiler offers the ability to write your own T-SQL queries and query the underlying database to get the data you need.
Scheduling
One of the main benefits of xSQL Profiler is the ability to schedule the running time of traces. This is one of the things that cannot be done through Microsoft's SQL Server Profiler and would be way to complicated to achieve with queries. With xSQL Profiler you can add multiple schedules for each trace to make the monitoring process as efficient and effective as possible
High level event definition
xSQL Profiler offers the ability to start traces with the same event definitions as SQL Server Profiler does. However, we noticed that these event definitions were too low-level and to audit particular aspects of SQL Server, such as user logins, query execution and so on, you have to create traces based on groups of multiple event definitions. Thus, we have created xSQL Profiler events, which are built in event definitions that allow you to audit the aforementioned aspects of SQL Server without needing to manually pick every SQL Server event. Also, if the built in event definitions don't fit your needs, you can always create your own.
So, there you have it, a guide to what xSQL Profiler can do to simplify monitoring of SQL Server Express instances.
Posts
