IndexOptimize and addition’s to Ola Hallengrens dbo.CommandLog

Of course you run Ola Hallengren’s IndexOptimize ( SQL Server Index and Statistics Maintenance ) as a job in the weekend with your own custom @FragmentationLevel1 and @FragmentationLevel2 levels and suddenly the jobs takes much longer to run.. why?

Well would it be nice to see how much work SQL Server had to do for index- and statistics maintenance? Well here it is.

Create some extra functions for ExtendedInfo xml columns (they will be bound to the column later).

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

If you already have only add these columns.

alter TABLE [dbo].[CommandLog] add
[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]))

If not, you could (re)create the table (make sure @LogToTable = ‘Y’).

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

See some action, run the script. This creates a fragmented index and should be maintained (But *not* according to Jeff Moden..)

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

Just run something like this.

exec [dbo].[IndexOptimize] @Databases = N'FragmentationDemo' ...

Eén reactie

Geef een reactieReactie annuleren