Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm running SQL Server 2012, and doing a simple SELECT * query, I can see that my disk IO in performance monitor is < 10MB/s. If I do a backup of the database, however, I see values > 200 MB/s.

Why is it that sql server is so slow at retrieving data for me?

share|improve this question
add comment

2 Answers

The reason you only see 10MB/sec is that those performance counters look at data from one second to the next, rather than singular actions within the same timeframe. So your query may have pulled 10MB in less than a second, but that'll only register in Performance Monitor as 10MB/sec.

A backup pulls large amounts of data across the board, so it'll register at the disk's full speed. Were it able to register disk IOs for individual actions, it would probably say 200mb/sec or something similar, possibly even more for smaller packets of data since speed becomes almost impossible to gauge for small packets.

Hope this helps.

share|improve this answer
add comment

Turns out that all of my reads from the query were coming straight out of memory, rather than from the disk. The way I was able to tell this was by running the following command before the query:

SET STATISTICS IO ON

After that, I ran the query and looked at the messages tab for the stats. Logical reads are from memory, physical reads are from disk.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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