Microsoft.SQLServer.Windows.Monitoring SQL Queries (v7.0.38.0)

Tyson Paul0 commentsSCOM 2016



Now that the SQL management packs rely heavily on DLLs it’s not easy to determine what the workflows are doing or how they do it. These are the queries used for many of the workflows within this management pack.

--ServicePackLevel
SELECT SERVERPROPERTY('ProductVersion') as [ProductVersion]



--HashIndexEmptyBucketsCount
DECLARE @dbName sysname, @sql nvarchar(2000);

IF OBJECT_ID ('tempdb.dbo.#HekatonTablesHashIndEmptyBucketResultTable') IS NOT NULL DROP TABLE #HekatonTablesHashIndEmptyBucketResultTable;
CREATE TABLE #HekatonTablesHashIndEmptyBucketResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[TableName] SYSNAME
	,[IndexName] SYSNAME
	,[EmptyBucketPercent] INT
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT
		d.[name]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb') 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb')
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	IF EXISTS(SELECT 1 FROM sys.tables WHERE is_memory_optimized = 1)
	BEGIN
		with bucket_cte as (
		select 
			object_name(hs.object_id) as [_object_name], 
			i.name as [index_name], 
			hs.total_bucket_count,
			hs.empty_bucket_count,
			floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) as [empty_bucket_percent],
			hs.avg_chain_length, 
			hs.max_chain_length
		from sys.dm_db_xtp_hash_index_stats as hs 
			join sys.indexes as i 
			on hs.object_id=i.object_id and hs.index_id=i.index_id
		)
		INSERT INTO #HekatonTablesHashIndEmptyBucketResultTable
		select top(@p1) 
			DB_ID() AS [DatabaseId],
			DB_NAME() AS [DatabaseName],
			[_object_name] as [TableName],
			[index_name] as [IndexName],
			[empty_bucket_percent] as [EmptyBucketPercent]
		from bucket_cte   
		where empty_bucket_percent <= @p2
		order by empty_bucket_percent asc;
	END;';

	BEGIN TRY
		EXEC sp_executesql @sql, N'@p1 INT, @p2 INT', @p1 = @errorTopLines, @p2 = @emptyBucketPercentThreshold
	END TRY
	BEGIN CATCH
	END CATCH;
	FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #HekatonTablesHashIndEmptyBucketResultTable

DROP TABLE #HekatonTablesHashIndEmptyBucketResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--ReadNumErrorLogs
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'




--DBLogBackupStatus
SELECT
    d.name AS [DatabaseName],
    CASE
		WHEN d.recovery_model != 3 THEN DATEDIFF(minute, COALESCE(MAX(b.backup_finish_date), d.create_date), GETDATE()) 
		ELSE -1
	END AS [MinutesSinceBackup]
FROM sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE
    d.is_in_standby = 0
    AND source_database_id is null
    AND d.name <> 'tempdb'
    AND (b.[type] = 'L' OR b.[type] IS NULL)
	AND d.replica_id IS NULL
GROUP BY
    d.name, d.create_date, d.recovery_model



--GetFilegroupSpaceData
DECLARE @dbName sysname, @sqlFiles nvarchar(2000), @sqlGroups nvarchar(2000), @needsMaster BIT;

IF OBJECT_ID ('tempdb.dbo.#FileGroupsTable') IS NOT NULL DROP TABLE #FileGroupsTable;
CREATE TABLE #FileGroupsTable (
	GroupKey NVARCHAR(22)
	,[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,FilegroupName SYSNAME
	,FilegroupType NVARCHAR(2)
);

IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;
CREATE TABLE #ResultTable (
	GroupKey NVARCHAR(22)
	,[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[FileName] SYSNAME
	,FileSizeMB FLOAT(53)
	,FileUsedSpaceMB FLOAT(53)
	,FileMaxSizeMB FLOAT(53)
	,IsAutoGrowEnabled BIT
	,IsPercentGrowth BIT
	,FileGrowth INT
	,VolumeMountPoint NVARCHAR(256)
	,VolumeId NVARCHAR(256)
	,IsReadOnly BIT
	,DiskFreeSpaceMB FLOAT(53)
	,PhysicalName NVARCHAR(260)
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,CAST(CASE
			WHEN d.[replica_id] IS NOT NULL OR d.[is_in_standby] = 1 THEN 1
			ELSE 0
		END AS BIT) AS [needsMaster] 
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,CAST(CASE
			WHEN d.[replica_id] IS NOT NULL OR d.[is_in_standby] = 1 THEN 1
			ELSE 0
		END AS BIT) AS [needsMaster] 
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @needsMaster;

WHILE @@Fetch_Status=0 BEGIN
	SET @sqlFiles = N'USE '+ QUOTENAME(@dbName,'"') + N';
	INSERT INTO #ResultTable
	SELECT
		CAST(DB_ID() AS nvarchar(10)) + N''_'' + CAST(fg.data_space_id AS nvarchar(10)) AS [GroupKey]
		,DB_ID() AS [DatabaseId]
		,DB_NAME() AS [DatabaseName]
		,f.name AS [FileName]
		,CONVERT(FLOAT(53), f.size / 128.0) AS [FileSizeMB]
		,CONVERT(FLOAT(53), FILEPROPERTY(f.name, ''SpaceUsed'') / 128.0) as [FileUsedSpaceMB]
		,CONVERT(FLOAT(53), CASE 
			WHEN f.max_size = -1 OR f.max_size = 268435456 THEN -1 
			ELSE f.max_size / 128.0 
			END) as [FileMaxSizeMB]
		,CASE
			WHEN f.growth = 0 THEN 0
			ELSE 1
			END AS IsAutoGrowEnabled
		,f.is_percent_growth as [IsPercentGrowth]
		,f.growth AS [FileGrowth]
		,ovs.volume_mount_point AS [VolumeMountPoint]
		,ovs.volume_id AS [VolumeId]
		,f.is_read_only AS [IsReadOnly]';

	IF @needsMaster = 0
	BEGIN
		SET @sqlFiles = @sqlFiles + N'
		,CONVERT(FLOAT(53), CASE 
			WHEN CharIndex(N''https://'',LTRIM(f.physical_name)) = 1 OR CharIndex(N''http://'',LTRIM(f.physical_name)) = 1 THEN @azureMaxFileSizeMB - f.size / 128.0
			ELSE ovs.available_bytes / 1048576.0 
			END) AS [DiskFreeSpaceMB]
		,f.physical_name AS [PhysicalName]
	FROM sys.database_files AS f WITH (NOLOCK)';
	END;
	ELSE
	BEGIN
		SET @sqlFiles = @sqlFiles + N'
		,CONVERT(FLOAT(53), CASE 
			WHEN CharIndex(N''https://'',LTRIM(mf.physical_name)) = 1 OR CharIndex(N''http://'',LTRIM(mf.physical_name)) = 1 THEN @azureMaxFileSizeMB - f.size / 128.0
			ELSE ovs.available_bytes / 1048576.0 
			END) AS [DiskFreeSpaceMB]
		,mf.physical_name AS [PhysicalName]
	FROM sys.database_files AS f WITH (NOLOCK)
	JOIN sys.master_files AS mf WITH (NOLOCK) ON f.file_id = mf.file_id AND mf.database_id = DB_ID()';
	END;

	SET @sqlFiles = @sqlFiles + N'
	INNER JOIN sys.filegroups fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
	CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) ovs
	WHERE f.[type] IN (0,1,2) AND (fg.[type] = @filegroupType OR @filegroupType COLLATE DATABASE_DEFAULT = N''ALL'');';

	SET @sqlFiles = @sqlFiles + N'
	INSERT INTO #FileGroupsTable
	SELECT 
		CAST(DB_ID() AS nvarchar(10)) + N''_'' + CAST(fg.data_space_id AS nvarchar(10)) AS [GroupKey]
		,DB_ID() AS [DatabaseId]
		,DB_NAME() AS [DatabaseName]
		,fg.[name] AS [FilegroupName]
		,fg.[type] AS [FilegroupType]
	FROM sys.filegroups fg WITH (NOLOCK)
	WHERE (fg.[type] = @filegroupType OR @filegroupType COLLATE DATABASE_DEFAULT = N''ALL'');';

	BEGIN TRY
		BEGIN TRAN
			EXEC sp_executesql @sqlFiles, N'@filegroupType NVARCHAR(3), @azureMaxFileSizeMB INT', @filegroupType = @filegroupType, @azureMaxFileSizeMB = @azureMaxFileSizeMB;
		COMMIT
	END TRY
	BEGIN CATCH
	END CATCH;
	FETCH NEXT FROM dbCursor INTO @dbName, @needsMaster;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #FileGroupsTable;
SELECT * FROM #ResultTable;

DROP TABLE #FileGroupsTable;
DROP TABLE #ResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--DBActiveConnectionsCount
SELECT 
	DB_NAME(db.database_id) AS [DatabaseName],
	ISNULL(info.NConnections, 0) AS [NConnections]
FROM 
sys.databases AS db
LEFT JOIN
(
	SELECT database_id, COUNT(database_id) AS [NConnections]
	FROM sys.dm_exec_connections AS c  
	INNER JOIN sys.dm_exec_sessions AS s  
	ON c.session_id = s.session_id 
	WHERE database_id > 0
	GROUP BY database_id
) AS info
ON db.database_id = info.database_id



--DBBackupStatus
SELECT
    d.name AS [DatabaseName],
    DATEDIFF(Day, COALESCE(MAX(b.backup_finish_date), d.create_date), GETDATE()) AS [DaysSinceBackup]
FROM
    sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset b
            ON d.name = b.database_name
WHERE
    d.is_in_standby = 0
    AND source_database_id is null
    AND d.name <> 'tempdb'
    AND (b.[type] IN ('D', 'I') OR b.[type] IS NULL)
		AND (
		(@databaseReplica = 1 AND d.replica_id IS NOT NULL)
		OR 
		(@databaseReplica = 0 AND d.replica_id IS NULL)
		)
GROUP BY
    d.name, d.create_date



--GetMaxServerMemory
DECLARE @maxServerMemoryMB FLOAT(53);

SELECT TOP 1 
	@maxServerMemoryMB = CONVERT(FLOAT(53), ISNULL([value_in_use], 2147483647))
FROM sys.configurations 
WHERE name = 'max server memory (MB)'
OPTION (RECOMPILE);

SELECT
	CASE 
		WHEN @maxServerMemoryMB != 2147483647 AND @maxServerMemoryMB IS NOT NULL THEN @maxServerMemoryMB
		ELSE CONVERT(FLOAT(53), [physical_memory_kb] / 1024.0)
	END AS ServerMemoryMB
	,1 AS DatabaseId
FROM sys.dm_os_sys_info;



--GetDBStatuses
IF OBJECT_ID ('tempdb.dbo.#StatusTable') IS NOT NULL DROP TABLE #StatusTable;
CREATE TABLE #StatusTable (
	DatabaseId INT,
	DatabaseName sysname,
	MonitoringStatus nvarchar(20),
	DatabaseState nvarchar(60),
	IsAccessible bit,
	IsMirroringMirror bit,
	IsAlwaysOnReplica bit,
	ErrorCode int, 
	ErrorDescription nvarchar(4000),
	IsAgOnline BIT
);

IF OBJECT_ID ('tempdb.dbo.#MirroringData') IS NOT NULL DROP TABLE #MirroringData;
CREATE TABLE #MirroringData (
	database_id INT
	,mirroring_state TINYINT
);

IF OBJECT_ID ('tempdb.dbo.#AlwaysOnData') IS NOT NULL DROP TABLE #AlwaysOnData;
CREATE TABLE #AlwaysOnData (
	database_id INT
	,db_is_primary_replica BIT
	,role_allow_connections TINYINT
	,db_suspended_state INT
	,database_state TINYINT
	,database_state_desc nvarchar(60)
	,is_ag_online BIT
);

DECLARE @AlwaysOnQuery varchar(max);
	
IF SERVERPROPERTY('ProductMajorVersion') > 11
	SET @AlwaysOnQuery = '
		SELECT 
			d2.database_id
			,drs.is_primary_replica AS db_is_primary_replica
			,CASE 
				WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections
				ELSE ar.secondary_role_allow_connections
			END AS role_allow_connections
			,CASE 
				WHEN drs.is_suspended = 0 THEN -1
				ELSE suspend_reason
			END AS db_suspended_state
			,drs.database_state
			,drs.database_state_desc
			,CASE
				WHEN dhags.primary_replica IS NULL OR dhags.primary_replica = '''' THEN 0
				ELSE 1
			END AS is_ag_online
		FROM sys.databases AS d2
			JOIN sys.dm_hadr_database_replica_states drs WITH(NOLOCK) ON drs.database_id = d2.database_id
			JOIN sys.availability_replicas ar WITH(NOLOCK) ON d2.replica_id = ar.replica_id
			JOIN sys.dm_hadr_availability_group_states dhags WITH(NOLOCK) ON ar.group_id = dhags.group_id
		WHERE drs.is_local = 1;'
ELSE
	SET @AlwaysOnQuery = '
		SELECT 
			d2.database_id
			,CASE 
				WHEN hars.[role] = 1 THEN 1 
				ELSE 0 
			END AS db_is_primary_replica
			, CASE  
				WHEN hars.[role] = 1 THEN ar.primary_role_allow_connections 
				WHEN hars.[role] = 2 THEN ar.secondary_role_allow_connections 
				ELSE 0 
			END AS role_allow_connections 
			,CASE 
				WHEN drs.is_suspended = 0 THEN -1
				ELSE suspend_reason
			END AS db_suspended_state
			,drs.database_state
			,drs.database_state_desc
			,CASE
				WHEN dhags.primary_replica IS NULL OR dhags.primary_replica = '''' THEN 0
				ELSE 1
			END AS is_ag_online
		FROM sys.databases AS d2
			JOIN sys.dm_hadr_database_replica_states drs WITH(NOLOCK) ON drs.database_id = d2.database_id
			JOIN sys.availability_replicas ar WITH(NOLOCK) ON d2.replica_id = ar.replica_id
			JOIN sys.dm_hadr_availability_replica_states hars WITH(NOLOCK) ON d2.replica_id = hars.replica_id
			JOIN sys.servers s WITH(NOLOCK) ON s.name = ar.replica_server_name AND s.server_id = 0
			JOIN sys.dm_hadr_availability_group_states dhags WITH(NOLOCK) ON ar.group_id = dhags.group_id
		WHERE (drs.is_local = 1 OR (drs.is_local IS NULL AND hars.[role] != 1 AND hars.[role] != 2))'

INSERT INTO #AlwaysOnData
EXEC (@AlwaysOnQuery);

IF OBJECT_ID('sys.database_mirroring', 'V') IS NOT NULL AND @_isReadOnlyMode = 1
	INSERT INTO #MirroringData
	SELECT 
		database_id
		,mirroring_state
	FROM sys.database_mirroring
	WHERE mirroring_role = 2;

INSERT INTO #StatusTable 
SELECT 
	[database_id] AS [DatabaseId]
	,[name] AS [DatabaseName]
	,CASE 
		--db is online and ready to accept connections
		WHEN [state] = 0 AND db_hasaccess = 1
			THEN 'Good'
		--db has just come online and is not ready to accept connections
		WHEN [state] = 0 AND db_hasaccess = 0 AND dv.collation_name IS NULL
			THEN 'NeitherGoodNorBad'
		WHEN [state] = 0 AND db_hasaccess = 0 AND collation_name IS NOT NULL AND has_mirroring_mirror = 0 AND has_alwayson = 0 AND user_access = 1
			THEN 'Good'
		--user does not have access due to lack of permissions
		WHEN [state] = 0 AND db_hasaccess = 0 AND collation_name IS NOT NULL AND has_mirroring_mirror = 0 AND has_alwayson = 0 AND user_access != 1
			THEN 'NeedToCheck'
		WHEN dv.[state] = 2
			THEN 'NeitherGoodNorBad'
		WHEN [state] IN (3, 4, 5, 6)
			THEN 'Bad'
		WHEN dv.is_in_standby = 1 AND is_read_only = 0
			THEN 'Bad'
		--Mirroring
		WHEN dv.[state] = 1 AND has_mirroring_mirror = 1
			THEN 'Good'
		WHEN dv.[state] = 1 AND has_mirroring_mirror = 0
			THEN 'NeedToCheck'
		--AlwaysOn
		WHEN [state] = 0 AND db_hasaccess = 0 AND has_alwayson = 1 AND db_is_primary_replica = 1
			THEN 'Bad'
		WHEN [state] = 0 AND db_hasaccess = 0 AND has_alwayson = 1 AND db_is_primary_replica = 0 AND role_allow_connections > 0
			THEN 'Bad'
		WHEN [state] = 0 AND has_alwayson = 1 AND db_is_primary_replica = 0 AND role_allow_connections = 0
			THEN 'Good'
		ELSE 'NeedToCheck'
		END AS [MonitoringStatus]
	,dv.state_desc AS [DatabaseState]
	,db_hasaccess AS [IsAccessible]
	,CAST(has_mirroring_mirror AS BIT) AS [IsMirroringMirror]
	,CAST(has_alwayson AS BIT) AS [IsAlwaysOnReplica]
	,0 AS [ErrorCode]
	,'' AS [ErrorDescription]
	,is_ag_online AS [IsAgOnline]
FROM (
	SELECT 
		d.database_id
		,d.[name]
		,d.collation_name
		,d.is_in_standby
		,d.is_read_only
		,d.user_access
		,d.[state]
		,d.[state_desc]
		,CAST(HAS_DBACCESS(d.[name]) AS BIT) AS db_hasaccess
		,CASE 
			WHEN dm.mirroring_state IS NULL	THEN 0
			ELSE 1
		END AS has_mirroring_mirror
		,CASE 
			WHEN aond.database_id IS NULL THEN 0
			ELSE 1
		END AS has_alwayson
		--Mirroring fields
		,dm.mirroring_state
		--AlwaysOn fields
		,aond.db_is_primary_replica
		,aond.db_suspended_state
		,aond.role_allow_connections
		,aond.database_state
		,CASE
		  WHEN aond.is_ag_online IS NULL THEN 1
		  ELSE aond.is_ag_online
		END AS is_ag_online
	FROM sys.databases d
	LEFT OUTER JOIN #MirroringData dm ON dm.database_id = d.database_id
	LEFT OUTER JOIN #AlwaysOnData aond ON aond.database_id = d.database_id
	WHERE (@_isReadOnlyMode = 1 AND (aond.db_is_primary_replica IS NULL OR (aond.db_is_primary_replica != 1 AND aond.role_allow_connections != 3))) 
		OR (@_isReadOnlyMode = 0 AND aond.db_is_primary_replica = 1 AND aond.role_allow_connections = 3)
	) dv;

DECLARE @dbname sysname, @sql nvarchar(2000)

DECLARE temp CURSOR LOCAL FAST_FORWARD FOR 
SELECT DatabaseName FROM #StatusTable WHERE MonitoringStatus = 'NeedToCheck';

OPEN temp;

FETCH NEXT FROM temp INTO @dbname;
		
WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbname ,'"');
	BEGIN TRY
		EXEC (@sql);		
	END TRY
	BEGIN CATCH
		UPDATE #StatusTable SET MonitoringStatus = 'NeitherGoodNorBad', ErrorCode = @@ERROR, ErrorDescription = ERROR_MESSAGE() WHERE DatabaseName = @dbname;
	END CATCH;
	FETCH NEXT FROM temp INTO @dbname;
End

CLOSE temp;
DEALLOCATE temp;

SELECT * FROM #StatusTable

DROP TABLE #StatusTable;
DROP TABLE #MirroringData;

IF @_isReadOnlyMode = 1
BEGIN
	IF EXISTS(SELECT 1 FROM #AlwaysOnData WHERE db_is_primary_replica = 1 AND role_allow_connections = 3)
		SELECT 1 AS ReadWriteDbCount
	ELSE
		SELECT 0 AS ReadWriteDbCount
END;

DROP TABLE #AlwaysOnData;



--BlockingSessions
DECLARE @wait_threshold INT 
SET @wait_threshold = 1000 * 60 * @WAIT_MINUTES
DECLARE @servermajorversion INT 
SET @servermajorversion = REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')), 2), '.', '') 
 
IF OBJECT_ID ('tempdb.dbo.#tmp_blockers') IS NOT NULL DROP TABLE #tmp_blockers 
IF OBJECT_ID ('tempdb.dbo.#tmp_head_blockers') IS NOT NULL DROP TABLE #tmp_head_blockers 
IF OBJECT_ID ('tempdb.dbo.#tmp_head_blocker_depth') IS NOT NULL DROP TABLE #tmp_head_blocker_depth 
 
 
SELECT 
	S.session_id, 
	CASE  
		WHEN R.blocking_session_id IS NULL OR R.blocking_session_id = 0 THEN 'TRUE' 
		ELSE 'FALSE' 
	END AS head_blocker, 
	R.blocking_session_id, 
	R.status AS request_status, 
	S.status AS session_status,  
	CAST(LEFT(CASE R.sql_handle 
		WHEN NULL THEN 
			(SELECT text FROM sys.dm_exec_sql_text(R.sql_handle))  
		ELSE  
			(SELECT text FROM sys.dm_exec_sql_text(C.most_recent_sql_handle)) 
	END, 4000) AS NVARCHAR(4000)) AS sql_stmnt, 
	S.program_name, 
	S.host_name, 
	S.host_process_id, 
	S.is_user_process, 
	S.login_name, 
	S.login_time, 
	R.start_time AS request_start_time, 
	R.wait_type, 
	R.last_wait_type, 
	CONVERT(NUMERIC(9,3),(R.wait_time / 1000.0)) AS wait_time_in_sec,	 
	R.command, 
	R.wait_resource, 
	CASE COALESCE(R.transaction_isolation_level, S.transaction_isolation_level) 
		WHEN 0 THEN '0-Unspecified' 
		WHEN 1 THEN '1-ReadUncomitted' 
		WHEN 2 THEN '2-ReadCommitted' 
		WHEN 3 THEN '3-Repeatable' 
		WHEN 4 THEN '4-Serializable' 
		WHEN 5 THEN '5-Snapshot' 
		ELSE CONVERT(VARCHAR(10), COALESCE(R.transaction_isolation_level, S.transaction_isolation_level)) + '-Unknown'  
	END AS transaction_isolation_level, 
	--SQLBUD #487091 
	CASE  
		WHEN R.open_transaction_count IS NULL THEN (SELECT open_tran FROM sys.sysprocesses AS SP WHERE SP.spid = S.session_id) 
		ELSE R.open_transaction_count 
	END AS open_transaction_count, 
	R.open_resultset_count, 
	CONVERT (decimal(5,2), R.percent_complete) AS percent_complete,  
	R.estimated_completion_time, 
	--SQLBUD #438189 (fixed in SP2) 
    CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2' COLLATE Latin1_General_BIN)  
      THEN R.logical_reads ELSE R.logical_reads - S.logical_reads END AS request_logical_reads,  
    CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2' COLLATE Latin1_General_BIN)  
      THEN R.reads ELSE R.reads - S.reads END AS request_reads,  
    CASE WHEN (@servermajorversion > 9) OR (@servermajorversion = 9 AND SERVERPROPERTY ('ProductLevel') >= 'SP2' COLLATE Latin1_General_BIN) 
      THEN R.writes ELSE R.writes - S.writes END AS request_writes,  
	R.cpu_time AS request_cpu_time, 
	R.lock_timeout, 
	R.deadlock_priority, 
	R.row_count AS request_row_count, 
	R.prev_error AS request_prev_error, 
	R.nest_level, 
	R.granted_query_memory, 
	R.user_id, 
	R.transaction_id, 
	S.cpu_time AS session_cpu_time, 
	S.memory_usage, 
	S.reads AS session_reads, 
	S.logical_reads AS session_logical_reads, 
	S.writes AS session_writes, 
	S.prev_error AS session_prev_error, 
	S.row_count AS session_row_count 
INTO 
	#tmp_blockers 
FROM  
	(sys.dm_exec_sessions AS S  
	 LEFT OUTER JOIN sys.dm_exec_requests AS R ON R.session_id = S.session_id) 
		LEFT OUTER JOIN sys.dm_exec_connections AS C ON C.session_id = S. session_id 
WHERE  
	(	--Active Request 
		R.session_id IS NOT NULL AND  
		R.blocking_session_id != 0 AND  
		S.session_id != @@SPID AND 
		R.wait_time > @wait_threshold AND 
		(S.is_user_process = 1 OR R.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping'))) 
    OR --Head Blocker  
    (S.session_id IN 
		(SELECT S.session_id  
			FROM sys.dm_exec_sessions AS S 
              INNER JOIN sys.dm_exec_requests AS BER ON BER.blocking_session_id = S.session_id 
              LEFT OUTER JOIN sys.dm_exec_requests AS ER ON ER.session_id = S.session_id 
          WHERE 
              (ER.blocking_session_id = 0 OR ER.blocking_session_id IS NULL) 
              AND BER.wait_time > @wait_threshold)); 
     
--Find Blocking Levels 
;WITH blocking_levels(session_id, blocking_session_id, blocking_level, head_blocker) AS  
( 
    SELECT session_id, blocking_session_id, 0 AS blocking_level, session_id AS head_blocker 
    FROM #tmp_blockers 
    WHERE blocking_session_id IS NULL OR blocking_session_id = 0 
    UNION ALL 
    SELECT TB.session_id, TB.blocking_session_id, BL.blocking_level + 1 AS blocking_level, BL.head_blocker 
    FROM #tmp_blockers AS TB 
        INNER JOIN blocking_levels AS BL 
        ON TB.blocking_session_id = BL.session_id 
) 
SELECT *  
INTO #tmp_head_blockers 
FROM blocking_levels 
 
SELECT COUNT(*) - 1 AS head_blocking_depth, head_blocker 
INTO #tmp_head_blocker_depth 
FROM #tmp_head_blockers 
GROUP BY head_blocker 
 
-- This query could be collapsed into the query above.  It is broken out here to avoid an excessively  
-- large memory grant due to poor cardinality estimates (no stats on many DMVs).  
 
SELECT TOP 20	 
	TB.session_id, 
	TB.blocking_session_id, 
	THB.blocking_level, 
	TB.head_blocker, 
	THBD.head_blocking_depth, 
	TB.request_status, 
	TB.session_status, 
	TB.sql_stmnt, 
	TB.request_start_time, 
	TB.wait_type, 
	TB.last_wait_type, 
	TB.wait_time_in_sec, 
	TB.command, 
	TB.program_name, 
	TB.host_name, 
	TB.host_process_id, 
	TB.is_user_process, 
	TB.login_name, 
	TB.login_time, 
	TB.wait_resource, 
	TB.transaction_isolation_level, 
	TB.open_transaction_count, 
	TB.open_resultset_count, 
	COALESCE(AT.name, AT2.name) AS transaction_name, 
	COALESCE(AT.transaction_begin_time, AT2.transaction_begin_time) AS transaction_begin_time, 
	CASE COALESCE(AT.transaction_type, AT2.transaction_type) 
		WHEN 1 THEN '1-Read/write transaction' 
		WHEN 2 THEN '2-Read-only transaction' 
		WHEN 3 THEN '3-System transaction' 
		WHEN 4 THEN '4-Distributed transaction' 
		ELSE CONVERT(VARCHAR(10), COALESCE(AT.transaction_type, AT2.transaction_type)) + '-Unknown' 
	END AS transaction_type, 
	CASE COALESCE(AT.transaction_state, AT2.transaction_state) 
		WHEN 0 THEN '0-The transaction has not been completely initialized yet.' 
		WHEN 1 THEN '1-The transaction has been initialized but has not started.' 
		WHEN 2 THEN '2-The transaction is active.' 
		WHEN 3 THEN '3-The transaction has ended. This is used for read-only transactions.' 
		WHEN 4 THEN '4-The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.' 
		WHEN 5 THEN '5-The transaction is in a prepared state and waiting resolution.' 
		WHEN 6 THEN '6-The transaction has been committed.' 
		WHEN 7 THEN '7-The transaction is being rolled back.' 
		WHEN 8 THEN '8-The transaction has been rolled back.' 
		ELSE CONVERT(VARCHAR(10), COALESCE(AT.transaction_state, AT2.transaction_state)) + '-Unknown' 
	END AS transaction_state, 
	TB.percent_complete, 
	TB.estimated_completion_time, 
	TB.request_logical_reads, 
	TB.request_reads, 
	TB.request_writes, 
	TB.request_cpu_time, 
	TB.lock_timeout, 
	TB.deadlock_priority, 
	TB.request_row_count, 
	TB.request_prev_error, 
	TB.nest_level, 
	TB.granted_query_memory, 
	TB.user_id, 
	TB.transaction_id, 
	TB.session_cpu_time, 
	TB.memory_usage, 
	TB.session_reads, 
	TB.session_logical_reads, 
	TB.session_writes, 
	TB.session_prev_error, 
	TB.session_row_count 
FROM  
	#tmp_blockers AS TB 
		LEFT OUTER JOIN sys.dm_tran_active_transactions AS AT ON AT.transaction_id = TB.transaction_id 
			LEFT OUTER JOIN sys.dm_tran_session_transactions AS TS ON TS.session_id = TB.session_id 
				LEFT OUTER JOIN sys.dm_tran_active_transactions AS AT2 ON AT2.transaction_id = TS.transaction_id 
					LEFT OUTER JOIN #tmp_head_blockers AS THB ON THB.session_id = TB.session_id 
						LEFT OUTER JOIN #tmp_head_blocker_depth AS THBD ON THBD.head_blocker = TB.session_id 
 
ORDER BY TB.head_blocker DESC, THB.blocking_level



--GetSqlOsCounters
DECLARE @xmlDoc XML = cast(@perfCountersConfigXml AS XML)
DECLARE @instancesDoc XML = cast(@instancesConfigXml AS XML)

DECLARE @RequestedCounters TABLE(
	CategoryName nvarchar(2000),
	CounterName nvarchar(2000),
	BaseCounterName nvarchar(2000),
	InstanceSelector tinyint,
	InstanceIndex tinyint,
	InstanceName nvarchar(128)
);

INSERT INTO @RequestedCounters
SELECT
	CASE NeedsPrefix
		WHEN 1 THEN @perfCounterObject + N':' + CategoryName
		ELSE CategoryName
	END AS CategoryName,
	CounterName,
	BaseCounterName,
	InstanceSelector,
	InstanceIndex,
	InstanceName
FROM
(
	SELECT 
		xc.value('./cat[1]', 'nvarchar(2000)') AS CategoryName,
		xc.value('./cnt[1]', 'nvarchar(2000)') AS CounterName,
		xc.value('./bs[1]', 'nvarchar(2000)') AS BaseCounterName,
		xc.value('./sel[1]', 'tinyint') AS InstanceSelector,
		xc.value('./np[1]', 'bit') AS NeedsPrefix,
		xc.value('./ii[1]', 'tinyint') AS InstanceIndex,
		null AS InstanceName
	FROM @xmlDoc.nodes('/*/counter') AS xt(xc)
) cc;

UPDATE @RequestedCounters
SET InstanceName = tbl.InstanceName
FROM 
(
    SELECT 
		xc.value('./in[1]', 'nvarchar(128)') AS InstanceName,		
		xc.value('./ii[1]', 'tinyint') AS InstanceId
	FROM
		@instancesDoc.nodes('/*/instance') AS xt(xc)
) tbl
WHERE 
    tbl.InstanceId = InstanceIndex

-- Get database list with Hekaton filegroup FX
DECLARE fileCursor CURSOR LOCAL FOR 
SELECT [name] FROM sys.databases WHERE HAS_DBACCESS([name]) = 1;

DECLARE @dbName sysname, @sql nvarchar(2000);

IF OBJECT_ID('tempdb..#HekatonDBs') IS NOT NULL DROP TABLE #HekatonDBs
CREATE TABLE #HekatonDBs(
	DBName sysname
)

OPEN fileCursor;
FETCH NEXT FROM fileCursor INTO @dbName;

WHILE @@Fetch_Status=0 
BEGIN
	
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	INSERT #HekatonDBs SELECT db_name() FROM sys.filegroups fg WHERE fg.[type] = @FilegroupType';

	EXEC sp_executesql @sql, N'@FilegroupType nvarchar(2)', @FilegroupType = N'FX'

	FETCH NEXT FROM fileCursor INTO @dbName;
END;

CLOSE fileCursor;
DEALLOCATE fileCursor;
--

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

DECLARE @minctr DATETIME, @maxctr DATETIME
SELECT @minctr = GETDATE()

-- When counter type = 272696576, 272696320 (find delta from two collection points)
-- When counter type = 65792 (face value)
-- When counter type = 1073939712 (base value, check next counter types)
-- When counter type = 1073874176 (find delta from current value and base value between two collection points). Base value is counter with type 1073939712.
-- When counter type = 537003264 (find delta from current value and base value). Base value is counter with type 1073939712.

SELECT	
	pc1.[object_name]
	,pc1.[counter_name]
	,pc1.[instance_name]
	,pc1.[cntr_value]
	,pc1.[cntr_type]
	,pc2.[cntr_value] AS [base_cntr_value]
INTO #PerfCounters
FROM 
	sys.dm_os_performance_counters pc1 (NOLOCK)	
	INNER JOIN @RequestedCounters rc 
		ON pc1.[object_name] = rc.CategoryName AND pc1.[counter_name] = rc.CounterName
	LEFT JOIN sys.dm_os_performance_counters pc2 (NOLOCK) 
		ON pc2.[cntr_type] = 1073939712 AND UPPER(pc2.[counter_name]) = UPPER(rc.BaseCounterName) AND pc1.[object_name] = pc2.[object_name] AND pc1.[instance_name] = pc2.[instance_name]
WHERE 	
	(pc1.[cntr_type] = 272696576 OR pc1.[cntr_type] = 1073874176 -- Get only counters whose delta between collection points matters                        
	OR pc1.[cntr_type] = 272696320)
	AND
	(
		(rc.InstanceSelector = 0) 
		OR (rc.InstanceSelector = 1 AND pc1.[instance_name] = N'_Total') 
		OR (rc.InstanceSelector = 2 AND pc1.[instance_name] = N'')
		OR (rc.InstanceSelector = 3 AND pc1.[instance_name] = rc.InstanceName)
		OR (rc.InstanceSelector = 4 AND pc1.[instance_name] IN (SELECT DBName FROM #HekatonDBs))		
	);


WAITFOR DELAY @delayStr;


SELECT @maxctr = GETDATE()

declare @difftime int
set @difftime = DATEDIFF(ss,@minctr,@maxctr)

if (@difftime=0)
begin
print '@difftime=0'
return
end


SELECT
	[ObjectName]
	,[CounterName]
	,[PerfInstanceName]
	,[CounterValue]
FROM (
	SELECT 
		rc.CategoryName [ObjectName]
		,rc.CounterName AS [CounterName]
		,rtrim(pc1.[instance_name]) AS [PerfInstanceName]
		,CASE 

			WHEN pc1.[cntr_type] = 65792 THEN 
				CONVERT(float,pc1.cntr_value)

			WHEN pc1.[cntr_type] = 272696576 OR pc1.[cntr_type] = 272696320 THEN 
				CONVERT(float,((pc1.[cntr_value] - pc.[cntr_value]) / DATEDIFF(ss,@minctr,@maxctr)))  -- Get value per s over last xx s

			WHEN pc1.[cntr_type] = 537003264 THEN			
				CONVERT(float,(pc1.[cntr_value] / CASE WHEN pc2.[cntr_value] <= 0 THEN 1 ELSE pc2.[cntr_value] END) * 100.0)
		
			WHEN pc1.[cntr_type] = 1073874176 THEN 
				CONVERT(float,((
					(pc1.[cntr_value] - pc.[cntr_value]) / CASE WHEN (pc2.[cntr_value] - pc.[base_cntr_value]) <= 0 THEN 1 ELSE (pc2.[cntr_value] - pc.[base_cntr_value]) END) 
					/ 
					DATEDIFF(ss,@minctr,@maxctr))) -- Get value per s over last xx s

		END AS [CounterValue]
	FROM
		sys.dm_os_performance_counters pc1 (NOLOCK)	
		INNER JOIN @RequestedCounters rc 
			ON pc1.[object_name] = rc.CategoryName AND pc1.[counter_name] = rc.CounterName
		LEFT JOIN sys.dm_os_performance_counters pc2 (NOLOCK) 
			ON pc2.[cntr_type] = 1073939712 AND UPPER(pc2.[counter_name]) = UPPER(rc.BaseCounterName) AND pc1.[object_name] = pc2.[object_name] AND pc1.[instance_name] = pc2.[instance_name]
		LEFT JOIN #PerfCounters pc
			ON pc.[object_name] = pc1.[object_name]	AND pc.[counter_name] = pc1.[counter_name] AND pc.[instance_name] = pc1.[instance_name]
	WHERE 
		(pc1.[cntr_type] = 65792 OR pc1.[cntr_type] = 272696576 OR pc1.[cntr_type] = 1073874176 OR pc1.[cntr_type] = 537003264 OR pc1.[cntr_type] = 272696320) 
		AND
		(
			(rc.InstanceSelector = 0) 
			OR (rc.InstanceSelector = 1 AND pc1.[instance_name] = N'_Total') 
			OR (rc.InstanceSelector = 2 AND pc1.[instance_name] = N'')
			OR (rc.InstanceSelector = 3 AND pc1.instance_name = rc.InstanceName)
			OR (rc.InstanceSelector = 4 AND pc1.[instance_name] IN (SELECT DBName FROM #HekatonDBs))
		)
) src
WHERE [CounterValue] IS NOT NULL



--GetAgentJobStatus
IF OBJECT_ID('tempdb.dbo.#job_activity') IS NOT NULL DROP TABLE #job_activity;

CREATE TABLE #job_activity (
	[session_id] [int] NOT NULL,
	[job_id] [uniqueidentifier] NOT NULL,
	[job_name] [sysname] COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
	[run_requested_date] [datetime] NULL,
	[run_requested_source] [sysname] COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
	[queued_date] [datetime] NULL,
	[start_execution_date] [datetime] NULL,
	[last_executed_step_id] [int] NULL,
	[last_exectued_step_date] [datetime] NULL,
	[stop_execution_date] [datetime] NULL,
	[next_scheduled_run_date] [datetime] NULL,
	[job_history_id] [int] NULL,
	[message] [nvarchar](1024) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
	[run_status] [int] NULL,
	[operator_id_emailed] [int] NULL,
	[operator_id_netsent] [int] NULL,
	[operator_id_paged] [int] NULL,
	[execution_time_minutes] [int] NULL 
);

INSERT INTO #job_activity(
	[session_id]
	,[job_id]
	,[job_name]
	,[run_requested_date]
	,[run_requested_source]
	,[queued_date]
	,[start_execution_date]
	,[last_executed_step_id]
	,[last_exectued_step_date]
	,[stop_execution_date]
	,[next_scheduled_run_date]
	,[job_history_id]	
	,[message]
	,[run_status]
	,[operator_id_emailed]
	,[operator_id_netsent]
	,[operator_id_paged])
EXECUTE [msdb].[dbo].[sp_help_jobactivity];

SELECT
	[ja].[job_name] AS [JobName]
	,[message] AS [Message]
	,CASE 
		WHEN [run_status] IS NULL THEN -1
		ELSE [run_status] 
	END AS [RunStatus]
	,CASE
		-- Has not finished a run
		WHEN [start_execution_date] IS NOT NULL AND [stop_execution_date] IS NULL THEN DATEDIFF(minute, [start_execution_date], GETDATE())
		-- Has finished that run, but we still need to time for monitor state
		WHEN [start_execution_date] IS NOT NULL AND [stop_execution_date] IS NOT NULL THEN  DATEDIFF(minute, [start_execution_date], [stop_execution_date])
		ELSE -1
	END AS [ExecutionTimeMinutes]
FROM #job_activity [ja]
JOIN [msdb].[dbo].[sysjobs_view] [sjv] ON [sjv].[job_id] = [ja].[job_id];

DROP TABLE #job_activity;



--GetHKFilePairs
DECLARE @dbName sysname, @sql nvarchar(2000);

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

CREATE TABLE #CFPResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[State] SYSNAME
	,[Count] INT
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND d.[state] = 0 
		AND d.[is_read_only] = 0 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb') 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND d.[state] = 0 
		AND d.[is_read_only] = 0 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb')
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@Fetch_Status=0 
BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT 
		DB_ID() AS [DatabaseId]
		,DB_NAME() AS [DatabaseName]
		,cf.[state] AS [State]
		,COUNT(*) as [Count]
	FROM sys.dm_db_xtp_checkpoint_files cf 
	WHERE  cf.[file_type] <=0
	GROUP BY cf.state
	';
	
	BEGIN TRY
		INSERT INTO #CFPResultTable
		EXEC sp_executesql @sql
		
	END TRY
	BEGIN CATCH					
	END CATCH;
	
	FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #CFPResultTable

DROP TABLE #CFPResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--GetDBConfigs
SELECT db.[name] AS [DatabaseName]
	,CASE db.[is_auto_update_stats_on]
		WHEN 0 THEN N'OFF'
		WHEN 1 THEN N'ON'
		END AS [AutoUpdateStat]
	,CASE db.[is_auto_update_stats_async_on]
		WHEN 0 THEN N'OFF'
		WHEN 1 THEN N'ON'
		END AS [AutoUpdateStatAsync]
	,CASE 
		WHEN db.[name] IN (N'master', N'msdb', N'tempdb') THEN N'N/A'
		WHEN db.[is_db_chaining_on] = 0 THEN N'OFF'
		WHEN db.[is_db_chaining_on] = 1 THEN N'ON'
		END AS [DBChaining]
	,CASE db.[is_auto_close_on]
		WHEN 0 THEN N'OFF'
		WHEN 1 THEN N'ON'
		END AS [AutoClose]
	,CASE db.[is_auto_shrink_on]
		WHEN 0 THEN N'OFF'
		WHEN 1 THEN N'ON'
		END AS [AutoShrink]
	,CASE db.[is_auto_create_stats_on]
		WHEN 0 THEN N'OFF'
		WHEN 1 THEN N'ON'
		END AS [AutoCreateStat]
	,CASE 
		WHEN db.[name] IN (N'master', N'msdb', N'tempdb') THEN N'N/A'
		WHEN db.[is_trustworthy_on] = 0 THEN N'OFF'
		WHEN db.[is_trustworthy_on] = 1 THEN N'ON'
		END AS [TrustWorthy]
	,CASE db.[page_verify_option]
		WHEN 0 THEN N'NONE'
		WHEN 1 THEN N'TORN_PAGE_DETECTION'
		WHEN 2 THEN N'CHECKSUM'
		END AS [PageVerify]
	,CASE db.[recovery_model]
		WHEN 1 THEN N'FULL'
		WHEN 2 THEN N'BULK_LOGGED'
		WHEN 3 THEN N'SIMPLE'
		END AS [RecoveryModel]
FROM sys.databases AS db
WHERE db.source_database_id IS NULL



--GetCurrentSQLDate
select getdate() as LogDate,'' as ProcessInfo,'' as [Text]



--GetDBSpaceData
DECLARE @dbName sysname, @sql nvarchar(2000), @needsMaster BIT;

IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;
CREATE TABLE #ResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[FileName] SYSNAME
	,[FileType] TINYINT
	,FileSizeMB FLOAT(53)
	,FileUsedSpaceMB FLOAT(53)
	,FileMaxSizeMB FLOAT(53)
	,IsAutoGrowEnabled BIT
	,IsPercentGrowth BIT
	,FileGrowth INT
	,VolumeMountPoint NVARCHAR(256)
	,VolumeId NVARCHAR(256)
	,GroupKey NVARCHAR(22)
	,FilegroupName SYSNAME
	,FilegroupType NVARCHAR(2)
	,IsReadOnly BIT
	,DiskFreeSpaceMB FLOAT(53)
	,PhysicalName NVARCHAR(260)
	);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,CAST(CASE
			WHEN d.[replica_id] IS NOT NULL OR d.[is_in_standby] = 1 THEN 1
			ELSE 0
		END AS BIT) AS [needsMaster] 
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,CAST(CASE
			WHEN d.[replica_id] IS NOT NULL OR d.[is_in_standby] = 1 THEN 1
			ELSE 0
		END AS BIT) AS [needsMaster] 
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @needsMaster;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT 
		DB_ID() AS [DatabaseId]
		,DB_NAME() AS [DatabaseName]
		,N'''' AS [FileName]
		,CASE
			WHEN f.[type] = 1 THEN 0
			WHEN fg.[type] COLLATE DATABASE_DEFAULT = ''FG'' THEN 1
			WHEN fg.[type] COLLATE DATABASE_DEFAULT = ''FD'' THEN 2
			WHEN fg.[type] COLLATE DATABASE_DEFAULT = ''FX'' THEN 3
			END AS [FileType]
		,CONVERT(FLOAT(53), f.size / 128.0) AS [FileSizeMB]
		,CONVERT(FLOAT(53), CASE
			WHEN f.[type] IN (0,1) THEN FILEPROPERTY(f.name, ''SpaceUsed'') / 128.0
			ELSE -1
			END) AS [FileUsedSpaceMB]
		,CONVERT(FLOAT(53), CASE 
			WHEN f.max_size = -1 OR f.max_size = 268435456 THEN -1 
			ELSE f.max_size / 128.0 
			END) as [FileMaxSizeMB]
		,CASE
			WHEN f.growth = 0 THEN 0
			ELSE 1
			END AS IsAutoGrowEnabled
		,f.is_percent_growth as [IsPercentGrowth]
		,f.growth AS [FileGrowth]
		,ovs.volume_mount_point AS [VolumeMountPoint]
		,ovs.volume_id AS [VolumeId]
		,N'''' AS [GroupKey]
		,N'''' AS [FilegroupName]
		,N'''' AS [FilegroupType]
		,f.is_read_only AS [IsReadOnly]';

	IF @needsMaster = 0
	BEGIN
		SET @sql = @sql + N'
		,CONVERT(FLOAT(53), CASE 
			WHEN CharIndex(N''https://'',LTRIM(f.physical_name)) = 1 OR CharIndex(N''http://'',LTRIM(f.physical_name)) = 1 THEN @azureMaxFileSizeMB - f.size / 128.0
			ELSE ovs.available_bytes / 1048576.0 
			END) AS [DiskFreeSpaceMB]
		,f.physical_name AS [PhysicalName]
	FROM sys.database_files AS f WITH (NOLOCK)';
	END;
	ELSE
	BEGIN
		SET @sql = @sql +N'
		,CONVERT(FLOAT(53), CASE 
			WHEN CharIndex(N''https://'',LTRIM(mf.physical_name)) = 1 OR CharIndex(N''http://'',LTRIM(mf.physical_name)) = 1 THEN @azureMaxFileSizeMB - f.size / 128.0
			ELSE ovs.available_bytes / 1048576.0 
			END) AS [DiskFreeSpaceMB]
		,mf.physical_name AS [PhysicalName]
	FROM sys.database_files AS f WITH (NOLOCK)
	JOIN sys.master_files AS mf WITH (NOLOCK) ON f.file_id = mf.file_id AND mf.database_id = DB_ID()';
	END;
	
	SET @sql = @sql + N'
	LEFT JOIN sys.filegroups fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
	CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) ovs
	WHERE ((f.[type] = 1 AND fg.data_space_id IS NULL) OR (f.[type] IN (0,2) AND fg.data_space_id IS NOT NULL))';
	
	BEGIN TRY
		INSERT INTO #ResultTable
		EXEC sp_executesql @sql, N'@azureMaxFileSizeMB INT', @azureMaxFileSizeMB = @azureMaxFileSizeMB
	END TRY
	BEGIN CATCH
	END CATCH;
	FETCH NEXT FROM dbCursor INTO @dbName, @needsMaster;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable;

DROP TABLE #ResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--HashIndexAvgChainLength
DECLARE @dbName sysname, @sql nvarchar(2000);

IF OBJECT_ID ('tempdb.dbo.#HekatonTablesHashIndexAvgChainLengthResultTable') IS NOT NULL DROP TABLE #HekatonTablesHashIndexAvgChainLengthResultTable;
CREATE TABLE #HekatonTablesHashIndexAvgChainLengthResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[TableName] SYSNAME
	,[IndexName] SYSNAME
	,[EmptyBucketPercent] INT
	,[AvgChainLength] INT
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb') 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb')
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	IF EXISTS(SELECT 1 FROM sys.tables WHERE is_memory_optimized = 1)
	BEGIN
		with bucket_cte as (
		select 
			object_name(hs.object_id) as [_object_name], 
			i.name as [index_name], 
			hs.total_bucket_count,
			hs.empty_bucket_count,
			floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) as [empty_bucket_percent],
			hs.avg_chain_length, 
			hs.max_chain_length
		from sys.dm_db_xtp_hash_index_stats as hs 
			join sys.indexes as i 
			on hs.object_id=i.object_id and hs.index_id=i.index_id
		)
		INSERT INTO #HekatonTablesHashIndexAvgChainLengthResultTable
		select top(@p1) 
			DB_ID() AS [DatabaseId],
			DB_NAME() AS [DatabaseName],
			[_object_name] as [TableName],
			[index_name] as [IndexName],
			[empty_bucket_percent] as [EmptyBucketPercent],
			[avg_chain_length] as [AvgChainLength]
		from bucket_cte   
		where empty_bucket_percent > @p2 and avg_chain_length > @p3
		order by avg_chain_length desc, empty_bucket_percent asc;
	END;';

	BEGIN TRY
		EXEC sp_executesql @sql,N'@p1 INT, @p2 INT, @p3 INT', @p1 = @errorTopLines, @p2 = @emptyBucketPercentThreshold, @p3 = @avgChainLengthThreshold
	END TRY
	BEGIN CATCH
	END CATCH;
	FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #HekatonTablesHashIndexAvgChainLengthResultTable;

DROP TABLE #HekatonTablesHashIndexAvgChainLengthResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--GetSecurablesServerConfig
DECLARE @xmlDoc XML = CAST(@SecurableConfigXml AS XML);

DECLARE @TargetSecurables TABLE(
	[Name] NVARCHAR(128)
	,[PermissionType] NVARCHAR(30)
	,[SecurableType] INT
	,[SqlCondition] INT
);

INSERT INTO @TargetSecurables
SELECT 
	xc.value('./name[1]', 'NVARCHAR(128)') AS [Name]
	,xc.value('./pt[1]', 'NVARCHAR(30)') AS [PermissionType]
	,xc.value('./st[1]', 'INT') AS [SecurableType]
	,xc.value('./cn[1]', 'INT') AS [SqlCondition]
FROM @xmlDoc.nodes('/*/dmv') AS xt(xc);

DECLARE @IsHadrEnabled INT = CAST(SERVERPROPERTY('IsHadrEnabled') AS INT);

WITH src AS(
	SELECT
		tObj.[Name]
		,tObj.SecurableType
		,CASE tObj.SecurableType
			WHEN 0 THEN HAS_PERMS_BY_NAME(NULL, NULL, tObj.[Name])
			ELSE HAS_PERMS_BY_NAME(tObj.[Name], 'OBJECT', tObj.PermissionType)
		END AS HasPermission
	FROM @TargetSecurables tObj
	WHERE tObj.SqlCondition = 0 OR (tObj.SqlCondition = 1 AND @IsHadrEnabled = 1)
)
SELECT
	src.[Name] AS [Name]
	,SecurableType AS [SecurableType]
FROM src
WHERE src.HasPermission = 0;




--GetSecurablesDbConfig
DECLARE @xmlDoc XML = CAST(@SecurableConfigXml AS XML);

DECLARE @dbName sysname, @sql nvarchar(2000), @dbId int

IF OBJECT_ID ('tempdb.dbo.#TargetDbSecurables') IS NOT NULL DROP TABLE #TargetDbSecurables;
IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;

CREATE TABLE #TargetDbSecurables(
	[Name] NVARCHAR(128)
	,[PermissionType] NVARCHAR(30)
	,[SecurableType] INT
);

INSERT INTO #TargetDbSecurables
SELECT 
	xc.value('./name[1]', 'NVARCHAR(128)') AS [Name]
	,xc.value('./pt[1]', 'NVARCHAR(30)') AS [PermissionType]
	,xc.value('./st[1]', 'INT') AS [SecurableType]
FROM @xmlDoc.nodes('/*/dmv') AS xt(xc);

CREATE TABLE #ResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[Name] NVARCHAR(128)
	,[SecurableType] INT
	,[HasIssue] BIT
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,d.[database_id]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,[database_id]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL AND HAS_DBACCESS(d.[name]) = 1 AND dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @dbId;

WHILE @@Fetch_Status=0 BEGIN
	BEGIN TRY
		SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
WITH src AS(
	SELECT
		tObj.[Name]
		,tObj.SecurableType
		,CASE tObj.SecurableType
			WHEN 0 THEN HAS_PERMS_BY_NAME(NULL, NULL, tObj.[Name])
			ELSE HAS_PERMS_BY_NAME(tObj.[Name], ''OBJECT'', tObj.PermissionType)
		END AS HasPermission
	FROM #TargetDbSecurables tObj
)
SELECT
	DB_ID() AS [DatabaseId]
	,DB_NAME() AS [DatabaseName]
	,src.[Name] AS [Name]
	,SecurableType AS [SecurableType]
	,CAST(1 AS BIT) AS [HasIssue]
FROM src
WHERE src.HasPermission = 0;
'
		INSERT INTO #ResultTable
		EXEC sp_executesql @sql

		IF @@ROWCOUNT = 0
			INSERT INTO #ResultTable(DatabaseId,DatabaseName,[Name],SecurableType,HasIssue) VALUES(@dbId, @dbName, NULL, 0, 0)
	END TRY
	BEGIN CATCH
	END CATCH;
	
	FETCH NEXT FROM dbCursor INTO @dbName, @dbId;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable

DROP TABLE #TargetDbSecurables;
DROP TABLE #ResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;




--DBEngineStatus
SELECT 1 AS [Status]



--GetAgentJobDuration
SELECT 
	sj.name AS [JobName], 
	DATEDIFF(minute, sja.start_execution_date, GETDATE()) AS [ExecutionTimeMinutes]
FROM msdb.dbo.sysjobactivity AS sja
	INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL



--GetHKActiveCFPRatio
DECLARE @dbName sysname, @sql nvarchar(2000), @dbId int;

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

CREATE TABLE #CFPStaleResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[StaleRatio] INT
	,[ReasonableCfpCount] INT
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,d.[database_id]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND state = 0 
		AND is_read_only = 0 
		AND name not in ('master', 'model', 'msdb', 'tempdb') 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,[database_id]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND state = 0 
		AND is_read_only = 0 
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @dbId;

WHILE @@Fetch_Status=0 
BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT 
	    DB_ID() AS [DatabaseId]
		,DB_NAME() AS [DatabaseName]
		,ISNULL(100*SUM(CASE WHEN cf.[state]>4 THEN 1E0 ELSE 0E0 END)/(CASE WHEN COUNT(*)=0 THEN 1 ELSE COUNT(*) END),-1) as [StaleRatio]
		,COUNT(*) as [ReasonableCfpCount] 
	FROM sys.dm_db_xtp_checkpoint_files cf
	';
	
	BEGIN TRY
		INSERT INTO #CFPStaleResultTable
		EXEC sp_executesql @sql
		
	END TRY
	BEGIN CATCH					
	END CATCH;
	
	FETCH NEXT FROM dbCursor INTO @dbName, @dbId;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #CFPStaleResultTable

DROP TABLE #CFPStaleResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--GetHKTableMemoryUsage
DECLARE @dbName sysname, @sql nvarchar(2000);

IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;
CREATE TABLE #ResultTable (
	[DatabaseId] INT,
	[DatabaseName] SYSNAME,
	[TablesUsedMemoryMB] FLOAT(53),
	[TablesUnusedMemoryMB] FLOAT(53),
	[IndexesUsedMemoryMB] FLOAT(53),
	[IndexesUnusedMemoryMB] FLOAT(53)
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND d.[state] = 0 
		AND d.[is_read_only] = 0 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb') 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND d.[state] = 0 
		AND d.[is_read_only] = 0 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb')
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	IF EXISTS(SELECT TOP 1 data_space_id FROM sys.filegroups WHERE [type] COLLATE DATABASE_DEFAULT = ''FX'')
		SELECT
			DB_ID() AS [DatabaseId]
			,DB_NAME() AS [DatabaseName]
			,CASE 
				WHEN [TablesUsedMemoryMB] IS NULL THEN 0
				ELSE [TablesUsedMemoryMB] 
			END AS [TablesUsedMemoryMB]
			,CASE 
				WHEN [TablesUnusedMemoryMB] IS NULL THEN 0
				ELSE [TablesUnusedMemoryMB] 
			END AS [TablesUnusedMemoryMB]
			,CASE 
				WHEN [IndexesUsedMemoryMB] IS NULL THEN 0
				ELSE [IndexesUsedMemoryMB] 
			END AS [IndexesUsedMemoryMB]
			,CASE 
				WHEN [IndexesUnusedMemoryMB] IS NULL THEN 0
				ELSE [IndexesUnusedMemoryMB] 
			END AS [IndexesUnusedMemoryMB]
		FROM (
			SELECT
				SUM((CONVERT(FLOAT(53), memory_used_by_table_kb)))/1024 AS [TablesUsedMemoryMB]
				,SUM((CONVERT(FLOAT(53), (memory_allocated_for_table_kb - memory_used_by_table_kb))))/1024 AS [TablesUnusedMemoryMB]
				,SUM((CONVERT(FLOAT(53), memory_used_by_indexes_kb)))/1024 AS [IndexesUsedMemoryMB]
				,SUM((CONVERT(FLOAT(53), (memory_allocated_for_indexes_kb - memory_used_by_indexes_kb))))/1024 AS [IndexesUnusedMemoryMB]
			FROM sys.dm_db_xtp_table_memory_stats
			WHERE object_id > 0
		) m';

	BEGIN TRY
		INSERT INTO #ResultTable
		EXEC sp_executesql @sql;
	END TRY
	BEGIN CATCH
	END CATCH;
	FETCH NEXT FROM dbCursor INTO @dbName;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable;

DROP TABLE #ResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--GetDBDiskLatency
IF OBJECT_ID('tempdb..#VfStatSnapshot1') IS NOT NULL
	DROP TABLE [#VfStatSnapshot1];

IF OBJECT_ID('tempdb..#VfStatSnapshot2') IS NOT NULL
	DROP TABLE [#VfStatSnapshot2];
 
SELECT 
    [database_id], 
    [file_id], 
    [num_of_reads], 
    [io_stall_read_ms],
    [num_of_writes], 
    [io_stall_write_ms],  
    [file_handle]
INTO #VfStatSnapshot1
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
 
WAITFOR DELAY '00:00:10';
 
SELECT 
    [database_id], 
    [file_id], 
    [num_of_reads], 
    [io_stall_read_ms],
    [num_of_writes], 
    [io_stall_write_ms],  
    [file_handle]
INTO #VfStatSnapshot2
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
 
WITH [SnapDiff] AS
(
	SELECT
        [s2].[database_id],
        [s2].[file_id],
        [s2].[num_of_reads] - COALESCE([s1].[num_of_reads], 0) AS [num_of_reads],
        [s2].[io_stall_read_ms] - COALESCE([s1].[io_stall_read_ms], 0) AS [io_stall_read_ms],
        [s2].[num_of_writes] - COALESCE([s1].[num_of_writes], 0) AS [num_of_writes],
        [s2].[io_stall_write_ms] - COALESCE([s1].[io_stall_write_ms], 0) AS [io_stall_write_ms]
    FROM [#VfStatSnapshot2] AS [s2]
    LEFT OUTER JOIN [#VfStatSnapshot1] AS [s1]
        ON [s2].[file_handle] = [s1].[file_handle]
),
[CounterCalc] AS(
	SELECT
		DB_NAME ([database_id]) AS [DatabaseName],
		CASE WHEN [num_of_reads] = 0 THEN 0 ELSE [io_stall_read_ms] / [num_of_reads] END AS [ReadLatency],
		CASE WHEN [num_of_writes] = 0 THEN 0 ELSE [io_stall_write_ms] / [num_of_writes] END AS [WriteLatency]
	FROM [SnapDiff]
)
SELECT
	[DatabaseName],
    CAST(MAX([ReadLatency]) AS FLOAT(53)) AS [ReadLatency],
    CAST(MAX([WriteLatency]) AS FLOAT(53)) AS [WriteLatency]
FROM [CounterCalc]
GROUP BY [DatabaseName];

IF OBJECT_ID('tempdb..#VfStatSnapshot1') IS NOT NULL
	DROP TABLE [#VfStatSnapshot1];

IF OBJECT_ID('tempdb..#VfStatSnapshot2') IS NOT NULL
	DROP TABLE [#VfStatSnapshot2];




--GetAgentServiceStatus
SELECT 
	startup_type AS [StartupType]
	,[status] AS [Status]
FROM sys.dm_server_services 
WHERE [filename] LIKE '"%\Binn\SQLAGENT.EXE" -i%'



--VLF
DECLARE @sql nvarchar(2000), @dbName sysname;

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

CREATE TABLE #VLFResultTable (
	[DatabaseName] SYSNAME,
	[Count] INT
	)

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

CREATE TABLE #VLFTmpTable (
	RecoveryUnitId INT,
	FileId SMALLINT,
	FileSize FLOAT,
	StartOffset BIGINT,
	FSeqNo BIGINT,
	[Status] INT,
	Parity TINYINT,
	CreateLSN NVARCHAR(48)
	)

DECLARE fileCursor CURSOR LOCAL STATIC FOR 
SELECT 
	d.[name]
FROM sys.databases d
WHERE HAS_DBACCESS(d.[name]) = 1;

OPEN fileCursor;
FETCH NEXT FROM fileCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN

	SET @sql = N'DBCC LOGINFO('+QUOTENAME(@dbName ,'"')+')';

	BEGIN TRY
		INSERT INTO #VLFTmpTable
		EXEC sp_executesql @sql
	END TRY
	BEGIN CATCH
	END CATCH;

	INSERT INTO #VLFResultTable VALUES (@dbName, (SELECT COUNT(1) FROM #VLFTmpTable))

	DELETE FROM #VLFTmpTable

	FETCH NEXT FROM fileCursor INTO @dbName;
END

CLOSE fileCursor;
DEALLOCATE fileCursor;

SELECT * FROM #VLFResultTable



--AvailabilityDBBackupStatus
SELECT
	[DatabaseName]
	,[DaysSinceBackup]
	,CASE
		--Primary
		WHEN ag.automated_backup_preference = 0 AND hars.role = 1 THEN 1
		WHEN ag.automated_backup_preference = 0 AND hars.role = 2 THEN 0
		--Secondary only
		WHEN ag.automated_backup_preference = 1 AND hars.role = 2 THEN 1
		WHEN ag.automated_backup_preference = 1 AND hars.role = 1 THEN 0
		--Prefer Secondary
		WHEN ag.automated_backup_preference = 2 THEN 1
		--Any Replica
		WHEN ag.automated_backup_preference = 3 AND ar.backup_priority > 0 THEN 1
		WHEN ag.automated_backup_preference = 3 AND ar.backup_priority = 0 THEN 0
		--Unknown or the replica in a transient state
		ELSE -1
	END AS [PreferredForBackup]
	,COALESCE(ag.automated_backup_preference_desc, N'') AS [AgAutomatedBackupPreference]
	,COALESCE(hars.role_desc, N'') AS [ArRole]
	,COALESCE(ar.backup_priority, -1) AS [ArBackupPriority]
FROM (
		SELECT
			d.name AS [DatabaseName]
			,DATEDIFF(Day, COALESCE(MAX(b.backup_finish_date), d.create_date), GETDATE()) AS [DaysSinceBackup]
			,d.replica_id
		FROM
			sys.databases d 
			LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
		WHERE
			d.is_in_standby = 0
			AND source_database_id is null
			AND d.name <> 'tempdb'
			AND (b.[type] IN ('D', 'I') OR b.[type] IS NULL)
			AND d.replica_id IS NOT NULL
		GROUP BY
			d.name, d.create_date, d.replica_id
	) d
	JOIN sys.availability_replicas ar WITH(NOLOCK) ON d.replica_id = ar.replica_id
	JOIN sys.dm_hadr_availability_replica_states hars WITH(NOLOCK) ON d.replica_id = hars.replica_id
	JOIN sys.availability_groups ag WITH(NOLOCK) ON ag.group_id = ar.group_id



--GetDBFileSpaceData
DECLARE @dbName sysname, @sql nvarchar(2000), @needsMaster BIT;

IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;
CREATE TABLE #ResultTable (
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[FileName] SYSNAME
	,[FileType] TINYINT
	,FileSizeMB FLOAT(53)
	,FileUsedSpaceMB FLOAT(53)
	,FileMaxSizeMB FLOAT(53)
	,IsAutoGrowEnabled BIT
	,IsPercentGrowth BIT
	,FileGrowth INT
	,IsReadOnly BIT
	,DiskFreeSpaceMB FLOAT(53)
	,DiskTotalSizeMB FLOAT(53)
	);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,CAST(CASE
			WHEN d.[replica_id] IS NOT NULL OR d.[is_in_standby] = 1 THEN 1
			ELSE 0
		END AS BIT) AS [needsMaster] 
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,CAST(CASE
			WHEN d.[replica_id] IS NOT NULL OR d.[is_in_standby] = 1 THEN 1
			ELSE 0
		END AS BIT) AS [needsMaster] 
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @needsMaster;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT 
		DB_ID() AS [DatabaseId]
		,DB_NAME() AS [DatabaseName]
		,f.name AS [FileName]
		,CAST(CASE
			WHEN f.type < 2 THEN f.type
			WHEN fg.[type] COLLATE DATABASE_DEFAULT = ''FX'' THEN 2
			WHEN fg.[type] COLLATE DATABASE_DEFAULT = ''FD'' THEN 3
			ELSE 10
		END AS TINYINT) AS [FileType]
		,CONVERT(FLOAT(53), f.size / 128.0) AS [FileSizeMB]
		,CONVERT(FLOAT(53), 
			CASE 
				WHEN FILEPROPERTY(f.name, ''SpaceUsed'') IS NOT NULL THEN FILEPROPERTY(f.name, ''SpaceUsed'') / 128.0
				ELSE -1
			END) AS [FileSpaceUsedMB]
		,CONVERT(FLOAT(53), CASE 
			WHEN f.max_size = -1 OR f.max_size = 268435456 THEN -1 
			ELSE f.max_size / 128.0 
			END) as [FileMaxSizeMB]
		,CASE
			WHEN f.growth = 0 THEN 0
			ELSE 1
			END AS IsAutoGrowEnabled
		,f.is_percent_growth as [IsPercentGrowth]
		,f.growth AS [FileGrowth]
		,f.is_read_only AS [IsReadOnly]';

	IF @needsMaster = 0
	BEGIN
		SET @sql = @sql + N'
		,CONVERT(FLOAT(53), CASE 
			WHEN LOWER(LTRIM(f.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''https://%'' OR LOWER(LTRIM(f.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''http://%'' THEN @azureMaxFileSizeMB - f.size / 128.0
			ELSE ovs.available_bytes / 1048576.0 
			END) AS [DiskFreeSpaceMB]
		,CONVERT(FLOAT(53), CASE 
			WHEN LOWER(LTRIM(f.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''https://%'' OR LOWER(LTRIM(f.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''http://%'' THEN @azureMaxFileSizeMB
			ELSE ovs.total_bytes / 1048576.0 
			END) AS [DiskTotalSizeMB]
	FROM sys.database_files AS f WITH (NOLOCK)';
	END;
	ELSE
	BEGIN
		SET @sql = @sql + N'
		,CONVERT(FLOAT(53), CASE 
			WHEN LOWER(LTRIM(mf.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''https://%'' OR LOWER(LTRIM(mf.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''http://%'' THEN @azureMaxFileSizeMB - f.size / 128.0
			ELSE ovs.available_bytes / 1048576.0 
			END) AS [DiskFreeSpaceMB]
		,CONVERT(FLOAT(53), CASE 
			WHEN LOWER(LTRIM(mf.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''https://%'' OR LOWER(LTRIM(mf.physical_name)) COLLATE DATABASE_DEFAULT LIKE ''http://%'' THEN @azureMaxFileSizeMB
			ELSE ovs.total_bytes / 1048576.0 
			END) AS [DiskTotalSizeMB]
	FROM sys.database_files AS f WITH (NOLOCK)
	JOIN sys.master_files AS mf WITH (NOLOCK) ON f.file_id = mf.file_id AND mf.database_id = DB_ID()';
	END;

	SET @sql = @sql + N'
	LEFT JOIN sys.filegroups fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
	CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) ovs
	WHERE (f.[type] IN (0,1) OR fg.[type] COLLATE DATABASE_DEFAULT = ''FX'');';

	BEGIN TRY
		INSERT INTO #ResultTable
		EXEC sp_executesql @sql, N'@azureMaxFileSizeMB INT', @azureMaxFileSizeMB = @azureMaxFileSizeMB
	END TRY
	BEGIN CATCH
	END CATCH;
	FETCH NEXT FROM dbCursor INTO @dbName, @needsMaster;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable;

DROP TABLE #ResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--SmartAdminHealthPolicy
SELECT 
	p.[name] AS PolicyName
	,h.end_date AS EndDate
	,COALESCE(h.result, 1) AS Result
	,CAST(LEFT(c.facet, 4000) AS NVARCHAR(4000)) AS Facet
	,p.help_text AS HelpText
FROM msdb.dbo.syspolicy_policies p
	INNER JOIN msdb.dbo.syspolicy_conditions c ON c.condition_id = p.condition_id
	LEFT JOIN msdb.dbo.syspolicy_policy_execution_history h ON h.policy_id = p.policy_id
	INNER JOIN (
		SELECT 
			p.policy_id
			,max(h.history_id) AS last_history_id
		FROM msdb.dbo.syspolicy_policies p
			INNER JOIN msdb.dbo.syspolicy_conditions c ON c.condition_id = p.condition_id
			LEFT JOIN msdb.dbo.syspolicy_policy_execution_history h ON h.policy_id = p.policy_id
			INNER JOIN (
				SELECT 
					p.policy_id
					,max(h.end_date) AS last_date
				FROM msdb.dbo.syspolicy_policies p
					INNER JOIN msdb.dbo.syspolicy_conditions c ON c.condition_id = p.condition_id
					LEFT JOIN msdb.dbo.syspolicy_policy_execution_history h ON h.policy_id = p.policy_id
				WHERE p.[name] = 'SmartAdminSystemHealthPolicy' OR p.[name] = 'SmartAdminUserActionsHealthPolicy'
				GROUP BY p.policy_id
				) pld ON pld.policy_id = p.policy_id AND (pld.last_date = h.end_date OR (pld.last_date IS NULL AND h.end_date IS NULL))
			GROUP BY p.policy_id
		) plh ON plh.policy_id = p.policy_id AND (plh.last_history_id = h.history_id OR (plh.last_history_id IS NULL AND h.history_id IS NULL))




--DBActiveRequestsCount
SELECT 
	DB_NAME(db.database_id) AS [DatabaseName],
	ISNULL(info.NRequests, 0) AS [NRequests]
FROM 
sys.databases AS db
LEFT JOIN
(
	SELECT database_id, COUNT(database_id) as [NRequests] 
	FROM sys.dm_exec_requests 
	WHERE database_id > 0
	GROUP BY database_id
) AS info
ON db.database_id = info.database_id



--GetResourcePoolMemory
DECLARE @dbName sysname, @sql nvarchar(2000)

IF OBJECT_ID ('tempdb.dbo.#FileGroupFx') IS NOT NULL DROP TABLE #FileGroupFx;
CREATE TABLE #FileGroupFx (
	[DatabaseName] SYSNAME
	,[Status] INT
	,[Error] INT
	,[ErrorMessage]  nvarchar(max)
	,[GroupName] SYSNAME
	,[ReadOnly] BIT
	,[GroupType] nvarchar(2)
	,[TypeDesc]  nvarchar(60)
)

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb')
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT TOP 1
		DB_NAME() AS [DatabaseName]
		,HAS_DBACCESS(DB_NAME()) as Status
		,0 as [Error]
		,'''' as [ErrorMessage] 
		,fg.[name] AS [GroupName]
		,fg.is_read_only AS [ReadOnly]
		,fg.[type] AS [GroupType]
		,fg.type_desc AS [TypeDesc]
	FROM sys.filegroups fg WITH (NOLOCK)
	WHERE fg.[Type] = ''FX''
	';
	
	BEGIN TRY
		INSERT INTO #FileGroupFx
		EXEC sp_executesql @sql
	END TRY
	BEGIN CATCH		
	END CATCH;
	
	FETCH NEXT FROM dbCursor INTO @dbName;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT TOP 1 WITH TIES
	db.name as DbName
	,db.database_id as DatabaseId
	,grp.name as Name
	,grp.min_memory_percent as Min_Memory_Percent
	,grp.max_memory_percent as Max_Memory_Percent
	,grp.max_memory_kb/1024 AS Max_Memory_MB
	,grp.used_memory_kb/1024 AS Used_Memory_MB 
	,grp.target_memory_kb/1024 AS Target_Memory_MB
	,grp.pool_id AS Pool_ID
	,CONVERT(bit, CASE 
		WHEN fg.GroupName IS NULL THEN 0
		ELSE 1
	END) AS IsFx
FROM master.sys.databases as db
	JOIN master.sys.dm_resource_governor_resource_pools AS grp ON grp.pool_id = CASE 
																					WHEN db.resource_pool_id IS NULL THEN 2 
																					ELSE db.resource_pool_id 
																				END
	LEFT JOIN #FileGroupFx fg ON fg.DatabaseName = db.name
WHERE db.source_database_id IS NULL 
	AND db.state = 0 
	AND db.is_read_only = 0 
	AND db.name not in ('master', 'model', 'msdb', 'tempdb')
ORDER BY ROW_NUMBER() OVER (PARTITION BY grp.pool_id ORDER BY db.Name);

DROP TABLE #FileGroupFx;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--VLF16SP2
SELECT d.[name] AS DatabaseName
	,COUNT(l.database_id) AS [Count]
FROM sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id) l
WHERE HAS_DBACCESS(d.[name]) = 1
GROUP BY d.[name]



--ThreadCount
SELECT
	CAST(max_workers_count AS INT) AS [WorkerCount],
	CAST(cpu_count AS INT) AS [CpuCount],
	CAST((SELECT count(*) FROM sys.dm_os_threads) AS INT) AS [ThreadCount]
FROM sys.dm_os_sys_info



--GetDBFileGroupsCD
DECLARE @dbName sysname, @sql nvarchar(2000)

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

CREATE TABLE #ResultTable (
	[DatabaseName] SYSNAME
	,[Status] INT
	,[Error] INT
	,[ErrorMessage]  nvarchar(max)
	,[GroupName] SYSNAME
	,[ReadOnly] BIT
	,[GroupType] nvarchar(2)
	,[TypeDesc]  nvarchar(60)
	)

DECLARE fileCursor CURSOR LOCAL FOR 
SELECT 
	[name] 
	FROM sys.databases
	--Where HAS_DBACCESS(name) = 1

OPEN fileCursor;
FETCH NEXT FROM fileCursor INTO @dbName;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT 
		DB_NAME() AS [DatabaseName]
		,HAS_DBACCESS(DB_NAME()) as Status
		,0 as [Error]
		,'''' as [ErrorMessage] 
		,fg.[name] AS [GroupName]
		,fg.is_read_only AS [ReadOnly]
		,fg.[type] AS [GroupType]
		,fg.type_desc AS [TypeDesc]
	FROM sys.filegroups fg WITH (NOLOCK)
	';
	
	BEGIN TRY
		INSERT INTO #ResultTable
		EXEC sp_executesql @sql
		
	END TRY
	BEGIN CATCH
		INSERT INTO #ResultTable
		SELECT 
		@dbName as [DatabaseName]
		,HAS_DBACCESS(@dbName) as Status
		,ERROR_NUMBER() as [Error]
		,ERROR_MESSAGE() as [ErrorMessage] 
		,'' AS [GroupName]
		,0 AS [ReadOnly]
		,'' AS [GroupType]
		,'' AS [TypeDesc]
			
	END CATCH;
	
	FETCH NEXT FROM fileCursor INTO @dbName;
END

CLOSE fileCursor;
DEALLOCATE fileCursor;

SELECT * FROM #ResultTable



--ReadErrorLogArchives
IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;

create table #ResultTable(
ArchiveNo int, 
LastWriteDate datetime, 
Size int)

insert #ResultTable exec master.dbo.sp_enumerrorlogs @LogSourceId

SELECT
*
FROM
#ResultTable er
ORDER BY
[ArchiveNo] ASC

drop table #ResultTable



--GetFullTextServiceStatus
SELECT TOP 1
	startup_type AS [StartupType]
	,[status] AS [Status]
FROM sys.dm_server_services 
WHERE [filename] LIKE '"%\Binn\fdlauncher.exe" -s %'



--LogShippingStatus
WITH 
ls_databases AS (
	SELECT 
		0 AS lstype
		,lspd.primary_id AS agent_id
		,lsmp.last_backup_date AS last_operation_date
		,DATEDIFF(minute, lsmp.last_backup_date, getdate()) AS last_operation_date_diff
		,lspd.primary_database AS useddatabase
		,lsmp.backup_threshold AS event_thresold
		,lsmp.threshold_alert_enabled AS event_thresold_enabled
		,0 AS latency
	FROM msdb.dbo.log_shipping_primary_databases lspd
		INNER JOIN msdb.dbo.log_shipping_monitor_primary lsmp ON lspd.primary_id = lsmp.primary_id
	
	UNION
	
	SELECT 
		1 AS lstype
		,lssd.secondary_id AS agent_id
		,lsms.last_restored_date AS last_operation_date
		,DATEDIFF(minute, lsms.last_restored_date, getdate()) AS last_operation_date_diff
		,lssd.secondary_database AS useddatabase
		,lsms.restore_threshold AS event_thresold
		,lsms.threshold_alert_enabled AS event_thresold_enabled
		,lsms.last_restored_latency AS latency
	FROM msdb.dbo.log_shipping_secondary_databases lssd
		INNER JOIN msdb.dbo.log_shipping_monitor_secondary lsms ON lssd.secondary_id = lsms.secondary_id
)
,min_date AS (
	SELECT MIN(lsd.last_operation_date) AS min_date
	FROM ls_databases lsd
)
,session_mapping AS (
	SELECT 
		lsd.useddatabase
		,lsd.agent_id
		,MAX(ds.session_id) AS session_id
	FROM ls_databases lsd
		INNER JOIN msdb.dbo.log_shipping_monitor_history_detail ds ON lsd.agent_id = ds.agent_id
	WHERE ds.log_time >= lsd.last_operation_date
	GROUP BY lsd.useddatabase, lsd.agent_id
)
,log_shipping_history_data AS (
	SELECT 
		sm.useddatabase
		,d.session_id
		,d.session_status
		,d.agent_id
		,d.message
	FROM session_mapping sm
		INNER JOIN msdb.dbo.log_shipping_monitor_history_detail d ON sm.session_id = d.session_id AND sm.agent_id = d.agent_id
	WHERE d.log_time >= (SELECT min_date FROM min_date m) 
		AND d.session_status IN (2, 3) AND d.agent_type IN (0, 1, 2)
)
,filtered_message_table AS (
	SELECT DISTINCT 
		lshd.useddatabase AS DatabaseName
		,lshd.session_id AS SessionID
		,lshd.session_status AS SessionStatus
		,lshd.agent_id AS AgentId
		,lsd.last_operation_date AS [LastOperationDate]
		,lsd.last_operation_date_diff AS [LastOperationDateDiffMinutes]
		,lsd.event_thresold AS [EventThresold]
		,lsd.event_thresold_enabled AS [EventThresoldEnabled]
		,lsd.latency AS [Latency]
		,lsd.lstype AS [LogShippingType]
		,lshd.message AS [Message]
		,CAST(0 AS BIT) AS [isGood]
	FROM log_shipping_history_data lshd
		INNER JOIN session_mapping sm ON sm.session_id = lshd.session_id
		INNER JOIN ls_databases lsd ON lsd.agent_id = lshd.agent_id
)
SELECT *
FROM filtered_message_table mt;




--GetLongRunningJobs
DECLARE @xmlDoc XML;

SET @xmlDoc = CAST(@includedJobsXmlString AS XML);

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

CREATE TABLE #included_jobs (
	job_name SYSNAME COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
);

INSERT INTO #included_jobs(job_name)
	SELECT DISTINCT xc.value('.', 'SYSNAME') 
	FROM @xmlDoc.nodes('/*/*') as xt(xc);

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

CREATE TABLE #job_activity (
	[session_id] INT NOT NULL
	,[job_id] UNIQUEIDENTIFIER NOT NULL
	,[job_name] SYSNAME COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
	,[run_requested_date] DATETIME NULL
	,[run_requested_source] SYSNAME COLLATE SQL_Latin1_General_CP1_CS_AS NULL
	,[queued_date] DATETIME NULL
	,[start_execution_date] DATETIME NULL
	,[last_executed_step_id] INT NULL
	,[last_exectued_step_date] DATETIME NULL
	,[stop_execution_date] DATETIME NULL
	,[next_scheduled_run_date] DATETIME NULL
	,[job_history_id] INT NULL
	,[message] NVARCHAR(1024) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
	,[run_status] INT NULL
	,[operator_id_emailed] INT NULL
	,[operator_id_netsent] INT NULL
	,[operator_id_paged] INT NULL
	,[execution_time_minutes] INT NULL
	);

INSERT INTO #job_activity (
	[session_id]
	,[job_id]
	,[job_name]
	,[run_requested_date]
	,[run_requested_source]
	,[queued_date]
	,[start_execution_date]
	,[last_executed_step_id]
	,[last_exectued_step_date]
	,[stop_execution_date]
	,[next_scheduled_run_date]
	,[job_history_id]
	,[message]
	,[run_status]
	,[operator_id_emailed]
	,[operator_id_netsent]
	,[operator_id_paged]
	)
EXECUTE [msdb].[dbo].[sp_help_jobactivity];

WITH cs_cte AS(
	SELECT DISTINCT
		job_id
	FROM
		msdb.dbo.sysjobschedules sjs
		join msdb.dbo.sysschedules ss on ss.schedule_id = sjs.schedule_id
	WHERE ss.freq_type = 64
)
SELECT 
	[ja].[job_name] AS [JobName]
	,DATEDIFF(minute, [start_execution_date], GETDATE()) AS [ExecutionTimeMinutes]
FROM #job_activity [ja]
LEFT JOIN cs_cte ON ja.job_id = cs_cte.job_id
LEFT JOIN #included_jobs ij ON ij.job_name COLLATE SQL_Latin1_General_CP1_CS_AS = ja.job_name
WHERE [start_execution_date] IS NOT NULL AND [run_status] IS NULL 
AND (cs_cte.job_id IS NULL OR ij.job_name IS NOT NULL);

DROP TABLE #included_jobs;
DROP TABLE #job_activity;



--CPUUtilization
DECLARE @interval_sec BIGINT = 120;
DECLARE @lower_boundary BIGINT;

SELECT 
	@lower_boundary = ms_ticks - @interval_sec * 1000
FROM sys.dm_os_sys_info;

SELECT 
	xml_rec.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [CpuUtilization]
FROM (
	SELECT TOP 1 
		[timestamp]
		,convert(XML, record) AS xml_rec
	FROM sys.dm_os_ring_buffers
	WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
		AND record LIKE N'%<SystemHealth>%' 
		AND [timestamp] >= @lower_boundary
	ORDER BY [timestamp] DESC
	) AS raw_data;



--DBActiveSessionsCount
SELECT 
	DB_NAME(db.database_id) AS [DatabaseName],
	ISNULL(info.NSessions, 0) AS [NSessions]
FROM 
sys.databases AS db
LEFT JOIN
(
	SELECT database_id, COUNT(database_id) AS [NSessions]
	FROM sys.dm_exec_sessions
	WHERE database_id > 0
	GROUP BY database_id
) AS info
ON db.database_id = info.database_id



--XTPConfiguration
DECLARE @dbName sysname, @sql nvarchar(2000), @poolId INT;

IF OBJECT_ID ('tempdb.dbo.#XTPConfResultTable') IS NOT NULL DROP TABLE #XTPConfResultTable;
CREATE TABLE #XTPConfResultTable
(
	[DatabaseId] INT
	,[DatabaseName] SYSNAME
	,[PoolID] INT
	,[HekatonCount] INT
	,[MinMemory] INT	
	,[MaxMemory] INT
	,[PoolDatabaseCount] INT
);

IF @_isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,d.resource_pool_id as [pool]
	FROM sys.databases d
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb') 
		AND HAS_DBACCESS(d.[name]) = 1;
END
ELSE
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.[name]
		,d.resource_pool_id as [pool]
	FROM sys.databases d
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		INNER JOIN master.sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL 
		AND d.[name] not in ('master', 'model', 'msdb', 'tempdb')
		AND HAS_DBACCESS(d.[name]) = 1 
		AND dhars.[role] = 1 
		AND ar.primary_role_allow_connections = 3;
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @poolId;

WHILE @@Fetch_Status=0 BEGIN
	SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
	SELECT 
	DB_ID() AS [DatabaseId]
	,DB_NAME() AS [DatabaseName]
	,ISNULL(@poolId, 0) AS [PoolID]
	,[HekatonCount] = (SELECT COUNT(object_id) FROM sys.tables WHERE is_memory_optimized = 1)
	,[MinMemory] = ISNULL((SELECT min_memory_percent FROM sys.dm_resource_governor_resource_pools WHERE pool_id = @poolId), 0)
	,[MaxMemory] = ISNULL((SELECT max_memory_percent FROM sys.dm_resource_governor_resource_pools WHERE pool_id = @poolId), 0)
	,[PoolDatabaseCount] = (SELECT Count(d.database_id) FROM sys.databases d WHERE resource_pool_id = @poolId)'

	BEGIN TRY
		INSERT INTO #XTPConfResultTable
		EXEC sp_executesql @sql, N'@poolId INT', @poolId = @poolId
	END TRY
	BEGIN CATCH
	END CATCH;

	FETCH NEXT FROM dbCursor INTO @dbName, @poolId;		
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * 
FROM #XTPConfResultTable 
WHERE [HekatonCount] > 0;

DROP TABLE #XTPConfResultTable;

IF @_isReadOnlyMode = 1
BEGIN
	SELECT COUNT(*) AS ReadWriteDbCount
	FROM master.sys.availability_replicas ar
		INNER JOIN master.sys.dm_hadr_availability_replica_states dhars ON ar.replica_id = dhars.replica_id
	WHERE dhars.[role] = 1 AND ar.primary_role_allow_connections = 3;
END;



--GetUserPolicies
SELECT 
	p.name AS [PolicyName], 
	h.end_date AS [EndDate], 
	hd.target_query_expression AS [TargetQueryExpression], 
	hd.result AS [Result], 
	CAST(LEFT(c.facet, 4000) as nvarchar(4000)) as [Facet]
FROM msdb.dbo.syspolicy_policies p 
JOIN msdb.dbo.syspolicy_conditions c ON c.condition_id = p.condition_id 
LEFT JOIN msdb.dbo.syspolicy_policy_execution_history h ON h.policy_id = p.policy_id 
LEFT JOIN msdb.dbo.syspolicy_policy_execution_history_details hd ON hd.history_id = h.history_id 
JOIN ( 
	SELECT 
		p.policy_id, 
		MAX(h.history_id) AS last_history_id 
	FROM msdb.dbo.syspolicy_policies p 
	JOIN msdb.dbo.syspolicy_conditions c ON c.condition_id = p.condition_id 
	LEFT JOIN msdb.dbo.syspolicy_policy_execution_history h ON h.policy_id = p.policy_id 
	INNER JOIN ( 
		SELECT 
			p.policy_id, 
			MAX(h.end_date) AS last_date  
		FROM msdb.dbo.syspolicy_policies p  
		JOIN msdb.dbo.syspolicy_conditions c ON c.condition_id = p.condition_id 
		LEFT JOIN msdb.dbo.syspolicy_policy_execution_history h ON h.policy_id = p.policy_id 
		WHERE p.is_system = 0 AND c.facet IN ('AvailabilityGroup', 'AvailabilityReplica', 'DatabaseReplicaState', 'Database') 
		GROUP BY p.policy_id  
	) pld ON pld.policy_id = p.policy_id AND (pld.last_date = h.end_date OR (pld.last_date IS NULL AND h.end_date IS NULL)) 
	GROUP BY p.policy_id 
) plh ON plh.policy_id = p.policy_id AND (plh.last_history_id = h.history_id OR (plh.last_history_id IS NULL AND h.history_id IS NULL)) 



--ReadErrorLog
if @DontUseStartDate = 1
begin
set @StartDate = null
end

if @DontUseEndDate = 1
begin
set @EndDate = null
end


EXEC xp_readerrorlog @NumLog, @LogSourceId, NULL, NULL, @StartDate, @EndDate, @Sort
← →

Leave a Reply

Your email address will not be published. Required fields are marked *