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?
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.
- 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 Estimationand
Query Optimizer Fixes. I had no other straws to pull, so I tried those... no change.
What more can I look at here?
What is happening for 5 seconds?