文件組：primarypr一世米一種r是primarydbcc shrinkfile() 完成後需要平衡每個文件的範圍計數

我正在使用約 1 TB 的數據庫，伺服器 40+ 核心，記憶體 500G+，SAN 上的數據庫文件（無法控制 lun 段），現在的瓶頸是文件爭用。目前執行 2014 年，但最終“發布”可能是企業的最新版本。

按架構大小（我們不使用 dbo）分隔到最大的塊數據使用量。找到磁碟大小約為 200G 的候選對象，將它們放入包含 4 個文件的文件組中。最後階段是採取

$$ primary $$並分成4個。下面是我為將模式移動到文件組中所做的過程，它不會以所需的方式對主要工作。

alter proc dbo.admin_MoveSchema ( 
        @schema varchar(128) 
       ,@exec bit = 0 
       ,@storage varchar(2048) = null 
       ,@filegroup varchar(128) = null 
       ,@files int = 4 
) 
as 
/*  *************************************************************************** 
*  example:   
   exec dbo.admin_MoveSchema @schema = 'Schema1', @exec = 1 
*  *************************************************************************** 
*/ 

begin try 
   declare 
        @msg varchar(255) 
       ,@separator varchar(255) = replicate('=',128)
   set nocount on; 
   if nullif(@storage,'') is null 
       begin 
           set @storage = (select top 1 reverse(right(reverse(physical_name),abs(charindex('\',reverse(physical_name)) - len(physical_name)))) + '\' from sys.database_files where physical_name like '%.mdf') 
       end; 
   set @filegroup = rtrim(ltrim(replace(replace(@filegroup,']',''),'[','')));
   if nullif(@filegroup,'') is null 
       begin 
           set @filegroup = 'FG_' + @schema  /* will obviously fail if the schema name is already at max size of 128 */ 
       end; 
   declare 
        @s nvarchar(max) 
       ,@i int = 1 
   set @msg = 'Creating Filegroup ' + @filegroup; 
   print @separator; 
   print '||' + replicate(' ', 39) + @msg; 
   raiserror(@separator,0,0) with nowait; 
   begin try 
       set @s = ' 
if not exists (select 1 from sys.filegroups where name = ''' + @filegroup + ''') and ''' + @filegroup + ''' <> ''primary'' 
   begin 
       alter database ' + quotename(db_name()) + ' 
       add filegroup ' + quotename(@filegroup) + '; 
       print ''' + quotename(@filegroup) + ' added!'' 
   end 
       else 
   begin 
       print ''' + quotename(@filegroup) + ' exists.'' 
   end;' 
       if @exec = 1 
           begin 
               exec(@s);
           end 
               else 
           begin 
               print(@s); 
           end; 
       set @msg = 'Creating Files for ' + @filegroup; 
       print @separator; 
       print '||' + replicate(' ', 39) + @msg; 
       raiserror(@separator,0,0) with nowait; 
       while @i <= @files 
           begin 
               set @s = '
if not exists (select * from sys.sysfiles where name = ''' + @filegroup + '_' + right('00' + rtrim(@i),3) + ''') 
   begin 
       alter database [' + db_name() + '] 
       add file 
       ( 
           name = ' + quotename(@filegroup + '_' + right('00' + rtrim(@i),3)) + ', 
           filename = ''' + @storage + @filegroup + '_' + right('00' + rtrim(@i),3) + '.ndf'', 
           size = 8mb, 
           maxsize = unlimited, 
           filegrowth = 10% 
       ) 
       to filegroup ' + quotename(@filegroup) + '; 
       print ''added file: ' + quotename(@filegroup + '_' + right('00' + rtrim(@i),3)) + ''';
   end
       else
   begin
       print ''' + quotename(@filegroup + '_' + right('00' + rtrim(@i),3)) + ' exists'';
   end; '; 
               if @exec = 1 
                   begin 
                       exec(@s); 
                   end 
                       else 
                   begin 
                       print(@s); 
                   end; 
               set @i = @i + 1; 
           end; 
   end try 
   begin catch 
       print error_message(); 
   end catch; 

   declare 
        @schema_name varchar(128) 
       ,@table_name varchar(128) 
       ,@column_name varchar(max) -- collection of columns with asc/desc
       ,@index_name varchar(128) 
   set @msg = 'Moving tables in the schema ' + quotename(@schema) + ' to filegroup ' + quotename(@filegroup); 
   print @separator; 
   print '||' + replicate(' ',18) + @msg; 
   raiserror(@separator,0,0) with nowait; 
   declare idxGen cursor local fast_forward
   for 
   select 
        s.name schema_name
       ,t.name table_name
       ,i.name index_name 
       ,stuff((select convert(varchar(max),',') + quotename(c.name) + (case when ic.is_descending_key = 1 then ' desc' else ' asc' end)
           from sys.columns c
           inner join sys.index_columns ic on c.column_id = ic.column_id and c.object_id = ic.object_id and ic.index_id = i.index_id
           where c.column_id = ic.column_id and t.object_id = ic.object_id 
           order by ic.key_ordinal for xml path('')),1,1,'') column_name 
   from sys.indexes (nolock) i 
   inner join sys.tables (nolock) t on t.object_id = i.object_id and i.type_desc = 'CLUSTERED' 
   inner join sys.schemas (nolock) s on s.schema_id = t.schema_id and t.is_ms_shipped = 0 
   where s.name = @schema 
   open idxGen; 
   fetch next from idxGen into @schema_name,@table_name,@index_name,@column_name; 
           /* 
               first match wins, covers unique clustered, clustered and "with" defaults on most tables 
               for unique and not. I'm not sure if the last 2 every trigger, will check later. 
            */ 
   while @@fetch_status = 0 
       begin 
               select @s = ' 
begin try 
   begin try 
       create unique clustered index ' + quotename(@index_name) + ' on ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' (' + @column_name + ') 
       with (drop_existing=on) 
       on ' + quotename(@filegroup) + '; 
   end try 
   begin catch 
       create clustered index ' + quotename(@index_name) + ' on ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' (' + @column_name + ') 
       with (drop_existing=on) 
       on ' + quotename(@filegroup) + '; 
   end catch; 
   raiserror(''[Moved/On]: ' + quotename(@schema_name) + '.' + quotename(@table_name) + ' to ' + quotename(@filegroup) + ''', 0,0);
end try 
begin catch 
   print error_message(); 
   raiserror(''[### MOVE FAILED ###]: ' + quotename(@schema_name) + '.' + quotename(@table_name) + ''', 0,0);
end catch; '; 
           if @exec = 1 
               begin 
                   exec(@s); 
               end 
                   else 
               begin 
                   print(@s); 
               end; 
           fetch next from idxGen into @schema_name,@table_name,@index_name,@column_name; 
       end; 
   close idxGen; 
   deallocate idxGen; 
   print @separator; 
   print '||' + replicate(' ',15) + 'Moved Tables in schema:' + quotename(@schema) + ' to filegroup:' + quotename(@filegroup); 
   print '||' + replicate(' ',15) + 'If any [### MOVE FAILED ###] were printed above, manual move of that table may be needed.' 
   print @separator; 
   print ' '; 

end try 
begin catch 
   print 'Error! find out why!'; 
   throw
end catch 
GO 

我現在的問題是自動化和/或複制輕鬆拆分

$$ primary $$數據到它自己的 4 拆分。 添加文件和執行 dbcc shrinkfile(1,emptyfile) 的過程很好，但我想在所有文件的頁數彼此相等或接近時自動停止該過程。

我正在考慮創建和啟動一個 sql 代理作業，執行一個循環來檢查每個文件的頁面，並在文件 >= mdf 頁面時通過 kill（dbcc 收縮文件的 spid）停止它，但我真的更願意保留它在一個沒有外部依賴項/要求的腳本中（如啟動 sql 代理）。

因為我需要打開一個新查詢並執行它，直到我接近匹配，然後停止執行 dbcc 命令。

if object_id('tempdb..#fileStats','U') is not null
   begin
       drop table #fileStats;
   end;

create table #fileStats (
    FileId int
   ,FileGroup int
   ,TotalExtents bigint
   ,UserExtents bigint
   ,Name nvarchar(128)
   ,FileName nvarchar(2048)
);
insert into #fileStats (FileId, FileGroup, TotalExtents, UserExtents, Name, FileName)
exec('dbcc showfilestats');

select 
    UserExtents - (select sum(userextents) / count(FileId) from #fileStats where FileGroup = 1) extleft
   ,UserExtents
   ,Name
   ,FileName
from #fileStats 
where FileGroup = 1 
;

我可以將它放在一個帶有waitfordelay的循環中並殺死/退出近場比賽，但我試圖將手動過程排除在外。它需要由不熟練或自動化的過程來執行。

有任何想法嗎？（問SO無濟於事）

如果您使用文件進行臨時儲存，則可以簡化此操作。

1. 向主文件添加 1 個附加文件
2. 執行 EMPTYFILE 將所有數據移動到臨時文件（注意：這將在最後的主數據文件上出錯，但您可以忽略並繼續）
3. 將 3 個數據文件添加到 PRIMARY
4. 對臨時文件執行 EMPTYFILE。

在第 4 步中，SQL 將使用比例填充算法將數據移動到四個文件中，從而生成 4 個大小幾乎相同的文件。

引用自：https://dba.stackexchange.com/questions/231873