What causes SQL database overloads?

Database: SQL-S开发者_运维百科erver

I built a program that runs on multiple machines and queries a database fairly frequently. What happened recently is that the queries coming from each instance of this program caused the database to grind to a screeching halt. It was almost impossible to query anything out of the database without waiting for many many seconds.

So I'm trying to understand this problem better, and figure out what caused this. I have a few questions:

1. If I have a read query that normally takes 2 seconds to run, what happens when there are many instances of my program trying to run this same query? Do they all run on the database at once, or do they run only one at a time? If they run one at a time, do the queries that are not being run line up and form a queue? I think if this queue can accumulate to a certain point, it would cause the database to slow down big time.

2. What typically causes a database to become unusably slow? Can this be caused by large tables (100,000+ rows) with multiple join queries? Or is this only possible if there's an infinite loop somewhere that is constantly trying to execute a query.

Addendum: I have a query that does multiple joins to a table with 100,000+ rows that uses a subquery in its where. So something like this:

var result = from a in db.TableNormal
    join b in db.TableHuge on a.id equals b.id
    where b.value = (db.TableOther.Select(z=>z.value).Max())
    select new {Normal = a};

So basically, the subquery is being run 100,000+ times every time its parent query is run. If this query is run ever second on 10 machines, would this be enough to significantly reduce the performance of an SQL server? (I have a very incomplete idea of the capacity of an SQL server).

There are so many things, statistics out of date, deadlocks, hardware issues, blocking from some large process, poorly written queries, poor design, lack of indexing. If you are having performance problems, you need to read a big fat book on performance tuning, there are literally hundreds of things that could be wrong. If you are not well familiar with the principles underlying good database performance, the probability that you have a poorly designed database or poorly designed queries is close to 100%.

Correlated subqueries end up needing to do a table scan and will get slower as more rows are added. They should rarely be used. Essentially you are unable to take advantage of indexing and in effeect it is like a little cursor in the query (cursors being a very bad thing in SQL Server). If this query is being run often it could definintely slow your system to a crawl. But to really know what it is doing you should look at the execution plan.

1. They will all be running concurrently, that is, at once.

2. Many things - usually unoptimized queries/database, without correct (or any) indexes. 100,000 rows is not many for a SQL database, though many joins can be a problem as well. Hardware issues can be the fault, not enough memory/CPU, slow hard drive. Design issues - badly designed schema (not normalized), badly designed query etc... etc... etc...

I recommend reading this two part article about solving performance issues in SQL Server.

In my experience, the only thing that grinds a SQL server to a halt is a bad query. It may be trying to retrieve too much data or making improper joins, but the first thing I would do is look very carefully at what you are trying to do and see if there is a better way.