Comprehensive data protection for all workloads
Post Reply
mkretzer
Veeam Legend
Posts: 1197
Liked: 415 times
Joined: Dec 17, 2015 7:17 am
Contact:

Slow SQL Performance after Server upgrade

Post by mkretzer »

Hello,

Case 03416572

we replaced our Veeam server with a fresh, powerfull new system running on W2019. We used config backup & restore.

Now the Veeam database actions seem very slow. CPU usage is ~20 % by SQL server all the time. The old system had only 16 cores, the new 36. If the old system had the same load it would have been unusable.

Main wait type is "Latch" and the SQL with the highest CPU most of the time is:
SET @newXmlLogLine = ( SELECT @myDoc.query('/Root/Log[@Id = sql:variable("@row_log_id")]')
)

Has anybody seen this?

Markus
poulpreben
Certified Trainer
Posts: 1024
Liked: 448 times
Joined: Jul 23, 2012 8:16 am
Full Name: Preben Berg
Contact:

Re: Slow SQL Performance after Server upgrade

Post by poulpreben »

What type of storage is used for hosting the SQL configuration database on the new server?

I am asking, because I have seen a few instances recently where customers have chosen "Boot Optimized Storage Solution" (BOSS) from DELL/EMC. This is great for an operating system drive, but it is extremely bad for anything transactional, since the BOSS controller does not have any cache.
mkretzer
Veeam Legend
Posts: 1197
Liked: 415 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Slow SQL Performance after Server upgrade

Post by mkretzer »

Old server: Dell H710, 512 MB Cache with 6 10k Disks in a RAID 10, always overloaded.
New server: RAID 930, 2 GB Cache with two Intel 1.60TB SSD in RAID 1, delivers streaming 6 GByte/s

I don't think disk is the problem here :-)
Gostev
Chief Product Officer
Posts: 31690
Liked: 7201 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Slow SQL Performance after Server upgrade

Post by Gostev »

mkretzer wrote: Feb 18, 2019 8:01 pmNow the Veeam database actions seem very slow. CPU usage is ~20 % by SQL server all the time. The old system had only 16 cores, the new 36. If the old system had the same load it would have been unusable.
Hello! What makes you think CPU load on the previous system was any different, do you have any metrics on the old system? I am not sure why do you think ~45% CPU load (derived from the current ~20% load based on 2 times less cores) would have made the old system "unusable"?
mkretzer
Veeam Legend
Posts: 1197
Liked: 415 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Slow SQL Performance after Server upgrade

Post by mkretzer »

Gostev, the old system was overloaded all the time but by the Veeam processes. Many times we checked task manager and never saw SQL server as one of the big processes. Sadly i have no per-process metrics. Maybe SQL did not have high CPU usage because its IO was so much slower and so CPU was not the bottleneck. But then i would expect VBR backups to be much faster now which is not the case.

And: about every 20 seconds SQL Server load goes to 100 % CPU - on all 36 cores. I really do not think we would have missed that.
mkretzer
Veeam Legend
Posts: 1197
Liked: 415 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Slow SQL Performance after Server upgrade

Post by mkretzer »

One more thing: I ran profiler. The SQL command posted in my first message causes over 1 second CPU time all the time. But the thing which runs every 20 seconds even causes > 132 seconds CPU time - every 20 seconds. Also 1 millions logical reads. That cannot be optimized correctly! On our old system that would have caused 7 seconds of 100 % cpu on all 18 cores and every 20 seconds.

Here is the SQL:

Code: Select all

WITH orderedOibChains AS
    (
        SELECT
            oibs.[id] AS [oib_id],
            backups.[id] AS [backup_id],
            bobjects.[id] AS [object_id],
            backups.[db_instance_id],
            ROW_NUMBER() OVER (PARTITION BY backups.[id], bobjects.[id] ORDER BY oibs.[creation_time] DESC) [number],
            COUNT(*) OVER (PARTITION BY backups.[id], bobjects.[id]) [oibs_count]
        FROM
            [dbo].[C.Backup.Model.OIBs] oibs
            INNER JOIN [dbo].[C.Backup.Model.Points] points ON oibs.[point_id] = points.[id] AND oibs.[db_instance_id] = points.[db_instance_id]
            INNER JOIN [dbo].[C.Backup.Model.Backups] backups ON points.[backup_id] = backups.[id] AND points.[db_instance_id] = backups.[db_instance_id]
            INNER JOIN [dbo].[C.BObjects] bobjects ON oibs.[object_id] = bobjects.[id] AND oibs.[db_instance_id] = bobjects.[db_instance_id]
        WHERE
            oibs.[is_corrupted] = 0 AND
            (
                EXISTS (SELECT 1 FROM @fullChangesDbInstanceIds full_changes WHERE backups.[db_instance_id] = full_changes.[value]) OR                
                EXISTS
                (
                    SELECT 1
                    FROM
                        #chainsChangesByBackupsAndObjects changes_by_backups_and_objects
                    WHERE
                        (changes_by_backups_and_objects.[backup_id] = backups.[id] AND changes_by_backups_and_objects.[db_instance_id] = backups.[db_instance_id]) AND
                        (changes_by_backups_and_objects.[object_id] = bobjects.[id] AND changes_by_backups_and_objects.[db_instance_id] = bobjects.[db_instance_id])
                ) OR
                EXISTS
                (
                    SELECT 1
                    FROM
                        #chainsChangesByBackups changes_by_backups
                    WHERE
                        changes_by_backups.[backup_id] = backups.[id] AND changes_by_backups.[db_instance_id] = backups.[db_instance_id]
                ) OR
                EXISTS
                (
                    SELECT 1
                    FROM
                        #chainsChangesByObjects changes_by_objects
                    WHERE
                        changes_by_objects.[object_id] = bobjects.[id] AND changes_by_objects.[db_instance_id] = bobjects.[db_instance_id]
                )                
            )
    )
    INSERT INTO #chainsToUpdate ([backup_id], [object_id], [db_instance_id], [oibs_count], [last_oib_id], [last_oib_display_name], [last_oib_creation_time])
    SELECT
        ordered_oib_chains.[backup_id],
        ordered_oib_chains.[object_id],
        ordered_oib_chains.[db_instance_id],
        ordered_oib_chains.[oibs_count],
        ordered_oib_chains.[oib_id],
		oibs.[display_name],
		oibs.[creation_time]
    FROM
        orderedOibChains ordered_oib_chains
		INNER JOIN [dbo].[C.Backup.Model.OIBs] oibs ON ordered_oib_chains.[oib_id] = oibs.[id] AND ordered_oib_chains.[db_instance_id] = oibs.[db_instance_id]
    WHERE
        ordered_oib_chains.[number] = 1
Gostev
Chief Product Officer
Posts: 31690
Liked: 7201 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Slow SQL Performance after Server upgrade

Post by Gostev »

This looks like something Enterprise Manager related. Do you have Enterprise Manager database running on the same instance as your backup server uses? This would explain extra load on SQL database.

Anyway, there are no SQL experts on the team behind this forum, so please continue working with support on that. They will escalate the case to SQL developers, and I am sure those will find some ways for optimizations.
mkretzer
Veeam Legend
Posts: 1197
Liked: 415 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Slow SQL Performance after Server upgrade

Post by mkretzer »

Gostev, you are right. This is the EM database on the same server.

Surely we will continue to work with support!
Gostev
Chief Product Officer
Posts: 31690
Liked: 7201 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Slow SQL Performance after Server upgrade

Post by Gostev »

Sounds good, btw based on some emails looks like SQL developers are already looking at your case anyway :D
astemums
Service Provider
Posts: 4
Liked: 2 times
Joined: Mar 28, 2018 1:01 pm
Full Name: Andreas Stepan
Contact:

Re: Slow SQL Performance after Server upgrade

Post by astemums »

mkretzer, did you resolve this?
We are experiance the same thing after upgrading EM where both DBs are hosted on the same SQL server.
veremin
Product Manager
Posts: 20352
Liked: 2284 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Slow SQL Performance after Server upgrade

Post by veremin »

As the root causes for particular issue might vary dramatically, it's recommended to open your own ticket with support team. Thanks!
astemums
Service Provider
Posts: 4
Liked: 2 times
Joined: Mar 28, 2018 1:01 pm
Full Name: Andreas Stepan
Contact:

Re: Slow SQL Performance after Server upgrade

Post by astemums » 1 person likes this post

Tnx!
In our case the SQL server was too constrained on memory. We apparently managed to run it on 6GB for all these years until now. Doubled to 12GB and it is fine now.
Post Reply

Who is online

Users browsing this forum: Google [Bot], Semrush [Bot] and 45 guests