SQL server availability issue: large query stops other connections from connecting

Posted by Carlos on Server Fault See other posts from Server Fault or by Carlos
Published on 2010-05-31T19:12:37Z Indexed on 2010/05/31 19:23 UTC
Read the original article Hit count: 249

Filed under:
|
|

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?

© Server Fault or respective owner

Related posts about sql-server-2008

Related posts about raid