none
Is it possible to test if I/O Is random or sequential RRS feed

  • Question

  • Hello,

    I have just read an article on blended I/O, see http://www.pearsonitcertification.com/articles/article.aspx?p=2240989&seqNum=3

    My clustered environment uses two VMs both sharing the same SAN. Each VM uses a separate range of disks for data, logs, tempdb etc.

    I was wondering if there is a way in which I can test if the I/O is random or sequential?

    Thank you very much for your time.

    Ben


    Mr Shaw

    Tuesday, February 24, 2015 5:38 PM

Answers

  • I/O is a very generic term for disk access, and therefore the physical method used to perform I/O workload depends on what is being done with what data.

    If you are talking about specific sql server structured data reads, it depends on how this data is stored on the disk. If data is stored in an ordered fashion, such as in an index, reads will always be sequential. Otherwise the engine is free to choose any algorythm it sees fit.


    Just because there are clouds in the sky it doesn't mean it isn't blue. But someone will come and argue that in addition to clouds, birds, airplanes, pollution, sunsets, daltonism and nuclear bombs, all adding different colours to the sky, this is an undocumented behavior and should not be relied upon.

    • Marked as answer by Mr Shaw Tuesday, February 24, 2015 5:52 PM
    Tuesday, February 24, 2015 5:51 PM
  • I was wondering if there is a way in which I can test if the I/O is random or sequential?

    Not really.  You can get counts of look-ahead reads, but these happen mostly on scans, and that's a mixed blessing.  For data stored in modern SANs with huge RAM buffers and RAID physical storage the whole concept of random and sequential becomes nearly meaningless, not to mention physical storage shared by many logical devices.  And finally SQL Server always reads in 8-page extents, so it is always sequential to that degree.

    Modern SQL Server systems and user expectations are such that, if you have to start inspecting the physical IO, you are already in more trouble than you can fix.  Buy more RAM, optimize your indexes, and look at the Hekaton stuff in SQL 2014.

    Josh

    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 10:17 AM
    Tuesday, February 24, 2015 6:38 PM

All replies

  • I/O is a very generic term for disk access, and therefore the physical method used to perform I/O workload depends on what is being done with what data.

    If you are talking about specific sql server structured data reads, it depends on how this data is stored on the disk. If data is stored in an ordered fashion, such as in an index, reads will always be sequential. Otherwise the engine is free to choose any algorythm it sees fit.


    Just because there are clouds in the sky it doesn't mean it isn't blue. But someone will come and argue that in addition to clouds, birds, airplanes, pollution, sunsets, daltonism and nuclear bombs, all adding different colours to the sky, this is an undocumented behavior and should not be relied upon.

    • Marked as answer by Mr Shaw Tuesday, February 24, 2015 5:52 PM
    Tuesday, February 24, 2015 5:51 PM
  • I was wondering if there is a way in which I can test if the I/O is random or sequential?

    Not really.  You can get counts of look-ahead reads, but these happen mostly on scans, and that's a mixed blessing.  For data stored in modern SANs with huge RAM buffers and RAID physical storage the whole concept of random and sequential becomes nearly meaningless, not to mention physical storage shared by many logical devices.  And finally SQL Server always reads in 8-page extents, so it is always sequential to that degree.

    Modern SQL Server systems and user expectations are such that, if you have to start inspecting the physical IO, you are already in more trouble than you can fix.  Buy more RAM, optimize your indexes, and look at the Hekaton stuff in SQL 2014.

    Josh

    • Marked as answer by Mr Shaw Wednesday, February 25, 2015 10:17 AM
    Tuesday, February 24, 2015 6:38 PM