11

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
Jon Seigel
  • 16,673
  • 6
  • 41
  • 83
Dean MacGregor
  • 601
  • 1
  • 8
  • 18
  • How high is your processor load and memory consumption related to SQL Engine when that long running query executes? –  Jan 21 '13 at 15:59
  • 2
    perhaps the data is already in RAM and no disk access is needed? – gbn Jan 21 '13 at 16:01
  • While executing a query that returns 9 million rows CPU usage will stay around 13% with 9% attributable to sqlserver. System memory usage is 55% used with sql taking just under 15gb. There's just minor differences between system usage and sql usage b/c this machine doesn't do anything except run sql server. –  Jan 21 '13 at 16:06
  • @gbn: Before running a query disk IO will be close to 0 and only during the execution of a query does it go up to the 7MB/sec so I don't think that is the case. Also, wouldn't it return the results quicker than 3 min if all data was in RAM? –  Jan 21 '13 at 16:10
  • 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)? –  Jan 21 '13 at 16:18
  • @DeanMacGregor: hard to say: it could be a bad query. Also, what SQL Server version please. And max memory setting? – gbn Jan 21 '13 at 16:20
  • What does `sys.dm_os_waiting_tasks` say as the wait type for these long duration queries? – Thomas Stringer Jan 21 '13 at 16:29
  • I found an article on sqlauthority as it pertained to sys.dm_os_waiting_tasks since I couldn't interpret the output [link](http://blog.sqlauthority.com/2011/02/04/sql-server-dmv-sys-dm_os_waiting_tasks-and-sys-dm_exec_requests-wait-type-day-4-of-28/) Running his really long query makes it appear that my problem is that all the data is going through the network even though I'm running from the local machine. Our network here is only 100Mbit so with network overhead it makes sense that it is capped where it is based on this information. – Dean MacGregor Jan 21 '13 at 16:56
  • Now the question becomes how do I eliminate the network bottleneck issue since I'm running query on server itself? Should I ask this as a new question or is it simple enough for someone to answer without creating new question? – Dean MacGregor Jan 21 '13 at 17:00
  • 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
  • 1
    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. – Mark Storey-Smith Jan 21 '13 at 18:02
  • when I checked the 'discard...' option My sys.dm_os_waiting query came back blank and disk IO doubled. My max memory setting is the default of 2147483647MB. I am using SSMS. @gbn I'm using server 2008 r2 10.50.1600.1 (x64). I then ran the DBCC DROPCLEANBUFFERS command and now disk IO gets up to about 18-20MB/sec. – Dean MacGregor Jan 21 '13 at 19:29
  • Perhaps the true performance of your disk through SQL Server would be visible if you did a backup of a reasonably large database (at least 10GB) from one folder to another on that same disk. I would be very surprised if you don't see extremely high throughput while doing a backup. I typically see 300MB to 400MB per second doing a backup to our SAN over 10GB Ethernet. – Hannah Vernon Jan 21 '13 at 21:44

1 Answers1

7

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.
Community
  • 1
Mark Storey-Smith
  • 31,155
  • 6
  • 85
  • 122
  • 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 04:45