SQL Server Performance

Split index from data or spread data and indexes over multiple files

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jorten, Sep 16, 2008.

  1. jorten New Member

    I've seen a lot of opinions about the value of putting non-clustered indexes on a different disk then data. I can also see the value of having multiple data files in a file group, each on a separate disk. It would be great if I could do both, but with limited resources I need to pick the best scenario.
    Parameters:
    2 disks, each RAID 5
    Log file is already on a separate RAID 10 disk
    Load is OLTP - 80% reads, 20% writes
    Database is 60 GB
    8 processors, 8GB ram
    Scenario 1:
    Place data on one disk and non-clustered indexes on the other
    Scenario 2:
    Keep data and indexes together in same file group, but create 2 data files, one on each disk.

  2. moh_hassan20 New Member

    i suggest senario 1, data in the primary group , and indexes on new file group fg_index
    plus
    as you have 8 cpu, using multi files enhance performance
    • create new file group fg_index (for indexes )
    • let every file group (primary FG and the new FG ) composed from two data files distributed over the two disks
    • let tempdb of two data files
  3. jorten New Member

    moh_hassan20,
    [quote user="moh_hassan20"]as you have 8 cpu, using multi files enhance performance[/quote]
    I've tried to find evidence that a cpu thread gets created for each file and all I've found are conflicting opinions. Do you have evidence, either personal or otherwise that this is true. If a cpu thread gets created per data file, then your plan would be a good one.
    Thanks
  4. moh_hassan20 New Member

    I find many topics that utilize cpu threads for IO operations, here some of many:
    http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
    Physical Database Storage Design
    excrpt: "The number of data files within a single filegroup should equal to the number of CPU cores."
    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
    excerpt: "Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.
    It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
    This is especially true for TEMPDB where the recommendation is 1 data file per CPU."

    http://sqlcat.com/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx
    http://blog.sqlauthority.com/2007/05/14/sql-server-ms-technet-storage-top-10-best-practices/
    ---------------------
    for tmpdb
    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. "
    http://msdn.microsoft.com/en-us/library/ms175527.aspx

    Exerpt: "As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."

  5. jorten New Member

    [quote user="moh_hassan20"]I find many topics that utilize cpu threads for IO operations, here some of many.......[/quote]
    Thanks. Great info.
  6. ghemant Moderator

    Hi,
    Scenario 1 is okay, also how about placing a table which has most read operation along with the index(s) on seprate file group!!

Share This Page