I have an SSD that, using IOmeter test, shows performance over 200MB/s. However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec. This holds true even for queries that take over 2 minutes to run. What could the bottleneck be that it is only using 7MB/sec from SSD?

I'm running:

  • Windows Server 2012 Standard
  • SQL server 2008 r2
  • Intel i7 3820
  • 32GB of ram
  • sandisk SSD
  • 2
    perhaps the data is already in RAM and no disk access is needed? – gbn Jan 21 '13 at 16:01
  • 1
    @DeanMacGregor - How are you consuming the results? If in SSMS what if you try the option to discard the results. Does that change anything? Also you might try looking at sys.dm_os_waiting_tasks whilst the query is running to see if there are other wait types it is encountering. – Martin Smith Jan 21 '13 at 16:18
  • 1
    Is the 200 MB/s for random access reads (random reads for 4 KB blocks)? I guess this is what a database would typically do. Does the query write to the disk (temporary file, temporary result set or table)? – Thomas Mueller Jan 21 '13 at 16:18
  • 2
    @DeanMacGregor - So to take that out of the equation you can assign the result to scalar variables (example query). DECLARE @Name VARCHAR(10), @High int; SELECT @Name=name, @High = high FROM master..spt_values. So no results are sent back to the client but the plan and IO will still be the same. – Martin Smith Jan 21 '13 at 17:02
  • 4
    Presume you're interpreting ASYNC_NETWORK_IO waits to mean that the problem is related to the network? It (typically) isn't. It's most likely as @MartinSmith has suggested (twice) that SSMS or the application you're using is not consuming the results as fast as SQL is serving them. Follow either of the suggested ways of omitting the consumption of the rows and you'll get a true(r) picture of the maximum IO throughput. – Mark Storey-Smith Jan 21 '13 at 17:13

From the comments chain, it looks like you're interpreting ASYNC_NETWORK_IO waits to mean that the problem is related to the network. It (typically) isn't.

As @MartinSmith hinted at (twice) the most likely explanation for that is SSMS or the application you're using not consuming the results as fast as SQL Server is serving them. Follow either of the suggested methods to remove the consumption of the rows from your measurement and you'll get a true(r) picture of the maximum IO throughput:

Just in case you haven't already, you'll obviously need to DBCC DROPCLEANBUFFERS to ensure the data is actually read from disk rather than buffer cache. Usual caveats of "on test only, don't do this in an active live environment" etc apply.

Honing in on a couple of your other comments:

While executing a query that returns 9 million rows CPU usage will stay around 13% with 9% attributable to sqlserver... wouldn't it return the results quicker than 3 min if all data was in RAM?

What exactly are we testing here, how and why? If your 9 million row query is anything other than a SELECT * FROM dbo.SomeTable then there are a 1001 factors that come in to play, other than just raw IO throughput.

Your Intel I7-3820 is a 4-core processor. If your test query doesn't generate a parallel plan, I'd be surprised if you could thrash more than 20% CPU utilisation from the system.

The 3 minutes to return 9 million rows is very suspicious and suggests we're not getting a full picture of what your testing. My guess would be this is a case of sub-optimal (non-parallel) query plan, stuffed full of nested-loop operators pulling millions of rows i.e. not just a single table SELECT to verify the IO consumption.

I suggest:

  1. SELECT * to test just IO through SQL Server.
  2. New question with the execution plan of your query if you want to dig into why it doesn't saturate the IO.
  • Actually it is just select * from dbo.sometable. Sorry I didn't mention that sooner; what I meant was that I could run that query off any table. The genesis of my question was that I noticed my disk IO was much less than I expected even from simple "give me lots of data" query. My main concern was that if disk IO is so low could performance be improved if root cause of low disk IO was rooted out. – Dean MacGregor Jan 22 '13 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.