1 / 41

Parallel Execution Plans

Parallel Execution Plans. Joe Chang jchang6@yahoo.com www.qdpma.com. About Joe Chang. SQL Server Execution Plan Cost Model True cost structure by system architecture Decoding statblob (distribution statistics) SQL Clone – statistics-only database Tools

kelii
Download Presentation

Parallel Execution Plans

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Parallel Execution Plans Joe Chang jchang6@yahoo.com www.qdpma.com

  2. About Joe Chang • SQL Server Execution Plan Cost Model • True cost structure by system architecture • Decoding statblob (distribution statistics) • SQL Clone – statistics-only database • Tools • ExecStats – cross-reference index use by SQL-execution plan • Performance Monitoring, • Profiler/Trace aggregation

  3. So you bought a 64+ core box Now • Learn all about Parallel Execution • All guns (cores) blazing • Negative scaling • Super-scaling • High degree of parallelism & small SQL • Anomalies, execution plan changes etc • Compression • Partitioning Yes, this can happen, how will you know No I have not been smoking pot How much in CPU do I pay for this? Great management tool, what else?

  4. Parallel Execution Plans This should be a separate slide deck

  5. Execution Plan Quickie I/O and CPU Cost components F4 Estimated Execution Plan Cost is duration in seconds on some reference platform IO Cost for scan: 1 = 10,800KB/s, 810 implies 8,748,000KB IO in Nested Loops Join: 1 = 320/s, multiple of 0.003125

  6. Index + Key Lookup - Scan Actual CPU Time (Data in memory) LU 1919 1919 Scan 8736 8727 (926.67- 323655 * 0.0001581) / 0.003125 = 280160 (86.6%) True cross-over approx 1,400,000 rows 1 row : page 1,093,729 pages/1350 = 810.17 (8,748MB)

  7. Index + Key Lookup - Scan Actual CPU Time LU 2138 321 Scan 18622 658 (817- 280326 * 0.0001581) / 0.003125 = 247259 (88%) 8748000KB/8/1350 = 810

  8. Actual Execution Plan Estimated Actual Note Actual Number of Rows, Rebinds, Rewinds Estimated Actual

  9. Row Count and Executions Outer Inner Source For Loop Join inner source and Key Lookup, Actual Num Rows = Num of Exec × Num of Rows

  10. Parallel Plans

  11. Parallelism Operations • Distribute Streams • Non-parallel source, parallel destination • Repartition Streams • Parallel source and destination • Gather Streams • Destination is non-parallel

  12. Parallel Execution Plans Note: gold circle with double arrow, and parallelism operations

  13. Parallel Scan (and Index Seek) 2X IO Cost same CPU reduce by degree of parallelism, except no reduction for DOP 16 DOP 1 DOP 2 8X 4X IO contributes most of cost! DOP 8 DOP 4

  14. Parallel Scan 2 DOP 16

  15. Hash Match Aggregate CPU cost only reduces By 2X,

  16. Parallel Scan • IO Cost is the same • CPU cost reduced in proportion to degree of parallelism, last 2X excluded? On a weak storage system, a single thread can saturate the IO channel, Additional threads will not increase IO (reduce IO duration). A very powerful storage system can provide IO proportional to the number of threads. It might be nice if this was optimizer option? The IO component can be a very large portion of the overall plan cost Not reducing IO cost in parallel plan may inhibit generating favorable plan, i.e., not sufficient to offset the contribution from the Parallelism operations. A parallel execution plan is more likely on larger systems (-P to fake it?)

  17. Actual Execution Plan - Parallel

  18. More Parallel Plan Details

  19. Parallel Plan - Actual

  20. Parallelism – Hash Joins

  21. Hash Join Cost DOP 4 DOP 1 DOP 2 Search: Understanding Hash Joins For In-memory, Grace, Recursive DOP 8

  22. Hash Join Cost CPU Cost is linear with number of rows, outer and inner source See BOL on Hash Joins for In-Memory, Grace, Recursive IO Cost is zero for small intermediate data size, beyond set point proportional to server memory(?) IO is proportional to excess data (beyond in-memory limit) Parallel Plan: Memory allocation is per thread! Summary: Hash Join plan cost depends on memory if IO component is not zero, in which case is disproportionately lower with parallel plans. Does not reflect real cost?

  23. Parallelism Repartition Streams DOP 8 DOP 2 DOP 4

  24. Bitmap BOL: Optimizing Data Warehouse Query Performance Through Bitmap Filtering A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. Essentially, the filter performs a semi-join reduction; that is, only the rows in the second table that qualify for the join to the first table are processed. SQL Server uses the Bitmap operator to implement bitmap filtering in parallel query plans. Bitmap filtering speeds up query execution by eliminating rows with key values that cannot produce any join records before passing rows through another operator such as the Parallelism operator. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. By removing unnecessary rows early in the query, subsequent operators have fewer rows to work with, and the overall performance of the query improves. The optimizer determines when a bitmap is selective enough to be useful and in which operators to apply the filter. For more information, see Optimizing Data Warehouse Query Performance Through Bitmap Filtering.

  25. What Should Scale? 3 2 2 Trivially parallelizable: 1) Split large chunk of work among threads, 2) Each thread works independently, 3) Small amount of coordination to consolidate threads

  26. More Difficult 4 3 2 3 2 Parallelizable: 1) Split large chunk of work among threads, 2) Each thread works on first stage 3) Large coordination effort between threads 4) More work … Consolidate

  27. Parallel Execution Plan Summary • Queries with high IO cost may show little plan cost reduction on parallel execution • Plans with high portion hash or sort cost show large parallel plan cost reduction • Parallel plans may be inhibited by high row count in Parallelism Repartition Streams • Watch out for (Parallel) Merge Joins!

  28. Test Systems

  29. Test Systems • 2-way quad-core Xeon 5430 2.66GHz • Windows Server 2008 R2, SQL 2008 R2 • 8-way dual-core Opteron 2.8GHz • Windows Server 2008 SP1, SQL 2008 SP1 • 8-way quad-core Opteron 2.7GHz Barcelona • Windows Server 2008 R2, SQL 2008 SP1 Build 2789 8-way systems were configured for AD- not good!

  30. Test Methodology • Boot with all processors • Run queries at MAXDOP 1, 2, 4, 8, etc • Not the same as running on 1-way, 2-way, 4-way server • Interpret results with caution

  31. TPC-H

  32. Continuing Development

  33. Suppose I need to ALTER TABLE ADD new columns? • Of course, then UPDATE to set default

  34. Write Operations Insert, Update and Delete (IUD) component operations are not parallelizable. Select portion of query may be parallelized. Select parallelization may be inhibited if row count is high.

  35. Mass Update Insert, Update and Delete (IUD) component operations are not parallelizable. Select portion of query may be parallelized. Select parallelization may be inhibited if row count is high.

  36. Compressed Table LINEITEM – real data may be more compressible Uncompressed: 8,749,760KB, Average Bytes per row: 149 Compressed: 4,819,592KB, Average Bytes per row: 82

More Related