since v10 we're experiencing some strange workload spikes every 5min on SQL-Server (which is all in one an backup server). After some performance tracking I found the query which causes this. (see below).
It seems to me it have to do something with SQL-AlwaysOn Backups. Is there a possibility to optimize the query a little bit. Our backup server has assigned 16vCores and 96GB of RAM. But no problem, this query runs longer than 1min at nearly 100% CPU-Usage and that approx. all 5min.
To encounter the performance drop on the whole machine I limited SQL-Server to 8vCPUs (one of two available NUMA-Nodes) and reduced "Max degree of parallelism" to 6. But this is only a workaround.
Here the figured out query:
Code: Select all
/*
This query text was retrieved from showplan XML, and may be truncated.
*/
WITH sqloibs_groups
AS (
SELECT groups.group_id
,sqloibs.obj_id
FROM [Backup.Model.AlwaysOnGuestDatabases] dbs
LEFT JOIN [Backup.Model.AlwaysOnGroups] groups ON dbs.group_id = groups.group_id
INNER JOIN dbo.[Backup.Model.SqlOIBs] sqloibs ON dbs.group_db_id = sqloibs.group_db_id
GROUP BY groups.group_id
,sqloibs.obj_id
)
,sqloibs_count_on_stg
AS (
SELECT b.id AS logbackup_id
,sqloibs.obj_id AS object_id
,COUNT(sqloibs.id) AS oibs_count
,MAX(sqloibs.creation_time) AS creation_time
,MAX(sqloibs.usn) AS usn
FROM dbo.[Backup.Model.SqlOIBs] sqloibs
INNER JOIN dbo.[Backup.Model.Storages] stg ON stg.id = sqloibs.stg_id
INNER JOIN dbo.[Backup.Model.Backups] b ON stg.backup_id = b.id
GROUP BY sqloibs.stg_id
,sqloibs.obj_id
,b.id
)
,points_count
AS (
SELECT DISTINCT log_backup.id AS id
,oibView.backup_id AS parent_id
,s_stg.object_id AS object_id
,MAX(oibView.secondary_vol) AS sec_vol
,sqloibs_groups.group_id
,(
CASE
WHEN MAX(oibView.storage_backup) = 1
THEN (
SELECT SUM(oibs_count)
FROM sqloibs_count_on_stg s
WHERE s.logbackup_id = id
AND s.object_id = s_stg.object_id
)
ELSE (
SELECT SUM(oibs_count)
FROM sqloibs_count_on_stg s
WHERE s.logbackup_id = id
)
END
) AS points
,MAX(s_stg.creation_time) AS creation_time
,MAX(s_stg.usn) AS usn
FROM sqloibs_count_on_stg s_stg
INNER JOIN dbo.[Backup.Model.Backups] log_backup ON s_stg.logbackup_id = log_backup.id
INNER JOIN [dbo].[DbAggregate.OibsView] oibView ON log_backup.parent_backup_id = oibView.fake_b_id
LEFT JOIN sqloibs_groups ON s_stg.object_id = sqloibs_groups.obj_id
GROUP BY oibView.backup_id
,log_backup.id
,oib_object
,group_id
,s_stg.object_id
)
,group_filer
AS (
SELECT id
,parent_id
,points
,usn
,creation_time
,sec_vol
,ROW_NUMBER() OVER (
PARTITION BY group_id ORDER BY group_id
) AS in_same_sql_group
FROM points_count
WHERE group_id IS NOT NULL
)
SELECT t.id
,t.parent_id
,t.points
,MAX(t.usn) AS usn
,MAX(t.creation_time) AS creation_time
,MAX(sec_vol) AS sec_vol
INTO #tmp
FROM (
SELECT id
,parent_id
,points
,usn
,creation_time
,sec_vol
FROM points_count
WHERE group_id IS NULL
UNION
SELECT id
,parent_id
,points
,usn
,creation_time
,sec_vol
FROM group_filer
WHERE in_same_sql_group >= 1
) t
GROUP BY t.id
,t.parent_id
,t.points