Performance Tuning SQL - How?

Question

How does one performance tune a SQL Query?

• What tricks/tools/concepts can be used to change the performance of a SQL Query?
• How can the benefits be Quantified?
• What does one need to be careful of?

What tricks/tools/concepts can be used to change the performance of a SQL Query?

• Using Indexes? How do they work in practice?
• Normalised vs Denormalised Data? What are the performance vs design/maintenance trade offs?
• Pre-processed intermediate tables? Created with triggers or batch jobs?
• Restructure the query to use Temp Tables, Sub Queries, etc?
• Separate complex queries into multiples and UNION the results?
• Anything else?

How can performance be Quantified?

• Reads?
• CPU Time?
• "% Query Cost" when different versions run together?
• Anything else?

What does one need to be careful of?

• Time to generate Execution Plans? (Stored Procs vs Inline Queries)
• Stored Procs being forced to recompile
• Testing on small data sets (Do the queries scale linearly, or square law, etc?)
• Results of previous runs being cached
• Optimising "normal case", but harming "worst case"
• What is "Parameter Sniffing"?
• Anything else?

Note to moderators: This is a huge question, should I have split it up in to multiple questions?

Note To Responders: Because this is a huge question please reference other questions/answers/articles rather than writing lengthy explanations.

Answer 1

I really like the book "Professional SQL Server 2005 Performance Tuning" to answer this. It's Wiley/Wrox, and no, I'm not an author, heh. But it explains a lot of the things you ask for here, plus hardware issues.

But yes, this question is way, way beyond the scope of something that can be answered in a comment box like this one.

Answer 2

Writing sargable queries is one of the things needed, if you don't write sargable queries then the optimizer can't take advantage of the indexes. Here is one example Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code this query went from over 24 hours to 36 seconds

Answer 3

Of course you also need to know the difference between these 3 join

loop join, hash join, merge join

see here: http://msdn.microsoft.com/en-us/library/ms173815.aspx