Read from large table without causing disk queue length issues I have a report that reads from many tables, one of which is 48GB. 

To help with performance I planned to create a table which stored a subsection of values from this large table. 

However, whenever I read from this table, disk queue length goes from 0.1-0.5 to 10-100.  I've also tried bcp and it has a similar effect on queue length. 

How I can get the data out of this table without affecting performance of the database server?
Disk queue length isn't a problem, by itself: SQL Server will batch off IO requests. It's a question of whether your storage subsystem can deliver data fast enough to keep up with your queries.

While the report runs, gather wait statistics - the list of bottlenecks on the server. My favorite way to grab it is with sp_BlitzFirst in the open source First Responder Kit (disclaimer: I'm one of the authors). To gather wait stats for a period of time, run:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30


To capture wait stats for 30 seconds. Then, you can tune based on your top wait type, and how much of it you're actually seeing.

For example, if your top wait type is PAGEIOLATCH, that means reading data pages from a data file. To reduce that wait, here's the fixes that I'd recommend, in order:


Tune the query
Tune indexes in the execution plan
Add memory to the SQL Server (to cache more data, giving the storage subsystem more time to pull data from disk)
Make the storage go faster


I order 'em that way because that's usually cheapest/easiest up to hardest/most-expensive.

If your wait type is something else, then don't worry about disk queue length. (Actually, don't worry about disk queue length, regardless.)