How do you use Extended Events?

SQL Server Administration
1

I'm starting to do more with Extended Events and I'm curious how others are using it.

Do you run many XE sessions? Do you pull the data into any type of central repository?

Any unusual events you've found that are helpful? (beyond login, logout, error, etc.)

2

I don't remember exactly but I used them once a few years ago when I was working on fixing a dead lock issue.

3

Hi Graz we have recently use Xevents to identify what Sp's are running on our environment but the actual file we stored the data on grew exceptionally, which cause the drive to get full. We then did the below solution which was nice using DMV's instead of Xevents

CREATE TABLE #demo_sp_exec_stats (
object_id INT,
database_id INT,
proc_name NVARCHAR(128),
last_execution_time DATETIME
)

MERGE INTO #demo_sp_exec_stats STAT
USING (
SELECT d.object_id,
d.database_id,
OBJECT_NAME(object_id, database_id) AS proc_name,
MAX(d.last_execution_time) AS last_execution_time
FROM sys.dm_exec_procedure_stats AS d
WHERE d.database_id = DB_ID('Database_Name')
GROUP BY d.object_id,
d.database_id,
OBJECT_NAME(object_id, database_id)
) AS SRC
ON STAT.object_id = SRC.object_id
WHEN MATCHED
AND STAT.last_execution_time < SRC.last_execution_time
THEN
UPDATE
SET last_execution_time = SRC.last_execution_time
WHEN NOT MATCHED
THEN
INSERT (
object_id,
database_id,
proc_name,
last_execution_time
)
VALUES (
SRC.object_id,
SRC.database_id,
SRC.proc_name,
SRC.last_execution_time
);

SELECT *
FROM #demo_sp_exec_stats