I have a SQL query that intermittently times out for some clients/users – but never for others. The query is submitted by connecting to an application server which then launches a SQL connection under an application service id. Some days everything works fine, but some days :
o if UserA runs the query from his pc under his id – it runs successfully in a few seconds, but if UserB runs the same request, from either their own or UserBs pc it may time out. o if UsersB times out they continue to do so unless they 'go away' for a period (not sure how long "perhaps an hour" - then usually everything works ok for a while. o if I run the same query 'natively' from SSMS on my pc– it always completes in a few seconds. o the query still times out under UserBs id even if it has just run successfully for UserA or myself (i.e. all data is already in the bufferpool and thus no physical i/o is required to return a result set)
There does not appear to be a 'geographical' pattern - i.e. UserA is always good whether remote via VPN or in the office, whereas UserB can be good or bad from either (I am a remote user).
UserA and UserB have identical access rights in SQL (and I'm told within the application).
The Server is not particularly stressed - CPU hovers around 40-60%, memory is sufficient, I/O not under pressure, no obvious locking / blocking etc issues. Traces/ XEs confirm that query timeouts are occurring but I can't find anything on the SQL Server to indicate why.
If we rule out that SQL is just being 'moody' - can anyone think of anything in the SQL layer that may explain this - my thoughts are that it may be something in the application tier
SQL is running 2014(!) on Windows 2012, with a default instance. I have been told there are no relevant network messages corresponding to when the issue is experienced.
Have tried Profiling, XEs, running sql application and 'natively' through SSMS, connecting from office via vpn. Query execution plans the same regardless of user / location etc. Difficult to reproduce 'on demand' due to intermittent nature but issue is persistent. No error / failure code captured on client side except 'Query has timed-out' messages - with no indication why / associated messages