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')
    ,  = 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:

SQL SERVER - SQL Profiler vs Extended Events devext1

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

SQL SERVER - SQL Profiler vs Extended Events devext2

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

SQL SERVER - SQL Profiler vs Extended Events devext3

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:

SQL SERVER - SQL Profiler vs Extended Events devext4

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

SQL SERVER - SQL Profiler vs Extended Events devext5

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)

, , ,
Previous Post
SQL SERVER – FIX – Linked Server Error 7416 – Access to the remote server is denied because no login-mapping exists
Next Post
SQL SERVER – Fix Error – 948 A downgrade path is not supported. The database cannot be opened because it is version.

Related Posts

8 Comments. Leave new

  • Sheldon Hull
    June 22, 2016 7:37 am

    I’m a long time advocate of their stuff.. I like event profiler though it has some really annoying bugs they really need to fix….. Like filters not being saved on edits, and no graphical plan option,which ssms offers.. Once they give it more attention it will be the best profiling toolout there for sure

    I’ve been meaning to write on it,, nice to see someone else has found out about it!

    Reply
  • Erin StellatoErin Stellato
    June 22, 2016 6:17 pm

    Hi Pinal-

    Just to understand something from your post.. You wrote:

    “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 very uncomfortable way. And if SSMS 2008

    is used, the stalemate situation occurs – this version of SSMS does not support Extended Events.”

    Can you explain why working with Extended Events in SSMS 2012+ is “uncomfortable”? I agree that for SQL Server versions 2008 and 2008R2, Extended Events isn’t that viable, and because all events from Trace don’t have a comparable event in XE until 2012, we recommend that people continue to use Trace through SQL Server 2008R2.

    However, with the introduction of the UI for Extended Events in 2012, it’s use is completely feasible, and in fact, data capture and analysis can all be done with SSMS, there is no reason to use a third party tool (or XML) for analysis. You can open, for example, event_file output (.xel files) right in SSMS for analysis.

    So, I’m really interested to understand what you find uncomfortable about the XE implement in SSMS 2012+.

    Thanks!

    Erin

    Reply
    • Hi Erin,

      Thank you for your kind note. You have a very valid point.

      Let me clarify what I mean, it is written keeping database user who are beginner and not everyone understands various details of the Extended Events. What I meant in the context was more of not easy to use for beginners.

      I agree that in SQL Server 2012 and now in 2016 we have interface which has made things easier.

      Now when I read the blog post with your comment, I think I should have used a different word instead of uncomfortable.

      Thanks for reading and helping me correct.

      Reply
  • Erin StellatoErin Stellato
    June 22, 2016 6:38 pm

    Pinal-

    Thanks for clarifying! Though I admit, I have more questions :) I am interested in understanding what is not easy about the Extended Events UI if you’re a beginner (and even if you’re not).

    I think it’s a pretty straight-forward UI, and I have a keen interest in helping people understand and use XE, so if you have any comments to share about what isn’t easy to understand, I’d love to hear them!

    Thanks!

    Erin

    Reply
    • Hi Erin,

      You are correct. Please pardon my English as it is my second/third language.

      Once we learn it is easy to understand for sure. As I said uncomfortable may not be the correct usage of words, I should have used new learning instead.

      I have changed the blog post.

      Thank you Erin!

      Reply
  • Hey,

    I think there is a typo in your code shown to pull data from the trace (missing ‘statement’ on the left side of the assignment for that column).

    Reply
  • Hi I am New, Please Help
    Find the USER which has change the Status of the JOB (From enabled to disabled) intentionally or accidently and business impacted.

    Reply

Leave a Reply

Menu