SQL Server Performance Forum – Threads Archive

Multiple database files on the same disk

Hi there It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk? I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

I hope data and log files are not on the same disk. Have you observed any disk contention during the process of those multiple data files with a resource intensive query.
How about the indexes? KBAhttp://support.microsoft.com/kb/231619 – to simulate the load and KBAhttp://support.microsoft.com/kb/328551 on tempdb enhancements too that will have affect here. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Good question and some general information on how you benefit from spillting to mutiple files is given in link below. http://searchsecurity.techtarget.com/tip/0,289483,sid87_gci1220308,00.html Cheers
Sat

Thanks guys. tempdb is not really big. log and data are partially separated, i.e. one file group (8GB) is on the log’s disk.
I have heard that a big data file could be a problem, so my question is whether splitting the big file into many files even on the same disk will ease SQL Server IO work.

Splitting big file say 20gb into multiple file 5gb each will have advantage in catering the data placement, but you need to test this fist by taking help of PERFMON or simulate similar load with SQLIOSIM utility. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Hi Aveil, My Theory Step 1 indentify the larger and frequently used tables in database which could be 20-30% of total size of database. Step 2 Place these tables on it its own data file , which in turn increases IO and parallelise queries. Note : Above will be of no help if you have no appropriate index in tables and tables are primiraly used to read data. Cheers
Sat

Thanks guys. Will check it out.

]]>