SQL Server Performance

DATA FILES AND CPU's

Discussion in 'Performance Tuning for Hardware Configurations' started by recycled, Jul 27, 2004.

  1. recycled New Member

    Does a configuration of 1 data file for each Server CPU increase performance over 1 data file for all CPU's? In other words, on a server with 8 CPU's, would the database be better configured using 8 data files @100MB each rather than 1 datafile @ 800MB?
  2. ykchakri New Member

    I think data files should be distributed based on the number of physical disks you have rather than number of CPUs. For example, if you have 4 individual disks (or arrays) mounted as 4 different volumes, then create 4 file groups in your database with each file group on each disk and then distribute the objects across these file groups which can give you better performance.
  3. Luis Martin Moderator

    Oppinable question.
    I think if you have 8 databases and, just and example, 3 are more used than rest, may be would be better to have 8 than one.
    But if all are used in same way, I believe is better to have only one.
    I thinking in how SQL use memory, because (I think) is a memory problem no CPU problem.

    Like I said, oppinable, so wait for other members.

    Good Point Chakry, I read your post after write.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. recycled New Member

    I was thinking that perhaps a separate thread would be used for each of the 8 data files rather in the example I used, rather than just 1 thread for a single file.
  5. Luis Martin Moderator

    From maintenance point of view, one datafile.
    How about, disk (Chakry post) and memory?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. derrickleggett New Member

    A seperate thread won't be used. What you need to worry about is what others have alluded to here. You need to have the data spread across as many disks and I/O bandwidth as possible. If you can achieve this better, and have the money to implement it, then create more files. Otherwise, I wouldn't do it.

    The memory utilization and processor threads won't really be affected enough to make any difference.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. FrankKalis Moderator

    Somewhere I have a document from EMC where it states that it's better to spread one single filegroup across as much disks as possible than to use multiple filegroups spread on few disks.

    IBM has also some empirical studies confirming this in their Redbook section. While this is related to DB2 I think it also holds true for SQL Server.

    --Frank
    http://www.insidesql.de
  8. derrickleggett New Member

    That's true about almost all disk subsystems Frank. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> The more you spread your IO out, the better distribution you get across the disks. There is a balance point though, depending on what type of RAID you are using on which system. Almost all the systems have "breakpoints" where the RAID type becomes less efficient if you go above or below a certain amount of disks. Once you reach that point, it can become faster to use multiple filegroups. <br /><br />Of course, on many of the EMC and IBM products, you can get around this and even increase performance more by creating a LUN that's spread across multiple RAID groups and thus even more disks. At this point, the usability of your single filegroup greatly increases. It's hard to say on a forum though without knowing the hardware, setup of the databases, etc.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  9. FrankKalis Moderator

    Yes, the most important word in here is "can". <br />My point was that it seems that answers (in general, nothing particular here) advocating the use of multiple filegroups with SQL Server are too quickly given. <br />Performance tuning advises you can't take off the shelf. There are some general recommendation and mabye guidelines, but the by far bigger part depends on the environment in case. To a certain degree it's a kind of trial and error, till you find a configuration that suits you.<br /><br />Off-topic: Best thread on this was one guy from Asia asking if 256 MB RAM will be sufficent for SQL Server running together with IIS on a PDC computer. The answers were funny, but also polite [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  10. derrickleggett New Member

    <img src='/community/emoticons/emotion-1.gif' alt=':)' /> I can imagine. I have 2005 Beta2 and IIS running on a virtual server with 256mb of RAM. lol It's surprising how reliable it's been so far.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  11. pfgm New Member

    Hi, I've done some DBA work in oracle, they allways recommend to split data from indexes in different tablespaces (Filegroups) and even so to split them in datafiles.
    Is this advisable ?
    BTW, are the Avg Disk Queue Read and Write counters reliable ?



    Saludos, Pablo
  12. Luis Martin Moderator

    Same recomendations for SQL Server.
    The counters are reliables.

    Igualmente,




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  13. pfgm New Member

    Is there any way to monitor the IO of the server separating the index from data ?
    Basically I want to know if I would get more performance by splitting index from data or by separating the most used data and indexes from the least used.

    I have a fixed amount of hard disks I can use, Just want to use them the best way I can.



    Saludos, Pablo
  14. Luis Martin Moderator

    If data and Index are in differents drives, you can see using Performance Monitor, each disk I/O.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  15. satya Moderator

    There are several ways to request I/O statistics from SQL Server such as the System Statistical functions, sp_monitor, and fn_virtualfilestats. Each method has its advantages and disadvantages.

    http://www.sql-server-performance.com/performance_monitor_counters_io.asp
    http://www.sql-server-performance.com/performance_monitor_counters.asp

    I find that the most useful technique is to gather statistics during peak usage at one-minute intervals. The key indicators are the I/O operations per second and the IoStallMS/Op. And alsoinvestigate other ratios for your server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. pfgm New Member

    I know how to monitor IO counters, but what I really like to know is when I have the data and indexes in the same file in the same disks, how can I tell what percentage of the I/O is using each .


    Saludos, Pablo
  17. Luis Martin Moderator

    No way in same file and disk.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  18. satya Moderator

    You can try to differentiate them in order to assess the performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page