you're reading...
SQL Server

Script to Create A Database File Per Processor

The Need

The best practice from Microsoft is to generally have 1 database file per processor on a dedicated disk. The files should be set large enough so that they meet your growth estimates for a good 6 months,  if you can afford the space.  I should warn you that there is some debate on this recommendation and many people run .25 database files per processor or even just 1 big file on less busy servers.

Update: There is not a lot of good information on this subject outside of the best practices recommendations. I am basing my recommendations on actual I/O experiences on a highly utilized OLTP environment. I am not advocating a certain number of files for database files in this post. Measure what I have provided against your own experiences in your environment and modify accordingly.

In General on my larger heavily used environments I like to run with 1 database file per core / processor. Of course  being big servers, these machines have a lot of cores. The last server I built had 32 cores. Creating individual files via the GUI would have taken 30 minutes to an hour assuming I did not fat finger something and have to redo it.

The Script

I have written a script to add a database file per processor. The script is easy enough to use. It looks at the file allocations currently in place, sets the max to infinite, sets growth to 10% of current size and assumes auto grow is on. Please adjust to your standards. Next you would simply double check your math and settings then run the script and n copies of that file would be created. The script generates the Alter Database statements however, you can set it to automatically execute the Alter database statements. I prefer to review before I execute. You could also add a check for available disk space and adjust the file settings accordingly. This could also be easily modified to handle log files as well (type_desc = ‘LOG’).

USE [master]
DECLARE @cpucount      int,
         @filecount     int,
         @logicalname   sysname,
         @filename      nvarchar(500),
         @physicalname  nvarchar(500),
         @size           int,
         @maxsize       int,
         @growth         int,
         @altercommand  nvarchar(max),
         @dbname         nvarchar(500),
         @dbid           int
         SET @dbname=’YourDB’
         SET @dbid = DB_ID(@dbname)
SELECT  @physicalname = physical_name,
         @size = size,
         @maxsize = 268435456,
         @growth = size/10
FROM   sys.master_files
WHERE   database_id = @dbid AND type_desc = ‘ROWS’
SELECT  @filecount = COUNT(*)
FROM     sys.master_files
WHERE   type_desc = ‘ROWS’ AND database_id = @dbid
SELECT  @cpucount = cpu_count
FROM    sys.dm_os_sys_info
WHILE @filecount < @cpucount — Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
     SELECT  @logicalname = ” + @dbname + ” + CAST(@filecount AS nvarchar)
     SELECT  @filename = REPLACE(@physicalname, @dbname + ‘.mdf’, @logicalname + ‘.ndf’)
     SELECT  @altercommand = ‘ALTER DATABASE ‘ + @dbname + ‘ ADD FILE ( NAME =N”’ + @logicalname + ”’, FILENAME =N”’ +  @filename + ”’, SIZE = ‘ + CAST(@size AS nvarchar) + ‘MB, MAXSIZE = ‘ + CAST(@maxsize AS nvarchar) + ‘MB, FILEGROWTH = ‘ + CAST(@growth AS nvarchar) + ‘MB )’
     PRINT   @altercommand
     –EXEC    sp_executesql @altercommand
     SELECT  @filecount = @filecount + 1


As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this is helpful.


About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: