Standalone backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)
weitzeca
Novice
Posts: 3
Liked: 5 times
Joined: Jul 23, 2020 12:55 pm
Full Name: Chris
Contact:

Re: SQL server cpu usage on idle state

Post by weitzeca »

I've had the same issue for at least 6 months on 2 different systems. I also just upgraded to the latest version of the agent and the problem persists.

My workaround is to pause the Veeam agent every day so that it doesn't impact my other work, especially now with doing so many video conferencing sessions that also eat up a lot of CPU. Veeam's idle state usage is unacceptable for an enterprise class backup solution.

My Case #04298544
g-rant001
Influencer
Posts: 18
Liked: 4 times
Joined: Jan 09, 2020 2:00 am
Contact:

Re: SQL server cpu usage on idle state

Post by g-rant001 » 2 people like this post

This is the solution support gave me. It did fix the issue and they are looking to find a solution with future updates.

1. Download and install Microsoft SQL Server 2012 Native Client and Microsoft SQL Server 2012 Command Line Utilites from the official site.

Expand Install Instructions and find there Microsoft® SQL Server® 2012 Native Client and Microsoft SQL Server 2012 Command Line Utilites and choose x86 or x64 correspondingly;

2. Make sure that the Veeam Agent for Microsoft Windows service is running and after that open up the command prompt admin and type 'sqlcmd -S SqlInstancePipeName –U VeeamLogin –P SqlPassword'.

The cursor will become 1>

SqlInstancePipeName and SqlPassword values might be found in HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Endpoint Backup

3. next print the commands below and press enter after each string:
sp_configure ‘show advanced options’,1;
GO
RECONFIGURE;
GO
sp_configure ‘max server memory’,400;
GO
RECONFIGURE;
GO
Exit

4. Restart Veeam Agent for Microsoft Windows service and verify the results.
weitzeca
Novice
Posts: 3
Liked: 5 times
Joined: Jul 23, 2020 12:55 pm
Full Name: Chris
Contact:

Re: SQL server cpu usage on idle state

Post by weitzeca » 1 person likes this post

Great info. Thanks!
- When I tried to install SQL Client 12, I received an error stating that a higher version was already installed "tough luck!"
No biggie...for now. I proceeded with the commands with the following results. All looks good, but even after restarting the service several times, it didn't make any difference in my case.

I do have SQL Client 12 installed, among others apparently. I don't know which instance Veeam is connected to, but rerunning the commands confirmed that the 400 memory change is still in effect.

Code: Select all

C:\WINDOWS\system32>sqlcmd -S np:removed_for_this_post\tsql\query -U VeeamLogin -P removed_for_this_post
1> sp_configure 'show advanced options',1;
2> GO
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
1> RECONFIGURE;
2> GO
1> sp_configure 'max server memory',400;
2> GO
Configuration option 'max server memory (MB)' changed from 2147483647 to 400. Run the RECONFIGURE statement to install.
1> RECONFIGURE;
2> GO
1> Exit
After

Code: Select all

Configuration option 'max server memory (MB)' changed from 400 to 400. Run the RECONFIGURE statement to install.
weitzeca
Novice
Posts: 3
Liked: 5 times
Joined: Jul 23, 2020 12:55 pm
Full Name: Chris
Contact:

Re: SQL server cpu usage on idle state

Post by weitzeca » 4 people like this post

Update - things are better after applying "the fix" and rebooting my system. Service restart apparently wasn't enough.
I'll post back if something changes.

Additional detail.
I was seeing two related but different issues with SQL Server Windows NT - 64 Bit and Veeam at different times.
1) Constant CPU usage between 10 - 20%
2) Frequent, random spikes in CPU between 0% - 10%

So far, it seems like the fix addressed both issues on one of my systems. I need to apply the fix to my other system, which only experiences the periodic spikes, to see if it addresses that issue.
Zek
Service Provider
Posts: 22
Liked: 12 times
Joined: Mar 29, 2019 3:32 am
Contact:

Re: SQL server cpu usage on idle state

Post by Zek » 2 people like this post

g-rant001 wrote: Jul 23, 2020 3:48 pm This is the solution support gave me. It did fix the issue and they are looking to find a solution with future updates.
...
Thx to share, but this solution doesn't quite solve the problem. Indeed, CPU spikes every minute are associated with SQL server memory management mechanisms. But decreasing the 'max server memory' parameter does not disable them, it simply limits the maximum possible calculated value to memory usage and thus reduces the load on the CPU.
To completely disable parasitic CPU usage by the SQL server memory management mechanism, you must set 'min server memory' parameter, or use the procedure for configuring a fixed amount of memory with both max and min equal settings at the same time. When memory used by SQL server is less than set in 'min server memory', memory release mechanisms are not triggered and don't waste CPU cycles.

Code: Select all

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'min server memory (MB)', N'400'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO
rammon
Lurker
Posts: 2
Liked: never
Joined: Feb 22, 2016 1:51 pm
Full Name: Rocco
Contact:

Re: SQL server cpu usage on idle state

Post by rammon »

Thank you guys! This reduces my permanent CPU usage from 80% to 1-2%!
g-rant001
Influencer
Posts: 18
Liked: 4 times
Joined: Jan 09, 2020 2:00 am
Contact:

Re: SQL server cpu usage on idle state

Post by g-rant001 »

I think you are missing the point bud. This isn't "THE FIX", it was simple solution to see if that resolved the problem. Now support is able to do some further testing to come up with a real solution. Again in my case it did resolve the problem and now we need Veeam to resolve this in a update because we should not have to make these changes at all.
wishr
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: SQL server cpu usage on idle state

Post by wishr » 1 person likes this post

Hi guys,

Thanks for all the feedback and special thanks to those who have finally managed to raise a support case allowing us to collect more info.

We are looking into that, but the issue lies on the SQL side so the real fix has to come from Microsoft which obviously will not happen quickly. That's why we'll do our best to find out what solution can be implemented on our end. I'll keep you posted.

Regards,
Fedor
matthewr
Influencer
Posts: 10
Liked: 2 times
Joined: May 28, 2019 8:08 pm
Full Name: Matthew R
Contact:

Re: SQL server cpu usage on idle state

Post by matthewr » 1 person likes this post

Just to add that I also have this issue, SQL Server using 20-30% CPU periodically.

I have tried Zek's suggested fix, will see if it works.
ottl05
Enthusiast
Posts: 30
Liked: 5 times
Joined: Oct 16, 2014 11:29 am
Contact:

Re: SQL server cpu usage on idle state

Post by ottl05 »

when I try to login to the DB:
sqlcmd -S np:\\.\pipe\removed\tsql\query –U VeeamLogin –P removed

I´m recieving following error:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2].
matthewr
Influencer
Posts: 10
Liked: 2 times
Joined: May 28, 2019 8:08 pm
Full Name: Matthew R
Contact:

Re: SQL server cpu usage on idle state

Post by matthewr »

I'm not sure if this is the cause, but I am using Microsoft ODBC Driver 11 not 13. Version 11 corresponds to SQL Server 2012, which is the edition used by Veeam. Perhaps the driver isn't backwards-compatible for some reason?
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: SQL server cpu usage on idle state

Post by Dima P. »

ottl05,

Make sure that you run cmd as admin. Some folks also recommend to disable windows firewall during this command execution. Cheers!
ottl05
Enthusiast
Posts: 30
Liked: 5 times
Joined: Oct 16, 2014 11:29 am
Contact:

Re: SQL server cpu usage on idle state

Post by ottl05 »

matthewr wrote: Jul 29, 2020 4:50 pm I'm not sure if this is the cause, but I am using Microsoft ODBC Driver 11 not 13. Version 11 corresponds to SQL Server 2012, which is the edition used by Veeam. Perhaps the driver isn't backwards-compatible for some reason?
the same with version11:
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
ottl05
Enthusiast
Posts: 30
Liked: 5 times
Joined: Oct 16, 2014 11:29 am
Contact:

Re: SQL server cpu usage on idle state

Post by ottl05 »

Dima P. wrote: Jul 30, 2020 4:38 pm ottl05,

Make sure that you run cmd as admin. Some folks also recommend to disable windows firewall during this command execution. Cheers!
I ran the cmd as admin. Firewall ist enabled (gpo) but port 1433 is allowed
Zek
Service Provider
Posts: 22
Liked: 12 times
Joined: Mar 29, 2019 3:32 am
Contact:

Re: SQL server cpu usage on idle state

Post by Zek » 2 people like this post

It seems that changing the memory settings does not completely solve the problem, with further testing I turned out that after a while SQL again starts to consume a significant amount of CPU resources approximately every minute, even when fixed amount of memory is set.
Upon further investigation of the problem, I found in the sql server configured extended event session "system_health" for logging events related to the allocation of virtual memory and some more others. CPU usage intervals equals with the timestamps of recorded events. The high CPU utilization is most likely the result of filling package0.ring_buffer with recorded events, which seems to be the second one reason for the high consumption of CPU resources. This also may explain why changing the memory usage settings briefly solves the problem - memory allocation events no longer occur, and event session writes less data to ring buffer. After event session logging was disabled, CPU usage stopped.

To disable event session, execute this commands in addition to memory settings previously listed, no restart is needed:

Code: Select all

ALTER EVENT SESSION [system_health] ON SERVER WITH (STARTUP_STATE = OFF);
ALTER EVENT SESSION [system_health] ON SERVER STATE = STOP;
 GO
So the complete list of settings looks like this:

Code: Select all

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'min server memory (MB)', N'400'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'400'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO
ALTER EVENT SESSION [system_health] ON SERVER WITH (STARTUP_STATE = OFF);
ALTER EVENT SESSION [system_health] ON SERVER STATE = STOP;
GO
You can also use the graphical application SQL Server Management Studio (SMSS) instead of Command Line Utilites for customization.

P.S. I would still recommend to Veeam developers stop sql server when backup tasks or control panel are not running, and start sql server only when database is needed. This will release memory and CPU usage from sql when it is not used, and it is the simplest CPU optimization solution, especially since currently this SQL process is not separate windows service and already started by the veeam agent service, it will not be difficult in implementation at all. Unfortunately, modern programmers don't care much about optimization, but it has a big impact on mobile devices, and these little things that determine the quality of the application. Even after done all configurations mentioned above, SQL Server still consumes 0.1-0.8% of my i5-8350 laptop CPU, that has a negative impact on battery life, although it is hardly noticeable and infinitely better than was before.
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: SQL server cpu usage on idle state

Post by Dima P. » 3 people like this post

Hello Zek,

Thank you for sharing the results of your investigation. I'll share your notes with the dev team, we currently investigating this problem, so any input helps a lot. Cheers!
swiffer
Novice
Posts: 4
Liked: never
Joined: Aug 03, 2020 7:15 pm
Contact:

Re: SQL server cpu usage on idle state

Post by swiffer »

same here - periodic cpu spikes every minute
matthewr
Influencer
Posts: 10
Liked: 2 times
Joined: May 28, 2019 8:08 pm
Full Name: Matthew R
Contact:

Re: SQL server cpu usage on idle state

Post by matthewr »

I implemented Zek's suggested list of changes, and things seemed better for a few weeks, but as of the last couple days, SQL has been running at 30-40% CPU almost constantly.
wishr
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: SQL server cpu usage on idle state

Post by wishr » 1 person likes this post

Hi guys,

Regardless of the root cause, we are still looking at possible solutions on our end. If you are experiencing the issue, please raise a support case to get notified once a solution is ready. At the same time, we'll keep sharing updates here.

Thanks
DinoMC
Lurker
Posts: 2
Liked: never
Joined: Jun 09, 2020 11:30 am
Contact:

Re: SQL server cpu usage on idle state

Post by DinoMC »

I ran Zek's commands and the high spikes seem to be gone, thanks for the fix!

sqlserver.exe is still using a few % of the CPU constantly tho, 24/7, which is a bit annoying to me.

I agree that it should definitely be stopped when Veeam is just idling. I'm running a backup for an hour a day, during off time. Don't need sqlserver.exe to run 24 hours for that.
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: SQL server cpu usage on idle state

Post by Dima P. » 1 person likes this post

DinoMC,

Thanks for updating this thread. We are looking for a way to address this issue in the next major release of Veeam Agent for Windows. Cheers!
tailslide
Lurker
Posts: 2
Liked: never
Joined: Nov 15, 2020 9:18 am
Full Name: Greg Pringle
Contact:

Re: SQL server cpu usage on idle state

Post by tailslide »

Also ran into this trying to find the biggest drain on my laptop battery. Considering making some kind of script to start up SQL before my backups run at night.
tailslide
Lurker
Posts: 2
Liked: never
Joined: Nov 15, 2020 9:18 am
Full Name: Greg Pringle
Contact:

Re: SQL server cpu usage on idle state

Post by tailslide »

Found my solution I think.. this lets you start/stop services on AC/Battery switch.
Can't Imagine I would ever want to run a backup while on battery.

https://archive.codeplex.com/?p=batterysaver
mts75
Novice
Posts: 3
Liked: never
Joined: Jul 02, 2020 9:32 pm
Contact:

Re: SQL server cpu usage on idle state

Post by mts75 »

Any Updates on this Problem? The Problem sill exist on multiple Win10-Instances and can't leave the Veeam-Service enabled all time ...
wishr
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: SQL server cpu usage on idle state

Post by wishr »

Hi Mts75,

Please refer to Dima's comment above.

Thanks
einhirn
Enthusiast
Posts: 53
Liked: 18 times
Joined: Feb 02, 2015 1:51 pm
Contact:

Re: SQL server cpu usage on idle state

Post by einhirn »

Just for completeness sake I opened a support case while my Veeam Agent install's MSSQL was showing the "one core fully loaded"-Symptom again even after applying Zek's suggested changes. It seems to run fine for some days after a windows restart, but after about 8 to 10 days of uptime sqlserver starts to act up, so that I often just disable the service manually. Of course there aren't any backups without the service :(

Anyway, my S/R#04552933 was closed unprocessed due to being a Free Product case and high Support Team load - understandably at this time. Just now you know where to look in case you need another data point...
Dima P.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: SQL server cpu usage on idle state

Post by Dima P. » 2 people like this post

einhirn,

We've already included several fixes in the upcoming agent major version and I believe all these issues will be addressed. Thanks for the update and for the case. Merry Christmas!
ottl05
Enthusiast
Posts: 30
Liked: 5 times
Joined: Oct 16, 2014 11:29 am
Contact:

Re: SQL server cpu usage on idle state

Post by ottl05 »

@dima: and when will the major version will be released?
wishr
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: SQL server cpu usage on idle state

Post by wishr » 1 person likes this post

Hi Ottl05,

The next Agent version is coming along with VBR v11.

Thanks
Veemzatetujecpu2121
Lurker
Posts: 1
Liked: never
Joined: Jan 29, 2021 6:04 pm
Contact:

Re: SQL server cpu usage on idle state

Post by Veemzatetujecpu2121 »

Was sqlservr.exe CPU usage fixed in Veeam Agent 5.0.0.4301 ?

I was unable to find any acknowledgement in change log.
And I still have 3h 28min CPU time for sqlservr.exe on system with uptime 7 days.

Thanks for some informations
Post Reply

Who is online

Users browsing this forum: nick82 and 37 guests