SQL Server Performance

Multiple database files on the same disk

Discussion in 'Performance Tuning for DBAs' started by Aviel, May 16, 2007.

  1. Aviel New Member

    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?




  2. satya Moderator

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

  4. Aviel New Member

    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.
  5. satya Moderator

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

    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

  7. Aviel New Member

    Thanks guys. Will check it out.

Share This Page