1

This appears to have started happening a week, maybe two weeks ago. Essentially, we cannot figure out whether it is the server or the client that is periodically pausing between SQL statements. In the image attached, there are two sets of back-to-back events from a client. Notice that the EndTime of the preceding row is about half a second from the StartTime of the next row.

I expanded the window enough to show it happen twice. In a 20 second trace, it very consistently shows up every half second, i.e. a few good back to back 3-10ms round trips, then a 500ms gap, then more 3-10ms round trips.

As far as the client app goes, there is no sleep or timer between the two statements. In fact, the same client app run from the SQL Server VM itself (on AWS) does not exhibit this behaviour.

What is causing this? Is there a new vulnerability in SQL Server drivers that has been hot-fixed by slowing client packet reception by just refusing remote traffic every half second??

enter image description here

Two sample connection strings (both exhibiting this behaviour)

  • Driver={ODBC Driver 18 for SQL Server};Server=server;Database=dbname;UID=userid;PWD=mypassword;
  • Provider=SQLNCLI11.1;Password=mypassword;Persist Security Info=True;User ID=userid;Initial Catalog=dbname;Data Source=server;
Improve this question
8
  • I don't really see a problem here. It's not consistent as there's only a couple of cases out of the 100 or so queries in your trace example, and not every procedure call seems to do it. I don't think there's much help anyone can give with the information provided so far. If you want to add the trace file to the Post it would probably be helpful. You can also try running sp_WhoIsActive during the timeframe of when this issue occurs to see what's running and maybe derive any clues either from the blocking sessions or the wait types in the wait_info column.
    – J.D.
    Commented Sep 28, 2023 at 12:22
  • @J.D. It's much of the same. Added clarification that this is a repeating pattern, not just the two-off. sp_WhoIsActive won't answer why the same workload (e.g. running a batch close-invoice operation) on the SQL server itself and another client in parallel - same app - runs differently. Same SQL Server client provider (tried native client 2012, oledb, latest ODBC v18). There is a counter on screen, one goes 1,2,3...100 the other manages 1,2,3..10 in the same time
    – 孔夫子
    Commented Sep 28, 2023 at 12:31
  • I understand it's repeating, but doesn't seem to occur enough to be concerning, honestly. "sp_WhoIsActive won't answer..." - you don't know that until you try it. No reason to ask for advice on here if you're not going to listen to it. Again, you may at least be able to derive what the queries are waiting on that cause that half a second delay. Outside of that, I'm unclear if you're saying you have 2 apps across 2 SQL Server instances that exhibit different behavior or 2 apps on the same SQL Server instance that exhibit different behavior. If the latter, then sounds like an app problem.
    – J.D.
    Commented Sep 28, 2023 at 12:37
  • client app running stocktake on SQL server locally. client app running stocktake on another VM in AWS - both at the same time, in parallel, concurrently. one goes 10x slower than the other because of the 500ms gaps in profiled events. It's not contention between the two. running them in isolation behaves the same, separately - server=ok, off-server=slow
    – 孔夫子
    Commented Sep 28, 2023 at 12:46
  • 1
    What is the connection string of the client app having this behavior?
    – Sean Gallardy
    Commented Sep 28, 2023 at 13:54

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.