08Jun 2020 by Grant Fritchey 3 Comments

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
25Jun 2012 by Grant Fritchey 3 Comments

Does Encryption Affect Seeing Statements in Deadlock Graphs?

Uncategorized
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: CREATE PROCEDURE DL2e WITH ENCRYPTION AS BEGIN TRANSACTION UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 2 WHERE ProductID = 448 AND PurchaseOrderID = 1255; Then I’ll execute things in the following order. From one connection this query: UPDATE Purchasing.PurchaseOrderHeader SET Freight = Freight * 0.9 --9% discount on shipping WHERE PurchaseOrderID = 1255; From a second connection, my stored procedure: EXEC dbo.dl2e; Then, back on the first connection, this query: UPDATE Purchasing.PurchaseOrderDetail SET OrderQty = 4 WHERE ProductID = 448 AND PurchaseOrderID = 1255; That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: <deadlock> <victim-list> <victimProcess id="process472310928" />…
Read More
23Jan 2012 by Grant Fritchey 6 Comments

Deadlock Monitoring

T-SQL
There are four different ways you can get information about deadlocks in your system. These are: traceflag 1204 traceflag 1222 trace events extended events For years I’ve been pushing traceflag 1222 as the best of the lot. Well, that’s over. I’ve been learning more and more about extended events and I’m currently in love with xml_deadlock_report event. Why? Simple, it has everything that traceflag 1222 has, but there are two glorious things about it. First, it’s not going to be filling up my error log with, for the error log, noise. Seriously. As much as I liked the information displayed from traceflag 1222, I didn’t like what it did to the log, but I saw it as a necessary evil. Second, it’s XML baby! That means you can set up…
Read More
06Aug 2008 by Grant Fritchey No Comments

Deadlocks vs. Blocks

SQL Server, T-SQL
It makes me crazy when I interview someone who has five or more years as a DBA, but they don't know the difference between a block and a deadlock. It's a complete showstopper for me. If you don't know this, you're an entry-level DBA, don't talk to me about your years of experience. Sorry, but there it is. Here's someone that's kinder than I am in every way. Not only have they cut people slack on this question, but he's provided a well done answer to the question. For those who may interview with me in the future, go and read this and understand it.
Read More