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:
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:
-- create our test database
-- create a table to hold our filegroup count test points and insert them
-- create a table to hold the results
-- declare some variables
-- open the cursor
-- Get the first filegroup count to test
-- close and deallocate the cursor
-- get the test result
PingBack from http://paidsurveyshub.info/story.php?id=62622