-
- Veeam Legend
- Posts: 1197
- Liked: 415 times
- Joined: Dec 17, 2015 7:17 am
- Contact:
Slow SQL Performance after Server upgrade
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
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
-
- 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
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.
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.
-
- Veeam Legend
- Posts: 1197
- Liked: 415 times
- Joined: Dec 17, 2015 7:17 am
- Contact:
Re: Slow SQL Performance after Server upgrade
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
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
-
- 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
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"?
-
- Veeam Legend
- Posts: 1197
- Liked: 415 times
- Joined: Dec 17, 2015 7:17 am
- Contact:
Re: Slow SQL Performance after Server upgrade
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.
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.
-
- Veeam Legend
- Posts: 1197
- Liked: 415 times
- Joined: Dec 17, 2015 7:17 am
- Contact:
Re: Slow SQL Performance after Server upgrade
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:
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
-
- 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
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.
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.
-
- Veeam Legend
- Posts: 1197
- Liked: 415 times
- Joined: Dec 17, 2015 7:17 am
- Contact:
Re: Slow SQL Performance after Server upgrade
Gostev, you are right. This is the EM database on the same server.
Surely we will continue to work with support!
Surely we will continue to work with support!
-
- 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
Sounds good, btw based on some emails looks like SQL developers are already looking at your case anyway
-
- 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
mkretzer, did you resolve this?
We are experiance the same thing after upgrading EM where both DBs are hosted on the same SQL server.
We are experiance the same thing after upgrading EM where both DBs are hosted on the same SQL server.
-
- 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
As the root causes for particular issue might vary dramatically, it's recommended to open your own ticket with support team. Thanks!
-
- 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
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.
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.
Who is online
Users browsing this forum: Amazon [Bot], Google [Bot], Semrush [Bot] and 44 guests