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

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)

Get Last Backup Times

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 IO Utilisation By Database

Get Top Avg. Elapsed Query Time On Instance

Get Most Frequently Executed Queries On Database

Get SQL Agent & Category Info

Get Database File Path & Growth Info

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

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