Query about Disk I/O on a Database

Question

Query about Disk I/O on a Database

marks_262

SSCommitted

Points: 1716
More actions
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?

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

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

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.

marks_262 SSCommitted Points: 1716 More actions · Answer 2

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.

marks_262 SSCommitted Points: 1716 More actions · Answer 3

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?