SQL Server Performance

Multi CPU and Multi Files

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by mtessier, Oct 8, 2008.

  1. mtessier New Member

    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
  2. MohammedU New Member

    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...
  3. mtessier New Member

    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 ?
  4. MohammedU New Member

    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..
  5. mtessier New Member

    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...
  6. moh_hassan20 New Member

    [quote user="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.[/quote]
    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
  7. ghemant Moderator

  8. moh_hassan20 New Member

    There is exception only for tempdb


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

Share This Page