Problem
Recently, I read an article on extended events where you can watch proportional fill happen on your files. It reminded of a process I wrote to solve a problem we had with page contention (PFS) with our systems processing upwards of 30K transactions per second. So I wrote a process that will take any of our files that reached a certain size and create new files so we can start using proportional fill. If the case that we already have multiple files in that group we just add four new files when that the existing files exceed the specified size so they can be used for future page allocations.
Normally, you would want to account for this in the initial design of the database but we all know that sometimes databases end up bigger than they were designed to be or in my case we have sharded environment so some databases are bigger than others so it depends on which customers get assigned to which databases and what activity they do on the size of the database.
Solution
The solution I offer allows you to break your files into any size you want by rebuilding your indexes. You will need some disk space for it to create the new files while it runs the process then it will drop the large file. This will also take up some space in your transaction log so if you not running your transaction log backups frequently enough you could have a lot of disk space taken up by that so watch out for that. All the code can be downloaded from my github repository here.
Step 1
First, we create a table that will keep with when we are in the process of doing a filegroup management process. The table will record when the files are added. As part of this process every index as to be rebuilt so there is the RebuildColumn to tell us if that has been completed and the ResizeComplete column to tells rather or not the resize is completed. The DropFile field to say whether to drop the file or not, this is used when you are just added files for the first time and have to rebuild the indexes it drops the original file and the DroppedFile filed let’s know it has been completed.
CREATE TABLE dbo.FilegroupMgt( FilegroupMgtID int IDENTITY(1,1) NOT NULL, DatabaseName nvarchar(128) NOT NULL, FilegroupName nvarchar(128) NOT NULL, AddedDatatimeGMT datetime NOT NULL CONSTRAINT DF_FilegroupMgt_AddedDatatimeGMT DEFAULT (getutcdate()), RebuildComplete bit NOT NULL CONSTRAINT DF_FilegroupMgt_RebuildComplete DEFAULT ((0)), ResizeComplete bit NOT NULL CONSTRAINT DF_FilegroupMgt_ResizeComplete DEFAULT ((0)), DropFile bit NOT NULL CONSTRAINT DF_FilegroupMgt_DropFile DEFAULT ((0)), DroppedFile bit NOT NULL CONSTRAINT DF_FilegroupMgt_DroppedFile DEFAULT ((0)), CONSTRAINT PK_FilegroupMgt PRIMARY KEY CLUSTERED ( FilegroupMgtID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))
Step 2
Next, we will create a table that our procedure can use to store our index rebuild statements as if the process is splitting up the filegroup file for the first time.
CREATE TABLE dbo.FilegroupMgtRebuilds( FilegroupMgtRebuildsID int IDENTITY(1,1) NOT NULL, DatabaseName nvarchar(128) NOT NULL, FilegroupName nvarchar(128) NOT NULL, SQL nvarchar(max) NOT NULL, CONSTRAINT PK_FilegroupMgtRebuilds PRIMARY KEY CLUSTERED ( FilegroupMgtRebuildsID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))
Step 3
Next, we create a stored procedure that determines if we need to split the file into multiple filegroups. Ok, this looks like a really long script but it really does only a couple of things. First, we get max file ID and total size in all the filegroups and store them into a temp table. Then we get the file details for each file and store them in a temp table. Then on line 194, we start a cursor to go through the files where they exceed the size set in parameter @FileSizeGBs or is below the size on only as 10% of the size set in parameter @FileSizeGBs free. Then we determine the number of files to add at the size specified in parameter @FileSizeGbs and insert a record into table FilegroupMGt. If the filegroup only has one file we will set our parameter to tell we need to build rebuild statements. We then also check to make sure we have enough space to add the file, if not we send an email out to get space added to the server to the email specified in the @email parameter use the mail profile specified in the @mailprofile parameter. Finally, if it determines if we only have one file in the filegroup if so it adds the necessary files, it not it just adds to files with that size so that the algorithm can use those files and spread the write load. If it only had one file in the filegroup it will call stored procedure FilegroupMgt_CreateRebuildStmts to create the rebuild statements from the indexes and store them in table FilegroupMgt_RunRebuildStmts.
----------------------------------------------------------------------------------
-- Procedure Name: dbo.FilegroupMgt_AddFiles
--
-- Desc: This procedure checks to see if the last file in a filegroup has reached
-- a certain size. If so it adds another file to the filegroup if there is enough
-- space on the MP, if not and email will be sent to DBOPs.
--
-- Parameters:
-- INPUT
-- @FileSizeGBs - What size to make the files
-- @Debug - Defaults to print information instead of performing actions
--
-- OUTPUT
--
-- Auth: Tracy Boggiano
-- Date: 01/23/2015
--
-- Change History
-- --------------------------------
-- Date - Auth: 9/29/2015 Tracy Boggiano
-- Description: Fix bug to limit number of files created when 4 or more already
-- exist to 4.
----------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[FilegroupMgt_AddFiles]
(
@FileSizeGBs TINYINT = 50,
@mailprofile nvarchar(128),
@email nvarchar(128),
@Debug BIT = 0
)
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX),
@DatabaseID INT,
@DataSpaceID INT,
@DataAvailableGB INT,
@DataVolumeMountPoint NVARCHAR(512),
@Body NVARCHAR(MAX),
@Subject NVARCHAR(255),
@LogicalName NVARCHAR(128),
@NumFiles TINYINT,
@Folder NVARCHAR(260),
@FileName NVARCHAR(260),
@IsPercentGrowth BIT,
@Growth INT,
@FilegroupName NVARCHAR(128),
@TotalSizeGBs DECIMAL(6, 1),
@NumFilesNeeded INT,
@FileLoop TINYINT,
@FileNum TINYINT,
@GBsNeeded INT,
@CallRebuilds BIT = 0,
@FileNumChar NVARCHAR(2),
@FileID INT;
CREATE TABLE #Filegroups
(
DatabaseName NVARCHAR(128) NOT NULL,
FilegroupName NVARCHAR(128) NOT NULL,
DataSpaceID INT NOT NULL,
FileName NVARCHAR(128) NOT NULL,
NumFiles INT NOT NULL,
TotalSizeGB DECIMAL(6, 1) NOT NULL
);
CREATE TABLE #DatabaseFile
(
DatabaseName NVARCHAR(128) NOT NULL,
FilegroupName NVARCHAR(128) NOT NULL,
PhysicalFilename NVARCHAR(260) NOT NULL,
FileID INT NOT NULL,
SizeGB DECIMAL(6, 2) NOT NULL,
FreeSpaceGB DECIMAL(6,2) NOT NULL,
LogicalName NVARCHAR(128),
NumFiles INT NOT NULL,
TotalSizeGB DECIMAL(6, 1) NOT NULL,
Growth INT NOT NULL,
IsPercentGrowth BIT NOT NULL,
DataSpaceID INT NOT NULL
);
CREATE TABLE #SQL
(
ExecSQL NVARCHAR(MAX) NOT NULL
);
CREATE TABLE #SpaceNeeded
(
DatabaseName NVARCHAR(128) NOT NULL,
FilegroupName NVARCHAR(128) NOT NULL,
TempFile INT NOT NULL DEFAULT(0),
TotalFGFiles INT NOT NULL DEFAULT (0),
LogFile INT NOT NULL DEFAULT (0)
);
--Get max file ID and total size in filegroup
SET @SQL = (
SELECT
REPLACE('INSERT INTO #Filegroups
(
DatabaseName,
FilegroupName,
DataSpaceID,
FileName,
NumFiles,
TotalSizeGB
)
SELECT ''' + d.name + ''' AS DatabaseName,
fg.name AS FilegroupName,
MAX(sdf.data_space_id) AS DataSpaceID,
MAX(Sdf.name) AS FileName,
COUNT(*) AS NumFiles,
SUM(sdf.size) / 128.0 / 1024 AS TotalSizeGB
FROM
' + d.name + '.sys.database_files sdf
INNER JOIN ' + d.name + '.sys.filegroups fg
ON sdf.data_space_id = fg.data_space_id
WHERE fg.type = ''FG''
GROUP BY
fg.name;', CHAR(13), '')
FROM dbo.databases AS d
FOR XML PATH('')
);
EXEC(@SQL);
--Get file details
SET @SQL = (
SELECT
REPLACE('USE ' + fg.DatabaseName + ';
INSERT INTO #DatabaseFile
(
DatabaseName,
FilegroupName,
PhysicalFilename,
FileID,
SizeGB,
FreeSpaceGB,
LogicalName,
NumFiles,
TotalSizeGB,
Growth,
IsPercentGrowth,
DataSpaceID
)
SELECT
tfg.DatabaseName,
tfg.FilegroupName,
msdf.physical_name AS PhysicalFilename,
msdf.file_id AS FileID,
msdf.size / 128.0 / 1024 AS SizeGB,
msdf.size / 128.0 / 1024 - CAST(FILEPROPERTY(msdf.name, ''SpaceUsed'') AS INT) / 128.0 / 1024 AS FreeSpaceGB,
msdf.name AS LogicalName,
tfg.NumFiles,
tfg.TotalSizeGB,
sdf.Growth,
sdf.is_percent_growth,
tfg.DataSpaceID
FROM #Filegroups tfg
INNER JOIN ' + fg.DatabaseName + '.sys.filegroups fg
ON tfg.FilegroupName = fg.name
INNER JOIN ' + fg.DatabaseName + '.sys.database_files sdf
ON sdf.Data_Space_ID = tfg.DataSpaceID AND ''' + fg.FilegroupName + ''' = tfg.FilegroupName
INNER JOIN (SELECT
physical_name,
file_id,
size,
name,
Data_Space_ID
FROM ' + fg.DatabaseName + '.sys.database_files sdf ) msdf
ON tfg.DataSpaceID = msdf.Data_Space_ID AND msdf.name = tfg.FileName
WHERE tfg.DatabaseName = ''' + fg.DatabaseName + '''
GROUP BY
tfg.DatabaseName,
tfg.FilegroupName,
msdf.physical_name,
msdf.file_id,
msdf.name,
msdf.size,
tfg.NumFiles,
tfg.TotalSizeGB,
sdf.Growth,
sdf.is_percent_growth,
tfg.DataSpaceID;', CHAR(13), '')
FROM #FileGroups AS fg
FOR XML PATH('')
);
EXEC (@SQL);
--Generate ALTER command and check for free space before creating, if not enough free space send email
--Put in cursor to be able to execute DMF sys.dm_os_volume_stats
DECLARE dfcursor CURSOR
READ_ONLY
FOR
SELECT d.database_id AS DatabaseID ,
df.DataSpaceID ,
df.LogicalName ,
NumFiles ,
REVERSE(RIGHT(REVERSE(PhysicalFilename), ( LEN(PhysicalFilename) - CHARINDEX('\', REVERSE(PhysicalFilename), 1) ) + 1)) AS Folder ,
LEFT(REVERSE(LEFT(REVERSE(PhysicalFilename), CHARINDEX('\', REVERSE(PhysicalFilename)) - 1)), LEN(REVERSE(LEFT(REVERSE(PhysicalFilename), CHARINDEX('\', REVERSE(PhysicalFilename)) - 1))) - 4) AS Filename ,
IsPercentGrowth ,
Growth / 128 Growth ,
df.FilegroupName ,
TotalSizeGB,
df.FileID
FROM #DatabaseFile df
INNER JOIN dbo.databases d ON d.name = df.DatabaseName
INNER JOIN ( SELECT DatabaseName ,
df.FilegroupName ,
MIN(df.FileID) FileID
FROM #DatabaseFile df
INNER JOIN dbo.vAccessibleChangeableUserDBs d ON d.name = df.DatabaseName
GROUP BY DatabaseName ,
df.FilegroupName
) t ON d.NAME = t.DatabaseName
AND df.FilegroupName = t.FilegroupName
AND df.FileID = t.FileID
WHERE ( SizeGB = @FileSizeGBs
AND FreeSpaceGB / SizeGB <= .1
)
OR SizeGB > @FileSizeGBs
OPEN dfcursor
FETCH NEXT FROM dfcursor INTO @DatabaseID, @DataSpaceID, @LogicalName, @NumFiles, @Folder, @FileName, @IsPercentGrowth,
@Growth, @FilegroupName, @TotalSizeGBs, @FileID;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @NumFiles > 1
BEGIN
--Find number of files and double to avoid off balance files
SET @NumFilesNeeded = @NumFiles * 2
IF @NumFiles >= 4
BEGIN
SET @NumFilesNeeded = 4
END
END
ELSE
BEGIN
--Get number of files plus one because we are dropping the intial file
SET @NumFilesNeeded = CEILING(@TotalSizeGBs / @FileSizeGBs)
SET @CallRebuilds = 1
END
SET @GBsNeeded = @NumFilesNeeded * @FileSizeGBs
SELECT
@DataAvailableGB = (available_bytes / 1024 / 1024 / 1024) - (total_bytes / 1024 /1024 / 1024 * 0.10),
@DataVolumeMountPoint = volume_mount_point
FROM sys.dm_os_volume_stats(@DatabaseID, @FileID) AS dovs;
IF @DataAvailableGB >= @GBsNeeded
BEGIN
IF @Debug = 0
BEGIN
INSERT INTO dbo.FilegroupMGt
(
DatabaseName,
FilegroupName,
DropFile
)
VALUES
(
DB_NAME(@DatabaseID),
@FilegroupName,
CASE @NumFiles
WHEN 1 THEN 1
ELSE 0
END
);
END
--Add enough files to distribute data
IF @NumFilesNeeded + @NumFiles <> @NumFiles AND @NumFilesNeeded > 1
BEGIN
SET @FileLoop = @NumFiles + 1
SET @FileLoop = 1
IF @NumFiles > 1
BEGIN
SET @FileNum = @NumFiles + 2
END
ELSE
BEGIN
SET @FileNum = 2
END
WHILE @FileLoop <= @NumFilesNeeded
BEGIN
SET @FileNumChar = CASE LEN(@FileNum)
WHEN 1 THEN '0' + CAST(@FileNum AS NVARCHAR(2))
ELSE CAST(@FileNum AS NVARCHAR(2))
END
SET @SQL = 'ALTER DATABASE [' + DB_NAME(@DatabaseID) + '] ADD FILE (NAME = N'''
+ CASE @NumFiles
WHEN 1 THEN @LogicalName
ELSE LEFT(@LogicalName, LEN(@LogicalName) - 3)
END
+ '_' + @FileNumChar
+ ''', '
+ 'FILENAME = N''' + @Folder
+ CASE @NumFiles
WHEN 1 THEN @FileName
ELSE LEFT(@Filename, LEN(@FileName) - 3)
END
+ '_' + @FileNumChar + '.NDF'', '
+ 'SIZE = ' + CAST(CAST(@FileSizeGBs AS TINYINT) AS NVARCHAR(5)) + ' GB, FILEGROWTH = ' + CAST(@Growth as NVARCHAR(8))
+ CASE @IsPercentGrowth
WHEN 1 THEN '%'
ELSE 'MB'
END
+ ' ) TO FILEGROUP [' + @FilegroupName + ']; '
IF @Debug = 1
BEGIN
PRINT @SQL;
END
ELSE
BEGIN
EXEC (@SQL);
END
SET @FileNum = @FileNum + 1
SET @FileLoop = @FileLoop + 1
END
END
END
ELSE
BEGIN
IF @GBsNeeded > @FileSizeGBs
BEGIN
IF @Debug = 1
BEGIN
PRINT 'Mount Point or Drive ' + @DataVolumeMountPoint + ' on ' + @@SERVERNAME
+ ' for database ' + DB_NAME(@DatabaseID) + ' and Filegroup ' + @FilegroupName
+ ' needs to be expanded to add new ' + CAST(@GBsNeeded as NVARCHAR(4))
+ ' GBs for rebalancing.';
END
ELSE
BEGIN
SET @Body = 'Mount Point or Drive ' + @DataVolumeMountPoint + ' on ' + @@SERVERNAME
+ ' for database ' + DB_NAME(@DatabaseID) + ' and Filegroup ' + @FilegroupName
+ ' needs to be expanded to add new ' + CAST(@GBsNeeded as NVARCHAR(4))
+ ' GBs for rebalancing.';
SET @Subject = 'Mount Point or Drive Expansion Needed on ' + @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @mailprofile,
@recipients = @email,
@body = @Body,
@subject = @Subject;
END
END
END
END
FETCH NEXT FROM dfcursor INTO @DatabaseID, @DataSpaceID, @LogicalName, @NumFiles, @Folder, @FileName, @IsPercentGrowth,
@Growth, @FilegroupName, @TotalSizeGBs, @FileID;
END
CLOSE dfcursor;
DEALLOCATE dfcursor;
DROP TABLE #FileGroups;
DROP TABLE #DatabaseFile;
DROP TABLE #SQL;
DROP TABLE #SpaceNeeded;
IF @CallRebuilds = 1
BEGIN
EXEC dbo.FilegroupMgt_CreateRebuildStmts @Debug = @Debug
END
GO
Step 4
Next, create procedure FilegroupMgt_CreateRebuildStmts. This procedure generates the rebuild statements needed to proportionally fill the files you created.
----------------------------------------------------------------------------------
-- Procedure Name: dbo.FilegroupMgt_CreateRebuildStmts
--
-- Desc: This procedure creates create index statements to rebalance files.
--
-- Parameters:
-- INPUT
-- @Debug BIT = 0
--
-- OUTPUT
--
-- Auth: Tracy Boggiano
-- Date: 01/23/2015
--
-- Change History
-- --------------------------------
-- Date - Auth: 09/14/2015 - Tracy Boggiano
-- Description: Add QUOTENAME around column names
----------------------------------------------------------------------------------
CREATE PROC [dbo].[FilegroupMgt_CreateRebuildStmts]
@Debug BIT = 0
AS
SET NOCOUNT ON
DECLARE
@FileGroupMgtID INT,
@DatabaseName NVARCHAR(128),
@FilegroupName NVARCHAR(128),
@SQL NVARCHAR(MAX),
@FilegroupMgtRebuildsID INT
DECLARE CursorFilegroup CURSOR
FOR
SELECT
fgm.FilegroupMgtID,
fgm.DatabaseName,
fgm.FilegroupName
FROM
dbo.FilegroupMgt fgm
WHERE
RebuildComplete = 0
OR ResizeComplete = 0
OPEN CursorFilegroup
FETCH NEXT FROM CursorFilegroup INTO @FileGroupMgtID, @DatabaseName, @FilegroupName
WHILE ( @@fetch_status = 0 )
BEGIN
SET @SQL = REPLACE(REPLACE(REPLACE(CAST( -- <<--- Added this CAST
'
USE {{DatabaseName}};
DECLARE
@SchemaName VARCHAR(100),
@TableName VARCHAR(256),
@IndexName VARCHAR(256),
@ColumnName VARCHAR(100),
@is_unique VARCHAR(100),
@IndexTypeDesc VARCHAR(100),
@FileGroupName VARCHAR(100),
@IndexOptions VARCHAR(MAX),
@IndexColumnId INT,
@IsDescendingKey INT,
@IsIncludedColumn INT,
@TSQLScripCreationIndex VARCHAR(MAX),
@PartitionScheme AS NVARCHAR(128),
@PartitionColumn AS NVARCHAR(128),
@IndexType AS TINYINT,
@ObjectID AS INT,
@IndexID AS INT,
@LOB AS TINYINT,
@SkipXMLSpatial TINYINT,
@IsFiltered BIT,
@FilteredDefinition NVARCHAR(MAX),
@DataCompressionDesc NVARCHAR(4);
DECLARE CursorIndex CURSOR
FOR
SELECT
SCHEMA_NAME(t.schema_id) SchemaName,
t.name TableName,
ix.name IndexName,
ISNULL(ps.name, "") PartitionName,
c.name PartitionColumn,
CASE WHEN ix.is_unique = 1 THEN "UNIQUE "
ELSE ""
END,
ix.type_desc,
CASE WHEN ix.is_padded = 1 THEN "PAD_INDEX = ON, "
ELSE "PAD_INDEX = OFF, "
END + CASE WHEN ix.allow_page_locks = 1 THEN "ALLOW_PAGE_LOCKS = ON, "
ELSE "ALLOW_PAGE_LOCKS = OFF, "
END + CASE WHEN ix.allow_row_locks = 1 THEN "ALLOW_ROW_LOCKS = ON, "
ELSE "ALLOW_ROW_LOCKS = OFF, "
END
+ CASE WHEN INDEXPROPERTY(t.object_id, ix.name, "IsStatistics") = 1 THEN "STATISTICS_NORECOMPUTE = ON, "
ELSE "STATISTICS_NORECOMPUTE = OFF, "
END + CASE WHEN ix.ignore_dup_key = 1 THEN "IGNORE_DUP_KEY = ON, "
ELSE "IGNORE_DUP_KEY = OFF, "
END + "SORT_IN_TEMPDB = OFF, FILLFACTOR = " + CAST(CASE ix.fill_factor
WHEN 0 THEN 100
ELSE ix.fill_factor
END AS VARCHAR(3)) AS IndexOptions,
fg.name FileGroupName,
ix.type,
t.object_id,
ix.index_id,
ix.has_filter,
ix.filter_definition,
p.data_compression_desc
FROM
sys.tables t
INNER JOIN sys.indexes ix
ON t.object_id = ix.object_id
INNER JOIN sys.partitions p
ON ix.object_id = p.object_id
AND ix.index_id = p.index_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ix.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.index_columns ic
ON (ic.partition_ordinal > 0)
AND (ic.index_id = ix.index_id AND ic.object_id = CAST(t.object_id AS INT))
LEFT OUTER JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
INNER JOIN sys.filegroups fg
ON COALESCE(dds.data_space_id, ix.data_space_id) = fg.data_space_id
WHERE
ix.type > 0
AND t.is_ms_shipped = 0
AND fg.name = "{{FilegroupName}}"
AND ix.is_unique_constraint = 0
ORDER BY
SCHEMA_NAME(t.schema_id),
t.name,
ix.name
OPEN CursorIndex
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @PartitionScheme, @PartitionColumn,
@is_unique, @IndexTypeDesc, @IndexOptions, @FileGroupName, @IndexType, @ObjectID, @IndexID, @IsFiltered,
@FilteredDefinition, @DataCompressionDesc
WHILE ( @@fetch_status = 0 )
BEGIN
DECLARE @IndexColumns VARCHAR(MAX)
DECLARE @IncludedColumns VARCHAR(MAX)
SET @LOB = 0
SET @IndexColumns = ""
SET @IncludedColumns = ""
SET @SkipXMLSpatial = 0
DECLARE CursorIndexColumn CURSOR
FOR
SELECT
col.name,
ixc.is_descending_key,
ixc.is_included_column
FROM
sys.tables tb
INNER JOIN sys.indexes ix
ON tb.object_id = ix.object_id
INNER JOIN sys.index_columns ixc
ON ix.object_id = ixc.object_id
AND ix.index_id = ixc.index_id
INNER JOIN sys.columns col
ON ixc.object_id = col.object_id
AND ixc.column_id = col.column_id
WHERE
ix.type > 0
AND SCHEMA_NAME(tb.schema_id) = @SchemaName
AND tb.name = @TableName
AND ix.name = @IndexName
AND (ixc.partition_ordinal <> ixc.key_ordinal --Lead column (fixes boo-boo)
AND (ixc.partition_ordinal <> 1 and ixc.key_ordinal > 0) --PartitionID or other partitioned field
OR (ixc.partition_ordinal = 1 and ixc.key_ordinal > 0)
OR ixc.is_included_column = 1) -- But Include if included column
ORDER BY
ixc.key_ordinal
OPEN CursorIndexColumn
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
WHILE ( @@fetch_status = 0 )
BEGIN
IF @IsIncludedColumn = 0
SET @IndexColumns = @IndexColumns + QUOTENAME(@ColumnName) + CASE WHEN @IsDescendingKey = 1 THEN " DESC, "
ELSE " ASC, "
END
ELSE
SET @IncludedColumns = @IncludedColumns + QUOTENAME(@ColumnName) + ", "
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
END
CLOSE CursorIndexColumn
DEALLOCATE CursorIndexColumn
SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1)
SET @IncludedColumns = CASE WHEN LEN(@IncludedColumns) > 0
THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1)
ELSE ""
END
/* Determine if the table contains LOBs */
IF @IndexType = 1
BEGIN
SELECT @LOB = COUNT(*)
FROM {{DatabaseName}}.sys.columns COLUMNS
INNER JOIN {{DatabaseName}}.sys.types types
ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1)
WHERE [object_id] = @ObjectID
AND types.name IN("image", "text", "ntext") ;
SELECT @SkipXMLSpatial = COUNT(*)
FROM {{DatabaseName}}.sys.columns COLUMNS
INNER JOIN {{DatabaseName}}.sys.types types
ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1)
WHERE [object_id] = @ObjectID
AND types.name IN("xml", "geometry", "geography") ;
END
ELSE IF @indexType = 2
BEGIN
SELECT @LOB = COUNT(*)
FROM {{DatabaseName}}.sys.index_columns index_columns
INNER JOIN {{DatabaseName}}.sys.columns columns
ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id
INNER JOIN {{DatabaseName}}.sys.types types
ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1)
WHERE index_columns.[object_id] = @ObjectID
AND index_columns.index_id = @indexID
AND types.name IN("image", "text", "ntext") ;
END
SET @TSQLScripCreationIndex = ""
SET @TSQLScripCreationIndex = "CREATE " + @is_unique + @IndexTypeDesc + " INDEX " + QUOTENAME(@IndexName)
+ " ON " + QUOTENAME(@SchemaName) + "." + QUOTENAME(@TableName) + "(" + @IndexColumns + ") "
+ CASE WHEN LEN(@IncludedColumns) > 0 THEN CHAR(13) + "INCLUDE (" + @IncludedColumns + ") "
ELSE " "
END + CHAR(13)
+ CASE WHEN @IsFiltered = 1 THEN "WHERE " + @FilteredDefinition + " "
ELSE " "
END
+ "WITH ( ONLINE = " + CASE @LOB WHEN 0 THEN "ON" ELSE "OFF" END + ", DROP_EXISTING = ON, "
+ "DATA_COMPRESSION = " + @DataCompressionDesc + ", "
+ @IndexOptions + ")"
+ CASE @PartitionScheme
WHEN "" THEN " ON " + QUOTENAME(@FileGroupName)
ELSE " ON " + @PartitionScheme + "(" + QUOTENAME(@PartitionColumn) + ")"
END
+ ";"
IF @indexType IN (1, 2) AND @SkipXMLSpatial = 0
BEGIN
INSERT INTO DBA.dbo.FilegroupMgtRebuilds
( DatabaseName,
FilegroupName,
SQL )
VALUES
( DB_NAME(),
"{{FilegroupName}}",
@TSQLScripCreationIndex )
END
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @PartitionScheme, @PartitionColumn,
@is_unique, @IndexTypeDesc, @IndexOptions, @FileGroupName, @IndexType, @ObjectID, @IndexID, @IsFiltered,
@FilteredDefinition, @DataCompressionDesc
END
CLOSE CursorIndex
DEALLOCATE CursorIndex' AS NVARCHAR(MAX))
,'{{DatabaseName}}', @DatabaseName)
,'{{FilegroupName}}', @FilegroupName)
,'"','''')
IF @Debug = 0
BEGIN
EXEC (@SQL);
END
ELSE
BEGIN
PRINT @SQL;
END
FETCH NEXT FROM CursorFilegroup INTO @FileGroupMgtID, @DatabaseName, @FilegroupName
END
CLOSE CursorFilegroup
DEALLOCATE CursorFilegroup
--Remove duplicate due to partitioning
DECLARE CursorDuplicates CURSOR
READ_ONLY
FOR
SELECT
DatabaseName,
MIN(FilegroupMgtRebuildsID)
FROM dbo.FilegroupMgtRebuilds
GROUP BY DatabaseName,
SQL
HAVING COUNT(*)>1
OPEN CursorDuplicates
FETCH NEXT FROM CursorDuplicates INTO @DatabaseName, @FilegroupMgtRebuildsID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DELETE FROM
dbo.FilegroupMgtRebuilds
WHERE
SQL IN ( SELECT
SQL
FROM
dbo.FilegroupMgtRebuilds
WHERE
FilegroupMgtRebuildsID = @FilegroupMgtRebuildsID
AND DatabaseName = @DatabaseName )
AND FilegroupMgtRebuildsID <> @FilegroupMgtRebuildsID
END
FETCH NEXT FROM CursorDuplicates INTO @DatabaseName, @FilegroupMgtRebuildsID
END
CLOSE CursorDuplicates
DEALLOCATE CursorDuplicates
GO
Step 5
Next, we need to create a procedure that will go through and rebuild the indexes if needed to.
----------------------------------------------------------------------------------
-- Procedure Name: dbo.FilegroupMgt_RunRebuildStmts
--
-- Desc: This procedure shrinks runs created index statements for rebalance.
--
-- Parameters:
-- INPUT
-- @Debug BIT = 0
--
-- OUTPUT
--
-- Auth: Tracy Boggiano
-- Date: 01/23/2015
--
-- Change History
-- --------------------------------
-- Date - Auth:
-- Description:
----------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[FilegroupMgt_RunRebuildStmts]
@Debug BIT = 0
AS
SET NOCOUNT ON
DECLARE
@Size TINYINT = 50,
@FilegroupMgtRebuildsID INT,
@DatabaseName NVARCHAR(128),
@FilegroupName NVARCHAR(128),
@SQL NVARCHAR(MAX),
@OldDatabaseName NVARCHAR(128) = '',
@OldFilegroupName NVARCHAR(128) = '',
@Count INT=0,
@ShrinkComplete BIT = 0,
@DropFile BIT
CREATE TABLE #File (FileName NVARCHAR(128))
DECLARE fg_cursor CURSOR READ_ONLY
FOR
SELECT
fgr.FilegroupMgtRebuildsID,
fgr.DatabaseName,
fgr.FilegroupName,
fgr.SQL
FROM
dbo.FilegroupMgtRebuilds AS fgr
INNER JOIN dbo.FilegroupMgt AS fm
ON fm.DatabaseName = fgr.DatabaseName AND fm.FilegroupName = fgr.FilegroupName
WHERE RebuildComplete = 0
OPEN fg_cursor
FETCH NEXT FROM fg_cursor INTO @FilegroupMgtRebuildsID, @DatabaseName, @FilegroupName, @SQL
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--Reset if DatabaseName or FilegroupName has changed
IF @OldDatabaseName <> @DatabaseName OR @OldFilegroupName <> @FilegroupName
BEGIN
SET @Count = 0
SET @ShrinkComplete = 0
--If databasename or filegroup name changed mark rebuild complete
IF @OldDatabaseName <> '' AND @Debug = 0
BEGIN
UPDATE dbo.FilegroupMgt SET RebuildComplete = 1
WHERE DatabaseName = @OldDatabaseName
AND FilegroupName = @OldFilegroupName
END
--if filgroupname changed shrink file
IF @OldFilegroupName <> '' AND @Debug = 0
BEGIN
EXEC dbo.FilegroupMgt_ShrinkFile @DatabaseName = @OldDatabaseName,
@FilegroupName = @OldFilegroupName,
@Size = @Size,
@Debug = @Debug,
@ShrinkComplete = @ShrinkComplete OUTPUT
END
ELSE IF @Debug = 0 --if filegroupname is new then shrink for first run
BEGIN
EXEC dbo.FilegroupMgt_ShrinkFile @DatabaseName = @DatabaseName,
@FilegroupName = @FilegroupName,
@Size = @Size,
@Debug = @Debug,
@ShrinkComplete = @ShrinkComplete OUTPUT
END
IF @OldDatabaseName <> '' AND @ShrinkComplete = 1 AND @Debug = 0
BEGIN
UPDATE dbo.FilegroupMgt SET ResizeComplete = 1
WHERE DatabaseName = @OldDatabaseName
AND FilegroupName = @OldFilegroupName
END
IF @Debug = 0
BEGIN
UPDATE dbo.FilegroupMgt SET RebuildComplete = 1
WHERE DatabaseName = @OldDatabaseName
AND FilegroupName = @OldFilegroupName
END
SET @OldDatabaseName = @DatabaseName
SET @OldFilegroupName = @FilegroupName
END
--Remove empty space before starting when changing databases or filegroups
--OR IF processed 10 indexes and the file is not completely shrunk
IF @Count <> 0 AND @Count % 10 = 0 AND @ShrinkComplete = 0 AND @Debug = 0--Every 10th index if Shrink not complete
BEGIN
EXEC dbo.FilegroupMgt_ShrinkFile @DatabaseName = @DatabaseName,
@FilegroupName = @FilegroupName,
@Size = @Size,
@Debug = @Debug,
@ShrinkComplete = @ShrinkComplete OUTPUT
IF @ShrinkComplete = 1
BEGIN
UPDATE dbo.FilegroupMgt SET ResizeComplete = 1
WHERE DatabaseName = @DatabaseName
AND FilegroupName = @FilegroupName
END
END
SET @SQL = 'USE ' + @DatabaseName + '; ' + @SQL
IF @Debug = 0
BEGIN
EXEC (@SQL)
DELETE FROM dbo.FilegroupMgtRebuilds
WHERE FilegroupMgtRebuildsID = @FilegroupMgtRebuildsID
END
ELSE
BEGIN
PRINT @SQL
END
SET @OldDatabaseName = @DatabaseName
SET @OldFilegroupName = @FilegroupName
SET @Count = @Count + 1
END
FETCH NEXT FROM fg_cursor INTO @FilegroupMgtRebuildsID, @DatabaseName, @FilegroupName, @SQL
END
CLOSE fg_cursor
DEALLOCATE fg_cursor
IF @ShrinkComplete = 0 AND @Debug = 0
BEGIN
EXEC dbo.FilegroupMgt_ShrinkFile @DatabaseName = @DatabaseName,
@FilegroupName = @FilegroupName,
@Size = @Size,
@Debug = @Debug,
@ShrinkComplete = @ShrinkComplete OUTPUT
END
IF @Debug = 0
BEGIN
UPDATE dbo.FilegroupMgt SET RebuildComplete = 1
WHERE DatabaseName = @OldDatabaseName
IF @ShrinkComplete = 1
BEGIN
UPDATE dbo.FilegroupMgt SET ResizeComplete = 1
WHERE DatabaseName = @DatabaseName
AND FilegroupName = @FilegroupName
END
END
--Final Shrinks if Needed
DECLARE CursorFinalShrink CURSOR
READ_ONLY
FOR
SELECT
DatabaseName,
FilegroupName,
DropFile
FROM dbo.FilegroupMgt
WHERE (ResizeComplete = 0
OR DropFile = 1)
AND DroppedFile = 0
OPEN CursorFinalShrink
FETCH NEXT FROM CursorFinalShrink INTO @DatabaseName, @FilegroupName, @DropFile
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @DropFile = 1
BEGIN
TRUNCATE TABLE #File
SET @SQL = REPLACE(REPLACE(REPLACE(CAST(
'USE {{DatabaseName}};
SELECT TOP 1
df.name
FROM
sys.database_Files df
INNER JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id
WHERE fg.name = "{{FilegroupName}}"
ORDER BY df.name;
' AS NVARCHAR(MAX))
,'{{DatabaseName}}', @DatabaseName)
,'{{FilegroupName}}', @FilegroupName)
,'"','''')
INSERT INTO #File
EXEC (@SQL);
SELECT @SQL = REPLACE(REPLACE(REPLACE(CAST(
'USE {{DatabaseName}}; DBCC SHRINKFILE ("{{FileName}}", EMPTYFILE);
ALTER DATABASE {{DatabaseName}} REMOVE FILE {{FileName}};
' AS NVARCHAR(MAX))
,'{{DatabaseName}}', @DatabaseName)
,'{{Filename}}', FILENAME)
,'"', '''')
FROM #File
IF @Debug = 0
BEGIN
EXEC (@SQL);
UPDATE dbo.FilegroupMgt SET ResizeComplete = 1,
DroppedFile = 1,
RebuildComplete = 1
WHERE DatabaseName = @DatabaseName
AND FilegroupName = @FilegroupName
END
ELSE
BEGIN
PRINT @SQL
END
END
ELSE IF @Debug = 0
BEGIN
EXEC dbo.FilegroupMgt_ShrinkFile @DatabaseName = @DatabaseName,
@FilegroupName = @FilegroupName,
@Size = @Size,
@Debug = @Debug,
@ShrinkComplete = @ShrinkComplete OUTPUT
IF @ShrinkComplete = 1
BEGIN
UPDATE dbo.FilegroupMgt SET ResizeComplete = 1
WHERE DatabaseName = @DatabaseName
AND FilegroupName = @FilegroupName
END
END
END
FETCH NEXT FROM CursorFinalShrink INTO @DatabaseName, @FilegroupName, @DropFile
END
CLOSE CursorFinalShrink
DEALLOCATE CursorFinalShrink
GO
Step 6
To use what you have so far you need to create a job with the following code in it. Just point it to the database you store your objects in, I have a DBA database I use for all processes. We schedule this process to just one once a day a 9 AM Monday through Friday.
USE DBA GO IF NOT EXISTS(SELECT 1 FROM dbo.FilegroupMgtRebuilds) AND NOT EXISTS (SELECT 1 FROM dbo.FilegroupMgt WHERE RebuildComplete = 0 OR ResizeComplete = 0) BEGIN EXEC dbo.FilegroupMgt_AddFiles END
Step 7
Then you schedule another job to actually rebuild your indexes. We have this schedule to run every hour just in case it gets stopped for some reason.
USE DBA GO EXEC dbo.FilegroupMgt_RunRebuildStmts @debug =0
Conclusion
If you need to split up a large filegroup this is a solution we have had in place at work for a couple of years that works and has relieved page allocation contention for us and allowed us to take advantage for proportion page fill. So set up a test database and give it a try with a small size and see if it something that will work for you.
