Tech Blog – Scripts – MSSQL Diagnostic Cheat Sheet


Get Best Practise Info

-- Get best practise info
SELECT 
	[name] AS 'SQL Configuration',
	[value_in_use] AS 'Values'
FROM 
	sys.configurations 
WHERE 
	[name] = 'optimize for ad hoc workloads'
	or
	[name] = 'cost threshold for parallelism'
	or
	[name] = 'max degree of parallelism'
	or
	[name] = 'max server memory (MB)'
	or
	[name] = 'scan for startup procs'
UNION
SELECT 
	'read committed snapshot is enabled on ' [name], 
	[name] [is_read_committed_snapshot_on]
FROM sys.databases
WHERE [name] NOT IN ('master', 'msdb','model','distribution','tempdb')
	AND [is_read_committed_snapshot_on]  >0

Get Disk & Space 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 %]
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);

Get Hardware Info

-- Hardware info.
SELECT 
  cpu_count AS [Logical CPU Count], scheduler_count, 
  (socket_count * cores_per_socket) AS [Physical Core Count], 
  socket_count AS [Socket Count], cores_per_socket, numa_node_count,
  physical_memory_kb/1024 AS [Physical Memory (MB)], 
  max_workers_count AS [Max Workers Count], 
  affinity_type_desc AS [Affinity Type], 
  sqlserver_start_time AS [SQL Server Start Time],
  DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [SQL Server Up Time (hrs)],
  virtual_machine_type_desc AS [Virtual Machine Type], 
  softnuma_configuration_desc AS [Soft NUMA Configuration], 
  sql_memory_model_desc
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Get Instance Level Configs

-- Get instance-level config
SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);

Get Database Level Configs (recovery, log & common options)

-- Show database level configs on a SQL instance.
SELECT 
  db.[name] AS [Database Name], 
  SUSER_SNAME(db.owner_sid) AS [Database Owner], 
  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.[compatibility_level] AS [DB Compatibility Level], 
  db.page_verify_option_desc AS [Page Verify Option], 
  db.is_read_committed_snapshot_on, 
  db.is_auto_close_on, 
  db.is_auto_shrink_on, 
  db.is_query_store_on
FROM 
  sys.databases AS db WITH (NOLOCK)
  INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.name = lu.instance_name
  INNER 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);

Get Last Backup Times

-- Show last backups on all databases
SELECT 
  ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model], 
  d.log_reuse_wait_desc AS [Log Reuse Wait Desc],
    MAX(CASE WHEN [type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
    MAX(CASE WHEN [type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
    MAX(CASE WHEN [type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup]
FROM 
  sys.databases AS d WITH (NOLOCK)
  LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
  ON bs.[database_name] = d.[name] 
  AND bs.backup_finish_date > GETDATE()- 30
WHERE d.name <> N'tempdb'
GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc, d.[name] 
ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);

Get Sizes Of All Databases

-- Get sizes of all databases and store into a temp table.
DECLARE @command NVARCHAR(MAX)
DROP TABLE IF EXISTS #MSSQL_Database_Sizes
CREATE TABLE #MSSQL_Database_Sizes ([DatabaseName] [nvarchar](max) NULL, [CurrentSizeMB] [int] NULL, [FreeSpaceMB] [int] NULL, [CollectionDate] [date] NULL)
SELECT @command =       
    'USE ? 
    INSERT INTO #MSSQL_Database_Sizes ([DatabaseName], CurrentSizeMB, FreeSpaceMB, [CollectionDate]) 
    SELECT 
        [name] AS [Database], 
        [size]/128.0 AS [CurrentSizeMB], 
        [size]/128.0 - CAST(FILEPROPERTY([name], ' + '''SpaceUsed''' + ') AS INT)/128.0 AS [FreeSpaceMB],
        CAST(GETDATE() AS DATE) AS [CollectionDate]
    FROM sys.database_files
    WHERE [type] = 0'
EXEC sp_MSforeachdb @command
SELECT * 
FROM #MSSQL_Database_Sizes
ORDER BY 3 DESC

Get Table Sizes (database scoped)

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Get CPU Utilisation By Database

-- Get CPU utilization 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
ORDER BY [CPU Rank] OPTION (RECOMPILE);

Get IO Utilisation By Database

-- Get I/O utilization 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 Top Avg. Elapsed Query Time On Instance

-- Get top average elapsed time queries for entire instance 
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) 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);

Get Most Frequently Executed Queries On Database

-- Get most frequently executed queries for this database
SELECT TOP(50) LEFT(t.[text], 50) AS [Short Query Text], qs.execution_count AS [Execution Count],
qs.total_logical_reads AS [Total Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.total_worker_time AS [Total Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.total_elapsed_time AS [Total Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) 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 
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

Get SQL Agent & Category Info

-- Get SQL Server Agent jobs and Category information
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 Database File Path & Growth Info

-- Database file path & growth info
SELECT 
  DB_NAME([database_id]) AS [Database Name], 
  [file_id], 
  [name], 
  physical_name, 
  [type_desc], 
  state_desc,
  is_percent_growth, 
  growth,
  CONVERT(bigint, growth/128.0) AS [Growth in MB], 
  CONVERT(bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]), [file_id] OPTION (RECOMPILE);

Get Fragmentation & Forward Record Counts

-- Get Fragmentation &Amp; Stats On A SQL Database
--		This May Take Time To Return Results On A Heavy Server. 
--		Run Against Largest Database First Before Using Sp_MSforeachdb (2nd Script Below).
 

-- ##### Run Against An Individual Database #####
SELECT
  Sysdbs.[Name], 
  Index_id,
  Object_name([Object_id]) AS TableName, 
  CASE WHEN Sysindx.Name Is Null THEN 'HEAP' ELSE Sysindx.Name END AS IndexName,
  Index_type_desc, 
  Avg_fragmentation_in_percent,
  Forwarded_record_count
FROM 
  Sys.Dm_db_index_physical_stats(Db_id(),Object_id('HeapRebuildTest'),Null,Null,'Detailed') AS Sysindxphyst
  INNER JOIN Sys.Sysindexes AS Sysindx ON Sysindxphyst.[Object_id] = Sysindx.[Id] AND Sysindxphyst.Index_id = Sysindx.Indid
  INNER JOIN Sys.Databases AS Sysdbs ON Sysindxphyst.[Database_id] = Sysdbs.[Database_id]
WHERE 
  Index_level = 0
ORDER BY 
  [Avg_fragmentation_in_percent] DESC


-- ##### Run Against All Databases - Change of database/table name required #####
DROP TABLE IF EXISTS [insight_team].[dbo].[MSSQL_Fragmentation]
CREATE TABLE [insight_team].[dbo].[MSSQL_Fragmentation](
	[Name] [sysname] NOT NULL,
	[Index_id] [int] NULL,
	[TableName] [nvarchar](128) NULL,
	[IndexName] [nvarchar](128) NULL,
	[Index_type_desc] [nvarchar](60) NULL,
	[Avg_fragmentation_in_percent] [float] NULL,
	[Forwarded_record_count] [bigint] NULL
) ON [PRIMARY]
EXEC Sp_MSforeachdb 'USE ? 
  INSERT INTO [insight_team].[dbo].[fragmentation_10feb] (Name,Index_id,TableName,IndexName,Index_type_desc,Avg_fragmentation_in_percent,Forwarded_record_count)
  SELECT 
    Sysdbs.[Name], 
    Index_id,
    Object_name([Object_id]) AS TableName, 
    CASE WHEN Sysindx.Name Is Null THEN ''HEAP'' ELSE Sysindx.Name END AS IndexName,
    Index_type_desc, 
    Avg_fragmentation_in_percent,
    Forwarded_record_count
  FROM 
    Sys.Dm_db_index_physical_stats(Db_id(),Object_id(''HeapRebuildTest''),Null,Null,''Detailed'') AS Sysindxphyst
    INNER JOIN Sys.Sysindexes AS Sysindx ON Sysindxphyst.[Object_id] = Sysindx.[Id] AND Sysindxphyst.Index_id = Sysindx.Indid
    INNER JOIN Sys.Databases AS Sysdbs ON Sysindxphyst.[Database_id] = Sysdbs.[Database_id]
  WHERE 
    Index_level = 0
  ORDER BY 
    [Avg_fragmentation_in_percent] DESC' 
 
	-- Show Most Fragmented Indexes.
	SELECT * 
	FROM [insight_team].[dbo].[fragmentation_10feb]
	WHERE Index_type_desc <> 'HEAP'
	ORDER BY [Avg_fragmentation_in_percent] DESC
 
	-- Show Highest Counts Of Forward Records.
	SELECT * 
	FROM [insight_team].[dbo].[fragmentation_10feb]
	ORDER BY [Forwarded_record_count] DESC
 
	-- Show Total Forward Record Counts On SQL Instance. 
	SELECT SUM(Forwarded_record_count) AS 'Total_forward_records' 
	FROM [insight_team].[dbo].[fragmentation_10feb]
 
	-- Show Which Databases Have The Most Forward Records
	SELECT [Name], SUM(Forwarded_record_count) AS 'Total_forward_records' 
	FROM [insight_team].[dbo].[fragmentation_10feb]
	GROUP BY [Name] 
	ORDER BY [Total_forward_records] DESC

Get Database Growth Events

DECLARE @filename NVARCHAR(1000); 
DECLARE @bc INT; 
DECLARE @ec INT; 
DECLARE @bfn VARCHAR(1000); 
DECLARE @efn VARCHAR(10);   

-- Get the name of the current default trace 
SELECT 
  @filename = CAST(value AS NVARCHAR(1000)) 
FROM 
  ::fn_trace_getinfo(DEFAULT) 
WHERE 
  traceid = 1 
AND 
  property = 2;   

-- rip apart file name into pieces 
SET @filename = REVERSE(@filename); 
SET @bc = CHARINDEX('.',@filename); 
SET @ec = CHARINDEX('_',@filename)+1; 
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc)); 
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));   

-- set filename without rollover number 
SET @filename = @bfn + @efn   
-- process all trace files 
SELECT    
  ftg.StartTime ,
  te.name AS EventName,
  DB_NAME(ftg.databaseid) AS DatabaseName,
  ftg.Filename,
  (ftg.IntegerData*8)/1024.0 AS GrowthMB,
  (ftg.duration/1000) AS DurMS 
FROM 
  ::fn_trace_gettable(@filename, DEFAULT) AS ftg  
INNER JOIN 
  sys.trace_events AS te ON ftg.EventClass = te.trace_event_id   
WHERE 
  (ftg.EventClass = 92 -- Date File Auto-grow     
OR 
  ftg.EventClass = 93) -- Log File Auto-grow 
ORDER BY 
  ftg.StartTime DESC

Get VLF Counts For All Databases

-- Get VLF Counts for all databases
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 SQL Services Info

-- 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 -- New in SQL Server 2016 SP1
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);