Sometime in 2008, I started what was to be a series on SQLBlog about the SQL Server execution plan cost model, i.e., the Cost Based Optimizer (CBO). This was to be an update of my material on the SQL Server 2000 execution plan formulas to SQL Server 2005. At the time, I could not upload images and an explanation without pictures is very tedious to follow. This is the same topic, except that I can now upload images, so important points are now illustrated. Also, because of the lateness, I will probably discontinue any work on SQL Server 2005. Any near term work will be on SQL Server 2008 SP1 or SP2 when it comes, and then shift to 2008 R2.
1)  Examples with AdventureWorks,
2)  Index Seek and Key Lookup,
3)  Multi-Row Key Lookups,
4)  Table Scan,
5)  Loop, Hash, Merge Joins,
6)  Insert, Update, Delete,
7)  Parallelism I (2010?)
8)  Parallelism II
9)  Parallelism III
The coverage here includes Index Seek, Key Lookup (formerly Bookmark Lookup),Table Scans, Nested Loops, Hash and Merge joins. In SQL Server 2005 and later, Key Lookups to a clustered index and the Nested Loops (or loop join) are now the same operation.
I started with using the Adventure Works database, as every can get the exact copy of this database. However, Adventure Works is too small to demonstrate parallel execution plans. The TPC-H database is suitable and the data generator is available on the TPC website. The project is now buildable directly from Visual Studio, no editing required. There is also a TPC-E data generator.
Job History Row Limiter in msdb 2013-03
Queries barely over the Cost Threshold for Parallelism 2012-11
(formerly Parallelism in Queries with Intermediate Plan Cost),
Decoding Stats Stream 2012-06,
Query Optimizer Gone Wild - Loop Join to Table Scan,
Every now and then, a query that normally runs within a reasonable time, runs forever. This is one example.
The Skip Scan feature that has been in Oracle for a long time. Now that I have been advocating DW on SSD, I think it is important for SQL Server to have this feature as well. Never be afraid to borrow other people's good ideas.
SIMD Extensions for the Database Storage Engine
Parallelism Strategy and Comments (2010 Oct?),
Execution Plan Cost Model,
IO Cost Structure (2010 Oct?),
Logical IO as a Metric,
Excerpts from BOL,
and from SQL-Server-Performance A First Look at Execution Plan Costs in Yukon Beta 1 2005-01
Back in 2002, I published on Sql-Server-Performance explaining in detail how the formulas used by SQL Server 2000 CBO can be derived using a set of tables populated to a range of specific row and page values. The cost model changed from SQL Server 2000 to 2005, but the full detail derivation of the exact SQL Server 2005 and 2008 cost model is not provided here. Rather, the Adventure Works database will be used to demonstrate basic points of the CBO model.
The original document for the SQL Server 2000 plan cost formulas seem to have broken links. I am reposting an interim copy for now SQL Server 2000 CBO. The formatting is rough, as it will take a while to clean up the html.