title: Improving Ad-Hoc Query Performance
body: <p>Details:
  SQL Server 2012.
  Multi-Tenant DB Architecture.</p>
  
  <p>I am having a problem with user generate ad-hoc reporting queries jamming up my SQL box.</p>
  
  <p>A typical report may include 7 tables, (ranging from 10k rows to over 500k
  rows), 5 WHERE clauses and 2 sorting filters. These queries, as you can
  imagine, are quite expensive and can take minutes. The problem is
  these queries will max out SQL CPU and can take up over 20GB of tempdb
  space. Thus causing every other app that uses the database to timeout.</p>
  
  <p>Since these are user generated reports, I can't really tune the queries for performance. At over 2000 clients and rising, giving each client its own set of views or schemas is out of the question.</p>
  
  <p>My question is how can I tune my database for ad-hoc queries or somehow limit a query so it doesn't take up all of the server resources? As of right now, if someone runs a big report, the entire server hangs until I reset SQL Service. Obviously this is not sustainable.</p>
  
  <p>Is there a best practice out there for expensive query multi-tenant
  databases?</p>
  
  <p>EDIT:</p>
  
  <p>I have a query that can replicate the problem, however if I run it on my local development server the tempDB grows the same, but the process doesn't eat up CPU like it does on my AWS instance. (3% cpu on local vs 58% on EC2 m3.large).</p>
  
  <p>Local CPU- Intel Xeon E3-1230 V2</p>
  
  <p>EC2 CPU- Intel Xeon E5-2670 V2</p>
  
  <p>From what I can see, all of the server settings are pretty much identical.</p>