SQL Server下实现利用SQL Server Agent Job对索引重建实现Balance Load

/*
this script works to compress pages of tables of all database of the server,
skipping those which were compressed and system databases like master, msdb, tempdb, model
*//*first of all, get the datbase list to construct the sql**/IF object_id('tempdb..#t') IS NOT NULL
BEGIN
DROP TABLE #t
END
GOCREATE TABLE #t(db SYSNAME, object_id INT, index_id INT)
GOIF object_id('tempdb..#t2') IS NOT NULL
BEGIN
DROP TABLE #t2
END
GOCREATE TABLE #t2(ID INT IDENTITY(1,1), db SYSNAME, stm VARCHAR(MAX), dpages INT, object_id INT, index_id INT)
GODECLARE @db SYSNAME,
@sql VARCHAR(MAX) = '',
@sql2 VARCHAR(MAX) = ''DECLARE cur CURSOR FOR
SELECT name FROM sys.databases WHERE name not in ('master', 'msdb', 'tempdb', 'model')OPEN curFETCH NEXT FROM cur INTO @dbWHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + 'UNION ALL select distinct '''+@db+''' db, object_id, index_id from [' + @db + '].sys.partitions(nolock) where data_compression_desc <> ''PAGE''' + CHAR(13);
SET @sql2 = @sql2 + 'UNION ALL SELECT '''+@db+''' as db, CASE id.type_desc WHEN ''HEAP'' THEN ''ALTER TABLE '+QUOTENAME(@db)+'.'' + QUOTENAME(sch.name) + ''.'' + QUOTENAME(ob.name) + '' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);''
ELSE ''ALTER INDEX '' + QUOTENAME(id.name) + '' ON '+QUOTENAME(@db)+'.'' + QUOTENAME(sch.name) + ''.'' + QUOTENAME(ob.name) + '' REBUILD WITH (DATA_COMPRESSION = PAGE);'' END AS stm, syid.dpages, id.object_id, id.index_id
FROM ['+@db+'].sys.indexes id join
['+@db+'].sys.sysindexes syid(nolock) ON syid.id = id.object_id AND syid.indid = id.index_id join
['+@db+'].sys.objects ob(nolock) ON ob.object_id = id.object_id join
['+@db+'].sys.schemas sch(nolock) ON sch.schema_id = ob.schema_id join
#t non_compr on non_compr.object_id = id.object_id and non_compr.index_id = id.index_id
WHERE ob.type_desc = ''USER_TABLE'' ' + CHAR(13); FETCH NEXT FROM cur INTO @db
ENDCLOSE cur
DEALLOCATE curSET @sql = RIGHT(@sql, LEN(@sql)-10)SET @sql2 = RIGHT(@sql2, LEN(@sql2)-10)--INSERT #t EXEC(@sql)/*table #t stores temp data queried from sys.partitions because I found that this DMV runs
slowly when joining other tables in some databases like DW. To avoid it, I store data in a temporary table.**/
INSERT #t EXEC(@sql)/*table #t2 works to output statements to rebuild indexes**/
INSERT #t2(db, stm, dpages, object_id, index_id) EXEC (@sql2) --SELECT * FROM #t2IF OBJECT_ID('Stage.dbo.table1') IS NOT NULL
DROP TABLE Stage.dbo.table1
GODECLARE @nbr_of_workers AS SMALLINT
SET @nbr_of_workers = 6; --actually @nbr_of_workers should be the number plus 1;WITH bal_load AS (
SELECT db, object_id, dpages as obj_dpages, ROW_NUMBER() OVER (PARTITION BY groupfactor ORDER BY dpages DESC) AS group_nbr FROM (
SELECT db, object_id, dpages, NTILE((SELECT COUNT(DISTINCT db+CAST(object_id AS VARCHAR)) FROM #t2)/@nbr_of_workers) OVER (ORDER BY dpages DESC) AS groupfactor FROM (
SELECT db, object_id, SUM(dpages) dpages FROM #t2 GROUP BY db, object_id) T) T)SELECT t.id, t.db, t.stm, t.dpages, t.object_id, t.index_id , bal_load.obj_dpages, bal_load.group_nbr, 0 AS compressed
INTO Stage.dbo.table1
FROM #t2 t JOIN bal_load ON bal_load.db = t.db AND bal_load.object_id = t.object_id
ORDER BY bal_load.group_nbr, bal_load.db, bal_load.obj_dpages DESC, bal_load.object_idCREATE UNIQUE CLUSTERED INDEX CLST_IX_table1 ON Stage.dbo.table1(ID);
GO

IF EXISTS(SELECT * FROM Stage.dbo.table1(NOLOCK) WHERE compressed = 0 AND group_nbr=1)
BEGIN
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(row_nbr INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ID INT, stm VARCHAR(MAX)) DECLARE @cmd VARCHAR(MAX), @ID INT, @stm VARCHAR(MAX), @curr_row_nbr INT = 1, @max_row_nbr INT INSERT #t(ID, stm)
SELECT ID, stm
FROM Stage.dbo.table1(NOLOCK)
WHERE compressed = 0 AND group_nbr=1; SET @max_row_nbr = SCOPE_IDENTITY(); WHILE @curr_row_nbr <= @max_row_nbr
BEGIN
BEGIN TRY
SELECT @ID = ID, @stm = stm FROM #t WHERE row_nbr = @curr_row_nbr;
EXEC(@stm);
UPDATE Stage.dbo.table1 SET compressed = 1 WHERE ID = @ID;
SET @curr_row_nbr = @curr_row_nbr + 1;
END TRY
BEGIN CATCH
--SELECT ERROR_MESSAGE()
SET @curr_row_nbr = @curr_row_nbr + 1;
CONTINUE
END CATCH
END
END