-- SQL and OS Version information for current instance (Query 1) (Version Info)

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];

------

-- Get selected server properties (Query 3) (Server Properties)

SELECT SERVERPROPERTY('MachineName') AS [MachineName],

SERVERPROPERTY('ServerName') AS [ServerName],

SERVERPROPERTY('InstanceName') AS [Instance],

SERVERPROPERTY('IsClustered') AS [IsClustered],

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],

SERVERPROPERTY('Edition') AS [Edition],

SERVERPROPERTY('ProductLevel') AS [ProductLevel], -- What servicing branch (RTM/SP/CU)

SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], -- Within a servicing branch, what CU# is applied

SERVERPROPERTY('ProductVersion') AS [ProductVersion],

SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],

SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],

SERVERPROPERTY('ProductBuild') AS [ProductBuild],

SERVERPROPERTY('ProductBuildType') AS [ProductBuildType], -- Is this a GDR or OD hotfix (NULL if on a CU build)

SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build

SERVERPROPERTY('ProcessID') AS [ProcessID],

SERVERPROPERTY('Collation') AS [Collation],

SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],

SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],

SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],

SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],

SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],

SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],

SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],

SERVERPROPERTY('ErrorLogFileName') AS [ErrorLogFileName],

SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version],

SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],

SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],

SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled],

SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS [IsTempdbMetadataMemoryOptimized];

------

-- Get instance-level configuration values for instance (Query 4) (Configuration Values)

SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced

FROM sys.configurations WITH (NOLOCK)

ORDER BY name OPTION (RECOMPILE);

------

-- SQL Server Process Address space info (Query 6) (Process Memory)

-- (shows whether locked pages is enabled, among other things)

SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],

locked_page_allocations_kb/1024 AS [SQL Server Locked Pages Allocation (MB)],

large_page_allocations_kb/1024 AS [SQL Server Large Pages Allocation (MB)],

page_fault_count, memory_utilization_percentage, available_commit_limit_kb,

process_physical_memory_low, process_virtual_memory_low

FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

------

-- SQL Server Services information (Query 7) (SQL Server Services Info)

SELECT servicename, process_id, startup_type_desc, status_desc,

last_startup_time, service_account, is_clustered, cluster_nodename, [filename],

instant_file_initialization_enabled

FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

------

-- Get SQL Server Agent jobs and Category information (Query 9) (SQL Server Agent Jobs)

SELECT sj.name AS [Job Name], sj.[description] AS [Job Description], SUSER_SNAME(sj.owner_sid) AS [Job Owner],

sj.date_created AS [Date Created], sj.[enabled] AS [Job Enabled],

sj.notify_email_operator_id, sj.notify_level_email, sc.name AS [CategoryName],

s.[enabled] AS [Sched Enabled], js.next_run_date, js.next_run_time

FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)

INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)

ON sj.category_id = sc.category_id

LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)

ON sj.job_id = js.job_id

LEFT OUTER JOIN msdb.dbo.sysschedules AS s WITH (NOLOCK)

ON js.schedule_id = s.schedule_id

ORDER BY sj.name OPTION (RECOMPILE);

------

-- Get SQL Server Agent Alert Information (Query 10) (SQL Server Agent Alerts)

SELECT name, event_source, message_id, severity, [enabled], has_notification,

delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time

FROM msdb.dbo.sysalerts WITH (NOLOCK)

ORDER BY name OPTION (RECOMPILE);

------

-- Host information (Query 11) (Host Info)

SELECT host_platform, host_distribution, host_release,

host_service_pack_level, host_sku, os_language_version,

host_architecture

FROM sys.dm_os_host_info WITH (NOLOCK) OPTION (RECOMPILE);

------

-- SQL Server NUMA Node information (Query 12) (SQL Server NUMA Info)

SELECT osn.node_id, osn.node_state_desc, osn.memory_node_id, osn.processor_group, osn.cpu_count, osn.online_scheduler_count,

osn.idle_scheduler_count, osn.active_worker_count,

osmn.pages_kb/1024 AS [Committed Memory (MB)],

osmn.locked_page_allocations_kb/1024 AS [Locked Physical (MB)],

CONVERT(DECIMAL(18,2), osmn.foreign_committed_kb/1024.0) AS [Foreign Commited (MB)],

osmn.target_kb/1024 AS [Target Memory Goal (MB)],

osn.avg_load_balance, osn.resource_monitor_state

FROM sys.dm_os_nodes AS osn WITH (NOLOCK)

INNER JOIN sys.dm_os_memory_nodes AS osmn WITH (NOLOCK)

ON osn.memory_node_id = osmn.memory_node_id

WHERE osn.node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

------

-- Good basic information about OS memory amounts and state (Query 13) (System Memory)

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],

available_physical_memory_kb/1024 AS [Available Memory (MB)],

total_page_file_kb/1024 AS [Page File Commit Limit (MB)],

total_page_file_kb/1024 - total_physical_memory_kb/1024 AS [Physical Page File Size (MB)],

available_page_file_kb/1024 AS [Available Page File (MB)],

system_cache_kb/1024 AS [System Cache (MB)],

system_memory_state_desc AS [System Memory State]

FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

------

-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)

SELECT cluster_name, quorum_type_desc, quorum_state_desc

FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);

------

-- Good overview of AG health and status (Query 16) (AG Status)

SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name],

drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant,

drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn,

drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn,

drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size,

drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate,

drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc

FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)

INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)

ON drs.group_id = adc.group_id

AND drs.group_database_id = adc.group_database_id

INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)

ON ag.group_id = drs.group_id

INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)

ON drs.group_id = ar.group_id

AND drs.replica_id = ar.replica_id

ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);

-- Get information on location, time and size of any memory dumps from SQL Server (Query 21) (Memory Dump Info)

SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]

FROM sys.dm_server_memory_dumps WITH (NOLOCK)

ORDER BY creation_time DESC OPTION (RECOMPILE);

------

-- Look at Suspect Pages table (Query 22) (Suspect Pages)

SELECT DB_NAME(database_id) AS [Database Name], [file_id], page_id,

event_type, error_count, last_update_date

FROM msdb.dbo.suspect_pages WITH (NOLOCK)

ORDER BY database_id OPTION (RECOMPILE);

------

-- Read most recent entries from all SQL Server Error Logs (Query 23) (Error Log Entries)

DROP TABLE IF EXISTS #ErrorLogFiles;

CREATE TABLE #ErrorLogFiles

([Archive #] INT,[Date] NVARCHAR(25),[Log File Size (Byte)]INT)

INSERT INTO #ErrorLogFiles

([Archive #],[Date],[Log File Size (Byte)])

EXEC master.sys.xp_enumerrorlogs;

DROP TABLE IF EXISTS #SQLErrorLog_AllLogs;

CREATE TABLE #SQLErrorLog_AllLogs

(LogDate DATETIME ,ProcessInfo NVARCHAR(12), LogText NVARCHAR(4000))

DECLARE @i INT = 0;

DECLARE @sql NVARCHAR(200) = N'';

DECLARE @logCount INT = (SELECT COUNT(*) FROM #ErrorLogFiles);

WHILE (@i < @logCount)

BEGIN

IF(@i in (SELECT [Archive #] FROM #ErrorLogFiles))

BEGIN

SET @sql = N'INSERT INTO #SQLErrorLog_AllLogs (LogDate, ProcessInfo, LogText)

EXEC master.sys.sp_readerrorlog ' + CAST(@i AS NVARCHAR(2)) + N';'

EXEC master.sys.sp_executesql @sql;

END

SET @i += 1;

END

SELECT TOP(1000)LogDate, ProcessInfo, LogText

FROM #SQLErrorLog_AllLogs WITH (NOLOCK)

ORDER BY LogDate DESC OPTION (RECOMPILE);

DROP TABLE IF EXISTS #ErrorLogFiles;

DROP TABLE IF EXISTS #SQLErrorLog_AllLogs;

GO

-- Get number of data files in tempdb database (Query 24) (TempDB Data Files)

EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';

------

------

-- Drive information for all fixed drives visible to the operating system (Query 26) (Fixed Drives)

SELECT fixed_drive_path, drive_type_desc,

CONVERT(DECIMAL(18,2), free_space_in_bytes/1073741824.0) AS [Available Space (GB)]

FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK) OPTION (RECOMPILE);

------

-- Volume info for all LUNS that have database files on the current instance (Query 27) (Volume Info)

SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name,

CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],

CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],

CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],

vs.supports_compression, vs.is_compressed,

vs.supports_sparse_files, vs.supports_alternate_streams

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

------

-- Drive level latency information (Query 28) (Drive Level Latency)

SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point],

CASE

WHEN num_of_reads = 0 THEN 0

ELSE (io_stall_read_ms/num_of_reads)

END AS [Read Latency],

CASE

WHEN num_of_writes = 0 THEN 0

ELSE (io_stall_write_ms/num_of_writes)

END AS [Write Latency],

CASE

WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0

ELSE (io_stall/(num_of_reads + num_of_writes))

END AS [Overall Latency],

CASE

WHEN num_of_reads = 0 THEN 0

ELSE (num_of_bytes_read/num_of_reads)

END AS [Avg Bytes/Read],

CASE

WHEN num_of_writes = 0 THEN 0

ELSE (num_of_bytes_written/num_of_writes)

END AS [Avg Bytes/Write],

CASE

WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0

ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes))

END AS [Avg Bytes/Transfer]

FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,

SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,

SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,

SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

INNER JOIN sys.master_files AS mf WITH (NOLOCK)

ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs

GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab

ORDER BY [Overall Latency] OPTION (RECOMPILE);

------

-- Calculates average latency per read, per write, and per total input/output for each database file (Query 29) (IO Latency by File)

SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_latency_ms],

CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_latency_ms],

CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_latency_ms],

CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads,

fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],

io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)]

FROM sys.dm_io_virtual_file_stats(null,null) AS fs

INNER JOIN sys.master_files AS mf WITH (NOLOCK)

ON fs.database_id = mf.database_id

AND fs.[file_id] = mf.[file_id]

ORDER BY avg_io_latency_ms DESC OPTION (RECOMPILE);

------

-- Look for I/O requests taking longer than 15 seconds in the six most recent SQL Server Error Logs (Query 30) (IO Warnings)

CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';

INSERT INTO #IOWarningResults

EXEC xp_readerrorlog 5, 1, N'taking longer than 15 seconds';

SELECT LogDate, ProcessInfo, LogText

FROM #IOWarningResults

ORDER BY LogDate DESC;

DROP TABLE #IOWarningResults;

------

-- Recovery model, log reuse wait description, log file size, log usage size (Query 32) (Database Properties)

-- and compatibility level for all databases on instance

SELECT db.[name] AS [Database Name], SUSER_SNAME(db.owner_sid) AS [Database Owner],

db.[compatibility_level] AS [DB Compatibility Level],

db.recovery_model_desc AS [Recovery Model],

db.log_reuse_wait_desc AS [Log Reuse Wait Description],

CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],

CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],

db.page_verify_option_desc AS [Page Verify Option], db.user_access_desc, db.state_desc, db.containment_desc,

db.is_mixed_page_allocation_on,

db.is_auto_create_stats_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, db.is_auto_close_on, db.is_auto_shrink_on,

db.target_recovery_time_in_seconds, db.is_cdc_enabled, db.is_published, db.is_distributor, db.is_sync_with_backup,

db.group_database_id, db.replica_id, db.is_memory_optimized_enabled, db.is_memory_optimized_elevate_to_snapshot_on,

db.delayed_durability_desc, db.is_query_store_on,

db.is_temporal_history_retention_enabled, db.is_remote_data_archive_enabled, db.is_accelerated_database_recovery_on,

db.is_master_key_encrypted_by_server, db.is_encrypted, de.encryption_state, de.percent_complete, de.key_algorithm, de.key_length

FROM sys.databases AS db WITH (NOLOCK)

LEFT OUTER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)

ON db.name = lu.instance_name

LEFT OUTER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)

ON db.name = ls.instance_name

LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK)

ON db.database_id = de.database_id

WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'

AND ls.counter_name LIKE N'Log File(s) Size (KB)%'

AND ls.cntr_value > 0

ORDER BY db.[name] OPTION (RECOMPILE);

------

-- Missing Indexes for all databases by Index Advantage (Query 33) (Missing Indexes All Databases)

SELECT CONVERT(decimal(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS [index_advantage],

FORMAT(migs.last_user_seek, 'yyyy-MM-dd HH:mm:ss') AS [last_user_seek], mid.[statement] AS [Database.Schema.Table],

COUNT(1) OVER(PARTITION BY mid.[statement]) AS [missing_indexes_for_table],

COUNT(1) OVER(PARTITION BY mid.[statement], mid.equality_columns) AS [similar_missing_indexes_for_table],

mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.user_seeks,

CONVERT(decimal(18,2), migs.avg_total_user_cost) AS [avg_total_user_,cost], migs.avg_user_impact,

REPLACE(REPLACE(LEFT(st.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text]

FROM sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_group_stats_query AS migs WITH(NOLOCK)

ON mig.index_group_handle = migs.group_handle

CROSS APPLY sys.dm_exec_sql_text(migs.last_sql_handle) AS st

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC OPTION (RECOMPILE);

------

-- Get VLF Counts for all databases on the instance (Query 34) (VLF Counts)

SELECT [name] AS [Database Name], [VLF Count]

FROM sys.databases AS db WITH (NOLOCK)

CROSS APPLY (SELECT file_id, COUNT(*) AS [VLF Count]

FROM sys.dm_db_log_info(db.database_id)

GROUP BY file_id) AS li

ORDER BY [VLF Count] DESC OPTION (RECOMPILE);

------

-- Get CPU utilization by database (Query 35) (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);

------

-- Get I/O utilization by database (Query 36) (IO 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 [ioTotalMB],

CAST(SUM(num_of_bytes_read ) / 1048576 AS DECIMAL(12, 2)) AS [ioReadMB],

CAST(SUM(num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [ioWriteMB]

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]

GROUP BY database_id)

SELECT ROW_NUMBER() OVER (ORDER BY ioTotalMB DESC) AS [I/O Rank],

[Database Name], ioTotalMB AS [Total I/O (MB)],

CAST(ioTotalMB / SUM(ioTotalMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Total I/O %],

ioReadMB AS [Read I/O (MB)],

CAST(ioReadMB / SUM(ioReadMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Read I/O %],

ioWriteMB AS [Write I/O (MB)],

CAST(ioWriteMB / SUM(ioWriteMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Write I/O %]

FROM Aggregate_IO_Statistics

ORDER BY [I/O Rank] OPTION (RECOMPILE);

------

-- Get total buffer usage by database for current instance (Query 37) (Total Buffer Usage by Database)

-- This may take some time to run on a busy instance with lots of RAM

WITH AggregateBufferPoolUsage

AS

(SELECT DB_NAME(database_id) AS [Database Name],

CAST(COUNT_BIG(*) * 8/1024.0 AS DECIMAL (15,2)) AS [CachedSize],

COUNT(page_id) AS [Page Count],

AVG(read_microsec) AS [Avg Read Time (microseconds)]

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

GROUP BY DB_NAME(database_id))

SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name],

CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent],

[Page Count], CachedSize AS [Cached Size (MB)], [Avg Read Time (microseconds)]

FROM AggregateBufferPoolUsage

ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);

------

-- Isolate top waits for server instance since last restart or wait statistics clear (Query 39) (Top Waits)

WITH [Waits]

AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],

(wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],

signal_wait_time_ms / 1000.0 AS [SignalS],

waiting_tasks_count AS [WaitCount],

100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]

FROM sys.dm_os_wait_stats WITH (NOLOCK)

WHERE [wait_type] NOT IN (

N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',

N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',

N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'CXCONSUMER',

N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',

N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',

N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT',

N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE',

N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',

N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',

N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',

N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',

N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',

N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',

N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',

N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',

N'PREEMPTIVE_OS_WRITEFILE', N'PREEMPTIVE_OS_WRITEFILEGATHER',

N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',

N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',

N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',

N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',

N'PWAIT_EXTENSIBILITY_CLEANUP_TASK',

N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',

N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',

N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',

N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',

N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',

N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SOS_WORK_DISPATCHER',

N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SOS_WORKER_MIGRATION', N'VDI_CLIENT_OTHER',

N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',

N'STARTUP_DEPENDENCY_MANAGER',

N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',

N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',

N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',

N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')

AND waiting_tasks_count > 0)

SELECT

MAX (W1.wait_type) AS [WaitType],

CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],

CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],

CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],

CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],

CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],

CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],

CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],

MAX (W1.WaitCount) AS [Wait Count],

CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.RowNum <= W1.RowNum

GROUP BY W1.RowNum, W1.wait_type

HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold

OPTION (RECOMPILE);

------

-- Get a count of SQL connections by IP address (Query 40) (Connection Counts by IP Address)

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);

------

-- Detect blocking (run multiple times) (Query 42) (Detect Blocking)

SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database],

t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], -- lock requested

t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], -- spid of waiter

(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK) -- get sql for waiter

CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])

WHERE r.session_id = t1.request_session_id) AS [waiter_batch],

(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,

(CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2

ELSE r.statement_end_offset END - r.statement_start_offset)/2)

FROM sys.dm_exec_requests AS r WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt

WHERE r.session_id = t1.request_session_id) AS [waiter_stmt], -- statement blocked

t2.blocking_session_id AS [blocker sid], -- spid of blocker

(SELECT [text] FROM sys.sysprocesses AS p -- get sql for blocker

CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])

WHERE p.spid = t2.blocking_session_id) AS [blocker_batch]

FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)

INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)

ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

------

-- Get top total worker time queries for entire instance (Query 44) (Top Worker Time Queries)

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],

REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],

qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time],

qs.max_worker_time AS [Max Worker Time],

qs.min_elapsed_time AS [Min Elapsed Time],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],

qs.max_elapsed_time AS [Max Elapsed Time],

qs.min_logical_reads AS [Min Logical Reads],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.max_logical_reads AS [Max Logical Reads],

qs.execution_count AS [Execution Count],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

qs.creation_time AS [Creation Time]

--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

------

-- Memory Clerk Usage for instance (Query 47) (Memory Clerk Usage)

-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)

SELECT TOP(10) mc.[type] AS [Memory Clerk Type],

CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]

FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)

GROUP BY mc.[type]

ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);

------

-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 48) (Ad hoc Queries)

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], t.[text] AS [Query Text],

cp.objtype AS [Object Type], cp.cacheobjtype AS [Cache Object Type],

cp.size_in_bytes/1024 AS [Plan Size in KB]

FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

WHERE cp.cacheobjtype = N'Compiled Plan'

AND cp.objtype IN (N'Adhoc', N'Prepared')

AND cp.usecounts = 1

ORDER BY cp.size_in_bytes DESC, DB_NAME(t.[dbid]) OPTION (RECOMPILE);

------

-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache

-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)

-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this

-- Enabling forced parameterization for the database can help, but test first!

-- Plan cache, adhoc workloads and clearing the single-use plan cache bloat

-- https://bit.ly/2EfYOkl

-- Get top total logical reads queries for entire instance (Query 49) (Top Logical Reads Queries)

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],

REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],

qs.total_logical_reads AS [Total Logical Reads],

qs.min_logical_reads AS [Min Logical Reads],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.max_logical_reads AS [Max Logical Reads],

qs.min_worker_time AS [Min Worker Time],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time],

qs.max_worker_time AS [Max Worker Time],

qs.min_elapsed_time AS [Min Elapsed Time],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],

qs.max_elapsed_time AS [Max Elapsed Time],

qs.execution_count AS [Execution Count],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

qs.creation_time AS [Creation Time]

--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

------

-- Helps you find the most expensive queries from a memory perspective across the entire instance

-- Can also help track down parameter sniffing issues

-- Get top average elapsed time queries for entire instance (Query 50) (Top Avg Elapsed Time Queries)

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],

REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],

qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],

qs.min_elapsed_time, qs.max_elapsed_time, qs.last_elapsed_time,

qs.execution_count AS [Execution Count],

qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],

qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads],

qs.total_worker_time/qs.execution_count AS [Avg Worker Time],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],

qs.creation_time AS [Creation Time]

--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY qs.total_elapsed_time/qs.execution_count DESC OPTION (RECOMPILE);

------

-- Helps you find the highest average elapsed time queries across the entire instance

-- Can also help track down parameter sniffing issues