How having too many filegroups can affect recovery time

I was on a conference-call on Friday with a customer and we were discussing their plan to have thousands of filegroups, with a table in each, as a way to improve recovery times in the event of a disaster (the tables are unrelated to each other so this solution isn’t unreasonable).

One drawback of the approach that was pointed out is that at database startup time, each file is opened to check the validity of the file header page. This is done sequentially, and the database cannot be brought online until it’s finished. I was curious as to how much this could delay database availability in the event of a disaster so I ran some tests.

The machine specs are as follows:

· Dell Precision 670

· Dual-proc, dual-core x64

· 4GB memory

· RAID 10 array with 4x142GB 15000rpm disks

So the machine is no slug.

I created a database and added various numbers of filegroups to it to test the startup time. Here are the results, with the startup time measured in seconds:

Filegroups Startup Time
100 1
200 2
500 7
1000 12
2000 29
5000 74
10000 184
15000 309

As you can see, when the number of filegroups starts to get high, the startup time delay gets into minutes (5 minutes for 15000 filegroups, not counting any crash recovery that may have to run) – which may be unacceptable if you have a very low maximum-downtime service level agreement.

One interesting thing is that as the number of files got over 15000 or so, the time it took to create additional files got way slower. For instance, creating filegroups 19600 through 19700 took 4 minutes! My guess is that this is an NTFS issue with such a huge number of files in a directory. I ran out of time after waiting a couple of hours so killed the test before it could complete the 20000 filegroups number.

If you’re interested, here’s the code I used to automate running this test:

USE

master;

GO

SET

NOCOUNT ON;

-- create our test database

IF

DATABASEPROPERTY (N'filegrouptest', 'Version') > 0

DROP DATABASE filegrouptest;

GO

CREATE

DATABASE filegrouptest

ON

(

NAME = fgt_mdf,

FILENAME = 'c:filegrouptestfilegrouptest.mdf',

SIZE = 2MB)

LOG

ON

(

NAME = fgt_log,

FILENAME = 'c:filegrouptestfilegrouptest.ldf',

SIZE = 1MB);

GO

-- create a table to hold our filegroup count test points and insert them

IF

OBJECTPROPERTY (OBJECT_ID (N'master.dbo.filegroups'), 'IsUserTable') = 1

DROP TABLE dbo.filegroups;

GO

CREATE

TABLE dbo.filegroups (counter INT);

GO

INSERT

INTO dbo.filegroups VALUES (101);

INSERT

INTO dbo.filegroups VALUES (201);

INSERT

INTO dbo.filegroups VALUES (501);

INSERT

INTO dbo.filegroups VALUES (1001);

INSERT

INTO dbo.filegroups VALUES (2001);

INSERT

INTO dbo.filegroups VALUES (5001);

INSERT

INTO dbo.filegroups VALUES (10001);

INSERT

INTO dbo.filegroups VALUES (15001);

INSERT

INTO dbo.filegroups VALUES (20001);

GO

-- create a table to hold the results

IF

OBJECTPROPERTY (OBJECT_ID (N'master.dbo.filegroups'), 'IsUserTable') = 1

DROP TABLE dbo.testresults;

GO

CREATE

TABLE dbo.testresults (counter INT, starttime INT);

GO

-- declare some variables

DECLARE

@fgnum INT;

DECLARE

@endfgnum INT;

DECLARE

@command CHAR (500);

DECLARE

@starttime DATETIME;

SELECT

@fgnum = 2;

DECLARE

counters CURSOR FOR

SELECT counter FROM dbo.filegroups;

-- open the cursor

OPEN

counters;

-- Get the first filegroup count to test

FETCH

NEXT FROM counters INTO @endfgnum;

WHILE

@@FETCH_STATUS = 0

BEGIN

-- status

SELECT 'Starting ' + CONVERT (VARCHAR, @fgnum) + ' to '

+ CONVERT (VARCHAR, @endfgnum - 1) + ' at '

+ CONVERT (VARCHAR, GETDATE ())

-- create the filegroups

WHILE (@fgnum < @endfgnum)

BEGIN

SELECT @command = 'ALTER DATABASE filegrouptest ADD FILEGROUP fgt'

+ CONVERT (VARCHAR, @fgnum) + ';'

EXEC (@command)

SELECT @command = 'ALTER DATABASE filegrouptest ADD FILE (NAME = fgt_fg'

+ CONVERT (VARCHAR, @fgnum)

+ ', FILENAME = ' + '''c:filegrouptestfgt_fg'

+ CONVERT (VARCHAR, @fgnum)

+ ''', SIZE = 512KB) TO FILEGROUP fgt'

+ CONVERT (VARCHAR, @fgnum) + ';'

EXEC (@command)

SELECT @fgnum = @fgnum + 1

END

-- detach the database

EXEC SP_DETACH_DB 'filegrouptest'

-- attach the database to measure startup time

SELECT @starttime = GETDATE ()

CREATE DATABASE filegrouptest

ON (FILENAME = 'c:filegrouptestfilegrouptest.mdf')

FOR ATTACH

INSERT INTO dbo.testresults VALUES (

@endfgnum

- 1,

DATEDIFF (ms, @starttime, GETDATE ()))

FETCH NEXT FROM counters INTO @endfgnum

END

;

-- close and deallocate the cursor

CLOSE

counters;

DEALLOCATE

counters;

GO

-- get the test result

SELECT

counter AS 'FG Count', starttime AS 'Startup Time'

FROM

dbo.testresults;

GO