0

I'm not even sure how to phrase this question - apologies if it falls outside the realm of this site... I guess my main question is: What to look for when an update seems quick, but the overall transaction is deadly slow?

Situation

Application is timing out as a result of slow-running queries.

Used SQL Server Profiler to inspect the operations being performed in the timeout period.

Duration values are all 0ms or close to 0ms.

There regular, specific queries that start and then there's no activity shown in the monitor for 5 seconds/10 seconds (as shown in start/end timestamps), but duration still 0ms...

These regular, specific queries are always the same queries, always UPDATE TABLE1 SET INNER_ID = @p1 WHERE PRIMARY_KEY_ID = @p2 or INSERT INTO TABLE2 ...

Running the same queries directly in MSSQLMS, they seem to run fine in good time (00:00:00).

Both the tables in question have a fair few indexes on them.

I've tried...

  • At first, I noticed all the primary key indexes were non-clustered. So I've dropped/recreated each primary key with a clustered index. This helped initially to get the durations down from ~150ms to 0ms.
  • The database was an old SQL Server 2005 database that has been updated several times (now SQL Server 2016). It still had compatibility level set at 2005, so I've upped that to 2016. No change...
  • I'm convinced it's index-related, so I've inspected the indexes on these tables and tried dropping them - no change - recreating them - no change...
  • A colleague suggested enabling Legacy Cardinality Estimation and Query Optimizer Fixes. I had no other straws to pull, so I tried those... no change.

Question

What more can I look at here?

What is happening for 5 seconds?

  • 1
    In these update statements, is any data being returned to the sending application? Could Network IO be the reason? – George.Palacios Apr 25 '18 at 8:21
  • @George.Palacios Hmm... I'm not sure it can be network IO because the rest of the application (navigation, loading records, etc.) seems to be running fine. – oliver-clare Apr 25 '18 at 9:39
  • Have you checked the plans in the cache are the same as the ones you are getting from SSMS? – MJH Apr 25 '18 at 9:56
  • @MJH I'm not even sure what this means or how to check it, but I'll take another look. Thanks for the suggestion. – oliver-clare Apr 25 '18 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.