How having too many filegroups can affect recovery time

How having too many filegroups can affect recovery time

  • Comments 1

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:\filegrouptest\filegrouptest.mdf',

   SIZE = 2MB)

LOG ON

   ( NAME = fgt_log,

   FILENAME = 'c:\filegrouptest\filegrouptest.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:\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;

GO

-- get the test result

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

FROM dbo.testresults;

GO

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post