Addition’s to Ola Hallengrens dbo.CommandLog

USE [master]
GO

CREATE FUNCTION dbo.GetExtendedInfo_SizeMB (@xml xml)
RETURNS decimal(18,4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @pages bigint =
        @xml.value('(/ExtendedInfo/PageCount)[1]', 'bigint');

    RETURN (@pages * 8.0) / 1024.0;   -- MB
END;
GO

CREATE FUNCTION dbo.GetExtendedInfo_SizeGB (@xml xml)
RETURNS decimal(18,4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @pages bigint =
        @xml.value('(/ExtendedInfo/PageCount)[1]', 'bigint');

    RETURN (@pages * 8.0) / 1024.0 / 1024.0;   -- GB
END;
GO

CREATE FUNCTION dbo.GetExtendedInfo_PageCount (@xml xml)
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
    RETURN @xml.value('(/ExtendedInfo/PageCount)[1]', 'bigint');
END;
GO

CREATE FUNCTION dbo.GetExtendedInfo_Fragmentation (@xml xml)
RETURNS numeric(7,5)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @xml.value('(/ExtendedInfo/Fragmentation)[1]', 'numeric(7,5)');
END;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CommandLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [sysname] NULL,
	[SchemaName] [sysname] NULL,
	[ObjectName] [sysname] NULL,
	[ObjectType] [char](2) NULL,
	[IndexName] [sysname] NULL,
	[IndexType] [tinyint] NULL,
	[StatisticsName] [sysname] NULL,
	[PartitionNumber] [int] NULL,
	[ExtendedInfo] [xml] NULL,
	[Command] [nvarchar](max) NOT NULL,
	[CommandType] [nvarchar](60) NOT NULL,
	[StartTime] [datetime2](7) NOT NULL,
	[EndTime] [datetime2](7) NULL,
	[ErrorNumber] [int] NULL,
	[ErrorMessage] [nvarchar](max) NULL,
	[duration]  AS (CONVERT([time],CONVERT([datetime],datediff(millisecond,[StartTime],[EndTime])/(86400000.0)))),
	[PageCount]  AS ([dbo].[GetExtendedInfo_PageCount]([ExtendedInfo])),
	[Fragmentation]  AS ([dbo].[GetExtendedInfo_Fragmentation]([ExtendedInfo])),
	[SizeMB]  AS ([dbo].[GetExtendedInfo_SizeMB]([ExtendedInfo])),
	[SizeGB]  AS ([dbo].[GetExtendedInfo_SizeGB]([ExtendedInfo])),
 CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
	, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
	, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

DROP TABLE IF EXISTS dbo.FragmentationDemo;
GO

CREATE TABLE dbo.FragmentationDemo
(
    ID uniqueidentifier NOT NULL DEFAULT NEWID(),
    Payload char(200) NOT NULL DEFAULT REPLICATE('X', 200),
    CONSTRAINT PK_FragmentationDemo PRIMARY KEY CLUSTERED (ID)
);
GO

SET NOCOUNT ON;

INSERT INTO dbo.FragmentationDemo DEFAULT VALUES;
GO 50000

SELECT 
    index_type_desc,
    avg_fragmentation_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID('dbo.FragmentationDemo'),
    NULL,
    NULL,
    'SAMPLED'
);