1

On Microsoft SQL Server 2019, there is a lot of long temp tables TRUNCATE operations with time over 5..10 seconds. Easy query like "TRUNCATE TABLE #tt22" can take over 15 seconds. It's an intermittent problem, sometimes it disappears for a day after restarting the server . What could be the reason?

UPD: According to the latest performance measurements, long TRUNCATE operations are observed not only in temporary tables, but also in physical ones.

Improve this question
19
  • 1
    If you run sp_WhoIsActive in a separate query window, while the truncation is hanging, what are the results? Specifically any blocking and what are the wait types on the truncation query?
    – J.D.
    Commented Oct 3, 2022 at 12:21
  • It's difficult to catch the situation when truncate hangs. I only see this in extended events.
    – aqis
    Commented Oct 4, 2022 at 7:32
  • Unfortunately there's not much that can be said without the aforementioned information. Only other idea is maybe you're misinterpreting the Extended Events. It's not usual for truncation to take a long time.
    – J.D.
    Commented Oct 4, 2022 at 12:35
  • @J.D. There is no any blocking. Wait type is SOS_SCHEDULER_YIELD
    – aqis
    Commented Oct 12, 2022 at 7:22
  • Was there any other processes / queries running at the same time? You should save the entire results you see from sp_WhoIsActive in case there are other clues. Also, next time you notice the problem, you should run sp_WhoIsActive multiple times in a row (10+) to see if you catch any other wait types for the TRUNCATE query, or if every time it's the same wait type of SOS_SCHEDULER_YIELD. (Some queries have multiple things they're waiting on.)
    – J.D.
    Commented Oct 12, 2022 at 11:59

1 Answer 1

Reset to default
0

It seems this is a MS bug. This situation could be reproduced in a test environment: MSSQL 2019 after some hours of work increases truncate time from milliseconds till nn seconds. In that case there were #tmp tables in 1c system. After sqlserver service reboot truncates became speedy again but for only some hours.

Russian leading performance team "Softpoint" investigations and attempts to work around that problem:

https://habr.com/ru/companies/softpoint/articles/739112/

Improve this answer
2
  • Its not really an answer
    – Rohit Gupta
    Commented Jul 13, 2023 at 11:22
  • @RohitGupta This can serve as a hint to solve the problem.
    – aqis
    Commented Jul 13, 2023 at 11:41

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.