Comprehensive data protection for all workloads
Post Reply
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

SQL Backup Logs periodically and tempdb heavy I/O

Post by lightsout »

Hey all,

So I'm on Veeam 8 P1, on Windows 2012 R2 running with a dedicated local SQL 2012 SP2 CU3. I've got half a dozen SQL servers (some running SharePoint) with "Backup Logs Periodically" set for the default 15 minutes. With this set, I see heavy CPU on my Veeam SQL instance, and heavy I/O on the tempdb instance specifically. If I stop these SQL log jobs, the CPU drops and the tempdb activity stops.

So my questions are:

1. Is the tempdb used to stage/process the SQL transaction logs? Is what I see expected?
2. If so, and if I want to use "Backup Logs Periodically", is there anyway to reduce the impact? e.g. If I set the "backup logs periodically" to every 60 minutes, would you expect the impact on tempdb to be lessened?
3. The reason I have this as an issue is the CPU load is very high, and it slows down my Veeam console a lot as a result, which is frustrating.

I've not submitted a case, but can do so, if that is going to be the best way forward.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Backup Logs periodically and tempdb heavy I/O

Post by foggy »

Yep, submitting the case is recommended. There's indeed some interaction with tempdb performed during logs backup, besides, it is used for some internal SQL Server activity (like storing temporary data that cannot be operationally stored in memory), so depending on the amount of processed data it could probably result in the observed behavior. Configuring less often logs backups will most likely decrease the load, but we'd like to look closer at what kind of activity is happening, so opening the case would be much appreciated. If you post case ID here, I will be able to forward it to the correct person directly. Thanks!
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Backup Logs periodically and tempdb heavy I/O

Post by lightsout »

Case 00743054 submitted.

I'm getting terrible console performance. SQL itself doesn't seem loaded highly, but it has a lot of threads (550 - more than double my other Veeam server). Turning SQL backups off or making them hourly instead of every 15 mins didn't make a difference it seems.

Enterprise Manager is also present too.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backup Logs periodically and tempdb heavy I/O

Post by veremin »

If I stop these SQL log jobs, the CPU drops and the tempdb activity stops.
Turning SQL backups off or making them hourly instead of every 15 mins didn't make a difference it seems.
I'm confused a little bit. Does disabling SQL log job result in less CPU load or not? Thanks.
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Backup Logs periodically and tempdb heavy I/O

Post by lightsout »

Yeah perhaps I wasn't so clear, so:

1. The bad performance seemed to start around the time we started doing SQL periodic backups. However, changing the frequency to an hour reduced the SQL load a lot, so I'm not see a very high SQL CPU/IO load now.
2. However the bad performance remains, even if I now turn periodic backups off. So I thought they were related, but now I don't think so.

I have two Veeam servers. My slow Veeam server has 550 SQL threads, my fast one has 250 SQL threads. Both are backing up roughly equivalent backup sets, so I don't know why my two servers have such a difference in thread count.
lightsout
Expert
Posts: 227
Liked: 62 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: SQL Backup Logs periodically and tempdb heavy I/O

Post by lightsout » 1 person likes this post

SQL was just a trick, my server itself was the problem. Support had me run this to speed it up:
use veeambackup
DECLARE @DateValue datetime
SET @DateValue = 'MM/DD/YYYY

delete A
from [Backup.Model.BackupTaskSessions] A
inner join [Backup.model.jobsessions] B
on A.session_id = B.id
where B.creation_time < @DateValue

delete from [Backup.model.jobsessions]
where creation_time < @DateValue
Which did the job nicely. Need to pick a "real" date, and it does wipe out anything older than that in your logs. But for me, it cleaned up nearly 1 million items.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: SQL Backup Logs periodically and tempdb heavy I/O

Post by veremin »

So, the problem was the presence of obsolete task and job session inside product database that slowed down the performance. Glad to hear you've nailed. Thank you for sharing the resolution.
Post Reply

Who is online

Users browsing this forum: Semrush [Bot] and 136 guests