Best method for isolating long running locks?
On a SaaS production database with 7 users (5 human, 2 backend) many are experiencing SQL cliënt timeouts at 30 seconds, our standard .Net CommandTimeout.
This SQL Server instance (out of our ten) contains some 5.000 customer databases, all performing pretty well for the past 20 years.
What is currently the fastest way to isolate the culprits of long running locks/transactions? Specifically > 30s.
Important: we use transaction isolation level REPEATABLE READ.
Activity monitor? Yuck!
Sql Server Profiler? Best events selection and filters?
Dmv's? Capture into tables? (I've done that before)
Extended events? Seems the way to go but extremely complex and long learning curve.
Brent Ozar sp_Blitz something or other...?
Isn't there a standard thingy/wheel (I hate reinventing wheels) out there that will simply and easily capture and list all the long running locks/transaction (and their sql text) on a specific db during a timespan?
Of course once captured, I have the challenge of correlating the culprit SQL statement or batch to a server code process, but that I can handle.
Suggestions welcome. I've worked with SQL Server for almost 25 years...
Some of the methodologies you've listed so far are reasonable ways to accomplish your goal. I personally like Adam Machanic's http://whoisactive.com/ which can be ran on demand or you can http://whoisactive.com/docs/25_capturing/ . That's been my methodology for the last few years, even on very transactionally heavy and report busy servers.
Just keep in mind it returns the query and entity text, so if your job logs those columns as well, you may want to add a second step to delete old data so that your log table doesn't consume too much disk space. My current job runs every 10 seconds and retains one months worth of data and my log table is roughly 100 GB big (with proper indexing). Though my setup is probably a little overkill.
There's also a multitude of monitoring tools out there. I'm just starting to get my hands dirty with the SolarWinds https://www.solarwinds.com/database-performance-analyzer/use-cases/sql-database-monitor . I chose them because they absorbed SentryOne who previously made really good tools, and this SQL Monitor tool sounded like it was very thorough in monitoring.