Database Health Check – Database Level

--1: Database property information
SELECT '--1: Database property information'
SELECT db.[name] AS [DatabaseName], db.recovery_model_desc AS [RecoveryModel], db.log_reuse_wait_desc AS [LogReuseWaitDescription],
db.[compatibility_level] AS [CompatibilityLevel],db.is_auto_create_stats_on,is_auto_create_stats_incremental_on,
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced, db.snapshot_isolation_state_desc,
db.is_read_committed_snapshot_on, is_auto_shrink_on, is_auto_close_on,db.is_ansi_null_default_on,db.is_ansi_nulls_on,db.is_ansi_padding_on,
db.is_ansi_warnings_on,db.is_trustworthy_on,db.is_broker_enabled, db.log_reuse_wait_desc
FROM sys.databases AS db WITH (NOLOCK)
WHERE db.database_id = db_id();

If (SELECT Compatibility_level FROM sys.databases WHERE database_id = DB_ID()) >=130
BEGIN
SELECT name as ConfiguratioName, [value] AS ConfiguratioValue
FROM sys.database_scoped_configurations WITH (NOLOCK)
END

--2: Database file size and space available
SELECT '--2: Database file size and space available'
SELECT name AS [FileName], physical_name AS [PhysicalPath], size/128 AS [TotalSize(MB)], Convert(bigint,FILEPROPERTY(name, 'SpaceUsed'))/128 AS [UsedSpace(MB)],
Convert(decimal(9,2),100 * (Convert(bigint,FILEPROPERTY(name, 'SpaceUsed'))/128.0)/(size/128.0)) AS [UsedSpace(%)]
FROM sys.database_files WITH (NOLOCK);

--3: Last statistics update for all indexes
SELECT '--3: Last statistics update for all indexes'
SELECT o.name as TableName, i.name AS IndexName,STATS_DATE(i.[object_id], i.index_id) AS [StatisticsDate], st.row_count as RowCounts
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id] AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

--4: Indexes Fragmentation
-- Note: This could take some time on a very large database
SELECT '--4: Indexes Fragmentation'
SELECT DB_NAME(database_id) AS [DatabaseName],OBJECT_NAME(ps.OBJECT_ID) AS [ObjectName],i.name AS [IndexName], ps.index_id as IndexID, index_type_desc as IndexType,
avg_fragmentation_in_percent as [AvgFragmentation(%)], fragment_count as FragmentationCount, page_count as PageCount
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID() AND page_count > 500 and avg_fragmentation_in_percent > 20
ORDER BY avg_fragmentation_in_percent DESC;

--5: Cached queries and SPs
SELECT '--5: Cached queries and SPs'
IF Substring(Convert(nvarchar(128),SERVERPROPERTY('ProductVersion')),1,CHARINDEX('.',Convert(nvarchar(128),SERVERPROPERTY('ProductVersion')))-1) = 12
and Convert(nvarchar(128),SERVERPROPERTY('ProductLevel')) < 'SP2'
BEGIN
EXEC sp_executesql N'SELECT qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;'
END
ELSE
BEGIN
EXEC sp_executesql N'SELECT qs.execution_count, total_grant_kb/execution_count as [GrantMemory/Execution(KB)],qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;'
END

SELECT p.name AS [SP Name], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.cached_time, qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites]
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC;