I have 2 X 4 Cores but it looks like one processor is working hard. I know one CPU is working on one file so i would like to know if it's a good idea to slipt my DB in 8 files.

If yes, how can i use DBCC SHRINK to spread the data over the 8 files.

Thank you much

If you have multiple files in single filegroup you will have less options to spread data manually where it spreads the evenly when the data inserted...

Where as you can create multiple file groups with multiple/single file and you can force the SQL server to spread the data as you want using ALTER INDEX sytax...

DBCC SHRINK can be used to move the data from file to another but I believe you only option is EMPTY to delete the file or EMPTY file...

Thanks for the answer.

Do you think it's a best practice to split my DB in 8 files ?

I was planning to create a BDTEMP in 8 files in 1 file groupe

Use DTSWizard to transfert my data from the old BD to BDTEMP.

is it a good way ?

 Another question, on my other serveur with SQL 2000, all CPU looks to work equaly (2 X Dual Core), but i have only une file. Is it normal ?

If you are keeping all 8 files on single disk then there is no benifi I believe...by default parallelism is enable and sql uses all CPU based on the Query plan it generates when it executes sql statements/procs...

If you want to proceed without benifit too then using dts to copy the data will cause you down time..

Hi,

I guess I have something wrong with my parallelism because it's looks like one CPU is working hard and all others is are sleeping. How can I check my parallelism option ? For your information, if I slipt my BD in 8 files, they will be on the same HD. ( and i understand the benefit to put one file per disk but actually it's not the case.) 

 I saw a lot of recommandation to slipt tempdb in numbers of CPU, it's not the samething with the application database ?

 I did the test to create a new BD with 8 files, when i start the transfert from the BD(1 files) to the BD(8files) i saw for the first time a strong activity on  eight processors.

Mohammed, thank you for you help...

mtessier:

I did the test to create a new BD with 8 files, when i start the transfert from the BD(1 files) to the BD(8files) i saw for the first time a strong activity on  eight processors.

That is good. That is insure that sql server 2005 utilize CPU for IO tasks.

 review discussion  in: http://sql-server-performance.com/Community/forums/p/28215/151362.aspx#151362

IMO spliting database or particular table(s) will give benefit only if they are on separate disks. Refer http://www.mssqltips.com/tip.asp?tip=967 for spliting database of multiple file/file group.

There is exception only for tempdb

 http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Excerpt: "Divide tempdb into multiple data files of equal size. These multiple files don’t necessarily be on different disks/spindles unless you are also encountering I/O bottlenecks as well. The general recommendation is to have one file per CPU because only one thread is active per CPU at one time. SQL Server allocates pages for tempdb objects in a round-robin fashion (also referred to as proportional fill) so that the latches on PFS and SGAM pages are distributed among multiple files. This is supported both in SQL Server 2000 and SQL Server 2005. There are improvements to the proportional fill algorithm in SQL Server 2005. "