SQL Server 2000 7 0 Execution Plan Cost

SQL Server 2000 (& 7. 0) Execution Plan Cost Formulas Joe Chang jchang 6@yahoo. com www. sql-server-performance. com/joe_chang. asp

Database Performance Code Tables, Indexes, Constraints, SQL statements Data Total rows, Rows involved, Statistics Execution Plan Formulas used by the CBO Server Architecture Processor, SMP, etc

Topics Component Operation Cost Model Cost formulas for basic operations Dependencies Rows & Pages involved - yes Index depth – no, Locks level – no WHERE conditions – no cost for logic only if row count affected

Index Seek - 1 row SELECT xx FROM N 1 C WHERE ID = @ID I/O CPU ≤ 1 GB 0. 0063285000 0. 0000796 > 1 GB 0. 0032034251 0. 0000796 Total 0. 006408100 0. 003283025

Index Seek Cost Formula Multiple rows, ≤ 1 GB I/O: 0. 00632850 + CPU: 0. 00007960 + Total: 0. 00640810 + + 0. 0000011 / add. 0. 00074074 per additional page 0. 00000110 per additional row 0. 00074074 /add. Page row

Plan Cost Unit of Measure Time? CPU-usage? time, in seconds 0. 0062500 sec -> 160/sec 0. 000740741 ->1350/sec (8 KB) ->169/sec(64 K)-> 10. 8 MB/sec Too fast for 7200 RPM disk random I/Os. About right for 1997 sequential disk transfer rate? S 2 K BOL: Administering SQL Server, Managing Servers, Setting Configuration Options: cost threshold for parallelism Option Query cost refers to the estimated elapsed time, in seconds, required to execute a query on a specific hardware configuration. S 2 K 5 BOL: Administering SQL Server, Managing Servers, Setting Configuration Options: cost threshold for parallelism Option The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

Disk Drive Performance Access time = rotational latency + seek time 7200 RPM = 4. 17 ms Rotational Latency 10000 RPM = 3 ms, 15000 RPM = 2 ms Rot. Avg. Sequential Year Model RPM Seek Transfer 1994 ST 12550 7. 2 K 8. 0 3. 5 -6. 0 MB/sec 1996 ST 34371 7. 2 K 9. 4 7. 1 -11. 7 1997 ST 34572 7. 2 K 9. 4 7. 9 -12. 5 1998 1999 ST 39102 ST 39103 10 K 5. 4 19. 0 -28. 9* 22. 7 -36. 2* 2000 2002 ST 318451 ST 373453 15 K 4. 1 3. 8 37. 4 -48. 9* 49 -75

Bookmark Lookup – Single Row SQL Server 2000 up to SP 3 (build 760) SQL Server 2000 after MS 03 -031 (build 818) Same cost for bookmark lookup on Heap and Clustered Index

Detail up to SP 3 (build 760) after MS 03 -031 (build 818)

BL Plan Cost – Single Row up to SP 3 (build 760) I/O Total ≤ 1 GB 0. 0062500001 > 1 GB 0. 0031249251 CPU 0. 0000011 0. 0062511 0. 0000011 0. 0031260 after MS 03 -031 (build 818) I/O CPU ≤ 1 GB 0. 000097656251 0. 0000011 > 1 GB 0. 000048826954 0. 0000011

Bookmark Lookup Multiple Rows CPU cost 0. 0000011 per applies in all cases I/O Cost: up to SP 3 (build 760) For table size up to approx. 100 pages, I/O cost is bounded by the number of pages, representing an estimate of # of I/O required, at cost 0. 0062500 / page For larger tables, I/O cost is not bounded by the number of pages after MS 03 -031 (build 818) A lower I/O cost per page (0. 00009765625) for up to 64 rows, original I/O cost applies at 65 rows and above

Bookmark Lookup I/O Cost – SP 3 By Table Size (pages) versus rows, steps 0. 0000625

Bookmark Lookup I/O Cost - 818 By Table Size (pages) versus rows, steps 0. 00009765 625

Bookmark Lookup I/O Cost – SP 3 Plan Cost by Table Size (pages) versus rows, SP 3 and earlier

Bookmark Lookup I/O Cost - 818 Plan Cost by Table Size (pages) versus rows, build 818+

Bookmark Lookup IO Count For a bookmark lookup for n rows to a table with p pages 1 st row: 1 I/O required 2 nd row: 1 I/O already done, 1 -1/p chance additional I/O F(n, p) = F(n-1, p) + 1 – F(n-1, p)/p = F(n-1, p)(1 -1/p) + 1 R = 1 -1/p F(n, p) = 1 + R 2 + … + Rn-1 = (1 -Rn)/(1 -R) = p – p (1 -1/p)n

Table Scan SELECT xx FROM N 1 H WHERE ID = @ID I/O: 0. 03757850 + 0. 00074074/page CPU: 0. 00007850 + 0. 00000110/row

Bookmark versus Scan Table scan cost for 50, 000 row, 506 pages Index Seek and Bookmark Lookup cost for ≤ 1 GB, SP 3

Aggregates SELECT MIN(x) FROM M 2 C WHERE Group. ID=1 Aggregate: MIN & MAX Aggregate & Compute Scalar AVG & SUM For single row result I/O: None CPU: 0. 0000001/row

Loop, Hash and Merge Joins SQL Server supports 3 types of joins Loop , Hash , Merge Hash join subtypes In memory, Grace, Recursive Different settings for SQL Batch & RPC Merge join one-to-many-to-many

Outer Source (Top Input) Loop Join Inner Source (Bottom Input)

Loop Join Cost = Outer Source Cost + Inner Source Cost + Join Cost

Loop Join, cont I/O Cost: 0 CPU Cost 0. 00000418 per row

Loop Join, Inner Source row count is expected matches per row for each row from outer source (rounded down) I/O and CPU cost is for 1 execute Number of executes is row count from outer source Cost is for all executes

Loop Join IS Cost Assumptions: CPU Cost: 0. 0000796003 per row applies for 1: 1 join IO Cost: Single row base cost same as Index Seek Multiple rows: estimate of number pages required For small IS table (<100 pages) Bounded by 0. 0000785 per page For large IS table (>200 pages) Bounded by 0. 0000785 per page for up to 132 rows Approx 0. 0063285 (≤ 1 GB) per row for 133 rows and above

Loop Join IS “I/O” Costs (1) Excluding base I/O cost 0. 0063285 Inner Source “I/O” Cost by table size versus rows

Loop Join IS “I/O” Costs (2) Excluding base I/O cost 0. 0063285 Inner Source “I/O” Cost by table size versus rows

Loop Join Costs Small & Big IS Source table size

Hash Join SELECT * FROM M 2 C m INNER HASH JOIN M 2 D n ON n. ID = m. ID WHERE m. Group. ID = @Group 1 AND n. Group. ID = @Group 2 Hash Join Cost = Outer Source + Inner Source + Hash Match

Hash Join Cost Outer Source & Inner Source are index seeks or scans 1 execute, 1 or more rows

Hash Join - IS Hash join cost independent of IS column count or size

Hash Join - OS Q 1 Q 2 Q 3 Hash join cost dependent on OS size

Hash Join Cost Q 2 Q 1 Q 3

Hash Join Cost Formula Base CPU Cost = 0. 017750000 base Fudge factors + 0. 0000001749 (2 -30 rows) + 0. 0000000720 (100 rows) 0. 000015266 15 byte, 1 st 100 (1: 1 join) Cost per row 0. 000015091 >1 K 0. 000015857 (parallel) 0. 000001880 per row per 4 bytes in OS 0. 000009750 OS 0. 000005516 / 0. 000005341 IS I/O Cost = 0. 0000421000 per row over >64 -102 MB? 0. 0000036609 per row per 4 byte Hash join spills to tempdb at 64 -102 MB in 32 -bit 1 -2 GB memory 700 MB+ in 64 -bit with 32 GB memory

Merge Join SELECT xx FROM M 2 C m INNER MERGE JOIN M 2 D n ON n. ID = m. ID WHERE m. Group. ID = @Group 1 AND n. Group. ID = @Group 2 Merge Join Cost = Outer Source + Inner Source + Merge cost

Merge Join Cost CPU: 0. 0056044 + 0. 000004480/row Discrepancy: 0. 0000030 + 0. 000002280/row IS + 0. 000002200/row OS

Merge + Sort

Many-to-Many Merge I/O: CPU: 0. 000313000 per row 0. 0056046 + 0. 00004948 per row

Loop, Hash & Merge Join

Loop, Hash & Merge Join (2)

Loop, Hash & Merge Join (3)

1 to Many Joins Each row from OS joins to n rows in IS Join Cost per additional IS row Loop 0. 00004180 Hash ~0. 00000523 -531 Merge ~0. 00000237 IS Index Seek cost: 0. 0000011/row + IO costs

Sort Cost I/O: 0. 011261261 CPU: 0. 000100079 + 0. 00000305849*(rows-1)^1. 26 weak dependency on size per row

Index Intersection Table M 2 x, Index on Group. ID Index on Code. ID SELECT xx FROM M 2 C WHERE Group. ID = @Group AND Code. ID = @Code SELECT xx FROM M 2 C a INNER JOIN M 2 C b ON b. ID = a. ID WHERE a. Group. ID = @Group AND b. Code. ID = @Code Merge Join cost formula different than previously discussed

Execution Plan Costs Recap ≤ 1 GB > 1 GB Additional page Additional rows I/O CPU Total 0. 006328500 0. 0000796 0. 006408100 0. 003203425 0. 0000796 0. 003283025 0. 00074074/p 0. 00000110/r Bookmark Lookup ≤ 1 GB > 1 GB I/O 0. 0062500 0. 0031249 Table Scan Base Additional page Additional row I/O CPU Total 0. 0375785 0. 0000785 0. 00074074/p 0. 0000011/r Index Seek CPU 0. 0000011 Total 0. 0062511 0. 0031260

Logical IO count Example: Index Depth 2, rows per page: 100 I/O per additional row Bookmark Lookup (Heap) 1 Bookmark Lookup (Clustered) 2 Loop Join (IS) 2 Very little relation between IO count and plan cost for different component operations IO count comparisons more relevant for similar operations I/O per addition 100 rows Index Seek 1 Hash & Merge join 2

Accurate Performance Testing Execution Plan - match Raw size of DB not as important: 1 M customers actual, 10 K test Cardinality more important 1 Customer – 10 orders – 10 order items per order Statistics & actual data queried Statistics could be accurate but actual queries favors different distribution

Aggregates, multiple result rows CPU Cost: 0. 00000280 per source row 0. 00000465 per result row CPU Cost per result row: 0. 01777 + 0. 0000188

Execution Plan Cost Summary Plan costs do not include RPC cost Plan costs are a model Index seek independent of index depth Bookmark L/U independent of table organization Logic by itself does not influence cost Costs are not influenced by lock hints Populate test DB with accurate cardinality

Additional Information www. sql-server-performance. com/joe_chang. asp SQL Server Quantitative Performance Analysis Server System Architecture Processor Performance Direct Connect Gigabit Networking Parallel Execution Plans Large Data Operations Transferring Statistics SQL Server Backup Performance with Imceda Lite. Speed jchang 6@yahoo. com