Performance impact of Splitting Table across files

Question

EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:

If I have a SQL Server 2005 database using a single .mdf file and I change it to use 2 files, should I expect performance to increase, decrease or remain the same?

We have an archive database that stores all reports sent out from our company since the beginning of time. As you can imagine the size of the database is large and grows quite quickly (currently 150GB and a few GB a week).

The only table in this database has a numeric primary key and a varbinary(MAX) column holding the file. All documents are retrieved via the primary key and performance is not currently an issue.

The problem we are facing is from the network team who backup and transfer the .mdf file over to our DR site every night. For them it would be better if we had multiple smaller files, rather than one big file. They are also saying it would make it easier to manage allocating space for future growth from our virtual infrastructure that it is sitting on.

Are there likely to be any performance loss/gain in splitting the table across multiple files on different virtual drives (potentially different physical drives).

I don't want to explore the problems of storing documents in a database, or running SQL Server on a VM, and we don't really want to change the database schema if we can help it, due to the cost in rewriting chunks of multiple apps.

Answer 1

If you place the new database file on a new disk then performance will be better. If the new data file is on the same disk as the current data file then performance will be the same.

Answer 2

The problem we are facing is from the network team who backup and transfer the MDF file over to our DR site every night.

Are they really transferring the mdf file to the DR site? If so, stop, now.

If thankfully backups are being taken, or you can persuade them to start taking backups instead of shifting the mdf, split the backup to multiple files.

BACKUP DATABASE [MyDatabase] 
TO 
    DISK = 'D:\MyDatabase_file1.bak'
    , DISK = 'D:\MyDatabase_file2.bak' 
    , DISK = 'D:\MyDatabase_file3.bak' 
    , DISK = 'D:\MyDatabase_file4.bak' 
WITH
    NAME = 'MyDatabase backup'
    , STATS = 10

They are also saying it would make it easier to manage allocating space for future growth from our virtual infrastructure that it is sitting on.

If you want to add additional storage to the database by way of additional files, that is also possible.

ALTER DATABASE [MyDatabase] 
ADD FILE 
(
    NAME = MyDatabase_file2,
    FILENAME = 'D:\MSSQL\Data\MyDatabase_file2.ndf',
    SIZE = 64GB,
    FILEGROWTH = 8GB
)

For this particular situation however, one does wonder why the administrators can't manage storage by way of the (storage) virtualisation layer.

Answer 3

If I have a SQL Server 2005 database using a single .mdf file and I change it to use 2 files, should I expect performance to increase, decrease or remain the same?

Adding a new/empty file will cause most of the data allocations to go to the new file. This is due to an algorithm called proportional fill, the goal of which is to balance the allocations evenly across all files in a filegroup (aka striping, or round-robin allocation).

If the new file is on the same physical device as the current file, performance will be about the same, or possibly get worse as the new file fills up if the device can't handle random I/Os. If it's on a different device, performance will reflect the capabilities of the other device until the files become more balanced. When the files are balanced, you should see a performance benefit using two different physical devices. This goes for both writes and reads to the database.

In terms of backup performance, though, you're probably going to be limited more by the target device(s) (writes) than the source device(s) (reads) because usually that's just the way things are most of the time.

The problem we are facing is from the network team who backup and transfer the .mdf file over to our DR site every night. For them it would be better if we had multiple smaller files, rather than one big file. They are also saying it would make it easier to manage allocating space for future growth from our virtual infrastructure that it is sitting on.

Have you considered using a backup strategy that involves differential backups? This would mean, for example, you would ship a full backup once/week and just the changes once/day. This will scale much, much better than shipping a full backup once/day.

I'm assuming this database is using the SIMPLE recovery model? If it's in FULL or BULK_LOGGED, consider doing transaction log shipping instead.

As Mark mentioned in his answer, you can also stripe a single backup (of any type) across multiple files. Depending on where each file lands, the performance of the backup operation could be worse, the same, or better.

Using a differential backup strategy, you would only need to stripe the full backup; the differentials are going to be small enough on their own. If there's one day out of the week where there is less activity in the database, pick that day (and even the time of day if you know it specifically) to do the full backup.