-- 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;
-- Update final status UPDATE dbo.IndexMaintenanceLog SET EndTime = GETDATE(), Status = 'Completed', CompressionUsed = @CompressionType WHERE TableName = @SchemaName + '.' + @TableName AND Status = 'Running'; END; GO -- Maintenance log table CREATE TABLE dbo.IndexMaintenanceLog ( LogID INT IDENTITY(1,1) PRIMARY KEY, TableName NVARCHAR(256), Action NVARCHAR(100), StartTime DATETIME, EndTime DATETIME, PartitionsProcessed INT DEFAULT 0, LastProcessedTime DATETIME, Status NVARCHAR(50), CompressionUsed NVARCHAR(10), CONSTRAINT CHK_Compression CHECK (CompressionUsed IN ('PAGE', 'ROW', 'NONE')) ); -- Error logging (Enterprise: uses compression) CREATE TABLE dbo.ErrorLog ( ErrorID INT IDENTITY(1,1) PRIMARY KEY, ProcedureName NVARCHAR(256), ErrorMessage NVARCHAR(MAX), ErrorDate DATETIME DEFAULT GETDATE() ); sql server 2012 enterprise
IF @ObjectID IS NULL BEGIN RAISERROR('Table not found', 16, 1); RETURN; END; -- Execute Enterprise optimization EXEC dbo
WHILE @PartitionNumber <= @MaxPartition BEGIN BEGIN TRY -- ONLINE rebuild with compression (Enterprise only) SET @SQL = ' ALTER INDEX ALL ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REBUILD PARTITION = ' + CAST(@PartitionNumber AS NVARCHAR(10)) + ' WITH ( ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = ' + CAST(@MaxDOP AS NVARCHAR(2)) + ', DATA_COMPRESSION = ' + @CompressionType + ', WAIT_AT_LOW_PRIORITY ( MAX_DURATION = ' + CAST(@WaitAtLowPriorityMaxDurationMinutes AS NVARCHAR(3)) + ' MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) );'; PRINT 'Processing partition ' + CAST(@PartitionNumber AS VARCHAR) + ' of ' + CAST(@MaxPartition AS VARCHAR); EXEC sp_executesql @SQL; -- Update log UPDATE dbo.IndexMaintenanceLog SET PartitionsProcessed = @PartitionNumber, LastProcessedTime = GETDATE() WHERE TableName = @SchemaName + '.' + @TableName AND Status = 'Running'; END TRY BEGIN CATCH -- Log error INSERT INTO dbo.ErrorLog (ProcedureName, ErrorMessage, ErrorDate) VALUES ('Enterprise_OptimizeTablePartitions', ERROR_MESSAGE(), GETDATE()); RAISERROR('Failed on partition %d: %s', 16, 1, @PartitionNumber, ERROR_MESSAGE()); END CATCH; SET @PartitionNumber = @PartitionNumber + 1; END; @TableName = 'SalesFact'