AWS RDS and SQL Server Deadlocks

AWS, RDS
What's the story with AWS RDS and SQL Server deadlocks? I'm approaching AWS RDS like I was taking on a new role at a new organization. Do we have backups in place? Yes, great. Can I test them? Yes. Do they meet our RTO & RPO? Yes. Moving on. What have we got for monitoring? AWS RDS has a good percentage of the fundamentals. Now, it's laid out a little oddly. You have the stuff going through CloudWatch which is largely OS oriented. Then you have enhanced monitoring, which you have to turn on, which covers eight key metrics for SQL Server. Finally, you can enable Performance Insights which gives you metrics on query behaviors (and yeah, any or all of these may be the subject of upcoming blog posts).…
Read More

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

Getting Started on AWS RDS

AWS, RDS
I'm expanding my skill set into AWS in a big way. So, one of the things I do when I'm learning a technology is to write blog posts about that tech. So, prepare yourself for a bunch of info on AWS. I'll be working with RDS, which will include SQL Server & PostgreSQL for certain. I'll also be posting quite a bit on AWS DevOps as I get into it. The usual stuff, including SQL Server, query tuning, Extended Events, Azure, Azure DevOps, and all the rest is not going away. I'm just adding another topic. Today, we're getting started on AWS RDS. Creating an RDS Instance The thing is, there's a lot of documentation available on getting started on AWS RDS, directly from Amazon. It's embedded neatly into the…
Read More