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;

SET NOCOUNT ON;

-- create our test database

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

DROP DATABASE filegrouptest;

CREATE DATABASE filegrouptest

( NAME = fgt_mdf,

FILENAME = 'c:\filegrouptest\filegrouptest.mdf',

SIZE = 2MB)

LOG ON

( NAME = fgt_log,

FILENAME = 'c:\filegrouptest\filegrouptest.ldf',

SIZE = 1MB);

-- 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;

CREATE TABLE dbo.filegroups (counter INT);

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);

-- create a table to hold the results

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

DROP TABLE dbo.testresults;

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

-- 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:\filegrouptest\fgt_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:\filegrouptest\filegrouptest.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;

-- get the test result

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

FROM dbo.testresults;

SQL Server Storage Engine Blog

AlwaysOn: Comparing Readable Secondary with the similar functionality available in DB2

AlwaysOn: I just enabled Readable Secondary but my query is blocked?

AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica

How having too many filegroups can affect recovery time