SQL Server is quite the beast. It eats most of your available RAM. And, with a high-volume database, it can also gobble up your IO, leaving your disk with a long queue.
Disk Queue Length, incidentally, is something you can see in Windows 7+, under the Resource Monitor (Start > Task Manager > Performance tab > Resource Monitor > Disk tab)
Anyway, during large inserts, or imports, or selects, you may see your disk queue length spike up. This means that your computer is requesting data from the disk, but it’s busy spinning and getting data for different requests; so they start to rack up.
If you see a queue length of, say, 50, that means you have some heavy IO that’s sucking up all your disk availability and queueing up tons of requests — like when you’re restoring an 8GB database into SQL Server (please, use Attach next time.)
Two easy ways to increase performance in this case:
- Not the Windows Partition: By default, SQL Server installs to C:\, the same place as Windows. Move the data files off of the Windows drive. You will immediately see an (often drastic) drop in queue length. Why? Because Windows makes a lot of requests, and if they start to queue up, it can make the queue much longer. If you have a second (physical, not logical) hard drive, that’s an ideal place to locate the files.
- Split MDF and LDF Drives: Ideally, if you have three or more drives, you should put Windows on one, your MDFs on a second, and your LDFs on a third for maximum performance.
Again, unless you see the specific problem of disk queue length, you might not need to do all this. Plus, memory is usually a heavier bottleneck for SQL Server, not disk — so check that first.
How can you set the location of the MDF and LDF files? With new databases, when creating them, SQL Server shows you “Database Files” listed under “General,” with a “Path” property. Just change it to something else.