Post reply

Query about Disk I/O on a Database

  • May 31, 2012 at 10:54 am

    #392400

    For a 50 Gb database that is using a single data file, when running dbcc showcontig the max disk I/O is 500Mb/s but when running a select * from table (500Mb of data) the disk I/O is only max 50Mb/s. From the showcontig information the table is not fragmented.

    As there no other users on the server, I would have expected the the max disk i/o to be the same for both TSQL commands?

  • May 31, 2012 at 11:26 am

    #1495407

    When running DBCC SHOWCONTIG where are you getting the max disk I/O figure from? According to BOL this isn't output from this function. Are you measuring this another way?

    Also 500MB/s is a big ask from any storage. How are you measuring your figures / timing your SELECT statement?

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • June 1, 2012 at 2:48 am

    #1495723

    The Disk I/O was measured via the PerfMon Disk counters.

    The storage is a SAN which has been benchmarked using SQLIO tool which reported the over 500Mb/s max for sustained throughput on there tests.

  • June 1, 2012 at 10:46 am

    #1495976

    With some basic network speed monitoring using the networking tab on Task Manager, when file copying between two servers the network usage is about 50% but when running a basic SQL command between the two servers the network usage is about 0.5%

    The server is Windows 2008R2 and SQL 2008 (not R2).

    Is there any settings that may be enabled which significantly buffers the throughput of SQL Server compared to file transfers?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply