A simple Best Practices Analyzer Tutorial with images, run on an old laptop with SQL Server 2005 and many planted issues, to help you start using BPA.
SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable
#TempTable and @TempVariable both live in tempdb, but they differ in scope and purpose. Their similarities and differences, explained side by side.
SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC
SP_EXECUTESQL vs EXECUTE/EXEC in SQL Server 2005: what they share for dynamic SQL, like late compile and variable scope, and how they differ.
SQL SERVER – 2005 Improvements in TempDB
Improvements in TempDB for SQL Server 2005: less logging and IO, lower latch contention, better worktable caching and more, compared with SQL Server 2000.
Memory Grants: Why a Query Waits for Memory
Memory grants can leave SQL Server queries waiting on RESOURCE_SEMAPHORE; inspect estimates, grants, and spills before adding memory.
SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005
What is Index Fragmentation and how do page splits cause it? How to measure it with DBCC SHOWCONTIG in SQL Server 2000 and sys.dm_db_index_physical_stats in 2005.
SQL SERVER – 2005 Row Overflow Data Explanation
Is 8060 bytes per row still the limit in SQL Server 2005? Yes, but varchar, nvarchar, varbinary, sql_variant and CLR types are excluded. That is row overflow data.