Page splits are bad for performance, but don't base your clustered index on a sequential column blindly. Here's a situation where it doesn't make sense to do so.

You should consider your data types carefully when designing SQL Server tables. If not done properly you may waste lots of bytes per page.

Implicit conversions are bad for performance as they may cause index scans instead of index seeks. Learn how to avoid this when using the uniqueidentifier data type.

When performing math using SQL Server decimals, the precision may change and cause implicit conversions. Learn how to predict and avoid this situation.

Learn what causes a SQL Server deadlock and how to solve it using locking hints and isolation modes. We'll be looking at the updlock hint as well as the serializable isolation mode.

Here's an example of the query optimization process. Fixing one thing allows you to optimize another, all the way until the query is perfect.

These are the slides & materials for my "Optimizing SQL Server 2005" talk presented at the Aarhus .NET User Group.