up vote 1 down vote favorite
share [g+] share [fb]

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?

EDIT

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?

link|improve this question
25% accept rate
This makes little sense. SQL does not do that normally. Overlaoding the ompelte computer is one scenario where I ahve seen this..... otherwise please tell us your hardware config (ram, cpu, discs) and SQL version. – TomTom Dec 6 '10 at 14:54
feedback

2 Answers

up vote 0 down vote

It means you have a hugely suboptimal query. Usual suspects:

  • no or bad indexes
  • functions on columns in WHERE clause (= ignored indexes)
  • data type conversions/precedence (= ignored indexes)
  • Scalar udfs with table access in SELECT clauses (= CURSOR affect)
  • Views querying/JOINING with views (A view is a macro that expands)

It could also be a plain resource issue: does the data returned touch the entire database so it uses too much memory that you get paging? Or do you get ASYNC_NETWORK_IO waits which can mean the client is not accepting results as fast as it should?

Generally, if you max out a server then it's poor code and/or design, not the DB engine.

link|improve this answer
My colleague's DB is optimized, with an index, specifically for what he is querying. I don't think he's joining anything, it's just a massive, massive table. I'm connecting with an ADO.NET application, and my connection times out. Also, we are on logically different databases, if that means anything. You probably do get paging with such a huge query, but why should it affect the little connection? Could it be the huge query is jamming up the network socket, so that I'm not getting my connect message through? – Carlos May 31 '10 at 20:07
feedback
up vote 0 down vote

As long as you're sure you've optimized as far as you can, you may want to look at your settings for parallelism; the default is to use all processors for parallel queries, you could change it to max out at a lower number depending on the number of processors you have, the query may take a little longer to run, but it should leave enough processing power free to service login requests. If the problem is isolated to this one query, rather than making a system-wide settings change, you can have your colleage change his/her query by adding a MAXDOP option to see if it helps.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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