0

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

Isin2000
  • 1
  • 1
  • Do the queries say `SELECT ... FROM table` or `SELECT ... FROM dbo.table`? Different default schemas, for example, could mean they are producing different execution plans. Have you _thoroughly_ compared the execution plans for the two users? Could you share a slow and fast plan at pastetheplan.com? – Aaron Bertrand Apr 21 '23 at 15:58
  • _Query execution plans the same regardless of user / location etc._ Doubtful, especially given the description of the problem. For the same plan to be produced, cached and reused a statement has to be byte-for-byte identical (i.e.: including whitespace) and using the same `SET OPTIONS` values. If you haven't already have a read through [Slow in the Application, Fast in SSMS? Understanding Performance Mysteries](https://www.sommarskog.se/query-plan-mysteries.html), pay attention to differences in defaults, especially plan-affecting options like `ARITHABORT`. – AlwaysLearning Apr 21 '23 at 23:41
  • Thanks all It is difficult to reproduce on demand as a user may time out or not, if I knew why I would not raised this). Users log into app-on a remote svr; run a query, call APIs, invoke SPs, pass parms etc, connects to SQL as a generic svc acct & run query (all logins show as the svc acct - ie. you can't trace for a login) Settings (ARITHABORT etc) are constant for timeout (30+) & good (c.2 secs) queries. I've tried various settings in SSMS but it has not signif affected QP or Exec times, eg 2.5 not 2 secs, but not the 30+ needed to timeout – Isin2000 Apr 25 '23 at 08:53
  • SQL is gen-ed by / part of an app - so 'good' & 'bad''failing' SQL is the same, input parms, & to date QPs are the same. Users have the same access rights Have been away & not trapped 'failing' query since returning. Users report "if a session times out - & they try again after 30 min (?!) - it usually works " Hence I wonder if there is (also) an external factor here - I've asked for connection strings but they have not been forthcoming. For record this is SQL 2014 on Win 2012 (please don't suggest this should be upgraded etc - I know this) – Isin2000 Apr 25 '23 at 09:01

0 Answers0