Even if I have made a certification exam on SQL Server Design and implementation, I have no clue about how to trace/debug/optimise performance in SQL Server.
Now the database I built is really business critical, and getting big, so it is time for me to dig into optimisation, specially regarding when/where to add indexes.
Can you recommend a good book on this subject ? (smaller is better :)
Just in case: I am using SQL Server 2008.
Thanks
"Inside Microsoft SQL Server 2008: T-SQL Programming". I own this book. Very good.
There is also a free eBook entitled 'Dissecting SQL Server Execution Plans' which is quite good.
Inside Microsoft SQL Server 2005: Query Tuning and Optimization
Even though the book is for Sql Server 2005 it is still very interesting and much applies to sql server 2008.
Some great reading material, given as recommended reading by Kalen Delaney: http://docs.google.com/Doc?id=d4nxnt7_3f7ngptdh
I throughly recommend the Inside Microsoft SQL Server books (which Kalen was series editor for I believe). In particular "Inside Microsoft SQL Server 2005 - Query Tuning and Optimisation" - I know that's 2005, but still recommended, I don't believe there is a 2008 one. But you'll gain great insight from the whole series.
I would highly recommend SQL Server 2008 Query Performance Tuning Distilled (Expert's Voice in SQL Server) by Grant Fritchey and Sajal Dam.
Grant Fritchey publishes some of the best performance tuning articles on Simple Talk - highly recommended!