11 Replies Latest reply: Apr 19, 2013 8:04 AM by Mark D Powell RSS

    Disk Usage with Partitioning in MSSQL versus Oracle

    RCE
      Hi All,

      Although this isn't a likely setup I would encounter/recommend, this question has been driving me nuts because I can't find the answer and I'm not sure how to test it on my own (lack of hardware/knowledge).

      I encountered a document on SQL Server 2012, and 2008R2, that states:
      When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.
      http://msdn.microsoft.com/en-us/library/ms190787.aspx
      >

      I'm curious if Oracle has the same 'limitation.' I see in the 10.2 docs that they say:
      Although you are not required to keep each table or index partition (or subpartition) in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces enables you to:
      * Reduce the possibility of data corruption in multiple partitions

      * Back up and recover each partition independently

      * Control the mapping of partitions to disk drives (important for balancing I/O load)

      * Improve manageability, availability, and performance

      http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm
      >

      But this does not specifically say it can access more than one drive at a time. I can't tell whether this applies to Oracle or not. Sorry for the reference to a previous version but I looked for this statement in the 11.2 documentation but couldn't find it.

      Can anyone shed some light on this? It's eating at me :)

      Thanks,
      Rich
        • 1. Re: Disk Usage with Partitioning in MSSQL versus Oracle
          Mark D Powell
          Rich, Oracle actually asks the OS to perform IO. If the logical data files presented to Oracle via the OS logical disk manager are stripped then multiple disks are accessed to support multi-block read requests. A single block read request would likely be on a single disk no matter the state of stripping at the OS level. But honestly what disk storage unit is not stripped in today's world?

          SQL Server just like Oracle gets its files from the OS. If you have a storage unit like we do then SQL Server has no idea the files are stripped across multiple disks. It just asks for IO against D:/MSSQL/data/what_ever. It is the disk storage unit that handles the details in most cases.

          HTH -- Mark D Powell --
          • 2. Re: Disk Usage with Partitioning in MSSQL versus Oracle
            Billy~Verreynne
            To add to Mark's posting - there is a distinct border between the logical data layer, and the physical.

            In the Oracle database, you deal (or should deal) with the logical layer. This logical layer consists of segments, extents, datafiles, tablespaces, block sizes, pctfree/pctused, deferred segment creation and so on. You do not (and should not) consider physical storage issues such as striping, at this logical level. The closest "thing" to the physical layer is a datafile of a tablespace - this is the physical object on the physical storage device that will be read and written via I/O calls.

            That datafile can be a raw partition. It can be a file on a cooked file system. It can be a scsi device such as a SAN LUN, or NAS device. It can be a SSD. It can be (if you really want), a USB drive. Or SD card.

            The datafile can be striped by the storage system across multiple devices. It can be mirrored. RAID'ed. Whatever. Managed by Oracle ASM, or Veritas, or numerous other storage manager.

            An Oracle server process will issue an I/O request (via the kernel driver for that device) to read from a file/device handle (the datafile), at a certain offset, a certain number of bytes/blocks. Whether the data being read is for a table, cluster, partition, index, or whatever, is on a single device, or a mirrored device, or a RAID device, has no bearing. This Oracle server process does not care how the physical storage layer looks like, works, or is structured. It issues an I/O read or write on a datafile - what happens below that is not relevant to the Oracle server process issuing that I/O call.
            • 3. Re: Disk Usage with Partitioning in MSSQL versus Oracle
              RCE
              Thanks Mark,

              I appreciate the response and explanation!

              I thought it was interesting (unusual?) that Microsoft specifically states what to do versus what not to do so it made me wonder if this was an RDBMS or Operating System 'limitation'. As you point out, the storage unit handles these things today so it shouldn't be a concern in 99.99% of environments.

              It's something my brain is trying to process still. I feel like there is a piece of this puzzle that's missing :)

              Regards,
              Rich
              • 4. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                RCE
                Thanks for adding to the discussion Billy,

                I appreciate your detailed description of the processes.
                • 5. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                  Mark D Powell
                  My home PC came with two 1T hard drives. By default HP would deliver them set up in a stripped configuration for performance (though I had the disk set up as a mirror). To Windows there is only a 'C' drive. Yet, most of the documentation you read still refers to disk drives like your disk consists of physical disk drives that you have access to. In today's world disk is often a block box, which is another issue and discussion.

                  HTH -- Mark D Powell --
                  • 6. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                    RCE
                    I was thinking the documentation must have been carrying this paragraph from way back when we had JBODs and we might have put a partition on each disk instead of using RAID. But that raises the question.. did MSSQL even support partitioning way back then? :)

                    Thanks for playing along, I appreciate it!
                    • 7. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                      jgarry
                      You might also consider that Oracle can do parallel operations, which each slave asking for different things. See http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html http://dbareactions.tumblr.com/post/48229870306/my-queries-before-the-junior-guy-set-maxdop-1
                      • 8. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                        jgarry
                        Mark, we're going to put you in a striped prison outfit if you keep on talking about stripping! :p
                        • 9. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                          rp0428
                          >
                          Mark, we're going to put you in a striped prison outfit if you keep on talking about stripping!
                          >
                          You may want to mind your "p's and q's" (especially your "p's).

                          Using two "p's" instead of one gives that sentence an entirely different meaning. ;)
                          • 10. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                            jgarry
                            rp0428 wrote:
                            >
                            Mark, we're going to put you in a striped prison outfit if you keep on talking about stripping!
                            >
                            You may want to mind your "p's and q's" (especially your "p's).

                            Using two "p's" instead of one gives that sentence an entirely different meaning. ;)
                            Go back and look at Mark's post, and we can all smile more.
                            • 11. Re: Disk Usage with Partitioning in MSSQL versus Oracle
                              Mark D Powell
                              Ooops. LOL. I will try to watch for that error next time. -- Mark --