Standalone backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)
weitzeca
Novice
Posts: 3
Liked: 4 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: 10
Liked: 2 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: 4 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: 4 times
Joined: Jul 23, 2020 12:55 pm
Full Name: Chris
Contact:

Re: SQL server cpu usage on idle state

Post by weitzeca » 3 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
Influencer
Posts: 12
Liked: 3 times
Joined: Mar 29, 2019 3:32 am
Contact:

Re: SQL server cpu usage on idle state

Post by Zek »

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: 10
Liked: 2 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
Veeam Software
Posts: 1819
Liked: 207 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 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
Novice
Posts: 8
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
Influencer
Posts: 23
Liked: 4 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
Novice
Posts: 8
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: 11766
Liked: 1035 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
Influencer
Posts: 23
Liked: 4 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
Influencer
Posts: 23
Liked: 4 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
Influencer
Posts: 12
Liked: 3 times
Joined: Mar 29, 2019 3:32 am
Contact:

Re: SQL server cpu usage on idle state

Post by Zek » 1 person likes 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: 11766
Liked: 1035 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

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: 3
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
Novice
Posts: 8
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
Veeam Software
Posts: 1819
Liked: 207 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 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

Post Reply

Who is online

Users browsing this forum: No registered users and 19 guests