HSQL

-- Listar Vers�o das Inst�ncias:
-->Colar directamente na sheet correcta
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = '
SELECT @@VERSION AS [SQL Server Version]
    , SERVERPROPERTY(''MachineName'') AS [MachineName]
    , SERVERPROPERTY(''ServerName'') AS [ServerName]
    , SERVERPROPERTY(''InstanceName'') AS [Instance]
    , sqlserver_start_time
    , SERVERPROPERTY(''Edition'') AS [Edition]
    , SERVERPROPERTY(''ProductLevel'') AS [ProductLevel]
    , SERVERPROPERTY(''ProductVersion'') AS [ProductVersion]
    , SERVERPROPERTY(''Collation'') AS [Collation]
    , SERVERPROPERTY(''IsFullTextInstalled'') AS [IsFullTextInstalled]
    , SERVERPROPERTY(''IsIntegratedSecurityOnly'') AS [IsIntegratedSecurityOnly]
    , SERVERPROPERTY(''IsClustered'') AS [IsClustered]
    , SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [ComputerNamePhysicalNetBIOS]
    , SERVERPROPERTY(''ProcessID'') AS [ProcessID]
    , (SELECT TOP 1 local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL) PortNumber
    , s1.value_in_use [MaxDOP]
    , s2.value_in_use [Max Memory Setting]
    , pc.cntr_value AS [Page Life Total]
    , CASE WHEN s2.value_in_use = 2147483647
        THEN CONVERT(decimal(18,2),(pc.cntr_value / ((CONVERT(decimal(18,2),(' + CASE WHEN Inst.Vers < 11 THEN 'oif.physical_memory_in_bytes/1048576' ELSE 'oif.physical_memory_kb/1024' END + ')) / 4096)*300)))
        ELSE CONVERT(decimal(18,2),(pc.cntr_value / ((CONVERT(decimal(18,2),s2.value_in_use) / 4096)*300)))
    END AS [Page Life Adjusted]
    , CONVERT(decimal(18,2),(bhr.cntr_value / (bhrb.cntr_value * 0.01))) B_H_Ratio
    , oif.cpu_count AS [Logical CPU Count]
    , ' + CASE WHEN Inst.Vers < 11 THEN 'oif.physical_memory_in_bytes/1048576' ELSE 'oif.physical_memory_kb/1024' END + ' AS [Physical Memory (MB)]
    , oif.hyperthread_ratio AS [Hyperthread Ratio]
    , oif.cpu_count/oif.hyperthread_ratio AS [Physical CPU Count]
    , CASE oif.affinity_type_desc WHEN ''AUTO'' THEN ''AUTO'' WHEN ''MANUAL'' THEN ''MANUAL: '' + CONVERT(VARCHAR(32), s3.value) END [CPU_Affinity_Mask]
    , GETDATE() AS SampleDate
FROM
    sys.configurations s1, sys.configurations s2, sys.configurations s3, sys.dm_os_performance_counters pc WITH (NOLOCK), sys.dm_os_sys_info oif WITH (NOLOCK),
    sys.dm_os_performance_counters bhr, sys.dm_os_performance_counters bhrb
WHERE
    s1.description LIKE ''%max%parallelism%''
    AND s2.name like ''max server memory (MB)''
    AND s3.configuration_id = 1535
    AND pc.[object_name] LIKE N''%Buffer Node%'' -- Handles named instances
--  AND pc.[instance_name] IN ('''', ''_Total'') -- Handles named instances
    AND pc.counter_name = N''Page life expectancy''
    AND bhr.[object_name] LIKE ''%Buffer Manager%'' AND bhr.[counter_name] = ''Buffer cache hit ratio''
    AND bhrb.[object_name] LIKE ''%Buffer Manager%'' AND bhrb.[counter_name] LIKE ''%Buffer cache hit ratio base%'' OPTION (RECOMPILE);
'
FROM (SELECT CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')), 1, CHARINDEX('.', CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion'))) -1 )) AS Vers) Inst

-- SELECT @SQL

EXEC sys.sp_executesql @SQL

-- Services:

SELECT servicename
    , process_id
    , startup_type_desc
    , status_desc
    , last_startup_time
    , service_account
    , is_clustered
    , cluster_nodename
    , [filename]
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

-- Trace Status: n�o retorna nada

DBCC TRACESTATUS (-1);

-- Windows Info:

SELECT CASE windows_release
    WHEN '5.2' THEN 'Windows XP or Windows Server 2003'
    WHEN '6.0' THEN 'Windows Vista or Windows Server 2008'
    WHEN '6.1' THEN 'Windows 7 or Windows Server 2008 R2'
    WHEN '6.2' THEN 'Windows 8 or Windows Server 2012'
    WHEN '6.3' THEN 'Windows 8.1, Windows 10 or Windows Server 2012 R2'
    WHEN '10.0' THEN 'Windows Server 2016'
    ELSE windows_release END AS "WINDOWS Version"
    , CASE windows_sku
    WHEN '4' THEN 'Enterprise Edition'
    WHEN '7' THEN 'Standard Server Edition'
    WHEN '8' THEN 'Datacenter Server Edition'
    WHEN '10' THEN 'Enterprise Server Edition'
    WHEN '48' THEN 'Professional Edition'
    ELSE windows_release END AS "WINDOWS Version"
    , windows_service_pack_level
    , os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

-- List Backups;(EXECUTAR EM PRD E DEPOIS EM QA)

SELECT  name
    , recovery_model_desc
    , state_desc
    , compatibility_level
    , d AS 'Last Full Backup'
    , i AS 'Last Differential Backup'
    , l AS 'Last log Backup'
    , CASE state_desc WHEN 'ONLINE'
        THEN CASE WHEN d IS NULL
            THEN 'MISSING'
            ELSE CASE WHEN DATEDIFF(day, d, GETDATE()) > 2
                THEN CASE WHEN i IS NULL
                    THEN 'MISSING'
                    ELSE CASE WHEN DATEDIFF(day, i, GETDATE()) > 2 THEN
                        'MISSING'
                        ELSE 'OK'
                    END
                END
                ELSE 'OK'
            END
        END
        ELSE 'OFFLINE'
    END AS [Problem DB File]
    , CASE state_desc WHEN 'ONLINE'
        THEN CASE recovery_model_desc WHEN 'FULL'
            THEN CASE WHEN l IS NULL
                THEN 'MISSING'
                ELSE CASE WHEN DATEDIFF(day, l, GETDATE()) > 1
                    THEN 'MISSING'
                    ELSE 'OK'
                END
            END
            ELSE ''
        END
        ELSE 'OFFLINE'
    END AS [Problem LOG File]
FROM ( SELECT    db.name ,
db.state_desc ,
db.recovery_model_desc ,
db.compatibility_level ,
type ,
backup_finish_date
FROM      master.sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
WHERE db.name NOT LIKE 'tempdb'
) AS Sourcetable
PIVOT
( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup
ORDER BY 'Last Full Backup' DESC

-- List DB File Sizes:

IF OBJECT_ID('tempdb.dbo.#DBFileSizeTmp') IS NOT NULL
    DROP TABLE #DBFileSizeTmp

CREATE TABLE #DBFileSizeTmp (
    Db [varchar](200) NOT NULL
    , database_id INT NOT NULL
    , recovery_model [varchar](20) NULL
    , name [varchar](200) NOT NULL
    , filetype [varchar](50) NOT NULL
    , state [varchar](20) NOT NULL
    , filename [varchar](200) NOT NULL
    , fileid INT NOT NULL
    , MAX_SIZE [varchar](200) NOT NULL
    , GROWTH_VALUE [varchar](200) NOT NULL
    , size DECIMAL(18,2) NOT NULL
    , used DECIMAL(18,2) NOT NULL
    , free DECIMAL(18,2) NOT NULL
    , perc_Free DECIMAL(18,2) NOT NULL
);

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #DBFileSizeTmp (Db, database_id, name, filetype, state, filename, fileid, MAX_SIZE, GROWTH_VALUE, size, used, free, perc_Free)
    select DB_NAME() AS [Database]
        , DB_ID()
        , left(ssf.NAME,len(ssf.NAME))
        , ssf.type_desc
        , ssf.state_desc
        , left(ssf.physical_name,len(ssf.physical_name))
        , ssf.file_id
        , CASE ssf.max_size WHEN -1 THEN ''UNLIMITED'' ELSE CONVERT(varchar(10), ssf.max_size) END AS MAX_SIZE
        , CASE ssf.is_percent_growth WHEN 1 THEN CONVERT(varchar(10), ssf.growth) + '' %'' ELSE CONVERT(varchar(10), round((ssf.growth * 8 /1024),2)) + '' MB'' END AS GROWTH_VALUE
        , convert(decimal(12,2),round(ssf.size/128.000,2))
        , convert(decimal(12,2),round(fileproperty(ssf.name,''SpaceUsed'')/128.000,2))
        , convert(decimal(12,2),round((ssf.size-fileproperty(ssf.name,''SpaceUsed''))/128.000,2))
        , convert(decimal(12,2),round((ssf.size-fileproperty(ssf.name,''SpaceUsed''))/128.000 / (ssf.size/128.000),2)) * 100
    from sys.database_files ssf;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

;WITH tUpdate AS
(
    SELECT d.recovery_model_desc
        , tmp.*
    FROM #DBFileSizeTmp tmp, sys.databases d
    WHERE tmp.database_id = d.database_id
)
UPDATE #DBFileSizeTmp
SET recovery_model = tUpdate.recovery_model_desc
FROM tUpdate
WHERE #DBFileSizeTmp.database_id = tUpdate.database_id

SELECT
    tmpFinal.Db AS DB_NAME
    , tmpFinal.recovery_model
    , tmpFinal.total_size_mb AS DBSize_MB
    , tmpFinal.name AS Logical_FILE_Name
    , tmpFinal.filetype
    , tmpFinal.state
    , tmpFinal.filename
    , tmpFinal.fileid
    , tmpFinal.MAX_SIZE
    , tmpFinal.GROWTH_VALUE
    , tmpFinal.size AS File_Size
    , tmpFinal.used AS Space_Used
    , tmpFinal.free AS FreeSpace
    , tmpFinal.perc_Free AS [%_Free]
    , volume_mount_point [Disk Mount Point]
    , file_system_type [File System Type]
    , logical_volume_name as [Logical Drive Name]
    , CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB] ---1GB = 1073741824 bytes
    , CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB]
    , CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM
(
    SELECT tmp.*
        , DBSize.total_size_mb
    FROM #DBFileSizeTmp tmp,
    (
    SELECT
          database_name = DB_NAME(database_id)
        , database_id
        , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
        , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
        , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
    FROM sys.master_files WITH(NOWAIT)
    GROUP BY database_id) DBSize
    WHERE tmp.database_id = DBSize.database_id
) tmpFinal
CROSS APPLY sys.dm_os_volume_stats(tmpFinal.database_id, tmpFinal.fileid)

IF OBJECT_ID('tempdb.dbo.#DBFileSizeTmp') IS NOT NULL
    DROP TABLE #DBFileSizeTmp

/*
EXEC sp_MSforeachdb
    N'
    USE [?]
    select
    DB_NAME() AS [Database],
    NAME = left(ssf.NAME,len(ssf.NAME)),
    FILE_TYPE = ssf.type_desc,
    STATE = ssf.state_desc,
    FILENAME = left(ssf.physical_name,len(ssf.physical_name)),
    ssf.file_id,
    [FILE_SIZE_MB]  = convert(decimal(12,2),round(ssf.size/128.000,2)),
    [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(ssf.name,''SpaceUsed'')/128.000,2)),
    [FREE_SPACE_MB] = convert(decimal(12,2),round((ssf.size-fileproperty(ssf.name,''SpaceUsed''))/128.000,2)),
    [FREE_PERC] = convert(decimal(12,2),round((ssf.size-fileproperty(ssf.name,''SpaceUsed''))/128.000 / (ssf.size/128.000),2))
from sys.database_files ssf';
*/

-- List DISK SPACE INFO:

SELECT DISTINCT
        volume_mount_point [Disk Mount Point],
        file_system_type [File System Type],
        logical_volume_name as [Logical Drive Name],
        CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
        CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],
        CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
ORDER BY volume_mount_point DESC

-- Get DB and LOG Size:

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select name
    , suser_sname( owner_sid ) owner
    , convert(decimal(12,2),(select sum(size) from fs where type = 0 and fs.database_id = db.database_id)) DataFileSizeMB
    , convert(decimal(12,2),(select sum(size) from fs where type = 1 and fs.database_id = db.database_id)) LogFileSizeMB
from sys.databases db

-- List Possible New Indexes for All DBs:

IF OBJECT_ID('tempdb.dbo.#DBTableSugestedIndexesTmp') IS NOT NULL
    DROP TABLE #DBTableSugestedIndexesTmp

CREATE TABLE #DBTableSugestedIndexesTmp (
    DbName [varchar](200) NOT NULL
    , index_advantage DECIMAL(18,2) NOT NULL
    , last_user_seek DATETIME NULL
    , table_full_name [varchar](200) NOT NULL
    , equality_columns [varchar](4000) NULL
    , inequality_columns [varchar](4000) NULL
    , included_columns [varchar](4000) NULL
    , unique_compiles INT NOT NULL
    , user_seeks INT NULL
    , avg_total_user_cost DECIMAL(18,2) NOT NULL
    , avg_user_impact DECIMAL(18,2) NOT NULL
    , table_Name [varchar](200) NOT NULL
    , table_Rows INT NOT NULL
);

EXEC sp_MSforeachdb
N'
USE [?]
INSERT INTO #DBTableSugestedIndexesTmp ( index_advantage, DbName, last_user_seek, table_full_name, equality_columns, inequality_columns, included_columns, unique_compiles, user_seeks, avg_total_user_cost, avg_user_impact, table_Name, table_Rows)
SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]
    , DB_NAME() [DB_NAME]
    , migs.last_user_seek
    , mid.[statement] AS [Database.Schema.Table]
    , mid.equality_columns
    , mid.inequality_columns
    , mid.included_columns
    , migs.unique_compiles
    , migs.user_seeks
    , migs.avg_total_user_cost
    , migs.avg_user_impact
    , OBJECT_NAME(mid.[object_id]) AS [Table Name]
    , p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID() AND p.index_id < 2
OPTION (RECOMPILE);
';

SELECT * FROM #DBTableSugestedIndexesTmp
ORDER BY index_advantage DESC

IF OBJECT_ID('tempdb.dbo.#DBTableSugestedIndexesTmp') IS NOT NULL
    DROP TABLE #DBTableSugestedIndexesTmp

-- List Bad NC Indexes for all DBs:

IF OBJECT_ID('tempdb.dbo.#DBTableBadNCsTmp') IS NOT NULL
    DROP TABLE #DBTableBadNCsTmp

CREATE TABLE #DBTableBadNCsTmp (
    DbName [varchar](200) NOT NULL
    , table_Name [varchar](200) NOT NULL
    , Index_Name [varchar](200) NOT NULL
    , type_desc [varchar](200) NOT NULL
    , create_date DATETIME NULL
    , index_id INT NOT NULL
    , is_disabled INT NOT NULL
    , Total_Writes INT NOT NULL
    , Total_Reads INT NOT NULL
    , Difference INT NOT NULL
);


EXEC sp_MSforeachdb
N'
USE [?]
INSERT INTO #DBTableBadNCsTmp ( DbName, table_Name, Index_Name, type_desc, create_date, index_id, is_disabled, Total_Writes, Total_Reads, Difference)
SELECT DB_NAME() AS DbName
    , OBJECT_NAME(s.[object_id]) AS [Table_Name]
    , i.name AS [Index_Name]
    , o.[type_desc]
    , o.create_date
    , i.index_id
    , i.is_disabled
    , user_updates AS [Total_Writes]
    , user_seeks + user_scans + user_lookups AS [Total_Reads]
    , user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON i.[object_id] = o.[object_id]
WHERE OBJECTPROPERTY(s.[object_id],''IsUserTable'') = 1 AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups) AND i.index_id > 1
OPTION (RECOMPILE);
';

SELECT * FROM #DBTableBadNCsTmp
ORDER BY [Difference] DESC, [Total_Writes] DESC, [Total_Reads] ASC

IF OBJECT_ID('tempdb.dbo.#DBTableBadNCsTmp') IS NOT NULL
    DROP TABLE #DBTableBadNCsTmp

-- List Fragmentation data for indexes in All DBs: (Pesada - Aten��o na sua execu��o)--> NAO EXECUTAR

IF OBJECT_ID('tempdb.dbo.#DBTableFragmentedIndexesTmp') IS NOT NULL
    DROP TABLE #DBTableFragmentedIndexesTmp

CREATE TABLE #DBTableFragmentedIndexesTmp (
    DbName [varchar](200) NOT NULL
    , Object_Name [varchar](200) NOT NULL
    , Index_Name [varchar](200) NULL
    , index_id INT NOT NULL
    , index_type_desc [varchar](200) NOT NULL
    , avg_fragmentation_in_percent DECIMAL(18,2) NOT NULL
    , fragment_count INT NULL
    , page_count INT NULL
    , fill_factor INT NULL
);

EXEC sp_MSforeachdb
N'
USE [?]
INSERT INTO #DBTableFragmentedIndexesTmp ( DbName, Object_Name, Index_Name, index_id, index_type_desc, avg_fragmentation_in_percent, fragment_count, page_count, fill_factor)
SELECT DB_NAME(ps.database_id) AS [DbName]
    , OBJECT_NAME(ps.OBJECT_ID) AS [Object_Name]
    , i.name AS [Index_Name]
    , ps.index_id
    , ps.index_type_desc
    , ps.avg_fragmentation_in_percent
    , ps.fragment_count
    , ps.page_count
    , i.fill_factor
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N''LIMITED'') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID() AND ps.page_count > 2500
OPTION (RECOMPILE);
';

SELECT * FROM #DBTableFragmentedIndexesTmp
ORDER BY avg_fragmentation_in_percent DESC

IF OBJECT_ID('tempdb.dbo.#DBTableFragmentedIndexesTmp') IS NOT NULL
    DROP TABLE #DBTableFragmentedIndexesTmp

-- Index Stats Last Update For All DBs:

IF OBJECT_ID('tempdb.dbo.#DBTableStatsLastUpdateTmp') IS NOT NULL
    DROP TABLE #DBTableStatsLastUpdateTmp

CREATE TABLE #DBTableStatsLastUpdateTmp (
    DbName [varchar](200) NOT NULL
    , Object_Name [varchar](200) NOT NULL
    , Object_Type [varchar](200) NULL
    , Index_Name [varchar](200) NULL
    , StatisticsDate DATETIME NULL
    , auto_created INT NOT NULL
    , no_recompute INT NOT NULL
    , user_created INT NOT NULL
    , row_count INT NOT NULL
    , used_page_count INT NOT NULL
);

EXEC sp_MSforeachdb
N'
USE [?]
INSERT INTO #DBTableStatsLastUpdateTmp ( DbName, Object_Name, Object_Type, Index_Name, StatisticsDate, auto_created, no_recompute, user_created, row_count, used_page_count)
SELECT DB_NAME() AS DbName
    , SCHEMA_NAME(o.Schema_ID) + N''.'' + o.NAME AS [Object_Name]
    , o.type_desc AS [Object_Type]
    , i.name AS [Index_Name]
    , STATS_DATE(i.[object_id], i.index_id) AS [StatisticsDate]
    , s.auto_created
    , s.no_recompute
    , s.user_created
    , st.row_count
    , st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id]
WHERE o.[type] IN (''U'', ''V'') AND st.row_count > 0
OPTION (RECOMPILE);
';

SELECT * FROM #DBTableStatsLastUpdateTmp
ORDER BY StatisticsDate DESC

IF OBJECT_ID('tempdb.dbo.#DBTableStatsLastUpdateTmp') IS NOT NULL
    DROP TABLE #DBTableStatsLastUpdateTmp

-- CPU By Database.

WITH DB_CPU_Stats
AS
(
    SELECT pa.DatabaseID
        , DB_Name(pa.DatabaseID) AS [Database Name]
        , SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
    FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
    CROSS APPLY (
        SELECT CONVERT(int, value) AS [DatabaseID]
        FROM sys.dm_exec_plan_attributes(qs.plan_handle)
        WHERE attribute = N'dbid'
    ) AS pa
    GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank]
    , [Database Name]
    , [CPU_Time_Ms] AS [CPU Time (ms)]
    , CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

-- IO By Database.

WITH Aggregate_IO_Statistics AS
(
    SELECT DB_NAME(database_id) AS [Database Name]
    , CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
    GROUP BY database_id
)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [Database Name]
    , io_in_mb AS [Total I/O (MB)]
    , CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);

-- MEMORY By Database.

WITH AggregateBufferPoolUsage AS
(
    SELECT DB_NAME(database_id) AS [Database Name]
    , CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [CachedSize]
    FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
    WHERE database_id <> 32767 -- ResourceDB
    GROUP BY DB_NAME(database_id)
)
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank]
    , [Database Name]
    , CachedSize AS [Cached Size (MB)]
    , CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);

-- Top CPU Consumers:

SELECT TOP (20) qs.execution_count
    , qs.total_rows
    , qs.total_logical_reads
    , qs.total_physical_reads
    , qs.total_elapsed_time
    , qs.total_worker_time
    , qs.creation_time
    , qs.last_execution_time
    , LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
        ELSE qs.statement_end_offset + 2 END - qs.statement_start_offset)/2), CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '),' ','~^'),'^~',''),'~^',' '), 32700) AS query_text
    , qs.last_rows
    , qs.min_rows
    , qs.max_rows
    , qs.last_logical_reads
    , qs.min_logical_reads
    , qs.max_logical_reads
    , qs.last_physical_reads
    , qs.min_physical_reads
    , qs.max_physical_reads
    , qs.last_elapsed_time
    , qs.min_elapsed_time
    , qs.max_elapsed_time
    , qs.last_worker_time
    , qs.min_worker_time
    , qs.max_worker_time
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

-- TOP IO:

SELECT  DB_NAME(vfs.database_id) AS database_name ,
        vfs.database_id "DB_ID",
        vfs.FILE_ID ,
        REPLACE(mf.type_desc, 'ROWS','DATA') File_Type,
        vfs.io_stall_read_ms "io_stall_R_ms",
        vfs.num_of_reads "N_Reads",
        vfs.io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_R_latency ,
        vfs.io_stall_write_ms "io_stall_W_ms",
        vfs.num_of_writes "N_Writes",
        vfs.io_stall_write_ms / NULLIF(num_of_writes, 0)
                                               AS avg_W_latency ,
        vfs.io_stall "io_stall_ms",
        vfs.io_stall / NULLIF(num_of_reads + num_of_writes, 0)
                                               AS avg_T_latency ,
        vfs.num_of_bytes_read "Num_Bytes_R",
        vfs.num_of_bytes_read / NULLIF(num_of_reads, 0)
                                               AS avg_By_per_R ,
        vfs.num_of_bytes_written "Num_Bytes_W",
        vfs.num_of_bytes_written / NULLIF(num_of_writes, 0)
                                               AS avg_By_per_W ,
        size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
        physical_name
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
        JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
                                       AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_T_latency DESC

-- Top Waits:

WITH [Waits]
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
          (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
           signal_wait_time_ms / 1000.0 AS [SignalS],
           waiting_tasks_count AS [WaitCount],
           100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
           ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
        N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
        N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',
        N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',
        N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
        N'PARALLEL_REDO_WORKER_WAIT_WORK',
        N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
        N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
        N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
        N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
        N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
        N'PREEMPTIVE_OS_WRITEFILE',
        N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
        N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
        N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
        N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
        N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
        N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
        N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
    AND waiting_tasks_count > 0)
SELECT
    MAX (W1.wait_type) AS [WaitType],
    CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
    CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
    CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
    MAX (W1.WaitCount) AS [Wait Count],
    CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
    CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
    CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
    CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);

-- List Jobs:

select j.name as 'JobName'
    , run_date
    , run_time
    , CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        ELSE 'INVALID' END as run_status
    , CONVERT(varchar(50), msdb.dbo.agent_datetime(run_date, run_time)) AS 'RunDateTime'
    , run_duration
    , ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) as 'RunDurationMinutes'
From msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 AND h.step_id = 0 -- Only Enabled Jobs and the Outcome Step
AND msdb.dbo.agent_datetime(run_date, run_time) >= DATEADD(day,-7, GETDATE())
order by JobName, RunDateTime desc

-- List Replication Status:-->EXECUTAR APEBAS NA INSTANCIA: outrasaplicacoes

SELECT
    (CASE
        WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
        WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
        WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
        WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
        WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
        WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
        ELSE CAST(mdh.runstatus AS VARCHAR)
    END) [Run Status]
    , mda.subscriber_db [Subscriber DB]
    , mda.publication [PUB Name]
    , RIGHT(LEFT(mda.name, LEN(mda.name)-(len(mda.id)+1)), LEN(LEFT(mda.name, LEN(mda.name)-(len(mda.id)+1))) - CHARINDEX('-',LEFT(mda.name, LEN(mda.name)-(len(mda.id)+1)),CHARINDEX('-',LEFT(mda.name, LEN(mda.name)-(len(mda.id)+1)), (CHARINDEX('-',LEFT(mda.name, LEN(mda.name)-(len(mda.id)+1)))+1))+1)) [SUBSCRIBER]
    , CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized]
    , und.UndelivCmdsInDistDB [UndistCom]
    , mdh.comments [Comments]
    , 'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info]
    , mdh.xact_seqno [SEQ_NO]
    , (CASE
        WHEN mda.subscription_type =  '0' THEN 'Push'
        WHEN mda.subscription_type =  '1' THEN 'Pull'
        WHEN mda.subscription_type =  '2' THEN 'Anonymous'
        ELSE CAST(mda.subscription_type AS VARCHAR)
    END) [SUB Type]
    , mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB]
    , mda.name [Pub - DB - Publication - SUB - AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN (
    SELECT s.agent_id
        , MaxAgentValue.[time]
        , SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK)
    ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN (
        SELECT hist.agent_id
            , MAX(hist.[time]) AS [time]
            , h.maxseq
        FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
        JOIN (
            SELECT agent_id
                ,ISNULL(MAX(xact_seqno),0x0) AS maxseq
            FROM distribution.dbo.MSdistribution_history (NOLOCK)
            GROUP BY agent_id) AS h
            ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
            GROUP BY hist.agent_id, h.maxseq
        ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
--and mdh.runstatus='6' --Fail
--and mdh.runstatus<>'2' --Succeed
order by mdh.[time]

-- List PLE Evolution (M3Database):

SELECT page_life_expectancy
    , start_date
FROM M3Database.[rsc].[server_memory_status] as mem, M3Database.[dbo].[session] as s
WHERE s.session_id = mem.session_id
AND s.start_date > '2019-06-26'
ORDER BY s.start_date ASC


-- List CPU Usage Evolution:

SELECT AVG(sql_process_utilization) SQLByHour
    , AVG(sql_process_utilization + other_process_utilization) ALLByHour
    , DATEADD(HH,DATEPART(HH,start_date),CAST(CAST(start_date AS DATE) AS DATETIME)) date_H
FROM M3Database.[rsc].server_cpu_status as scs, M3Database.[dbo].[session] as s
WHERE s.session_id = scs.session_id
AND s.start_date > '2019-06-26'
GROUP BY DATEADD(HH,DATEPART(HH,start_date),CAST(CAST(start_date AS DATE) AS DATETIME))
ORDER BY DATEADD(HH,DATEPART(HH,start_date),CAST(CAST(start_date AS DATE) AS DATETIME)) ASC


-- List regrasComposicaoBreakRentabilidade Evolution:(EXECUTAR APENAS NA INSTANCUA DO GMEDIA)

select DATEDIFF(DAY, gesvisProcDataIni, gesvisProcDataFim) Days_Proc, DATEDIFF(SECOND, gesvisDatahora, gesvisDatahoraConclusao) Execution_Time, *
from
GMediaAD.dbo.gesVisProcessamento
where gesVisProcSQLCommand like '%regrascomposicaobreakrentabilidade%'
AND gesvisDatahora > '2019-06-26'
ORDER BY gesvisDatahora DESC
--ORDER BY DATEDIFF(SECOND, gesvisDatahora, gesvisDatahoraConclusao) DESC

-- Deadlocks:(EXECUTAR APENAS NO GMEDIA)

SELECT CONVERT(date, Interval) Date_Day, Dbid, sum(numEvents) [Count]
FROM [M3Database].[dbo].[DeadLocksReport]
--WHERE dbid = 10
GROUP BY CONVERT(date, Interval), Dbid
ORDER BY Date_Day DESC

--APENAS PARA VALIDAR, s� colocar no excel se existir algum problema evidente
-- ALWAYS ON FailOver Events:

Declare @TEMP_ERROR_LOG Table (
    LogID int identity(1, 1) not null primary key,
    LogDate datetime null,
    ProcessInfo nvarchar(100) null,
    LogText nvarchar(4000) null
)

INSERT INTO @TEMP_ERROR_LOG (LogDate, ProcessInfo, LogText)
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, "desc"

SELECT *
FROM @TEMP_ERROR_LOG
WHERE LogText LIKE '%RESOLVING_NORMAL%' OR LogText LIKE '%PRIMARY_PENDING%'
ORDER BY LogID ASC

-- ERROR LOG Default Instance:

Declare @TEMP_ERROR_LOG Table (
    LogID int identity(1, 1) not null primary key,
    LogDate datetime null,
    ProcessInfo nvarchar(100) null,
    LogText nvarchar(4000) null
)

INSERT INTO @TEMP_ERROR_LOG (LogDate, ProcessInfo, LogText)
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, "desc"

SELECT *
FROM @TEMP_ERROR_LOG
WHERE LogText NOT LIKE 'Replication-Replication%'
AND LogText NOT LIKE 'Log was backed up%'
AND LogText NOT LIKE 'Database backed up%'
AND LogText NOT LIKE 'Database differential changes were backed up%'
AND LogDate > '2019-06-26'
ORDER BY LogID ASC

-- MEMORY DISTRIBUITION

--Great query for making the argument to use "Optimize for Ad Hoc Workloads":
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
       S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
       CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
       S.Total_MB_1_Use,   S.Total_MB,
       CAST( (S.Total_MB_1_Use   * 1.0 / S.Total_MB  ) as Decimal(18,2) )[Pct_MB_1_Use]
  FROM
  (
    SELECT CP.objtype[CacheType],
           COUNT(*)[Total_Plan],
           SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
           SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
           SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
           CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
           CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
                      / 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
           CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
           CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
                         ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
      FROM sys.dm_exec_cached_plans as CP
     GROUP BY CP.objtype
  ) AS S
 ORDER BY S.CacheType

-- ALWAYS ON FailOver Events:

SELECT SERVERPROPERTY('ServerName') AS [ServerName], F.object_name, event_data, timestamp_utc
FROM sys.fn_xe_file_target_read_file('AlwaysOn_health*.xel', null, null, null) AS F
WHERE object_name LIKE 'alwayson_ddl_executed' OR object_name LIKE 'availability_replica_state_change'
ORDER BY timestamp_utc DESC

-- ALWAYS ON FailOver Events:

DECLARE @FileName NVARCHAR(4000)
SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)')
FROM (
        SELECT CAST(target_data AS XML) target_data FROM sys.dm_xe_sessions s JOIN sys.dm_xe_session_targets t
        ON s.address = t.event_session_address WHERE s.name = N'AlwaysOn_health'
    ) ft

SELECT SERVERPROPERTY('ServerName') AS [ServerName],
    XEData.value('(event/@timestamp)[1]','datetime2(3)') AS event_timestamp,
    XEData.value('(event/data[@name="availability_replica_name"]/value)[1]', 'varchar(255)') AS availability_replica_name,
    XEData.value('(event/data[@name="statement"]/value)[1]', 'varchar(255)') AS statement,
    XEData.value('(event/data[@name="client_app_name"]/value)[1]', 'varchar(255)') AS client_app_name,
    XEData.value('(event/data[@name="nt_username"]/value)[1]', 'varchar(255)') AS nt_username,
    XEData.value('(event/data[@name="client_hostname"]/value)[1]', 'varchar(255)') AS client_hostname,
    XEData.value('(event/data[@name="previous_state"]/text)[1]', 'varchar(255)') AS previous_state,
    XEData.value('(event/data[@name="current_state"]/text)[1]', 'varchar(255)') AS current_state

FROM (
        SELECT CAST(event_data AS XML) XEData, *
        FROM sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL)
        WHERE object_name = 'alwayson_ddl_executed' OR object_name = 'availability_replica_state_change'
     ) event_data
ORDER BY event_timestamp DESC;