May 11, 2015 at 8:39 am
I have a stored procedure which I've inherited and I'm having a few performance issues. When the stored proc is run it produces an SSRS report showing the status of the backup and restores of log shipping on a number of servers.
I've looked at the execution plan and there are quite a few Nested Loops - with the warning 'No Join Predicate'.
Could some posibily help me out in removing these warnings so the query performs better. It currently takes around 15mins to run.
The query the stored proc runs is:
USE [CCReports]
-- create temp table to hold timings and status of all backup and restore jobs
create table #LogShippingJobs
(ServerName varchar(50), JobName varchar(128), Enabled bit, Run_Duration int)
-- get AP stats
insert into #LogShippingJobs
select top 1 'Database1', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
from[Database1_DR].msdb.dbo.sysjobs sj
join[Database1_DR].msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Restore Run'
order by sjh.instance_id desc
insert into #LogShippingJobs
select top 1 'Database1', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
fromDatabase1.msdb.dbo.sysjobs sj
joinDatabase1.msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Backups Run'
order by sjh.instance_id desc
-- get BRD
insert into #LogShippingJobs
select top 1 'Database2', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
from[Database2_DR].msdb.dbo.sysjobs sj
join[Database2_DR].msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Restore Run'
order by sjh.instance_id desc
insert into #LogShippingJobs
select top 1 'Database2', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
fromDatabase2.msdb.dbo.sysjobs sj
joinDatabase2.msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Backups Run'
order by sjh.instance_id desc
-- MDN
insert into #LogShippingJobs
select top 1 'Database3', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
from[Database3_DR].msdb.dbo.sysjobs sj
join[Database3_DR].msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Restore Run'
order by sjh.instance_id desc
insert into #LogShippingJobs
select top 1 'Database3', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
fromDatabase3.msdb.dbo.sysjobs sj
joinDatabase3.msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Backups Run'
order by sjh.instance_id desc
-- BLF
insert into #LogShippingJobs
select top 1 'Database4', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
from[Database4_DR].msdb.dbo.sysjobs sj
join[Database4_DR].msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Restore Run'
order by sjh.instance_id desc
insert into #LogShippingJobs
select top 1 'Database4', sj.Name, sj.Enabled, (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 ) as run_duration
fromDatabase4.msdb.dbo.sysjobs sj
joinDatabase4.msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
wheresj.[name] = 'Log Shipping Backups Run'
order by sjh.instance_id desc
-- MAIN UNION query
selectb.[Server],
b.[Database],
b.[ReadOnly],
b.[Created],
b.[recovery_model_desc],
b.[LogShippingStatus],
b.[LastLogBackup],
b.[LastLogRestore],
c.Enabled as BackupEnabled,
c.run_duration as BackupDuration,
a.Enabled as RestoreEnabled,
a.run_duration as RestoreDuration
from
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database1' and JobName = 'Log Shipping Restore Run'
) a,
(
select
'Database1' [Server],
db.[name] [Database],
db.is_read_only [ReadOnly],
cast(convert(varchar,db.create_date,112) as smalldatetime) [Created],
db.[recovery_model_desc],
case when lsl.DatabaseName is not null then 'Active' else 'Inactive' end as [LogShippingStatus],
bs.[LastLogBackup],
rh.[LastLogRestore]
from
Database1.master.sys.databases db
left join
(select DatabaseName from Database1.Dialler_Admin.dbo.LogShipping_List
where active = 1) lsl on db.[name] = lsl.DatabaseName
left join
(select max(Backup_Finish_Date) [LastLogBackup], Database_Name
from Database1.msdb.dbo.backupset where [Type] = 'L' group by Database_Name
) bs on db.[name] = bs.Database_Name
left join
(select max(Restore_Date) [LastLogRestore], Destination_Database_Name
from [Database1_DR].msdb.dbo.restorehistory where [Restore_Type] = 'L' group by Destination_Database_Name
) rh on db.[name] = rh.Destination_Database_Name
where
db.database_id > 4
and
db.is_read_only <> 1
) b,
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database1' and JobName = 'Log Shipping Backups Run'
) c
UNION
selectb.[Server],
b.[Database],
b.[ReadOnly],
b.[Created],
b.[recovery_model_desc],
b.[LogShippingStatus],
b.[LastLogBackup],
b.[LastLogRestore],
c.Enabled as BackupEnabled,
c.run_duration as BackupDuration,
a.Enabled as RestoreEnabled,
a.run_duration as RestoreDuration
from
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database2' and JobName = 'Log Shipping Restore Run'
) a,
(
select
'Database2' [Server],
db.[name] [Database],
db.is_read_only [ReadOnly],
cast(convert(varchar,db.create_date,112) as smalldatetime) [Created],
db.[recovery_model_desc],
case when lsl.DatabaseName is not null then 'Active' else 'Inactive' end as [LogShippingStatus],
bs.[LastLogBackup],
rh.[LastLogRestore]
from
Database2.master.sys.databases db
left join
(select DatabaseName from Database2.Dialler_Admin.dbo.LogShipping_List
where active = 1) lsl on db.[name] = lsl.DatabaseName
left join
(select max(Backup_Finish_Date) [LastLogBackup], Database_Name
from Database2.msdb.dbo.backupset where [Type] = 'L' group by Database_Name
) bs on db.[name] = bs.Database_Name
left join
(select max(Restore_Date) [LastLogRestore], Destination_Database_Name
from [Database2_DR].msdb.dbo.restorehistory where [Restore_Type] = 'L' group by Destination_Database_Name
) rh on db.[name] = rh.Destination_Database_Name
where
db.database_id > 4
and
db.is_read_only <> 1
) b,
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database2' and JobName = 'Log Shipping Backups Run'
) c
UNION
selectb.[Server],
b.[Database],
b.[ReadOnly],
b.[Created],
b.[recovery_model_desc],
b.[LogShippingStatus],
b.[LastLogBackup],
b.[LastLogRestore],
c.Enabled as BackupEnabled,
c.run_duration as BackupDuration,
a.Enabled as RestoreEnabled,
a.run_duration as RestoreDuration
from
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database3' and JobName = 'Log Shipping Restore Run'
) a,
(
select
'Database3' [Server],
db.[name] [Database],
db.is_read_only [ReadOnly],
cast(convert(varchar,db.create_date,112) as smalldatetime) [Created],
db.[recovery_model_desc],
case when lsl.DatabaseName is not null then 'Active' else 'Inactive' end as [LogShippingStatus],
bs.[LastLogBackup],
rh.[LastLogRestore]
from
Database3.master.sys.databases db
left join
(select DatabaseName from Database3.Dialler_Admin.dbo.LogShipping_List
where active = 1) lsl on db.[name] = lsl.DatabaseName
left join
(select max(Backup_Finish_Date) [LastLogBackup], Database_Name
from Database3.msdb.dbo.backupset where [Type] = 'L' group by Database_Name
) bs on db.[name] = bs.Database_Name
left join
(select max(Restore_Date) [LastLogRestore], Destination_Database_Name
from [Database3_DR].msdb.dbo.restorehistory where [Restore_Type] = 'L' group by Destination_Database_Name
) rh on db.[name] = rh.Destination_Database_Name
where
db.database_id > 4
and
db.is_read_only <> 1
) b,
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database3' and JobName = 'Log Shipping Backups Run'
) c
UNION
selectb.[Server],
b.[Database],
b.[ReadOnly],
b.[Created],
b.[recovery_model_desc],
b.[LogShippingStatus],
b.[LastLogBackup],
b.[LastLogRestore],
c.Enabled as BackupEnabled,
c.run_duration as BackupDuration,
a.Enabled as RestoreEnabled,
a.run_duration as RestoreDuration
from
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database4' and JobName = 'Log Shipping Restore Run'
) a,
(
select
'Database4' [Server],
db.[name] [Database],
db.is_read_only [ReadOnly],
cast(convert(varchar,db.create_date,112) as smalldatetime) [Created],
db.[recovery_model_desc],
case when lsl.DatabaseName is not null then 'Active' else 'Inactive' end as [LogShippingStatus],
bs.[LastLogBackup],
rh.[LastLogRestore]
from
Database4.master.sys.databases db
left join
(select DatabaseName from Database4.Dialler_Admin.dbo.LogShipping_List
where active = 1) lsl on db.[name] = lsl.DatabaseName
left join
(select max(Backup_Finish_Date) [LastLogBackup], Database_Name
from Database4.msdb.dbo.backupset where [Type] = 'L' group by Database_Name
) bs on db.[name] = bs.Database_Name
left join
(select max(Restore_Date) [LastLogRestore], Destination_Database_Name
from [Database4_DR].msdb.dbo.restorehistory where [Restore_Type] = 'L' group by Destination_Database_Name
) rh on db.[name] = rh.Destination_Database_Name
where
db.database_id > 4
and
db.is_read_only <> 1
) b,
(
SELECT Enabled, Run_Duration from
#LogShippingJobs
WHERE ServerName = 'Database4' and JobName = 'Log Shipping Backups Run'
) c
drop table #LogShippingJobs
May 29, 2015 at 7:00 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply