SQL server cpu usage on idle state

[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]

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]

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]

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]

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]

Thank you guys! This reduces my permanent CPU usage from 80% to 1-2%!

[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]

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]

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]

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]

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.]

ottl05,

Make sure that you run cmd as admin. Some folks also recommend to disable windows firewall during this command execution. Cheers!

[ottl05]

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]

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]

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.]

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]

same here - periodic cpu spikes every minute

[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]

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]

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.]

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]

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]

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]

Any Updates on this Problem? The Problem sill exist on multiple Win10-Instances and can't leave the Veeam-Service enabled all time ...

[wishr]

Hi Mts75,

Please refer to Dima's comment above.

Thanks

[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.]

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]

@dima: and when will the major version will be released?

[wishr]

Hi Ottl05,

The next Agent version is coming along with VBR v11.

Thanks

[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