| Metric | SQL Query |
|---|---|
| Database Name | SELECT DB_NAME() AS DatabaseName; |
| Uptime | SELECT sqlserver_start_time AS 'SQL SERVER START TIME' FROM sys.dm_os_sys_info; |
| Active User Sessions | SELECT login_name AS LoginName, COUNT(session_id) AS SessionCount FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY login_name ORDER BY SessionCount DESC; |
| Metric | SQL Query |
|---|---|
| Top 10 Longest Running Queries | SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_duration_ms, qs.execution_count, SUBSTRING(qt.text, qs.statement_start_offset/2, (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 qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY avg_duration_ms DESC; |
| Queries per Second | SELECT qs.total_elapsed_time / qs.execution_count / 1000.0 AS 'Avg Duration ms', qs.execution_count AS 'Exuction Count', SUBSTRING(qt.text, qs.statement_start_offset/2, (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 qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY 'Avg Duration ms' DESC; |
| Query Cache Hit Rate | SELECT (CAST(SUM(CASE WHEN usecounts > 0 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*)) * 100 AS Cache_Hit_Ratio FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc' OR objtype = 'Prepared'; |
| Query Plan Cache Efficiency | SELECT cacheobjtype AS CacheObjectType, objtype AS ObjectType, usecounts AS 'Use Count', size_in_bytes AS 'Cache Size' FROM sys.dm_exec_cached_plans WHERE usecounts < 10; |
| Wait Stats Overview | DBCC SQLPERF (WAITSTATS); |
| Current Connections | SELECT COUNT(session_id) AS Connections FROM sys.dm_exec_sessions WHERE is_user_process = 1; |
| Query Latency | SELECT TOP 10 creation_time AS CreationTime, total_logical_reads AS LogicalReads FROM sys.dm_exec_query_stats ORDER BY total_elapsed_time DESC; |
| Execution Plans Performance | SELECT TOP 10 st.text AS QueryText, qs.total_worker_time AS CPUTime, qs.total_elapsed_time AS TotalTime, qs.total_logical_reads AS LogicalReads FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY qs.total_worker_time DESC; |
| Metric | SQL Query |
|---|---|
| Query Latency | SELECT TOP 10 creation_time AS CreationTime, total_elapsed_time/1000 AS TotalElapsedTimeMS, execution_count AS ExecutionCount, total_worker_time/1000 AS CPUTimeMS FROM sys.dm_exec_query_stats ORDER BY total_elapsed_time DESC; |
| Running Threads | SELECT COUNT(*) AS Running_Threads FROM sys.dm_exec_requests WHERE status = 'running'; |
| Open Files Limit | SELECT COUNT(*) AS OpenFiles FROM sys.dm_io_pending_io_requests; |
| Active Transactions | SELECT transaction_id AS TransactionID, transaction_begin_time AS BeginTime, transaction_state AS State, transaction_type AS Type FROM sys.dm_tran_active_transactions; |
| Temp Tables Created On Disk | SELECT SUM(user_objects_alloc_page_count) AS 'User Object Pages', SUM(internal_objects_alloc_page_count) AS 'Internal Object Pages' FROM sys.dm_db_session_space_usage; |
| Wait Times Monitoring | SELECT wait_type, SUM(wait_time_ms) AS WaitTimeMS, SUM(waiting_tasks_count) AS TaskCount FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_TASK_STOP', 'SQLTRACE_BUFFER_FLUSH') GROUP BY wait_type ORDER BY WaitTimeMS DESC; |
| Table Locks | SELECT request_session_id AS SessionID, resource_database_id AS DatabaseID, resource_associated_entity_id AS EntityID, request_mode AS LockType, request_status AS Status FROM sys.dm_tran_locks; |
| Metric | SQL Query |
|---|---|
| Buffer Pool Hit Rate | SELECT CAST(COUNT(*) * 8.0 / 1024 AS DECIMAL(10, 2)) AS 'Buffer Pool Used MB', CAST(SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10, 2)) AS 'Dirty Buffer MB', CAST(COUNT(*) * 8.0 / 1024 AS DECIMAL(10, 2)) AS 'Buffer Pool Total MB', CAST(SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10, 2)) AS 'Dirty Buffer Used MB' FROM sys.dm_os_buffer_descriptors WHERE database_id > 4; |
| Buffer Pool Usage | SELECT (total_physical_memory_kb / 1024) AS 'Total Physical Memory MB', (available_physical_memory_kb / 1024) AS 'Available Physical Memory MB', (total_page_file_kb / 1024) AS 'Total Page File MB', (available_page_file_kb / 1024) AS 'Available Page File MB', (system_memory_state_desc) AS 'System Memory State Description' FROM sys.dm_os_sys_memory; |
| Index Usage | SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE s.database_id = DB_ID(DB_NAME()) ORDER BY s.user_seeks DESC; |
| Page Life Expectancy | SELECT [object_name], [counter_name], [cntr_value] AS PageLifeExpectancy FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page life expectancy'; |
| Metric | SQL Query |
|---|---|
| Total Space MB | SELECT ISNULL(t.NAME, 'Total') AS TableName, ISNULL(s.NAME, '') AS SchemaName, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.0), 2) AS DECIMAL(18, 2)) AS TotalSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY ROLLUP(t.Name, s.Name) ORDER BY CASE WHEN t.Name IS NULL THEN 1 ELSE 0 END, TotalSpaceMB DESC; |
| Used Space MB | SELECT ISNULL(t.NAME, 'Total') AS TableName, ISNULL(s.NAME, '') AS SchemaName, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.0), 2) AS DECIMAL(18, 2)) AS UsedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY ROLLUP(t.Name, s.Name) ORDER BY CASE WHEN t.Name IS NULL THEN 1 ELSE 0 END, UsedSpaceMB DESC; |
| Unused Space MB | SELECT ISNULL(t.NAME, 'Total') AS TableName, ISNULL(s.NAME, '') AS SchemaName, CAST(ROUND(((SUM(a.total_pages - a.used_pages) * 8) / 1024.0 * 100), 2) AS DECIMAL(18, 2)) AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY ROLLUP(t.Name, s.Name) ORDER BY CASE WHEN t.Name IS NULL THEN 1 ELSE 0 END, UnusedSpaceMB DESC; |
| Memory Grants Pending | SELECT COUNT(*) AS MemoryGrantsPending FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL; |
| Transaction Log Space Usage | DBCC SQLPERF (LOGSPACE); |
| Database File Usage | SELECT name AS FileName, size/128 AS FileSizeMB, physical_name AS PhysicalName FROM sys.master_files WHERE database_id = DB_ID(); |
| Backup Status | SELECT database_name, backup_start_date, backup_finish_date, backup_size / 1024 / 1024 AS BackupSizeMB FROM msdb.dbo.backupset ORDER BY backup_start_date DESC; |
| Row Count | SELECT ISNULL(t.NAME, 'Total') AS TableName, ISNULL(s.NAME, '') AS SchemaName, SUM(p.rows) AS RowCounts FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY ROLLUP(t.Name, s.Name) ORDER BY CASE WHEN t.Name IS NULL THEN 1 ELSE 0 END, RowCounts DESC; |
| Memory Usage | SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, available_page_file_kb FROM sys.dm_os_sys_memory; |
| TempDB Disk Monitoring | SELECT database_id, file_id, io_stall_read_ms AS DiskReadStall, io_stall_write_ms AS DiskWriteStall, num_of_reads, num_of_writes FROM sys.dm_io_virtual_file_stats (2, NULL); |
| Metric | SQL Query |
|---|---|
| Job Execution Frequency | SELECT job.name AS JobName, COUNT(run.run_date) AS ExecutionCount FROM msdb.dbo.sysjobs AS job INNER JOIN msdb.dbo.sysjobhistory AS run ON job.job_id = run.job_id WHERE run.run_date >= CONVERT(VARCHAR, GETDATE() - 7, 112) -- Last 7 days GROUP BY job.name ORDER BY ExecutionCount DESC |
| Jobs in progress | SELECT DISTINCT job.name AS JobName, activity.run_requested_date AS StartTime, DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS RunDurationSeconds FROM msdb.dbo.sysjobs AS job INNER JOIN msdb.dbo.sysjobactivity AS activity ON job.job_id = activity.job_id WHERE activity.run_requested_date IS NOT NULL AND activity.stop_execution_date IS NULL ORDER BY activity.run_requested_date DESC; |
| Scheduled and Running Jobs | SELECT TOP 20 job.name AS JobName, CASE WHEN activity.run_requested_date IS NULL THEN 'Scheduled' ELSE 'Running' END AS JobStatus, schedule.next_run_date AS NextRunDate, schedule.next_run_time AS NextRunTime FROM msdb.dbo.sysjobs AS job LEFT JOIN msdb.dbo.sysjobactivity AS activity ON job.job_id = activity.job_id LEFT JOIN msdb.dbo.sysjobschedules AS schedule ON job.job_id = schedule.job_id WHERE activity.stop_execution_date IS NULL -- Running jobs OR schedule.next_run_date IS NOT NULL -- Scheduled jobs ORDER BY schedule.next_run_date, schedule.next_run_time; |
| Job Execution History Duration | SELECT job.name AS JobName, run.run_date AS RunDate, run.run_duration AS RunDuration FROM msdb.dbo.sysjobs AS job INNER JOIN msdb.dbo.sysjobhistory AS run ON job.job_id = run.job_id WHERE run.step_id = 0 -- 0 indicates job level information ORDER BY run.run_date DESC |
| Failed Jobs Overview | SELECT job.name AS JobName, run.run_date AS RunDate, run.run_time AS RunTime, run.run_duration AS RunDuration, run.message AS ErrorMessage FROM msdb.dbo.sysjobs AS job INNER JOIN msdb.dbo.sysjobhistory AS run ON job.job_id = run.job_id WHERE run.run_status = 0 -- 0 indicates failure ORDER BY run.run_date DESC |