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

system_health Extended Events in Azure SQL Database

Azure, SQL Server
The system_health Extended Events session is incredibly useful. Further, it's running, by default, in every server you have under management that is 2008 or greater. Things are not the same in Azure though. system_health in Azure SQL Database If you look at the documentation for system_health, it shows that it's applicable to Azure SQL Database. However, if you try to run the example query, it won't work. This is because the implementation of Extended Events inside Azure SQL Database is a little different. Instead, you need to use the Azure SQL Database equivalent system views to create the same query like this: SELECT CAST(dxdst.target_data AS XML) FROM sys.dm_xe_database_session_targets AS dxdst JOIN sys.dm_xe_database_sessions AS dxds ON dxds.address = dxdst.event_session_address WHERE dxds.name = 'system_health'; Now, running this in Azure, prepare to be…
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