SQL SERVER – SQL Profiler vs Extended Events

To tell you the truth, I didn’t know how to start this article about SQL Profiler vs Extended Events. After all, I decided to take mind off things and to watch a movie. The movie was Iron Man 2. In addition to the brilliant performance of Robert Downie Jr., one phrase stuck in my mind and immediately inspired my writing.

“Don’t get so attached to things, learn to let go.”
~ Iron Man 2 (Robert Downie Jr)

It seemed to me, that this phrase reflects status quo when it comes to selection of a query profiling solution. What would you choose: the old reliable SQL Profiler or something new? My personal choice is Extended Events. Why?

Try running SQL Profiler…or even several instances of it:) on the overloaded production server. And leave it alone for five-ten minutes.

Then, try executing a query to see what delays occurred during the work of SQL Trace:

SELECT
      wait_type
    , wait_time = wait_time_ms / 1000.
    , wait_resource = (wait_time_ms - signal_wait_time_ms) / 1000.
    , wait_signal = signal_wait_time_ms / 1000.
    , waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE [wait_type] IN (
        'TRACEWRITE', 'OLEDB', 'SQLTRACE_LOCK',
        'SQLTRACE_FILE_BUFFER', 'SQLTRACE_FILE_WRITE_IO_COMPLETION'
    )

It is important to realize that if the trace filter is not set, SQL Profiler may significantly load your server. That’s why the new tracing mechanisms, called Extended Events were included into SQL Server 2008.  They require less server resources and allow to trace more events.

To deal with Extended Events, we should go back to SQL Profiler and comprehend its work.

First, a new SQL trace is created, and information to be traced is noted:

DECLARE @id INT
EXEC sys.sp_trace_create @id OUTPUT, 2, N'D:\MyTrace'
EXEC sys.sp_trace_setevent @id, 10, 1, 1
EXEC sys.sp_trace_setevent @id, 10, 13, 1
EXEC sys.sp_trace_setevent @id, 10, 15, 1
EXEC sys.sp_trace_setevent @id, 12, 1, 1
EXEC sys.sp_trace_setevent @id, 12, 10, 1
EXEC sys.sp_trace_setevent @id, 12, 13, 1
EXEC sys.sp_trace_setevent @id, 12, 15, 1

To understand where these magical digits come from, you need to execute the following query:

SELECT
      EventCategory = c.Name
    , EventClass = e.Name
    , EventColumn = t.Name
    , EventID = e.trace_event_id
    , ColumnID = b.trace_column_id
FROM sys.trace_categories c
JOIN sys.trace_events e ON e.category_id = c.category_id
JOIN sys.trace_event_bindings b ON b.trace_event_id = e.trace_event_id
JOIN sys.trace_columns t ON t.trace_column_id = b.trace_column_id
--WHERE b.trace_event_id IN (10, 12)

Then, we can filter information that will be traced. For instance, let’s ignore queries sent by SQL Profiler:

EXEC sys.sp_trace_setfilter @id, 10, 1, 7, N'SQL Profiler'

After all settings, we can run the trace:

EXEC sys.sp_trace_setstatus @id, 1

To enable SQL Profiler to output trace results, we need to execute a query similar to the following:

SELECT SPID, TextData, ApplicationName, Duration = Duration / 1000, EndTime
FROM (
    SELECT TOP(1) [path]
    FROM sys.traces
    WHERE [path] LIKE N'D:\MyTrace%'
) t
CROSS APPLY sys.fn_trace_gettable(t.[path], DEFAULT)

When tracing is no longer required, we can stop and delete it:

DECLARE @id INT = (
    SELECT TOP(1) id
    FROM sys.traces
    WHERE [path] LIKE N'D:\MyTrace%'
)
EXEC sys.sp_trace_setstatus @id, 0
EXEC sys.sp_trace_setstatus @id, 2

The same principle is used in Extended Events. First, the event should be created:

CREATE EVENT SESSION XEvent ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (
        sqlserver.database_id,
        sqlserver.session_id,
        sqlserver.username,
        sqlserver.client_hostname,
        sqlserver.sql_text,
        sqlserver.tsql_stack
    )
    --WHERE sqlserver.sql_statement_completed.cpu > 100
    --    OR sqlserver.sql_statement_completed.duration > 100
)
ADD TARGET package0.asynchronous_file_target
(
    SET FILENAME = N'D:\XEvent.xet',
    METADATAFILE = 'D:\XEvent.xem'
)

To learn the event list, you can execute the following query:

SELECT
    package_name = p.name,
    event_name = o.name
FROM sys.dm_xe_packages p
JOIN sys.dm_xe_objects o ON p.[guid] = o.package_guid
WHERE o.object_type = 'event'

After creation, we need to run the event:

ALTER EVENT SESSION XEvent ON SERVER STATE = START

In the general case, you can get data from the trace with the following query:

SELECT
      duration = x.value('(event/data[@name="duration"])[1]', 'INT') / 1000
    , cpu_time = x.value('(event/data[@name="cpu_time"])[1]', 'INT') / 1000
    , logical_reads = x.value('(event/data[@name="logical_reads"])[1]', 'INT')
    , writes = x.value('(event/data[@name="writes"])[1]', 'INT')
    , row_count = x.value('(event/data[@name="row_count"])[1]', 'INT')
    , [sql] = x.value('(event/data[@name="statement"])[1]', 'NVARCHAR(MAX)')
    , [db_name] = DB_NAME(x.value('(event/action[@name="database_id"])[1]', 'INT'))
    , end_time = x.value('(event/@timestamp)[1]', 'DATETIME')
FROM (
    SELECT x = CAST(event_data AS XML).query('.')
    FROM sys.fn_xe_file_target_read_file('D:\XEvent*.xet','D:\XEvent*.xem', NULL, NULL)
) t

When the trace is no longer required, you can turn it off temporarily:

ALTER EVENT SESSION XEvent ON SERVER STATE = STOP

Or delete it:

IF EXISTS(
    SELECT *
    FROM sys.server_event_sessions
    WHERE name='XEvent'
) DROP EVENT SESSION XEvent ON SERVER

As I wrote earlier, Extended Events offer more monitoring options. If we compare the quantity of events (e.g. in SQL Server 2014) that can be traced with xEvents, SQL Trace is not a patch on Extended Events:

SELECT xevents = COUNT_BIG(*)
FROM sys.dm_xe_objects o
WHERE o.object_type = 'event'
SELECT sql_trace = COUNT_BIG(*)
FROM sys.trace_events e

xevents
————
874

sql_trace
————
180

On this point, we could draw a line and forget about SQL Profiler. But there is one nuance.

The work with xEvents in SSMS 2012…2016 is implemented in a new learning if you are a beginner. And if SSMS 2008 is used, the stalemate situation occurs – this version of SSMS does not support Extended Events.

I think that this is the main reason why very few people use Extended Events. Some are scared by its ascetic GUI. But admit that this is not the reason to abandon this technology… moreover, there is a great tool that drew my attention long ago.

dbForge Event Profiler for SQL Server  is a free and the more functional counterpart to SQL Server Profiler that can work with xEvents. What do I like in it? Well, in the first place it is syntax highlighting, which we will hardly ever see in Server Profiler:

Secondly, the tool allows to filter and sort the grid with tracing results:

The next advantage is lots of predefined templates for query profiling:

dbForge Event Profiler has one more feature that does not directly correlate with xEvents.

I wonder, have you ever faced a situation when a large SQL file must be executed, but SSMS rejected to open it or crashed because of OutOfMemory? If you have, I have a solution for your problems – with dbForge Event Profiler, you can execute large scripts without opening them in the editor:

All you have to do is to specify the database for the application of all changes:

And to click Execute.

It’s time to sum everything up. The obvious pros of Extended Events are:

1. xEvents requires less server resources and works faster
2. You can trace more server events
3. Flexible settings mechanism

The only con I can point out is inconvenience of working with xEvents and SSMS. But for me it is not a reason to stay an obedient aficionado of SQL Profiler. I can definitely recommend the free tool dbForge Event Profiler and open a brave new world of Extended Events to yourself.

Reference: Pinal Dave (https://blog.sqlauthority.com)