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