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