-- Get max partition number SELECT @MaxPartition = MAX(partition_number) FROM sys.partitions WHERE object_id = @ObjectID;
-- 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; -- Get max partition number SELECT @MaxPartition =
