Sql-server – Connecting to database engine again when a new query or running view

database-enginesql serverssms

When I connect to a server enter image description here

via SSMS, I can connect fine albeit a slow.

When I click on a new query or try to run a view in a database on this server, the same prompt comes up but with everything greyed out and then after a while the prompt disappears and the view runs or new query tab opens:

enter image description here

It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query. How do I stop this prompt appearing?

Best Answer

It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query.

That's because it is. That's how it works, each connection is a new connection. The initial connection dialog you see if for object explorer. Each open "new query" tab is its own connection.

How do I stop this prompt appearing?

It's appearing because there is either very high network latency or the pre-login/login times are long. Almost always it's due to very bad latency/undersized vms. Check your network round trip time to the server and check that the server is under heavy load or undersized.

Adding in Aaron's comments which are also on point:

Could also be a not-very-efficient logon trigger or an expensive resource governor classifier function on that server (connecting via a local SSMS on the VM might rule this out). The error log might yield some clues too, or an extended events session that captures connection/log in information.

Related Question