How xSQL Profiler works?

Introduction


xSQL Profiler uses the same underlying technology as Microsoft SQL Server Profiler, but improves it by offering additional capabilities such as:
  • ability to execute the same trace on multiple database servers simultaneously.
  • powerful filtering allowing the user to define precise traces that capture exactly what the user wants, nothing more and nothing less.
  • advanced scheduling
  • Support for all versions of SQL Server from SQL Server 2000 to 2016
  • .
Additionally, it has a very simple installation and doesn’t require any agent on the servers that you want to monitor. You just install xSQL Profiler on your monitoring machine and you can monitor any server to which you can connect using SQL Server or Windows authentication.

Trace Structure


The diagram below explains how a trace defined in xSQL Profiler is structured. At the lowest level we have the SQL Server events that are defined by SQL Server such as “RPC:Starting” or “Lock:Cancel”. On top of these low level events xSQL Profiler defines its own events called “xSQL Profiler Events”. xSQL Profiler includes some common events, but the full power of it relies on the ability to allow the user to fine tune them or define completely new ones.

An xSQL Profiler event can be defined by:
  • Selecting the underlying SQL Server events that you want to monitor.
  • Selecting the columns that you're interested in.
  • Defining filters to get only the results you need.

For example to define a xSQL Profiler event to monitor DELETE statements, you can select the “RPC:Starting” and “SP:Starting” events and apply a filter on the TextData column similar to “TextData LIKE ‘%DELETE%FROM%’”.

When you want to actually monitor some of your servers, you define a trace. The elements of a trace include:
  • xSQL Profiler events that you will include in the trace.
  • Servers and/or databases you want to monitor.
  • Additional filters on the selected events. For example, if you’re interested only in delete statements on the “USERS” tables, you’ll include a filter on the TextData column to get only those results.
  • You can start and stop the trace manually or you can schedule it to automatically start and stop at specified interval.
xSQL Profiler Trace Structure

When the trace is started the necessary SQL traces will be created on each selected server and the data will be automatically loaded in the xSQL Profiler central storage.

Trace Data


As explained above xSQL Profiler use the standard SQL Server trace technology to gather the trace result. The diagram below explains how this works.

xSQL Profiler Trace Data

When the trace is started (by the user or by the scheduler) the necessary traces will be created on all servers included in the trace. In this case “Trace 1” will be created on both “Server 1” and “Server 2”. When the trace is running the results are logged on temporary files on each server. At regular intervals (or when the user selects the “Load Data” from the menu) the data is imported from this data files to the Temp database on the remote servers. In this case the corresponding data will be loaded on the Temp database of “Server 1” and the Temp database of the “Server 2”. From the Temp database on each remote server the data are automatically transferred to the “xSQL Profiler Central Storage”. During this process the regular expression filters are applied and only the necessary data are moved to the central storage database.

Click here to download your copy of the xSQL Profiler now.