SQL Server hosting VeeamDB high CPU usage (75-100%)

Availability for the Always-On Enterprise

SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby lenart.plausteiner » Sat Jan 14, 2017 1:20 pm 1 person likes this post

Hi guys.

We are having strange behavior on our external SQL server hosting VeeamBackup DB. After a couple of days (1-2 days) our SQL server CPU usage goes UP to 100% percent of usage and this leads to problems like this below:
[27.12.2016 16:48:14] <01> Error Microsoft SQL server hosting the configuration database is currently unavailable. Possible reasons are heavy load, networking issue, server reboot, or hot backup.
[27.12.2016 16:48:14] <01> Error Please wait, and try again later.

Workaround for this problem is to restart SQL service every 1-2 days. Our SQL server was fine prior to Upgrade to v9.5 and implementing BackupCopy JOB to our new EMC DataDomain box.

SQL Server hosting vCenter DB and Veeam DB is 4vCPU and 8Gb of RAM. Both databases are small, Veeam DB is small as 2,5Gb.

With Veeam support, we already defragmented DB.

We use external DB because we would love to have all System DB of environment on one SQL.

Our Veeam server has its primary Backup Repository on SAN disk, which is Windows De-Dup, enabled. All our JOB’s are configured with Weekly Active full and everyday incremental.

I would love to hear from anyone out there, that had similar experience and has fixed the problem.

We have opened the case at Veeam, Case # is: 02026715

Regards,
Lenart
lenart.plausteiner
Influencer
 
Posts: 17
Liked: 5 times
Joined: Mon Apr 13, 2015 9:23 pm
Full Name: Lenart Plausteiner

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby Mike Resseler » Mon Jan 16, 2017 5:52 am

Lenart,

I saw in the support case that you are working with the support engineer so please keep doing that. The only thing I can assume that is happening is that a specific task inside the database is demanding all those resources. But with the above information it will be difficult to find what job is running :-)

Certainly some things you can look at:
* Does it always happen around the same time (in other words, a triggered task)
* Is it Ad-hoc (in other words, triggered by something that is not scheduled or different tasks all causing the same issue)
* Is there something in the logs that you can see which database is causing this peak
Mike Resseler
Veeam Software
 
Posts: 3382
Liked: 384 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby lenart.plausteiner » Mon Jan 16, 2017 6:41 pm

Hi,
As far as I know for now is:
* This is happening in VeeamBackup DB (I send one screen shoot of %proc time against VeeamBackup, I send it to your technician)
* It happens every 1-1,5 days (I'm almost sure that is not always the same time)

SQL Admin checked the logs and there should be no problems with SQL itself
He exposed one strange query against SQL server, and we are not doing any Oracle backups for now
Code: Select all
SELECT  c.oib_id,
                                               c.id,
                            c.usn,
                                               s.[path],
                                               s.size,
                                               s.scn_from,
                                               s.scn_to,
                                               s.utc_time_from,
                                               s.utc_time_to,
                                               s.seq_num,
                                               s.database_id
                    INTO    #tempOracleArcLogs
                    FROM    [dbo].[Backup.Model.OibOracleArchiveLogs] c
                                        JOIN [dbo].[Backup.Model.OibOracleArchiveLogsSensitiveInfo] s on s.oib_oracle_archive_logs_id = c.id
                    WHERE   c.oib_id IN (SELECT id FROM #tempOibIds) AND
                            c.usn >= @usn AND
                            c.id NOT IN (SELECT id FROM #tempOracleArcLogsEncrypted)
                                  --------------------------------------------
                                  --Crypto

Today I have moved the DB back to the backup server to SQL Express, I hope that this will help. I'm not admin on that SQL box so I can't change some settings.
lenart.plausteiner
Influencer
 
Posts: 17
Liked: 5 times
Joined: Mon Apr 13, 2015 9:23 pm
Full Name: Lenart Plausteiner

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby Mike Resseler » Tue Jan 17, 2017 5:19 am

Lenart,

Thanks for the additional information. Please keep us informed about the outcome. (And yes, that SQL query seems strange)
Mike Resseler
Veeam Software
 
Posts: 3382
Liked: 384 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby alexander sh » Wed Jan 18, 2017 12:40 pm 1 person likes this post

Hey Lenart,
there's nothing mysterious about this particular SQL query, it's just a part of our metadata generation procedure. It grabs all the necessary data from VBR database to write it out to disk to .vbm metadata file. If you don't have any Oracle log jobs, it will simply collect nothing from these tables.
Judging from the log files, it seems the problem indeed has to do with this metadata generation SQL procedure. We've seen something similar a couple times after upgrade to 9.5.
Do I understand correctly that the issue still persists?
There's a trick that probably is going to help with SQL recourse utilization. It's included in 9.5 U1 which should be available soon, but I'll go ahead and ask one of Tier 2 support engineers to grab your case and implement that fix to see if it actually helps.
alexander sh
Veeam Software
 
Posts: 23
Liked: 16 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby lenart.plausteiner » Wed Jan 18, 2017 6:04 pm

Hey,

Yes, the problem is still here. I've moved VeeamDB to local SQL instance, as proposed from your support, and after two days of running problem is back. Now I'll leave system as is (no reboots or restarts of SQL service) I hope that backup will survive and all jobs will finish today, but I doubt it :(.

Just to clarify, problem is, that JOB that should run in one moment can't reach SQL Server, server is there and available (I can connect with SQL Management studio and browse tables).
lenart.plausteiner
Influencer
 
Posts: 17
Liked: 5 times
Joined: Mon Apr 13, 2015 9:23 pm
Full Name: Lenart Plausteiner

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby alexander sh » Thu Jan 19, 2017 10:22 am

Hey Lenart,

I see from the support case notes that you've installed 9.5 U1, so let's see if it helps. If the problem persists, we'll make sure to arrange a remote session with you to get to the root of the issue.
Thanks!
alexander sh
Veeam Software
 
Posts: 23
Liked: 16 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby lenart.plausteiner » Mon Jan 23, 2017 1:24 pm

It seems that basic problem is solved with Update 1. Now we don't have any "phantom" CPU utilization without any running jobs as we had prior installation of update.

Trough the weekend we had some problems with system resources, especially with RAM, this is because we moved SQL DB from external SQL server to local instance last week, as proposed by support and we didn't added additional RAM for this workload. Now I've added additional RAM to this VM, after this action everything worked till now.

I have send one additional log to your support, I think that they will confirm my assumption.
lenart.plausteiner
Influencer
 
Posts: 17
Liked: 5 times
Joined: Mon Apr 13, 2015 9:23 pm
Full Name: Lenart Plausteiner

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby alexander sh » Mon Jan 23, 2017 6:24 pm

That's great to hear, appreciate the update!
If the problems comes back, please don't hesitate to bump this thread.
alexander sh
Veeam Software
 
Posts: 23
Liked: 16 times
Joined: Thu Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby steinscherer » Mon Jan 23, 2017 8:30 pm

Hi all
Having the same behavior and fighting about 1 month with this issue:
-Restarting SQL Service
-Cleanup Database
-Stopped unnecessary Veeam Services
-Update SQL Server from Express to Standard
….. does not help :-((

Analyzed the same SQL query (SELECT c.oib_id ) which causing the high SQL server cpu load
Hopefully 9.5 Update 1 will help
steinscherer
Lurker
 
Posts: 1
Liked: never
Joined: Wed Oct 24, 2012 6:55 am
Full Name: steinscherer

Re: SQL Server hosting VeeamDB high CPU usage (75-100%)

Veeam Logoby foggy » Mon Jan 23, 2017 9:08 pm

Update 1 is available for download, so you can install it and check whether the issue persists.
foggy
Veeam Software
 
Posts: 15294
Liked: 1133 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], Google [Bot], Pat490, tdewin and 1 guest