LocalDB constantly using CPU ressources(15-80%)

[william.scholes]

Another option, proably easier to implement for Veeam would be to use MS SQL 2019 LocalDB rather than the current Microsoft SQL Server 2012 SP4 Express LocalDB Edition that is shipped with Veeam Agent 5.0... comon guys SQL 2012 is EOL ! Or let us choose an existing SQL instance...

[MaximillianC]

Ok, so I feel the need to address the people piling on to bash SQL Server. I'm sorry, but **this is not a SQL Server issue!!!** I have been a SQL Server DBA for around 20 years now, and can tell you that this isn't an issue with SQL Server or localDB being "overkill" or a "behemoth". It is 100% a bug with the way Veeam is **using** SQL Server. As I stated above, I was able to identify the cause of my high SQL Server CPU usage from Veam as being due to Veeam running a **very expensive** stored procedure, that included at least one query that used SQL Server's XML query functionality (which, by nature just cannot be as efficient as queries against normal SQL Server tables), and it was running that very-expensive query **multiple times per second** (*that's* the only "overkill" I see here).

Given how impactful demanding a company change RDBMSes is (and how it appears to being done by some, in this case, for unjustified reasons), this is a good example of why you know the details (and read the information others have provided) before bashing a tool or piece of software. I have been working with Microsoft SQL Server since 2001, and I can tell you from those 20 years of experience (and experience working with MySQL, Oracle, and SQLite throughout my career as well) that SQL Server is a stellar, solid RDBMS. It's impressively efficient and high-performing when tuned properly, and its little brother local DB is just a lighter-weight version, with a much of the same great performance characteristics. *Any* RDBMS will perform terribly when not used properly, and if I have learned anything from my time in DBA roles and software teams, it is that most software teams do not put nearly enough focus on making sure the database access in their applications is being done properly. I don't think I've seen any place where all back-end SQL developers (anyone writing queries) get proper training on writing high-performance SQL queries (and they should!), and very few dev teams have a DBA who reviews all queries for proper design (which helps keep out performance-killing queries).

I see I.T. folks all too often clamoring to vilify and shelve products (both software and hardware), either because they refuse to take the time to learn the product, or because they incorrectly blame great products for poor implementations of those products. Companies literally waste millions of dollars and hundreds of staff hours a year doing things like that (I've witnessed it repeatedly at companies of all sizes), and it stalls progress in other areas. It's better to just do the work to identify and fix the actual problem. Considering that, in my case (per above), it was a poorly-designed query that was being run multiple times per second, it's likely that Veeam just needs to put more resources into having SQL DBA's write, or at least, review the DB queries their apps are running, so they're not just running needlessly-costly queries, or running them at needlessly-high-frequencies. I hope those of you blaming the RDBMS in this case will consider that.

Regards,

Maximillian C.

[Gostev]

I agree with you in general, however the issue discussed in this thread is indeed a SQL Server issue... so those I.T. folks are actually correct blaming the RDBMS in this particular case

The issue is well understood by now with the help of low-level debugging, and is confirmed to be a SQL Server bug occurring in low system memory conditions. I don't expect 20 years professional DBAs like yourself to ever face this issue though, just because you run your SQL Servers on proper hardware configurations! But in our case, it is common for the database engine to be memory-contrained.

We have even created an experimental hotfix that hooks the problematic SQL Server function and this fully addressed the issue. But of course, it is not really a good candidate to be a "production" fix.

This is not to say other bugs don't exist in Veeam, such as expensive queries that are incorrectly called too often in some abnormal condition (normally no query should be called multiple times a second simply because no agent functionality requires such frequency of updates). But such bugs are trivial to fix and this is done immediately after the first support case makes its way into the R&D, with fixes going into the immediate release.

[lucius_the]

@MaximillianC: Hi, I'm that one calling it "overkill" (and "behemoth").
Please understand I do agree with you on most points. I myself use SQL Server professionally for almost 20 years. Yes, it's a very solid RDBMS, it's quite performant, it's not that I don't like it - I do. And yes, we all know some developers can write queries that work just fine on their power machines, but sometimes forget where this is going to be running and don't bother much, or at all, if there is a more efficient way of doing it. That's classic, developers can be very feature driven and some can also be thin on workings of an RDBMS, or simply too lazy to test out how well their queries perform. That's all very true and I believe no software company is immune to that.

But there's also that moment of picking the right tool for the right purpose. A product such as Veeam Agent doesn't really need an engine as powerful and feature-rich as SQL Server. It possibly might not even need an RDBMS, but let's skip that consideration -> there are engines like sqlite (or firebird embedded) where you get SQL compatibility packed in a very efficient and robust engine, it's a clear candidate. Of course there are numerous other methods for store data, but sqlite lets you keep most of the SQL syntax, is light on resources, so.

About your finding a query that uses XML: congratulations, I have also checked myself what queries were running, but didn't notice it loops an XML query. Nice catch ! Not sure why it's there, surely it could be reworked to have better performance, but if you're an architect (or whoever decides what developers will use when making a new product) and you choose something like sqlite from the start, then developers don't have a full fledged SQL Server, with all features, including XML queries, to start with, so a query of this sorts is much less likely to end up in the product. Debatable if that's a bonus in general, but in this case it could have been.

About SQL Server being an overkill here. Let's first agree on an assumption that Veeam Agent needs persistent storage just to store it's own config and job history (it's an assumption, of course, but I'm probably not far away - they are using sqlite in Agent for Linux, after all). They are not dealing with anything large and complex here, when we're talking about a database. Sqlite is simply a better match in this case. It will do the job nicely and can even scale an order of magnitude up if it ever needs to. When we compare SQL Server's and sqlite's storage and RAM requirements (what you need to just run an empty database) we are comparing a mice to an elephant. So: yeah, overkill. Maybe calling it "behemoth" wasn't proper. My apologies. But if there's a tool available that's a hundred times smaller and does an equally good job, than using the other is overkill. It's a just config datastore with maybe a few other things in there. Even if there was no XML query (though the problem is more likely in loops than the actual query), the SQL Server LocalDB would still be an elephant in this picture.

SQL Server also does have an unwanted side effect, as @Gostev already explained, a bug it seems, so it's not *just* developers not coding for performance in this particular case. That said, I do agree with you that, in general, SQL Server is not the primary target to blame. Whoever decided to use it for the Agent - is and then come developers with their contributions that possibly make things worse.

But all of this is normal and pretty standard. You got one product and make another, based on technology and code base you already have from before, than optimize later. Nothing new. I might be doing things differently, I'd probably choose to replace SQL Server with sqlite (or something similarly light-weight) before even shipping v1 of Agent - but that's just me. Note the size and success of my company compared to Veeam is another mice to elephant comparison, so my decisions might not be all the right ones From technological perspective the choice is mostly a no-brainer, but depends on other factors: like what developers you have at hand, what they are comfortable with, your project timeline, costs, etc. So you might end up choosing something that looks easy at first, just to fix it later. That's also pretty standard, nothing new.

Now that we found what works (Veeam found it on their own before this post, obviously, as in Agent for Linux they utilize sqlite) I would like to see next Windows Agent version shipped with sqlite too, as would many others. It would make the Agent on Windows behave much more efficiently, especially on lower spec'd machines. Because Veeam Agent is something you need to have running in there. But it's not doing anything 95-99% of the time, so it's just wasting resources waiting to be triggered on schedule to do it's one (very important) thing. If it could do that without wasting resources on the machine while it's not doing anything, that'd be even better. And that's the whole point and motivation behind this thread. We want to make things better and Veeam is with us on this, and that's great. And quite rare, I might add.

[Gostev]

In any case, this is probably a moot point to discuss at least as it comes to Veeam, because we're getting rid of the infamous behemoth in the next major release of the agent

[Gerald]

Thank you so much for getting rid of MSSQL. Very much looking forward to this next major release.

[mcz]

Anton, does the upgrade then automatically remove MSSQL or only the database? Or does it check if the veeam db is the only one and in such a case do the uninstallation?

[lucius_the]

@mcz since it's the LocalDB version of SQL Server it's something that is started by Veeam Agent service. Not shared with anything. So we can be pretty sure it's not going be running after the upgrade (it's probably going to be started once to perform a DB migration and then switched off, as it's not needed any more). That's just my guess though.

[Gostev]

At this point in time I have absolutely no idea how the upgrade ends up working. Because the line can potentially be drawn anywhere depending on QC resources availability, bugs in related functionality, time left before RTM etc.