-- Log start INSERT INTO dbo.IndexMaintenanceLog (TableName, Action, StartTime, Status) VALUES (@SchemaName + '.' + @TableName, 'Enterprise Optimize', GETDATE(), 'Running');
-- Create clustered columnstore index on error log (Enterprise 2012+) CREATE CLUSTERED COLUMNSTORE INDEX CCI_ErrorLog ON dbo.ErrorLog WITH (MAXDOP = 4, COMPRESSION_DELAY = 0); GO -- Create partitioned table (Enterprise feature) CREATE PARTITION FUNCTION pf_DateRange (DATETIME) AS RANGE RIGHT FOR VALUES ( '2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01', '2025-01-01' ); CREATE PARTITION SCHEME ps_DateRange AS PARTITION pf_DateRange ALL TO ([PRIMARY]); sql server 2012 enterprise
-- 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; -- Log start INSERT INTO dbo
IF @ObjectID IS NULL BEGIN RAISERROR('Table not found', 16, 1); RETURN; END; COMPRESSION_DELAY = 0)
-- 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;
EXEC dbo.sp_add_jobstep @job_name = N'Enterprise_PartitionOptimization', @step_name = N'Optimize Sales Partition', @command = N'EXEC dbo.Enterprise_OptimizeTablePartitions ''dbo'', ''SalesFact'', ''PAGE'', 4, 5;';