Note: This writeup is based on a production issue that I encountered for a vendor app. I used what I learned from the experience to write up an independent demo to show the potential impact this problem can have on a production system. We’re going to demonstrate the impact of parameter data types on performance. For this scenario we will create a 1 table database that contains user sessions. During typical use, we’d have 1 row per active session in this table and inactive sessions are cleared on a schedule.

While analyzing the system_health on a few SQL Servers, I noticed that error 17830 is logged about 30 times every minute, and this behavior is consistent across a set of servers. After doing some googling, I came across a very detailed writeup about this error on bobsql.com. This post saved me quite a bit of troubleshooting time as it explained the details behind what may cause this error and a potential fix.

I’m pretty new to sp_blitzLock which is a great utility stored procedure that’s part of the First Responder Kit that makes analyzing deadlocks simpler by parsing out a lot of the information from the XML deadlock reports. Unfortunately, I have noticed that on some servers, runtime of the SP can take quite a while. My analysis has shown that this is not really an issue with sp_blitzLock but rather SQL Server’s ability to work with XML.