Friday, February 5, 2021

How does xSQL Profiler work

xSQL Profiler uses the same underlying technology as Microsoft SQL Server Profiler and adds additional capabilities such as:

  • ability to execute the same trace on multiple SQL Server instances simultaneously.
  • powerful filtering allowing the user to define precise traces that capture exactly what the user wants, nothing more and nothing less.
  • advanced scheduling 
Furthermore, the installation of xSQL Profiler is very simple and does not 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 to using either SQL Server authentication or Windows authentication.

Trace Structure

The diagram below illustrates 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 events. 


xSQL Profiler events 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 will 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. 

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 uses the standard SQL Server trace technology to gather the trace result. The diagram below illustrates how this works.

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 the temporary 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.

xSQL Profiler supports all editions of SQL Server including MSDE and SQL Server Express. Download the Profiler now and try for yourself. 

0 comments:

Post a Comment