-- Verify Enterprise Edition (required for compression + online + partitioning) IF CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) NOT LIKE '%Enterprise%' AND CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) NOT LIKE '%Developer%' BEGIN RAISERROR('This feature requires SQL Server Enterprise or Developer Edition', 16, 1); RETURN; END;

-- Log start INSERT INTO dbo.IndexMaintenanceLog (TableName, Action, StartTime, Status) VALUES (@SchemaName + '.' + @TableName, 'Enterprise Optimize', GETDATE(), 'Running');

-- Execute Enterprise optimization EXEC dbo.Enterprise_OptimizeTablePartitions @SchemaName = 'dbo', @TableName = 'SalesFact', @CompressionType = 'PAGE', @MaxDOP = 4; -- Monitor real-time progress (Enterprise DMVs) CREATE PROCEDURE dbo.Monitor_EnterpriseOptimization @TableName NVARCHAR(256) = NULL AS BEGIN -- Show current index rebuild progress SELECT session_id, command, percent_complete, estimated_completion_time, start_time, TEXT AS query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE command LIKE '%INDEX%REBUILD%' AND (@TableName IS NULL OR TEXT LIKE '%' + @TableName + '%'); -- Show partition statistics SELECT OBJECT_NAME(p.object_id) AS TableName, p.partition_number, p.rows, p.data_compression_desc, ps.used_page_count * 8 / 1024 AS SizeMB FROM sys.partitions p JOIN sys.dm_db_partition_stats ps ON p.partition_id = ps.partition_id WHERE OBJECT_NAME(p.object_id) = ISNULL(@TableName, OBJECT_NAME(p.object_id)); END; GO 5. Automated Job (SQL Agent) -- Create SQL Agent job for weekly maintenance USE msdb; GO EXEC dbo.sp_add_job @job_name = N'Enterprise_PartitionOptimization', @enabled = 1;

SET @ObjectID = OBJECT_ID(@SchemaName + '.' + @TableName);