TransWikia.com

Spike and time out errors every x th minute

Database Administrators Asked by BeginnerDBA on December 25, 2020

I have very complicated situation here.

Application team was reporting that they see spikes and errors every 20 mins. We checked what was running and found log backups.

So we tuned log backups using different compression level available in idera third party software. They used to run for 5 mins and now started competing in 3 mins.

Application team saw little relieved but issues seems to be relevant and they are still concern on spikes.

To play little further, we changed log backup to run every 13 mins to find whats really going on. And here it is, mo more time outs when log backup completes or run other than every 20 mins schedule.

Checked everything on SQL Server and did not see any other stuff running every 20 mins.

The issue starts every 20 mins which last for about on avg 20 seconds. Unable to find any SQL queries or related process. So far no scheduled task at O/S level found as well.

Waits: LCK-XX being the higher no followed by Pageiolatch…

Snaphshot isolation for all db’s: RCSI

I will try to add more info, but please suggest what can be checked here to find and resolve this performance issue occurring every x mins.

One Answer

This is to help others as this was a very tricky solution to find:

We found below to be the problem where all the available core schedulers were not visible online

i.e we had SQL server enterprise edition because of which all 128 cores were not used and instead visible online were 40 only.

After upgrading to enterprise: core license , we were able to use all schedulers and viola!

Hope this helps as sometimes licensing could be tricky and we might skip

Answered by BeginnerDBA on December 25, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP