diagnosing SQL server locks

jtaylor

We have a SQL 2017 Server running on Windows Server 2019.  The server locks, almost on a schedule, each day around 3:30-5:00.  We can wait for it to open up.  Activity monitor on SSMS doesn't show anything out of the ordinary and reports show no locks.  This server is ONLY SQL and no other software running.  At the recommendation of a good friend I've installed SQL Monitor in hopes of finding a solution.  Can anyone recommend a specific place I should start looking?  This is pretty much stopping the company during this time.  Thanks for the input!!

Ben_P

Hi there,

SQL Monitor captures data on Blocking processes and also alerts on this, but doesn't directly send out reports on it. 

You can view information on blocking processes on the server overview for a SQL instance and the data is stored for the time period set in the data retention configuration.  So there is historical information on blocking available that you can see using the back in time mode https://documentation.red-gate.com/sm/sql-server-performance-and-activity-monitoring/dashboards-and-overviews/server-overview-pages/activity-graph-and-snapshot-metrics/rewinding-time-with-back-in-time-mode


But there is not directly access to this information in the Reports.  You can make a tile using the Alert tile type:
​

And use a filter that they create on the Alerts page to only show Blocking alerts:
​

And use this on the Alert tile:
​

This would generate a report of the alerts on blocking processes.