Database Health Check – Instance Level

USE [master]
GO

--1: SQL Server and operating system information
SELECT '--1: SQL Server and operating system information'
SELECT @@VERSION AS [SQLServer&OSVersionInfo];

--2: Hardware information
--SQL SERVER 2014 and higher
SELECT '--2: Hardware information'
Declare @InstantFileInitializationEnabled nvarchar(10) = 'Unknown'
IF SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')),0,CHARINDEX('.',CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion')))) >=13
BEGIN
EXEC sp_executesql N'select @IFI = instant_file_initialization_enabled from sys.dm_server_services where servicename like ''SQL Server (%''',
N'@IFI nvarchar(10) OUTPUT',@IFI=@InstantFileInitializationEnabled OUTPUT;
END

SELECT cpu_count AS [LogicalCPUCount], hyperthread_ratio AS [HyperthreadRatio],cpu_count/hyperthread_ratio AS [PhysicalCPUCount],
physical_memory_kb/1024 AS [PhysicalMemory(MB)],affinity_type_desc as [CPUAffinityType],
virtual_machine_type_desc as [VirtualMachineType],sqlserver_start_time as [LastStartTime],@InstantFileInitializationEnabled as InstantFileInitializationEnabled
FROM sys.dm_os_sys_info WITH (NOLOCK);

--3: Instance configuration values
-- Get configuration values for instance
SELECT '--3: Instance configuration values'
SELECT name as [ConfigName], value as [ConfigValue], value_in_use as [ConfigValueInUse], minimum, maximum, [description] as [ConfigDescription],
is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
-- Focus on
-- Automatic soft-NUMA disabled (should be 0 in most cases)
-- backup compression default (should be 1 in most cases)
-- Backup checksum default (should be 1)
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism (recommended setting is zero)
-- max server memory (MB) (set to an appropriate value) Should be set to the maximum amount of memory, in MB, that is allocated to the SQL instance. E.g. if 256GB is allocated to SQL, then set this value to 256000. Be sure to leave sufficient memory to the OS. On a server that is dedicated to SQL, at least 4GB is required for the OS to function. 
-- max worker threads (should be zero)
-- min server memory (MB) (set to an appropriate value) Should be set to the maximum amount of memory, in MB, that is allocated to SQL instance. E.g. if 256GB is allocated to SQL, then set this value to 256000. This value should be equal to the “Max Server Memory” value. 
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)

--4: Database filenames, paths and growth
-- File Names and Paths for Tempdb and all user databases in instance
SELECT '--4: Database filenames, paths and growth'
SELECT DB_NAME([database_id])AS [DatabaseName], [file_id] as FileID, name as [DataFileName], physical_name as [PhysicalPath],
type_desc as [FileType],state_desc as [DatabaseState], CONVERT( bigint, size/128.0) AS [TotalSize(MB)],
Case when is_percent_growth = 0 Then CONVERT(varchar(10), CONVERT(bigint, growth/128.0)) + ' MB'
else CONVERT(varchar(10), growth) + '%' END AS [GrowthSize(MB)]
FROM sys.master_files WITH (NOLOCK)
WHERE ([database_id] > 4 AND [database_id] <> 32767) OR [database_id] = 2
ORDER BY DB_NAME([database_id]);
-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?
-- Are there percentage growth?

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

--6: Login count information
-- Get logins that are connected and how many sessions they have
SELECT '--6: Login count information'
SELECT DB_NAME(database_id) as DBName,login_name as LoginName, COUNT(session_id) AS [SessionCount]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY DB_NAME(database_id),login_name
ORDER BY DB_NAME(database_id),COUNT(session_id) DESC;
-- This can help characterize the workload

SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name, 
COUNT(ec.session_id) AS [connection count] 
FROM sys.dm_exec_sessions AS es WITH (NOLOCK) 
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
ON es.session_id = ec.session_id 
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name 
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);

--7: Memory information
SELECT '--7: Memory information'
SELECT total_physical_memory_kb/1024 as [TotalPhysicalMemory(MB)], available_physical_memory_kb/1024 as [AvailablePhysicalMemory(MB)],
system_memory_state_desc as [SystemMemoryState]
FROM sys.dm_os_sys_memory WITH (NOLOCK);
-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure

SELECT physical_memory_in_use_kb/1024 as [SQL Server Memory Usage (MB)], memory_utilization_percentage as [MemoryUtilization%],
process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK);
-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure

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

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

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