6

I have been dealing with deadlocks, specifically getting their information, as you can see on this post here.

On this same post Shanky recommended "You must now rely on extended events trace to capture deadlock information"

My questions would be:

1) is there any increase of CPU by using extended events?

2) how much memory, disk space and I/O does it use?

any link to related documentation will be a bonus.

2
  • 1
    I think you should also focus on increases in wait time as well as increases in resource usage. Perhaps worth an edit? Sep 7, 2015 at 19:59
  • 1
    Deadlocks are captured in the system health extended events session which is enabled by default anyway. You can extract them eg. with dba.stackexchange.com/questions/10644/… Apr 1, 2016 at 4:18

2 Answers 2

7

Of course there will be an increase on CPU, as any other process on the server. But Extended Events are recommended precisely due to the low resource needs of running them. Using Extended Events to capture information takes much fewer resources than using the long old known profiler tool for example. Use it wisely of course, don't setup and run thousands of sessions to capture tons of data, then you can have a problem. We have used Extended Events on our own servers for auditing different processes and from our experience, we almost didn't saw a measurable increase on CPU activity. Yes, it takes some disc space to record all data it gathers, but again, think ahead and plan were to save it in order to affect as less as possible production environment.

As for 2nd question: it depends, as usual. Each system, platform and configuration are different, none is exactly to the other. So, if not possible to tell you how much RAM, CPU, disk space or IO will gonna take. But certainly will not be so much. Again, use common sense, don't start hundreds of sessions capturing gazillion of data, because then obviously you will have a penalty on performance.

For more detailed info check here, here here and here.

1
  • 2
    To add to this, XE has its own process space, over against profiler trace so resource consumption is external to the sql os, as far as i recall.
    – swasheck
    Mar 31, 2016 at 19:45
3

When enabling extended events, use filters, filter more aggressively in the beginning (e.g. filter on a duration above 100ms, then drop it down if you are happy there is no negative impact). Additionally deploy the Extended Event Traces to your non-production environments first.

For the best results, measure the impact with the queries below.

The query below gives an indication of how much data is written in GB to the ringbuffer/eventfile/router etc. NOTE: bytes_written field is not available in SQL 2016 and lower versions.

--Extended Event File/Ring Buffer Operational Stats (SQL2017, 2019)
;WITH t
AS
(
    SELECT
      s.name
    , st.execution_count
    , st.target_name
    , execution_duration_s  = CAST(st.execution_duration_ms/1000.0 AS DECIMAL(18,1))
    , GB_written    = CAST(st.bytes_written/POWER(1024.0,3) AS DECIMAL(18,3))
    , target_data           = CAST(st.target_data AS XML)
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s 
      ON s.address = st.event_session_address   
)
SELECT
name
, target_name
, execution_count
, execution_duration_s
, GB_written
, [file_name] = COALESCE(
               xed.target_data.value('(@name)[1]', 'NVARCHAR(512)'), target_name)
FROM t
OUTER APPLY t.target_data.nodes('//EventFileTarget/File') AS xed (target_data)

Use the below query for SQL 2016 and lower versions

--Extended Event File/Ring Buffer Operational Stats (SQL 2012 to 2016) 
;WITH t
AS
(
    SELECT
      s.name
    , st.execution_count
    , st.target_name
    , execution_duration_s  = CAST(st.execution_duration_ms/1000.0 AS DECIMAL(18,1))
    , target_data           = CAST(st.target_data AS XML)
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s 
      ON s.address = st.event_session_address   
)
SELECT
name
, target_name
, execution_count
, execution_duration_s
, [file_name] = COALESCE(
               xed.target_data.value('(@name)[1]', 'NVARCHAR(512)'), target_name)
FROM t
OUTER APPLY t.target_data.nodes('//EventFileTarget/File') AS xed (target_data)

This query is useful to see if events are being dropped, this is more to measure the Extended Event's effectiveness. More reading here.

--Extended Event Operational Statistics
SELECT
     s.name
   , s.total_regular_buffers
   , s.regular_buffer_size
   , s.total_large_buffers
   , s.large_buffer_size
   , s.dropped_event_count
   , s.dropped_buffer_count
   , s.largest_event_dropped_size
FROM sys.dm_xe_sessions s
2
  • very useful thanks for posting. I noticed it works well on sql 2019 but not on sql 2016 though. Jun 1, 2021 at 13:48
  • 1
    Thanks Marcello. I see it works fine for SQL 2017 and up. For lower versions I have removed the bytes_written column. Jun 2, 2021 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.