Some book suggestions for performance tuning I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in different ways affect performance and also   understanding things like table scans, indexes  an statistics. 

I don't just want to blindly add everything the tuning advisor says so I am looking for the knowledge required to assess these recommendations in the context our environment and how to fully utilise the tools available.  I am working with MSSQL2008+ so a book the utilises this environment specific would be good.

Thanks in advance
I like Kalen Delaney's internals book:


Microsoft SQL Server 2008 Internals


I also like the Brent Ozar / Christian Bolton book:


Professional SQL Server 2008 Internals and Troubleshooting


For T-SQL tuning, you can't get much a better T-SQL wizard than Itzik Ben-Gan:


Inside Microsoft SQL Server 2008: T-SQL Programming
Inside Microsoft SQL Server 2008: T-SQL Querying


And query tuning overall, Grant Fritchey and Sajal Dam:


SQL Server 2008 Query Performance Tuning Distilled


There are also some interesting solutions and opinions presented in various chapters in the two MVP Deep Dives titles (from which all proceeds go to two charities, War Child and Operation Smile):


SQL Server MVP Deep Dives Volume 1 (War Child)
SQL Server MVP Deep Dives Volume 2 (Operation Smile)


And a couple of the Red Gate titles @Kev left out, but which are both excellent:


Inside the SQL Server Query Optimizer (Ben Nevarez)
Troubleshooting SQL Server: A Guide for the Accidental DBA (Jonathan Kehayias / Ted Kreuger)