deadlockextended-eventssql serversql-server-2012
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.
The conflict boils down to network_activity being an Indexed View which needs to be maintained (internally) across the DML statements. That is most likely why SP1 is wanting a lock on the IX_follow-member_id_includes index as it is probably used by the View (it looks to be a covering index for the View).
Two possible options:
Consider dropping the Clustered Index on the View so that it is no longer an Indexed View. Does the benefit of having it outweigh the maintenance cost? Do you select from it frequently enough or is the performance gain of having it indexed worth it? If you run these procs rather frequently, then maybe the cost is higher than the benefit?
If the benefit of having the View be indexed does outweigh the cost, then consider isolating DML operations against the base tables of that View. This can be done through the use of Application Locks (see sp_getapplock and sp_releaseapplock). Application Locks let you create locks around arbitrary concepts. Meaning, you can define the @Resource as "network_activity" in both of your Stored Procs which will force them to wait their turn. Each proc would follow the same structure:
BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'network_activity', @LockMode = 'Exclusive';
...current proc code...
EXEC sp_releaseapplock @Resource = 'network_activity';
COMMIT TRANSACTION;
You need to manage errors / ROLLBACK yourself (as stated in the linked MSDN documentation) so put in the usual TRY...CATCH. But, this does allow you to manage the situation.
Please note: sp_getapplock / sp_releaseapplock should be used sparingly; Application Locks can definitely be very handy (such as in cases like this one) but they should only be used when absolutely necessary.
In SQL Server 2012 the event XML output of Extended Events changed dramatically; enough where it broke any code used for SQL Server 2008. A write of this can be found by Jonathan Kehayias: Extended Events Changes in SQL Server 2012 – Event XML for XML data elements
You will need to change the CTE for xDeadlock to the following:
;WITH xDeadlock (Contents)
AS
(
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
SELECT XEvent.query('.') AS XEvent
FROM (
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' ) AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent )
) AS src
), Vicitims AS...
I moved the script from that blog post to my GitHub repository and you can download it from here, have blog post scheduled for this morning with the link as well.
Best Answer
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.