DBADash - Improve performance of TableSize report

TableSize.sql

------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
DROP VIEW IF EXISTS dbo.vw_TableSize_Snapshots;
GO
CREATE OR ALTER VIEW dbo.vw_TableSize_Snapshots
	WITH SCHEMABINDING
AS
SELECT InstanceID, DatabaseID, SnapshotDate, ObjectCount = COUNT_BIG(*)
FROM dbo.TableSize
GROUP BY InstanceID, DatabaseID, SnapshotDate;
GO

--DROP INDEX CIX_TableSize_InstanceID_DatabaseID_SnapshotDate ON dbo.vw_TableSize_Snapshots
CREATE UNIQUE CLUSTERED INDEX CIX_TableSize_InstanceID_DatabaseID_SnapshotDate
	ON dbo.vw_TableSize_Snapshots (InstanceID, DatabaseID, SnapshotDate);
GO
------------------------------------------------------------------------------

------------------------------------------------------------------------------
GO
CREATE OR ALTER PROC dbo.TableSize_Get (
	@InstanceIDs	IDs READONLY,
	@GrowthDays		int = 30,
	@Top			int = 1000,
	@DatabaseID		int
)
AS
BEGIN;
	DECLARE @MinSnapshotDate datetime2;
	SELECT @MinSnapshotDate = DATEADD(d,-@GrowthDays, SYSUTCDATETIME());

	IF OBJECT_ID('tempdb..#tmp_ss','U') IS NOT NULL DROP TABLE #tmp_ss; --SELECT TOP(100) * FROM #tmp_ss;
	CREATE TABLE #tmp_ss (
		InstanceID			int				NOT NULL,
		InstanceDisplayName	nvarchar(128)	NOT NULL,
		DatabaseID			int				NOT NULL,
		DatabaseName		nvarchar(128)	NOT NULL,
		LatestSnapshot		datetime2(7)	NOT NULL,
		OldestSnapshot		datetime2(7)	NOT NULL,
	
		INDEX IX_InstanceID_LatestSnapshot_DatabaseID NONCLUSTERED (InstanceID, LatestSnapshot, DatabaseID),
		INDEX IX_InstanceID_OldestSnapshot_DatabaseID NONCLUSTERED (InstanceID, OldestSnapshot, DatabaseID),
	);

	IF OBJECT_ID('tempdb..#TS_Latest','U') IS NOT NULL DROP TABLE #TS_Latest; --SELECT * FROM #TS_Latest
	CREATE TABLE #TS_Latest (
		InstanceID		int				NOT NULL,
		DatabaseID		int				NOT NULL,
		ObjectID		bigint			NOT NULL,
		SnapshotDate	datetime2(7)	NOT NULL,
		row_count		bigint			NOT NULL,
		reserved_pages	bigint			NOT NULL,
		used_pages		bigint			NOT NULL,
		data_pages		bigint			NOT NULL,
		index_pages		bigint			NOT NULL,

		INDEX IX CLUSTERED (InstanceID, SnapshotDate, DatabaseID)
	);

	IF OBJECT_ID('tempdb..#TS_Oldest','U') IS NOT NULL DROP TABLE #TS_Oldest; --SELECT * FROM #TS_Oldest
	CREATE TABLE #TS_Oldest (
		InstanceID		int				NOT NULL,
		DatabaseID		int				NOT NULL,
		ObjectID		bigint			NOT NULL,
		SnapshotDate	datetime2(7)	NOT NULL,
		row_count		bigint			NOT NULL,
		reserved_pages	bigint			NOT NULL,
		used_pages		bigint			NOT NULL,
		data_pages		bigint			NOT NULL,
		index_pages		bigint			NOT NULL,

		INDEX IX CLUSTERED (InstanceID, SnapshotDate, DatabaseID)
	);

	INSERT INTO #tmp_ss (InstanceID, InstanceDisplayName, DatabaseID, DatabaseName, LatestSnapshot, OldestSnapshot)
	SELECT v.InstanceID, I.InstanceDisplayName, v.DatabaseID, DatabaseName = D.[name]
		, LatestSnapshot = MAX(SnapshotDate)
		, OldestSnapshot = MIN(SnapshotDate)
	FROM dbo.vw_TableSize_Snapshots v
		JOIN dbo.Instances I ON I.InstanceID = v.InstanceID
		JOIN dbo.[Databases] D ON D.DatabaseID = v.DatabaseID
	WHERE v.SnapshotDate >= @MinSnapshotDate
		AND (v.DatabaseID = @DatabaseID OR @DatabaseID IS NULL)
		AND EXISTS (SELECT * FROM @InstanceIDs t WHERE t.ID = v.InstanceID)
		AND I.IsActive = 1
		AND D.IsActive = 1
	GROUP BY v.InstanceID, I.InstanceDisplayName, v.DatabaseID, D.[name];

	INSERT INTO #TS_Latest (InstanceID, DatabaseID, ObjectID, SnapshotDate, row_count, reserved_pages, used_pages, data_pages, index_pages)
	SELECT ts.InstanceID, ts.DatabaseID, ts.ObjectID, ts.SnapshotDate, ts.row_count, ts.reserved_pages, ts.used_pages, ts.data_pages, ts.index_pages
	FROM #tmp_ss ss
		CROSS APPLY (
			SELECT ts.InstanceID, ts.DatabaseID, ts.ObjectID, ts.SnapshotDate, ts.row_count, ts.reserved_pages, ts.used_pages, ts.data_pages, ts.index_pages
			FROM dbo.TableSize ts
			WHERE ts.InstanceID = ss.InstanceID
				AND ts.SnapshotDate = ss.LatestSnapshot
				AND ts.DatabaseID = ss.DatabaseID
		) ts;

	INSERT INTO #TS_Oldest (InstanceID, DatabaseID, ObjectID, SnapshotDate, row_count, reserved_pages, used_pages, data_pages, index_pages)
	SELECT ts.InstanceID, ts.DatabaseID, ts.ObjectID, ts.SnapshotDate, ts.row_count, ts.reserved_pages, ts.used_pages, ts.data_pages, ts.index_pages
	FROM #tmp_ss ss
		CROSS APPLY (
			SELECT ts.InstanceID, ts.DatabaseID, ts.ObjectID, ts.SnapshotDate, ts.row_count, ts.reserved_pages, ts.used_pages, ts.data_pages, ts.index_pages
			FROM dbo.TableSize ts
			WHERE ts.InstanceID = ss.InstanceID
				AND ts.SnapshotDate = ss.OldestSnapshot
				AND ts.DatabaseID = ss.DatabaseID
		) ts;

	SELECT TOP(@Top) Latest.ObjectID
		, Latest.InstanceID
		, Instance = ss.InstanceDisplayName
		, Latest.[SnapshotDate]
		, SnapshotStatus = ISNULL(SSD.[Status], 3)
		, [DB] = ss.DatabaseName
		, O.SchemaName, O.ObjectName
		, [Rows] = Latest.row_count
		, Reserved_KB = Latest.reserved_pages * 8
		, Used_KB = Latest.used_pages * 8
		, Data_KB = Latest.data_pages * 8
		, Index_KB = (Latest.index_pages) * 8
		, Avg_Rows_Per_Day = (Latest.row_count - Oldest.row_count) * 1440.0 / NULLIF(DATEDIFF(mi, Oldest.SnapshotDate, Latest.SnapshotDate), 0)
		, Avg_KB_Per_Day = (Latest.used_pages - Oldest.used_pages) * 8 * 1440.0 / NULLIF(DATEDIFF(mi, Oldest.SnapshotDate, Latest.SnapshotDate), 0)
		, CalcDays = NULLIF(DATEDIFF(mi, Oldest.SnapshotDate, Latest.SnapshotDate), 0) / 1440.0
	FROM #tmp_ss ss
		JOIN      #TS_Latest Latest ON Latest.InstanceID = ss.InstanceID AND Latest.DatabaseID = ss.DatabaseID
		LEFT JOIN #TS_Oldest Oldest ON Oldest.InstanceID = ss.InstanceID AND Oldest.DatabaseID = ss.DatabaseID AND Oldest.ObjectID = Latest.ObjectID
		JOIN dbo.DBObjects O ON Latest.ObjectID = O.ObjectID AND O.DatabaseID = Latest.DatabaseID
		LEFT JOIN dbo.CollectionDatesStatus SSD ON SSD.InstanceID = Latest.InstanceID AND SSD.Reference = 'TableSize'
	WHERE O.IsActive = 1
	ORDER BY Latest.reserved_pages DESC
	OPTION(RECOMPILE);
END;
GO
------------------------------------------------------------------------------

------------------------------------------------------------------------------
DECLARE @InstanceIDs IDs;

INSERT INTO @InstanceIDs (ID)
SELECT InstanceID
FROM dbo.Instances
WHERE IsActive = 1

EXEC dbo.TableSize_Get @InstanceIDs = @InstanceIDs, @GrowthDays = 30, @Top = 1000, @DatabaseID = NULL
------------------------------------------------------------------------------

------------------------------------------------------------------------------