Sql-server – sql server slow only on LAN

local-area-networkperformancesql server

I have a MS SQL Server instance on my server.

Running select * from sys.columns takes 00:00 seconds. (just as a sample, most queries have same issue)

When i run the same query in SSMS, it takes 30 seconds+. sometimes a few minutes.

What might be of interest: the first approxiamtely 500-600 rows come back immediately, then the rest come back slowly.

This issue only started happening today.

What i've tried:

  • I turned off kaspersky on my client pc
  • I turned off opendns
  • I restarted both pcs
  • Turned off most apps conectiong to the db
  • I checked activity monitor, nothing major going on there
  • Client statistics doesnt either show anything much

Still the issue persists

On the server its consistently 0 seconds, remotely on the lan approx a minute

Moreover, i connected from my client pc via SSMS to remote servers overseas, and it was also 0 seconds

Seems to be only between the pcs here and the server

What can the issue be? Where can I look?

UPDATE
2 days ago I had a technician, worked for a while, restarted stuff here and there, and yesterday all worked okay. today the issue is back

I tried disconnecting all client pcs from the database (closed all apps that connect to the db) and running the query again. again its taking circa 35 seconds

UPDATE 2

Hi all. I'm experiencing this same issue again. Though now it's even odder, since I'm experiencing this also on a client pc which is a brand new installation of windows 10. No custom firewall/antivirus or anything like that.

What I've tried:

  1. I realized that the server's dns address was pointing to OpenDns, since I don't use that anymore I switched the server to 10.0.0.138/8.8.8.8. This didn't help much.

  2. I manually added the entries to the servers' Hosts file (i.e 10.0.0.1 MyClient etc). This made a huge difference in SSMS from MyClient to MyServer, but didn't solve the problem all the way. Meaning:

    • Most queries are returning now instantaneously, but not all.
    • nslookup MyClient still gives the same error (google-public-dns-a.google.com can't find MyClient: Non-existent domain)
    • FWIW ping MyClient return immediately on the server with no issues
    • A .net app using Entity Framework still takes approximately 30 seconds to return the same query that SSMS return in 0 seconds on the same machine

If possible, I wouldnt mind totally disabling the nslookup on SQL Server. I have no need for it. So far though, I couldnt find any such option.

What else can be done?

Thanks!

Best Answer

This is a bit of a guess (as it could be a variety of things), but it might be reverse DNS lookup of the clients IP is timing out for some reason on the server. Quick test would be to see how long nslookup <client IP> takes to resolve or perhaps add the client IP to the servers hosts file entry and test the query.

Related Topic