Group index optimize total size from logged data by OLA Maintenance Procedures https://ola.hallengren.com

group-ola-indexoptimize.sql

--
--	Group index optimize total size from logged data by https://ola.hallengren.com
--
WITH cte AS
(
	SELECT
		DatabaseName
	,	StartTime
	,	ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'bigint') AS pages
	FROM	CommandLog
	WHERE	CommandType = 'ALTER_INDEX' -- AND DatabaseName = 'dbname'
)
SELECT	YEAR(StartTime) AS [Year]
,		MONTH(StartTime) AS [Month]
,		DAY(StartTime) AS [Day]
,		DatabaseName
,		SUM(pages * 8192 / 1024.0 / 1024.0) AS SizeMB
FROM	cte
GROUP BY
		YEAR(StartTime), MONTH(StartTime), DAY(StartTime), DatabaseName

group-ola-indexoptimize_variant.sql

WITH cte AS
(
	SELECT
		DatabaseName
	,	StartTime
	,	EndTime
	,	CommandType
	,	ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'bigint') AS pages
	FROM	CommandLog
	WHERE	CommandType IN ('ALTER_INDEX', 'UPDATE_STATISTICS') -- AND DatabaseName = 'dbname'
)
SELECT	YEAR(StartTime) AS [Year]
,		MONTH(StartTime) AS [Month]
,		DAY(StartTime) AS [Day]
,		SUM(
			CASE WHEN CommandType = 'UPDATE_STATISTICS' THEN 1 ELSE 0 END
		) AS StatisticsUpdated
,		SUM(
			CASE WHEN CommandType = 'ALTER_INDEX' THEN 1 ELSE 0 END
		) AS IndexesMaintained
,		SUM(DATEDIFF(minute, StartTime, EndTime)) AS MinElapsed
,		DatabaseName
,		CAST(SUM(ISNULL(pages, 0) * 8192 / 1024.0 / 1024.0) AS decimal(15, 2)) AS SizeMB
FROM	cte
GROUP BY
		YEAR(StartTime), MONTH(StartTime), DAY(StartTime), DatabaseName
ORDER BY
		1, 2, 3, 4

group-ola-indexoptimize_variant2.sql

WITH cte AS
(
	SELECT
		DatabaseName
	,	StartTime
	,	EndTime
	,	CommandType
	,	CASE
			WHEN CommandType = 'ALTER_INDEX' AND Command LIKE '%REBUILD%' THEN 'REBUILD'
			WHEN CommandType = 'ALTER_INDEX' AND Command LIKE '%REORG%' THEN 'REORG'
		END AS AlterIndexType
	,	ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'bigint') AS pages
	FROM	CommandLog
	WHERE	CommandType IN ('ALTER_INDEX', 'UPDATE_STATISTICS') -- AND DatabaseName = 'dbname'
)
SELECT	YEAR(StartTime) AS [Year]
,		MONTH(StartTime) AS [Month]
,		DAY(StartTime) AS [Day]
,		DatabaseName
,		SUM(
			CASE WHEN CommandType = 'UPDATE_STATISTICS' THEN 1 ELSE 0 END
		) AS StatisticsUpdated
,		SUM(
			CASE WHEN CommandType = 'ALTER_INDEX' THEN 1 ELSE 0 END
		) AS IndexesMaintained
,		SUM(DATEDIFF(minute, StartTime, EndTime)) AS MinElapsed
,		CAST(SUM(
			CASE WHEN AlterIndexType = 'REBUILD'
				THEN ISNULL(pages, 0) * 8192 / 1024.0 / 1024.0
				ELSE 0
			END
			) AS decimal(15, 2)) AS RebuildSizeMB
,		CAST(SUM(
			CASE WHEN AlterIndexType = 'REORG'
				THEN ISNULL(pages, 0) * 8192 / 1024.0 / 1024.0
				ELSE 0
			END
			) AS decimal(15, 2)) AS ReorgSizeMB
FROM	cte
GROUP BY
		YEAR(StartTime), MONTH(StartTime), DAY(StartTime), DatabaseName
ORDER BY
		1, 2, 3, 4