Sql-server – SQL Server system views VERY slow at one computer

sql server

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

or

SELECT * FROM sys.dm_exec_sessions

I have no idea what to do about this.

Diagram

Update: I have done following further checks:

  • exec sp_WhoIsActive shows nothing during the query.

  • exec sp_who2 mentions "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)

Best Answer

This sounds more like a name resolution / reverse lookup issue. Have you tried adding an entry in the hosts file on the "Computer outside of domain"?

Windows keeps it in C:\Windows\System32\drivers\etc

Edit - https://stackoverflow.com/questions/10872557/how-slow-are-tcp-sockets-compared-to-named-pipes-on-windows-for-localhost-ipc

Related Question