Secrets of the Query Optimizer Revealed Presenting Ideas

Secrets of the Query Optimizer Revealed Presenting Ideas That Win Executive Support Kevin Kline, Principal Program Manager

2 Please silence cell phones

Explore everything PASS has to offer Free online webinar events Local user groups around the world Free 1 -day local training events Online special interest user groups Free Online Resources Newsletter PASS Blog PASS Connector White Papers BA Insights Session Recordings Business analytics training Get involved www. pass. org

Session evaluations Your feedback is important and valuable. Submit by 5 pm Friday, November 10 th to win prizes. 3 Ways to Access: Go to pass. Summit. com Download the Guide. Book App and search: PASS Summit 2017 Follow the QR code link displayed on session signage throughout the conference venue and in the program guide

Founder & Past President of PASS Kevin was one of the original nine independent founders of PASS and served two terms as president from 2004 -2008. Industry Expert Kevin authored his first technology book out of 11 (on Oracle, gasp!) in 1995. His best known book is SQL in a Nutshell, a bestseller. He has been a Microsoft Data Platform MVP since 2003. Kevin Kline Principal Program Manager, Sentry. One /KEKline @KEKline The Horde KEKline Kevin has a big blended family of one son, now 26, and SIX daughters, ranging in age from 24 to 14. The Horde has taught him patience at the cost of his hair, literally.

About the session What this session is not: • • An end-to-end optimizer session A performance tuning session Goals of this session • • • Additional and deeper understanding of SQL Server optimizer internals Understanding query processing queries Provide an additional skill for performance tuning Special thanks to Brian Hansen: Twitter: https: //twitter. com/tf 3604 Blog: http: //www. tf 3604. com/ Also, thanks to Rob Volk, Paul White, and Benjamin Nevarez.

Agenda Background: • Physical processing considerations Executing a query: • Parse, bind, transform, optimize, execute Revealing the secrets: • • Heuristics, transformation rules, parse trees, memos with trace flags 860 x Extended events and DMVs Limitations & DMVs

I Want Hidden Secrets! Combine T 3604 with DBCC {RULEOFF | RULEON} • • DBCC RULEOFF ('Get. To. Scan') DBCC RULEON ('Get. To. Scan') • • DBCC RULEON ('JNto. HS') DBCC RULEON ('JNto. SM') Use DBCC {SHOWONRULES | SHOWOFFRULES} to see the access methods chosen by the optimizer. Use OPTION ( {QUERYRULEON | QUERYRULEOFF} ) for querylevel hints.

Example Query: People vs SQL Server

Example Query: SQL Server vs People

Deep Dive on the Query Processing

Parsing and Binding • Sometimes called the ‘algebrizer’ or ‘normalizer’ • Validates correct syntax • Identifies constants • Expands views • Builds initial logical parse tree* • Metadata discovery & name resolution; Beware deferred name resolution • Checks user permissions • Data type resolution (e. g. UNIONs) • Aggregate binding; Associates table/column names to system catalog • Generates a hash based on query text • Checks plan cache to see if it exists

Parse trees

Parse trees More like a WHERE clause • Internal representation of logical query operators • Nodes may be logical or physical operators; logical at first • 0 to infinity inputs, 1 output • SQL Server will output parse trees at various phases of optimization • • Special trace flags will trigger output Used to initialize the Memo structure More like a SELECT clause

Early Stage Metadata • Early stages of QP do NOT depend on contents from the database: • • The data itself Statistics Cost estimates Cost-based decisions • Only need database schema and query definition.

Simplification • • Make this query easier to process! Do things like standardize queries, remove redundancies. For example: • • • Convert subqueries to joins; Convert inner join to outer join Remove redundant joins, such as foreign key join elimination Predicate pushdown Contradiction detection Aggregates on unique keys

Trivial Plans • • • The last step before true query optimization takes place. Only one way to solve the query, or one obviously best way, use a trivial plan. Trace flags: • • T 8757 disables trivial plans entirely. T 2861 enables forced caching of trivial plans.

sys. dm_exec_query_optimizer_info • Documented. Sort of. • Three columns: • • • counter: Name of the optimizer event occurrence: Number of times the event was recorded since last restart value: Average value per event to date • Collect before and after images of this view on a quiet system. • Don’t forget system-generated queries.

SYS. DM_EXEC_QUERY_OPTIMIZER_INFO Counter Descriptions From the book Microsoft SQL Server 2014 Query Tuning & Optimization By Benjamin Nevarez, Mc. Graw-Hill Osborne Media, 2014

Query Optimization

Query Optimization Description Optimization is cost-based: • Optimized for worst case scenario: “everything comes from disk” • Require comparisons of candidate plans’ costs in order to guide decisions Load statistics and cardinality estimation • • • Checks for manually and auto-created statistics SQL Server 7 vs 2014 cardinality estimator Considers other physical properties, such as amount of memory or number of CPUs

Query Optimization Search Phases Multiple phases or “searches”. Probes the search space to find transformations it might apply: • Phase 0: Transactional plans • • Simple, basic tests and a low internal cost threshold. Looks for simple rule matches. For example, nested loops without parallelism. • Plans that can be simplified. Applies more transformation rules, some join reordering, limited parallel exploration and a medium internal cost threshold. Looks for intermediate rule matches. For example, comparing the cost of the best serial plan versus best parallel plan. • Phase 1: Quick plans • • Phase 2: Full plans: • • • Complex queries, parallelism, spills & spools to tempdb. Applies the full set of transformation rules, invokes heuristics to exclude entire branches of the full search space; Pruning vs Matching. Normally exits on timeout.

Optimal Plan vs Best Plan • The query optimizer seeks an optimal plan, not the best plan. • To find the best plan, the optimizer might have to assess every possible execution plan that achieves the directive of a given query. • Assume a, b, c, d are tables with a clustered index and 3 non-clustered indexes each. • How many physical access methods per table are available for this query? • Consider: SELECT. . . FROM a JOIN b ON. . . JOIN c ON. . . JOIN d ON. . . 72 Possible physical data access methods 120 Possible logical join orders 3 Physical joins possible per logical join + May require intermediate sort operation -----------------= 25, 920 possible plans

How Does the Optimizer Go So Fast? • Heuristic application of transformation algorithms speeds up SQL Server query optimization. • Based on relational algebra. • Alternatives are stored in memory structures called “Memos”. • Four transformation types: • • Simplification Exploration Implementation Physical Property Enforcement

Exploration Transformation • Start from a logical operation (may be a sub-branch of the full query) • Find equivalent logical operations, i. e. a substitute operation • Examples: • • • Join commutativity: A⋈B B⋈A Join associativity: (A⋈B)⋈C A⋈(B⋈C) Aggregate before join

Implementation Transformation • Start from a logical operation • Find equivalent physical operation • Examples: • • • A⋈B A (nested loops join) B A⋈B A (merge join) B A⋈B A (hash join) B • Obtain costing on physical operations • Can prune expensive branches from tree

Physical Property Enforcement Transformation • Properties associated with parse tree nodes • • Uniqueness, type, nullability, sort order Constraints on column values • Transformation rules may cause certain properties to be enforced • Example: sort order for a merge join

sys. dm_exec_query_transformation_stats • One row per transformation rule: • • • “Promise_Total” –Estimate of how useful might the rule be for this query “Built_Substitute” –Number of times the rule generated an alternate tree “Succeeded” –Number of times the rule was incorporated into search space • Collect before and after images of this view on a quiet system.

Prefer Xevents? • Use sqlserver. query_optimizer_ estimate_cardinality to return the statistics being used for a given query. • Now, look at the XE session: Calculator is the algorithm used to estimate cardinality • Subcalculator shows predicate cardinality • Input_relation - shows input tree • Stats. Collection will show the loaded statistics. • Stats. Collection. ID appears as a property of the operator in the plan as well. • The Stats. Collection. ID is in the Calculator which tells you what stats is used for what predicate. •

The Memo Structure • Used to explore different alternatives to a portion of the query tree. Can be visually huge. • Can think of it as a matrix: • • Rows (groups) represent substitutes – each entry is logically equivalent optimizer choice and is hashed to prevent duplication Columns represent application of a transformation rule • Both logical and physical alternatives go into the Memo. But only physical alternatives are costed.

Deciphering the Initial Memo Structure Entry number, all zero since there’s only one logical entry per group. Logical operation. Group number of child groups, referencing its two inputs, group 8 and 9, and the logical comparison defined by sub-tree of group 12. Cardinality estimate

Deciphering the Final Memo Structure Costing evaluation Original operation

Give the optimizer more time • Use trace flag -T 8780. • Doesn’t literally give the optimizer more time. It raises the number of transformations allowed, but roughly works out to the same thing. DOES NOT GUARANTEE: • • …an alternative plan. …that an alternative plan will be better.

Appendix: Trace Flags Reference T 2312 and T 9481 enables or disables the SQL 2014 cardinality estimator, respectively. T 2372 Memory before and after properties and rules, with Search Phases T 2373 Memory before and after deriving properties and rules (verbose). T 2861 enables caching trivial plans. T 4199 enables all fixes that were previously made for the query processor under many trace flags/hot fixes based on “special” policy. T 7357 Unique hash optimization used. T 8605 shows query initial tree representation created by SQL Server. T 8606 shows simplified logical trees used during the optimization process. T 8607 shows the optimization output tree. T 8608 shows the input tree for cost-based optimization when first copied into the Memo structure. T 8609 Task and operation type counts. T 8615 shows the final memo structure. T 8675 shows information on the stages of optimization phases and search times. T 8757 disables trivial plans entirely. T 8739 shows group optimizer info and results. TF 9130 will show the pushed predicate in the query plan. T 9292 shows the statistics objects the optimizer is considering for the optimization process. T 9204 shows the statistics objects used to produce a cardinality estimated loaded during the optimization process.

Appendix: Command Reference DBCC {TRACEON | TRACEOFF} DBCC {RULEON | RULEOFF} DBCC {SHOWONRULES | SHOWOFFRULES} OPTION (RECOMPILE, QUERYTRACEON n, QUERYRULEOFF ‘xxx’) • sys. dm_exec_query_optimizer_info • sys. dm_exec_query_transformation_stats • •

Summary ü SQL Server doesn’t statem see SQ ents th L e way h them. umans see ü Step s in opt imizatio phases n searc ? h ü Com mon tra nsform ü Trace ations? flag les sons: 3 8780 604, 86 xx, ü Mem o struc tures te alterna lls you tives co the nsidere d

Thank You Learn more from Kevin Kline @KEKline kkline@sentryone. com