--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;
--GetTdeEncryptionKeyState
SELECT
DB_NAME(dek.database_id) AS DatabaseName,
pvt_key_last_backup_date AS KeyLastBackupDate,
c.name AS CertificateName
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint
--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 OR 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,
COALESCE(db.name, N'') AS db_name,
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
LEFT OUTER JOIN sys.databases db on S.database_id = db.database_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.db_name AS database_name,
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
--GetDBEngineDiskLatency
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
[database_id],
[file_id],
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
ovs.volume_mount_point AS [MountPoint],
CAST(MAX([ReadLatency]) AS FLOAT(53)) AS [ReadLatency],
CAST(MAX([WriteLatency]) AS FLOAT(53)) AS [WriteLatency]
FROM [CounterCalc]
CROSS APPLY sys.dm_os_volume_stats([database_id], [file_id]) ovs
GROUP BY ovs.volume_mount_point
IF OBJECT_ID('tempdb..#VfStatSnapshot1') IS NOT NULL
DROP TABLE [#VfStatSnapshot1];
IF OBJECT_ID('tempdb..#VfStatSnapshot2') IS NOT NULL
DROP TABLE [#VfStatSnapshot2];
--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
DECLARE @session_id INT = NULL;
DECLARE @agentStartDate DATETIME = NULL;
SELECT TOP(1)
@session_id = session_id,
@agentStartDate = agent_start_date
FROM
msdb.dbo.syssessions
ORDER by agent_start_date DESC;
-- Possible values of field run_status in table sysjobhistory
-- 0 = Failed
-- 1 = Succeeded
-- 2 = Retry
-- 3 = Canceled
-- 4 = In Progress
-- In the current implementation we deal with step 0 (outcome) only.
-- This step may contain one of the following values: 0, 1, 3
;WITH sm AS (
SELECT
job_id,
MAX(instance_id) AS max_completed_id
FROM msdb.dbo.sysjobhistory jh
WHERE step_id = 0 AND (@onlyCurrentSession = 0 OR msdb.dbo.agent_datetime(jh.run_date, jh.run_time) > @agentStartDate)
GROUP BY job_id
)
SELECT
jv.name AS JobName,
CASE
WHEN jh.run_status IS NOT NULL THEN jh.run_status
ELSE -1
END AS LastCompletedRunStatus,
CASE
WHEN jh.run_status IS NOT NULL AND (@interpretCancelledAsFailed = 0 OR jh.run_status != 3) THEN jh.run_status
WHEN jh.run_status = 3 AND @interpretCancelledAsFailed = 1 THEN 0
ELSE -1
END AS LastCompletedRunStatusMon,
jh.message AS LastCompletedRunMessage,
CAST(CASE
WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1
ELSE 0
END AS BIT) AS IsExecuting,
fs.failed_count AS ConsecutiveFailCount
FROM
msdb.dbo.sysjobactivity ja
LEFT JOIN sm on ja.job_id = sm.job_id
LEFT JOIN msdb.dbo.sysjobhistory jh ON sm.max_completed_id = jh.instance_id
INNER JOIN msdb.dbo.sysjobs_view jv ON ja.job_id = jv.job_id
OUTER APPLY (
SELECT COUNT(*) AS failed_count
FROM (
SELECT TOP (@numberOfConsecutiveFails) jh.run_status
FROM msdb.dbo.sysjobhistory jh
WHERE step_id = 0 AND job_id = ja.job_id AND (@onlyCurrentSession = 0 OR msdb.dbo.agent_datetime(jh.run_date, jh.run_time) > @agentStartDate)
ORDER BY instance_id DESC
) src
WHERE run_status = 0 OR (@interpretCancelledAsFailed = 1 AND run_status = 3)
) fs
WHERE ja.session_id = @session_id;
--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]
--AvailabilityDBLogBackupStatus
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 AND b.[type] = 'L'
WHERE
d.is_in_standby = 0
AND source_database_id is null
AND d.name <> 'tempdb'
AND d.replica_id IS NOT NULL
GROUP BY
d.name, d.create_date, d.recovery_model
--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;
--LongRunningQueries
SELECT TOP (@TopQueries)
CONVERT(VARCHAR(128), r.sql_handle, 1) AS [SqlHandle],
CONVERT(VARCHAR(16), r.query_hash, 1) AS [QueryHash],
r.session_id AS [SessionId],
d.name AS [DatabaseName],
r.status AS [RequestStatus],
r.command AS [Command],
r.cpu_time AS [CpuTimeMs],
r.total_elapsed_time AS [TotalElapsedTimeMs],
r.wait_type AS [WaitType],
s.program_name AS [ApplicationName]
-- s.host_name AS [HostName]
FROM sys.dm_exec_requests r
JOIN sys.databases d ON r.database_id = d.database_id
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE sql_handle IS NOT NULL AND r.session_id != @@SPID AND r.total_elapsed_time >= @DurationThresholdMs AND <<#EXCLUDE_FILTER#>>
ORDER BY r.total_elapsed_time DESC;
--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
DECLARE @session_id INT = NULL;
SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC;
SELECT
sj.name AS [JobName],
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
msdb.dbo.sysjobactivity AS ja
INNER JOIN msdb.dbo.sysjobs AS sj ON ja.job_id = sj.job_id
WHERE ja.session_id = @session_id AND
(@onlyRunningJobs = 0 OR (ja.start_execution_date IS NOT NULL AND ja.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];
--ClusteredIndexFragmentation
DECLARE @dbName sysname, @sql nvarchar(2000);
IF OBJECT_ID ('tempdb.dbo.#ClusterIndexFragmentationResultTable') IS NOT NULL DROP TABLE #ClusterIndexFragmentationResultTable;
CREATE TABLE #ClusterIndexFragmentationResultTable (
[DatabaseId] INT
,[DatabaseName] SYSNAME
,[IndexList] NVARCHAR(MAX)
,[IndexCount] INT
,[MaxFragmentationPercent] FLOAT
);
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'[') + N';
DECLARE @IndexList nvarchar(MAX);
DECLARE @IndexCount int = 0;
DECLARE @MaxFragmentationPercent int = 0;
WITH ind_info AS(
SELECT
CONCAT(SCHEMA_NAME(t.schema_id), ''.'', t.[name], ''.'', i.[name]) AS IndexFullName,
stat.fragmentation,
stat.page_count
FROM sys.indexes AS i WITH (NOLOCK)
INNER JOIN sys.tables AS t WITH (NOLOCK) ON t.[object_id] = i.[object_id] AND t.is_ms_shipped = 0
INNER JOIN (
SELECT
ps.[object_id],
ps.index_id,
SUM(ps.avg_fragmentation_in_percent) AS fragmentation,
SUM(ps.page_count) AS page_count
FROM sys.indexes i2 WITH (NOLOCK)
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i2.object_id, i2.index_id, NULL, ''SAMPLED'') ps
WHERE ps.index_level = 0 AND ps.alloc_unit_type_desc = ''IN_ROW_DATA'' AND i2.type = 1
GROUP BY ps.[object_id], ps.index_id
) stat ON stat.index_id = i.index_id AND stat.[object_id] = i.[object_id]
WHERE i.[type] = 1
AND page_count > @page_count_threshold AND fragmentation >= @fragmentation_percent_threshold
),
aggr_stats AS(
SELECT
COUNT(*) AS IndexCount,
MAX(fragmentation) AS MaxFragmentationPercent
FROM ind_info
)
SELECT TOP (@n_top)
@IndexList = COALESCE(@IndexList + N'', '',N'''') + ind_info.IndexFullName, @IndexCount = IndexCount, @MaxFragmentationPercent = MaxFragmentationPercent
FROM ind_info, aggr_stats
ORDER BY ind_info.fragmentation DESC
INSERT INTO #ClusterIndexFragmentationResultTable
SELECT DB_ID(), DB_NAME(), @IndexList, @IndexCount, @MaxFragmentationPercent';
BEGIN TRY
EXEC sp_executesql @sql,N'@page_count_threshold INT, @fragmentation_percent_threshold INT, @n_top INT', @page_count_threshold = @PageCountThreshold, @fragmentation_percent_threshold = @FragmentationPercentThreshold, @n_top = @IndexTopLines
END TRY
BEGIN CATCH
END CATCH;
FETCH NEXT FROM dbCursor INTO @dbName;
END
CLOSE dbCursor;
DEALLOCATE dbCursor;
SELECT * FROM #ClusterIndexFragmentationResultTable;
DROP TABLE #ClusterIndexFragmentationResultTable;
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;
--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(*) AS ThreadCount
FROM sys.dm_os_threads dot
INNER JOIN sys.dm_os_schedulers dos
ON dot.scheduler_address = dos.scheduler_address
WHERE dos.status = 'VISIBLE ONLINE') 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