I have a weird problem and do not know how to debug or solve this issue.
We have a Microsoft SQL Server 2012 stored at a HyperV VM in the network. Clients in the same network access this SQL Server. The VM and the clients are part of a local domain. The database access is done via the ADO libaries (Delphi programs). The NT Authentification is used. Everything works fine.
One computer is in the network, but not in the domain. The whole TCP/IP stuff seems to be correctly setup. The server can be connected, and database access works fine and fast. All ADO based (Delphi) programs work fine and fast. But queries to the system tables are extremely slow and need up to 5 seconds.
The query is as simple as
SELECT * FROM sys.dm_exec_connections
SELECT * FROM sys.dm_exec_sessions
I have no idea what to do about this.
Update: I have done following further checks:
exec sp_WhoIsActiveshows nothing during the query.
exec sp_who2mentions "Awaiting command"
Logging in with "sa" is not changing anything.
In Delphi/ADO (outside domain) it is slow, in MS SQL Studio (outside domain) it is fast. Both tested with and without NT authentication.
I found the problem. Every time I connect to the server using the machine name (e.g.
SERVER1\ABC), the system-view queries are slow, but everything else is fast. If I connect via IP-Address
10.0.0.x\ABC, then the system-view queries are fast too. That's very weird. At least I have a workaround. (Note: The SQL server is inside the domain)