title: What causes SQL database overloads?
body: <p><strong>Database: SQL-Server</strong></p>
  
  <p>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.  </p>
  
  <p>So I'm trying to understand this problem better, and figure out what caused this. I have a few questions:</p>
  
  <ol>
  <li><p>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.  </p></li>
  <li><p>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.  </p></li>
  </ol>
  
  <p>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:</p>
  
  <pre><code>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=&gt;z.value).Max())
      select new {Normal = a};
  </code></pre>
  
  <p>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).  </p>