2

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.

  • Your log backups take three minutes to run? What happens if you use native? Maybe the holdup is your vendor writing to an unoptimized log table that never gets cleared out? This kind of thing can happen with SQL Server Agent Jobs too... – Aaron Bertrand Oct 3 '18 at 19:18
  • 1
    When's the last time you cleaned out msdb tables? – Erik Darling Oct 3 '18 at 19:33
  • 1
    Instead of once a month you might want to consider running that every day, removing entries more than a month old. That daily job will run more predictably and the tables will remain a consistent size instead of sawtooth. – Aaron Bertrand Oct 3 '18 at 19:48
  • 1
    @AaronBertrand and Erik, thanks for your comments. I will definitely try that. But i still did not get why that msdb records has anything to do with time outs which are now not colliding with log backups running anymore. Currently that msdb holds 8000 records when pulled for a month using query here sqlperformance.com/2015/07/sql-maintenance/msdb – BeginnerDBA Oct 3 '18 at 19:53
  • 1
    Keep an eye on threadpool waits. – Erik Darling Oct 3 '18 at 20:00
0

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

| improve this answer | |
  • This will be useful sqlperformance.com/2012/11/system-configuration/… – Kin Shah Oct 18 '18 at 15:26
  • @Kin, thanks for the link. Whats the difference then changing alter server configuration properties to use those schedulers again and one i did like upgrading ? Upgrading is whole lot bigger process , so as mentioned in article can that change be safe and works as expected? – BeginnerDBA Oct 18 '18 at 15:39
  • 1
    You have to test it out in your environment. – Kin Shah Oct 18 '18 at 16:22

Your Answer

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

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