Fun Fact: AWS RDS and system_health

AWS, RDS
Did you know that the system_health Extended Event session was running in your RDS instances? Well, it is. HOWEVER. This query, which works perfectly fine on my on premises instance of SQL Server, will fail: SELECT @path = dosdlc.path FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc; SELECT @path = @path + N'system_health_*'; WITH fxd AS (SELECT CAST(fx.event_data AS XML) AS Event_Data FROM sys.fn_xe_file_target_read_file(@path, NULL, NULL, NULL) AS fx ) SELECT dl.deadlockgraph FROM ( SELECT dl.query('.') AS deadlockgraph FROM fxd CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl; Whereas, thanks to Aaron Bertrand, this query will work just fine: WITH fxd AS (SELECT CAST(fx.event_data AS XML) AS Event_Data FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx ) SELECT dl.deadlockgraph FROM ( SELECT dl.query('.') AS deadlockgraph FROM fxd CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl;…
Read More

Extended Events: system_health and a Long Running Query

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but, without actually doing any work to capture query metrics? Oh, yeah, I can do that right now, and so can you. All we need is something that is built into every single server you currently have under management (sorry, not Azure SQL Database) that is SQL Server 2008 or better: system_health system_health Extended Event Session The system_health Extended Events session is very well documented by Microsoft here. I've talked about it before, in relation to Azure SQL Database, and, on the same topic we're going over today, long running queries and waits (cause if you have a long running query, you…
Read More

Extended Events, the system_health Session, and Waits

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I advocate for, use, document, teach, and just downright love, Extended Events. They are so much better than the old Trace Events (aka, Profiler) that it's sometimes difficult to keep from just gushing. Let's talk about a common situation that you're going to run into on your servers all the time and how you can put Extended Events to work to help you, without actually doing any work at all. What's that? Be lazy and get rewards? Yes. The Extended Events system_health Session On your servers, any of them that are SQL Server 2008 or newer, right now, unless you've performed actions to prevent this, you're running the Extended Events system_health session. It's just happening, currently, on all your servers. Nothing you need to do about it at all. I'll…
Read More