Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005

    Unanswered: how to get optimal driver performance for a DB?

    So I am pretty green when it comes to hardware configurations.

    I just asked a coworker and he said we're using RAID 60 (I assume is the same as RAID 6+0).

    I am doing some tests on a large table (1 billion rows), and setup a different partition for each day of data. From what I understand I would want to ideally put each partition on a separate filegroup and each filegroup would get its own dedicated disk.

    Am I right that creating "virtual" drives for each FG is not going to make a difference since all of the storage is coming off of a RAID 60 "device"?

    Is there anything I can do to improve disk performance when creating an index on a table of this size?

  2. #2
    Join Date
    Feb 2004
    A different filegroup per day? I assume that's only for the test duration only?

    I've heard amazing stories about placing the data in one filegroup, the indices in another and the log in yet another filegroup. The filegroups had different raid configurations.

  3. #3
    Join Date
    Jun 2005
    I like it, I assume I need 3 different raid controllers to achieve that, will I most likely need a special mobo in order to achieve that. I think I will hit up the to see what they know about setting something like this up.

  4. #4
    Join Date
    Jul 2003
    San Antonio, TX
    Disk performance only comes to play when your system does not have adequate amount of memory. Here's what I mean, - suppose your "very large table" total size is 10GB, as reported by sp_spaceused. Now suppose the intended usage of this table is to involve 70%+ of the rows in most of the queries (by direct retrieval, or by accessing them while performing query processing operations like Scan or Seek). In this case you would expect utilization of up to 12GB of memory, if you take into account service memory requirements, in addition to OS requirements. When I say "non-adequate amount of memory", in this case it means less than 12GB. In this case physical placement does matter. But it also means that you need to address memory needs before trying to answer other questions. But when your memory needs are satisfied, the biggest contributing factor is the combined size of each filegroup, and individual sizes of files in each file group. Remember the 4GB-based GAM and SGAM mapping, along with IAM wich follows the same concept, coupled with contiguous memory allocation requests. Mostly, remember that SQL doesn't know what a "real" IO is. When it thinks that it does IO, it is actually only operating on contents of available memory pages. It doesn't even know 100% of the time if the required data is currently loaded in memory or not.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts