Slow SQL Performance after Server upgrade

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

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]

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]

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.

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]

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]

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]

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]

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

Surely we will continue to work with support!

[Gostev]

Sounds good, btw based on some emails looks like SQL developers are already looking at your case anyway

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

As the root causes for particular issue might vary dramatically, it's recommended to open your own ticket with support team. Thanks!

[astemums]

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.