-- 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;
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; sql server 2012 enterprise
-- Get max partition number SELECT @MaxPartition = MAX(partition_number) FROM sys.partitions WHERE object_id = @ObjectID; -- Execute Enterprise optimization EXEC dbo