MS SQL queries sometimes taking 500ms longer

Question

I'm experiencing some queries that are sometimes taking 500 ms longer than they should to return a result from the server. To be precise: Normal response times are 5-6 ms, same as the ping time to the server. Normal query duration from SQL profiler is around 1 ms. Sometimes the response time rises to around 500ms. Never 300 or 400 ms, always pretty close to 500ms. The actual query duration reported by SQL profiler is still only around 1 ms. I analyzed the network traffic with Wireshark, and found that when I have 500ms response times I receive an ACK for the request package almost immediately (within the 5-6 ms ping time). This must mean that the server receives the request fast and it's not a network related issue right?

So for some reason the SQL server receives the request, but waits, or does soemthing else that isn't included in "Duration" from SQL profiler, before performing the query. It seems very strange that it's always around 500ms - does this create any associations for anyone? PS: I also feel it might be a bit strange for the SQL server to send an ACK so fast? Normally I would expect it to wait a little longer, and in fact when the reponse times are low then no ACK is sent (just piggybacked on the response package).

Edit: I'm testing this in a loop. Most responses are fast, and a small percentage has this delay. The delays are often grouped. This is a production server, and I think it might have something to do with other requests happening at the same time, but it puzzles me why it's always 500ms delay, and why it's not included in the Duration column from the profiler.

Answer 1

Do check out wait statistics. Whenever SQL server executes any query, it has the ability to and so it checks, where are the performance hits in the query being executed. You will have to query SQL Server to get the detail. It is provided through sys.dm_os_wait_stats which can be joined to other tables or views to get more details.

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

http://downloads.red-gate.com/simpletalk/whitepaper_wait_statistics.pdf

https://www.brentozar.com/sql/wait-stats/

Answer 2

We had the exact same problem and in our case the root cause turned out to be https://kb.vmware.com/s/article/2129176.

Since this problem seems to be very rare on Internet I think it's likely that this was the cause in your case as well.