title: Performance Tuning SQL - How?
body: <p><strong>How does one performance tune a SQL Query?</strong></p>
  
  <ul>
  <li>What tricks/tools/concepts can be used to change the performance of a SQL Query?</li>
  <li>How can the benefits be Quantified?</li>
  <li>What does one need to be careful of?</li>
  </ul>
  
  <p><br>
  <strong>What tricks/tools/concepts can be used to change the performance of a SQL Query?</strong></p>
  
  <ul>
  <li>Using Indexes?  How do they work in practice?</li>
  <li>Normalised vs Denormalised Data?  What are the performance vs design/maintenance trade offs?</li>
  <li>Pre-processed intermediate tables?  Created with triggers or batch jobs?</li>
  <li>Restructure the query to use Temp Tables, Sub Queries, etc?</li>
  <li>Separate complex queries into multiples and UNION the results?</li>
  <li>Anything else?</li>
  </ul>
  
  <p><br>
  <strong>How can performance be Quantified?</strong></p>
  
  <ul>
  <li>Reads?</li>
  <li>CPU Time?</li>
  <li>"% Query Cost" when different versions run together?</li>
  <li>Anything else?</li>
  </ul>
  
  <p><br>
  <strong>What does one need to be careful of?</strong></p>
  
  <ul>
  <li>Time to generate Execution Plans?  (Stored Procs vs Inline Queries)</li>
  <li>Stored Procs being forced to recompile</li>
  <li>Testing on small data sets (Do the queries scale linearly, or square law, etc?)</li>
  <li>Results of previous runs being cached</li>
  <li>Optimising "normal case", but harming "worst case"</li>
  <li>What is "Parameter Sniffing"?</li>
  <li>Anything else?</li>
  </ul>
  
  <p><br>
  <em>Note to moderators:</em>
  This is a huge question, should I have split it up in to multiple questions?</p>
  
  <p><em>Note To Responders:</em>
  Because this is a huge question please reference other questions/answers/articles rather than writing lengthy explanations.</p>