To make it possible, I built tables to save only the last status to it earns performance, these are the statements:
--=================================================================
--// It saves the last status of Backup (used for Missing Backups)
--=================================================================
USE [DBAWEB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [REAL].[ServerBackup](
[ServerBackupGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[BackupIndicatorCategoryName] [varchar](100) NOT NULL,
[BackupIndicatorName] [varchar](100) NOT NULL,
[BackupAVGIndicatorValue] [float] NOT NULL,
[BackupIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of Locks
--=================================================================
CREATE TABLE [REAL].[ServerLock](
[ServerLockGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of CPU Performance
--=================================================================
CREATE TABLE [REAL].[ServerPerformance](
[ServerPerformanceGUID] [int] NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of Error Logs
--=================================================================
CREATE TABLE [REAL].[ServerSQLErrorLog](
[ServerSQLLogGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of Error Logs
--=================================================================
CREATE TABLE [REAL].[ServerSQLErrorLog](
[ServerSQLLogGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of SQL Jobs
--=================================================================
CREATE TABLE [REAL].[ServerSQLJob](
[ServerSQLJobGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of SQL Jobs
--=================================================================
CREATE TABLE [REAL].[ServerSQLJob](
[ServerSQLJobGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It saves the last status of SQL Logs
--=================================================================
CREATE TABLE [REAL].[ServerSQLLog](
[ServerSQLLogGUID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NOT NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[CreatedDateOn] [datetime] NOT NULL
) ON [PRIMARY]
GO
--=================================================================
--// It feeds the Missing Backups from all the servers
--=================================================================
USE [DBAWEB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [REAL].[spDBAWEBLoadServerBackup]
@ServerName varchar(60) = 'All'
as
/*****************************************************************************
Name: REAL.spDBAWEBLoadServerBackup
Load the Real-Time table REAL.ServerBackup from a operational table previously populated in DBA.
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
16 Aug 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
exec REAL.spDBAWEBLoadServerBackup @ServerName = 'TG-SVR-SQL4\PRD'
******************************************************************************/
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG1\PRD' Begin
merge REAL.ServerBackup d
using (
--// CG1
--==================================
Select
top 1
ServerName,
BackupIndicatorCategoryName,
BackupIndicatorName,
BackupAVGIndicatorValue,
BackupIndicatorValue,
CreatedDateOn
from DBAWEB_CG1.DBA.DBO.vwServerBackup
) s on s.ServerName = d.ServerName and s.BackupIndicatorCategoryName = d.BackupIndicatorCategoryName and s.BackupIndicatorName = d.BackupIndicatorName
when matched
then
update
set
BackupAVGIndicatorValue = s.BackupAVGIndicatorValue,
BackupIndicatorValue = s.BackupIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, BackupIndicatorCategoryName, BackupIndicatorName, BackupAVGIndicatorValue, BackupIndicatorValue, CreatedDateOn)
values (ServerName, BackupIndicatorCategoryName, BackupIndicatorName, BackupAVGIndicatorValue, BackupIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG2\PRD2' Begin
merge REAL.ServerBackup d
using (
--// CG2
--==================================
Select
top 1
ServerName,
BackupIndicatorCategoryName,
BackupIndicatorName,
BackupAVGIndicatorValue,
BackupIndicatorValue,
CreatedDateOn
from DBAWEB_CG2.DBA.DBO.vwServerBackup
) s on s.ServerName = d.ServerName and s.BackupIndicatorCategoryName = d.BackupIndicatorCategoryName and s.BackupIndicatorName = d.BackupIndicatorName
when matched
then
update
set
BackupAVGIndicatorValue = s.BackupAVGIndicatorValue,
BackupIndicatorValue = s.BackupIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, BackupIndicatorCategoryName, BackupIndicatorName, BackupAVGIndicatorValue, BackupIndicatorValue, CreatedDateOn)
values (ServerName, BackupIndicatorCategoryName, BackupIndicatorName, BackupAVGIndicatorValue, BackupIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-SQL4\PRD' Begin
merge REAL.ServerBackup d
using (
--// SQL4
--==================================
Select
top 1
ServerName,
BackupIndicatorCategoryName,
BackupIndicatorName,
BackupAVGIndicatorValue,
BackupIndicatorValue,
CreatedDateOn
from DBA.DBO.vwServerBackup
) s on s.ServerName = d.ServerName and s.BackupIndicatorCategoryName = d.BackupIndicatorCategoryName and s.BackupIndicatorName = d.BackupIndicatorName
when matched
then
update
set
BackupAVGIndicatorValue = s.BackupAVGIndicatorValue,
BackupIndicatorValue = s.BackupIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, BackupIndicatorCategoryName, BackupIndicatorName, BackupAVGIndicatorValue, BackupIndicatorValue, CreatedDateOn)
values (ServerName, BackupIndicatorCategoryName, BackupIndicatorName, BackupAVGIndicatorValue, BackupIndicatorValue, CreatedDateOn);
End
--=================================================================
--// It feeds the SQL Locks from all the servers
--=================================================================
USE [DBAWEB]
GO
/****** Object: StoredProcedure [REAL].[spDBAWEBLoadServerLock] Script Date: 16/10/2018 10:42:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [REAL].[spDBAWEBLoadServerLock]
@ServerName varchar(60) = 'All'
as
/*****************************************************************************
Name: REAL.spDBAWEBLoadServerLock
Load the Real-Time table REAL.ServerLock from a operational table previously populated in DBA.
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Act 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
exec REAL.spDBAWEBLoadServerLock
******************************************************************************/
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG1\PRD' Begin
Truncate table REAL.ServerLock;
merge REAL.ServerLock d
using (
--// CG1
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue,
CreatedDateOn
from DBAWEB_CG1.DBA.DBO.ServerLock
Where ModifiedDateOn > DateAdd(Minute, -4, getdate())
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG2\PRD2' Begin
merge REAL.ServerLock d
using (
--// CG2
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue,
CreatedDateOn
from DBAWEB_CG2.DBA.DBO.ServerLock
Where ModifiedDateOn > DateAdd(Minute, -4, getdate())
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-SQL4\PRD' Begin
merge REAL.ServerLock d
using (
--// SQL4
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue,
CreatedDateOn
from DBA.DBO.ServerLock
Where ModifiedDateOn > DateAdd(Minute, -4, getdate())
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
--=================================================================
--// It feeds the Server Performance from all the servers
--=================================================================
USE [DBAWEB]
GO
/****** Object: StoredProcedure [REAL].[spDBAWEBLoadServerPerformance] Script Date: 16/10/2018 10:43:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [REAL].[spDBAWEBLoadServerPerformance]
@ServerName varchar(60) = 'All'
as
/*****************************************************************************
Name: REAL.spDBAWEBLoadServerPerformance
Load the Real-Time table REAL.ServerPerformance from a operational table previously populated in DBA.
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
16 Aug 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
exec REAL.spDBAWEBLoadServerPerformance
******************************************************************************/
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG1\PRD' Begin
merge REAL.ServerPerformance d
using (
--// CG1
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue,
CreatedDateOn
from DBAWEB_CG1.DBA.DBO.ServerPerformance
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG2\PRD2' Begin
merge REAL.ServerPerformance d
using (
--// CG2
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue,
CreatedDateOn
from DBAWEB_CG2.DBA.DBO.ServerPerformance
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-SQL4\PRD' Begin
merge REAL.ServerPerformance d
using (
--// SQL4
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue,
CreatedDateOn
from DBA.DBO.ServerPerformance
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
--=================================================================
--// It feeds the SQL Jobs from all the servers
--=================================================================
USE [DBAWEB]
GO
/****** Object: StoredProcedure [REAL].[spDBAWEBLoadServerSQLJob] Script Date: 16/10/2018 10:43:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [REAL].[spDBAWEBLoadServerSQLJob]
@ServerName varchar(60) = 'All'
as
/*****************************************************************************
Name: REAL.spDBAWEBLoadServerSQLJob
Load the Real-Time table REAL.ServerSQLJob from a operational table previously populated in DBA.
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Act 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
exec REAL.spDBAWEBLoadServerSQLJob
******************************************************************************/
--// Clean up all the data
Delete REAL.ServerSQLJob
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG1\PRD' Begin
merge REAL.ServerSQLJob d
using (
--// CG1
--==================================
Select
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = sum(PerformanceIndicatorValue),
CreatedDateOn = Max(CreatedDateOn)
from DBAWEB_CG1.DBA.DBO.ServerSQLJob
Where PerformanceIndicatorValue > 0
Group By
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG2\PRD2' Begin
merge REAL.ServerSQLJob d
using (
--// CG2
--==================================
Select
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = sum(PerformanceIndicatorValue),
CreatedDateOn = Max(CreatedDateOn)
from DBAWEB_CG2.DBA.DBO.ServerSQLJob
Where PerformanceIndicatorValue > 0
Group By
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-SQL4\PRD' Begin
merge REAL.ServerSQLJob d
using (
--// SQL4
--==================================
Select
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = sum(PerformanceIndicatorValue),
CreatedDateOn = Max(CreatedDateOn)
from DBA.DBO.ServerSQLJob
Where PerformanceIndicatorValue > 0
Group By
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End