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