I've got a high-spec (multicore, RAID) server running MS SQL 2008, with several databases on it. I have a low throughput process that periodically needs a small amount of information from one of the DBs, and the code seems to work fine.
However, sometimes when one of my colleagues does a huge query against one of the other DBs, I see full CPU usage on the machine, and connections from my app time out.
Why does this happen? I would have thought the many cores and harddisks would somehow (together with cleverly written DB server) be able to keep at least some of the resources free for other apps? I'm pretty sure he doesn't use multiple connections for his query.
What can I do to prevent this?
I don't have a lot of specifics about the hardware. It uses ordinary HDDs, raided, with Server 2k3. It's an HP that's maybe a couple of years old. Basically, it doesn't make sense to me that the hardware is the problem, so I figured I might have configured something wrong?