2

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)

  • 2
    While the slow query is running, run sp_WhoIsActive a few times. What does the wait info column say? For tutorial on sp_WhoIsActive, hit brentozar.com/go/active. – Brent Ozar Aug 29 '16 at 21:57
  • 1
    (I'm betting it's either waiting on authentication, or async_network_io, both of which would have dramatically different solution methods, so the wait type will be key.) – Brent Ozar Aug 29 '16 at 21:58
  • Another thing to try - put SQL Server Management Studio on the computer outside of the domain, connect in with a SQL login, and try queries. See if they're slow. Try with the same login you're using in Delphi, and other logins. – Brent Ozar Aug 29 '16 at 22:02
  • I would suggest that trying to run both scenarios with an "sa" privileged account and see whether the difference still persists. – jyao Aug 29 '16 at 22:18
  • I have done all the tests mentioned above, and I found the problem. Everytime 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-Adress 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) – Daniel Marschall Sep 2 '16 at 14:35
3

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

  • I was about to say same thing; it seems time is being consumed in resolving host name of the server and not at database level however strange part is why system tables are slower and not user tables. – Learning_DBAdmin Jul 31 '18 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.