I have inherited a SQL Server 2005 database that is 400GB in size with 300GB of data on one data file on a RAID 10 disk and a second 100GB data file with indexes only on another drive. The log is on a separate drive as well.

I would like to split the data data file into multiple files and the index data file into multiple files potentially to span different disks to achieve parallel i/o. I believe this can be accomplished with the creating several data files then using the empty file command or by using the restore method with move.

Does anyone have any insights into a "best solution" method?

Thanks.  

Welcome to the forums!

What is the furture growth of this database?

DO you have any performance issues at the moment?

Why you are thinking about partitioning the data?

Satya, 

I want to split the one big data file and potentially index file into 4 or more files for file portability and for parallel i/o. These files reside on a SAN.

Thanks,

Eric

Yes you can achieve that by uisng ALTER DATABASE statment, if you have the data in 1 file already then you should move them to these files in order to see what you want to achieve.

 

satya:

Yes you can achieve that by uisng ALTER DATABASE statment, if you have the data in 1 file already then you should move them to these files in order to see what you want to achieve.

 

 How will you actually move data across this newly created datafiles?

Welcome to the forums.....

First you need to add those new data files to the database by using alter database as suggested by Satya.

Then you need to divide your table into groups as what tables you want on which data file. Now you need to move those table data to the respective datafile. And you can achieve this by CREATE INDEX with the DROP_EXISTING clause.

Usually DB Architects consider splitting database files influenced by table size.. rather than actual table usage. for 'example' if a db has 10 tables, its seen in practice you create as following [Opt 1] 5 files having 2 tables each- simply called equal share OR [opt 2] 4 files having 1 table each of larger sizes and 6 tables in 1 file only - called table size distribution OR [Opt 3] this I usually follow, I look into each table importance/usage by application and corresponding size and create files having balanced DISKIO because not all tables are equally important and being used frequently; it helps increasing parallelism on SAN and faster query results. though its difficult to implement as a lot ground work needs to be done before making decision about which table goes where for balanced division. Good luck;

Thanks a lot for you replies guys.

I suppose to increasing parallelism on SAN and faster query results you will have to split this datafiles (.ndf files) accross different disk arrays served by an independent disk controller on SAN. Sorry, if i may sound a bit ignorent here but i dont really understand SAN Architecture very well.

Regards,

Viv

Yup, database files (mdf/ndf/ldf)should be placed in physically/logically different SAN RAID array's & controllers. by having this placement you will get true parallelism benefits. have a quick look on this article: http://www.novell.com/coolsolutions/appnote/19687.html

Sorry if this discussion is already closed but I had a question. Is there any performance improvements gained by splitting a database into separate files if your server is not running an array and is a single drive server? i ask this because the emphasis seems to be on parallel IO as the reason for file splitting but with a single disk this is not going to help.

Welcome to the forum!

Have a look here: http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

And also...

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx

http://sqlblog.com/forums/14081/ShowThread.aspx

 

 Hi cteune

There will not be significant if any  performance improvements improvement if you are planning to split database into seperate files on a server with one physical disk.

As per microsoft, If you had a disk array, Each separate file is physically placed on a disk or set of disks (if the hardware disks are striped). SQL Server maintains a map of each file's location on the disk. If one file is created across a hardware stripe of four disks, one map points to the location of data on all four disks. If four files are created across a hardware stripe of four disks, four maps point to the location of the data on all four disks, one map in each file for each object in that file.Whenever a data object (table) is accessed sequentially, a separate thread is created for each file in parallel. Therefore, a tablescan for a table that is assigned to a filegroup with four files uses four separate threads to read the data in parallel.

 So in the the case of disk array you are better off with the multiple file setup.

 I understand this is not all comprehensive message but there whole lot more to this subject. Please feel free to ask questions.

 Vivek

 

 

 

To addup you have got many great references out there on that question, but within my experience it depends on hwo the database is normalized. No matter if you sprawl out the db files in multiple disks the performance will suffer if the data modification/insert process hammers the database heavily.

cteune:

Sorry if this discussion is already closed but I had a question. Is there any performance improvements gained by splitting a database into separate files if your server is not running an array and is a single drive server? i ask this because the emphasis seems to be on parallel IO as the reason for file splitting but with a single disk this is not going to help.