Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Checks for tables and indexes with a high percentage of allocated but unused space in all DBs, and generates remediation commands for it
DECLARE
@TopPerDB int = 50,
@MinimumRowCount int = 1000,
@MinimumUnusedSizeMB int = 1024,
@MinimumUnusedSpacePct int = 40,
@RebuildIndexOptions varchar(max) = 'ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 1' -- , RESUMABLE = ON -- adjust as needed
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @command NVARCHAR(MAX);
DECLARE @TempResult AS TABLE (DatabaseName sysname NOT NULL, SchemaName sysname NULL, TableName sysname NULL, IndexName sysname NULL, Fill_Factor int NULL
, DatabaseID int NOT NULL, ObjectId int NOT NULL, IndexId int NULL
, CompressionType tinyint NULL, CompressionType_Desc AS (CASE CompressionType WHEN 0 THEN 'NONE' WHEN 1 THEN 'ROW' WHEN 2 THEN 'PAGE' WHEN 3 THEN 'COLUMNSTORE' WHEN 4 THEN 'COLUMNSTORE_ARCHIVE' ELSE 'UNKNOWN' END)
, RowCounts bigint NULL, TotalSpaceMB money NULL, UsedSpaceMB money NULL, UnusedSpaceMB money NULL
, UserSeeks int NULL, UserScans int NULL, UserLookups int NULL, UserUpdates int NULL);
SELECT @command = 'IF EXISTS (SELECT * FROM sys.databases WHERE [name] = ''?'' AND state = 0 AND HAS_DBACCESS([name]) = 1 AND database_id > 4 AND is_distributor = 0 AND DATABASEPROPERTYEX([name], ''Updateability'') = ''READ_WRITE'')
BEGIN
USE [?];
SELECT TOP (' + CONVERT(nvarchar(max), @TopPerDB) + N')
DB_NAME() AS DatabaseName,
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.fill_factor,
DB_ID(), i.object_id, i.index_id,
MAX(p.data_compression) AS CompressionType,
SUM(p.rows) AS RowCounts,
ROUND(SUM(a.total_pages) / 128.0, 2) AS TotalSpaceMB,
ROUND(SUM(a.used_pages) / 128.0, 2) AS UsedSpaceMB,
ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.0, 2) AS UnusedSpaceMB,
MAX(us.user_seeks) AS user_seeks,
MAX(us.user_scans) AS user_scans,
MAX(us.user_lookups) AS user_lookups,
MAX(us.user_updates) AS user_updates
FROM
sys.indexes i
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 JOIN
sys.dm_db_index_usage_stats AS us ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
WHERE
OBJECT_NAME(i.object_id) NOT LIKE ''dt%''
AND OBJECT_SCHEMA_NAME(i.object_id) <> ''sys''
AND i.object_id > 255
and p.rows >= 1
GROUP BY
i.object_id, i.name, i.index_id, i.fill_factor
HAVING
SUM(p.rows) >= ' + CONVERT(nvarchar(max), @MinimumRowCount) + N'
AND (SUM(a.total_pages) - SUM(a.used_pages)) / 128 >= ' + CONVERT(nvarchar(max), @MinimumUnusedSizeMB) + N'
AND (SUM(a.used_pages) * 1.0) / SUM(a.total_pages) <= 1 - (' + CONVERT(nvarchar(max), @MinimumUnusedSpacePct) + ' / 100.0)
ORDER BY TotalSpaceMB DESC
END'
INSERT INTO @TempResult
EXEC sp_MSforeachdb @command
SELECT r.*
, UnusedSpacePercent = UnusedSpaceMB / TotalSpaceMB * 100
--, frg.avg_fragmentation_in_percent, frg.page_count
--, frg.avg_page_space_used_in_percent
, RebuildCommand = N'USE ' + QUOTENAME(DatabaseName) + N'; ' +
CASE WHEN IndexName IS NULL THEN N'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
ELSE N'ALTER INDEX ' + QUOTENAME(IndexName) + N' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
END + N' REBUILD WITH (FILLFACTOR = ' + CONVERT(nvarchar(max), ISNULL(NULLIF(Fill_Factor,0),100)) + ISNULL(N', ' + NULLIF(@RebuildIndexOptions,N''), N'') + N');'
, ReorganizeCommand = N'USE ' + QUOTENAME(DatabaseName) + N'; ' +
CASE WHEN IndexName IS NULL THEN N'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
ELSE N'ALTER INDEX ' + QUOTENAME(IndexName) + N' ON ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
END + N' REORGANIZE;'
, CleanTableCommand = N'USE ' + QUOTENAME(DatabaseName) + N'; DBCC CLEANTABLE ('
+ QUOTENAME(DatabaseName, N'''') + N', ' + QUOTENAME(QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName), N'''')
+ N', 10000 ) WITH NO_INFOMSGS;'
FROM @TempResult AS r
--OUTER APPLY sys.dm_db_index_physical_stats(DatabaseID, ObjectId, IndexId, NULL, 'LIMITED') AS frg
ORDER BY UnusedSpaceMB DESC
OPTION (MAXDOP 1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment