Whenever I try to navigate through the objects in sql server management studio object explorer I get the error message
Lock request time out period exceeded. (Microsoft SQL Server, error: 1222).
It is impossible to list columns in tables, show code of views and procedures, etc.
It's a SQL Server 2016.
How to look for the cause of this ?
There is a lot of ETL running on the server, can that be the cause ? How to pinpoint the cause ?
Someone's got an open transaction involving the system tables.
Look at sys.dm_tran_locks.
While SSMS is waiting you can examine sys.dm_os_waiting_tasks to see which session it's blocked by.
I agree with David Browne - Microsoft that you can look at sys.dm_tran_locks and sys.dm_os_waiting_tasks.
You can also see which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to.
But if you want to make your life easier have a look at sp_whoisactive or some alternatives also good.
You can also start monitoring blocking.
You can use DBeaver instead of SSMS. It doesn't suffer from this issue.
Our organisation has very large legacy apps on SQL Server, but is gradually moving to PostgreSQL on AWS. As a result, I use both SSMS and DBeaver heavily every day. SSMS is great for SQL Server while DBeaver is great for PostgreSQL and passable for SQL Server.
When I encounter the issue described in the question in SSMS, I find DBeaver doesn't get blocked. While there may be a short delay, DBeaver is able to display all objects even when SQL Server is not able to display anything at all. I do not know enough yet about the DBeaver internals to know if it is achieving this by caching.
