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 everyone 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 build-able directly from Visual Studio, no editing required. There is also a data generator for TPC-E. (I can build version 1.8 on VS2010, but not on later VS nor can I build version 1.12?)
Statistics that need special attention 2014-11
Top Clause and Other Factors in Problematic Execution Plans 2014-04 (Relativity 8.1)
Bush Joins 2014-04
Hekaton and Benchmarks 2014-03 (preliminary)
Load Testing 2013-06
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,
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.
Parallelism Strategy and Comments (2010 Oct?),
IO Cost Structure (2010 Oct?),
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.
Below are links to Oracle query optimization material that may be of interest. We should not be averse to see what is happening outside of SQL Server world.