1 / 26

T-SQL Query Tuning

T-SQL Query Tuning. 20-04-2012, SQL Zaterdag. Menzo Steinhorst , Senior Premier Field Engineer, Microsoft Services, menzos@microsoft.com. Agenda. Introduction to Query Optimizing Query Plan Compilation Analyzing Execution Plans. Components involved. Query Optimizing and Processing.

amish
Download Presentation

T-SQL Query Tuning

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. T-SQL Query Tuning 20-04-2012, SQL Zaterdag MenzoSteinhorst, Senior Premier Field Engineer, Microsoft Services, menzos@microsoft.com

  2. Agenda • Introduction to Query Optimizing • Query Plan Compilation • Analyzing Execution Plans

  3. Components involved

  4. Query Optimizing and Processing

  5. Query Processing

  6. Logical Query Processing (5) SELECT (5-2) DISTINCT (5-3) TOP (5-1) <select_list> (1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate> (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias> (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias> (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias> (2) WHERE <where_predicate> (3) GROUP BY <group_by_specification> (4) HAVING <having_predicate> (6) ORDER BY <order_by_list>

  7. Demo 1: Logical Query Processing & Statistics

  8. Analyzing Query Plans • Tools of the trade: • SQL Server Management Studio • Estimated and actual showplan • Set Options • Statistics IO • Statistics Time • Statistics Profile • Showplan_all

  9. Graphical Versus Text Showplan • Graphical + Query need not execute + Visually compelling symbols + Easily identify costly steps + Provides help in explaining the operators – Difficult to see whole picture at one • Text + More detailed estimated statistics + Easy to save, manipulate and compare in a spreadsheet + More easily see structure ‘at a glance’ + Compare actual rows and iterations to estimates (with Statistics Profile On) + More detailed descriptions of operators – Visually dense and difficult

  10. Demo 2: SSMS Graphical ShowplansDemo 3: Set Options

  11. What to look for? • Large row counts or execution counts • Large estimated costs • Join techniques (hash, loop, merge) • Access techniques (seeks, scans, bookmark lookups) • Aggregation techniques (hash, stream) • Other operations (sort, top, …)

  12. What operators to look for? • Data Access operators • Scans & low rowcount • Seeks possible with proper indexing • Join operators • Efficient join type? • High relative cost? • Sort operators • Avoidable by indexes? • Aggregates • Use (persisted) computed columns? • Operators with high relative cost!

  13. Table Scan Clustered Index Scan Clustered Index Seek Non-Clustered Index Scan Non-Clustered Index Seek Key Lookup Rid Lookup Data Access Operators

  14. Data Access Operators - Guidance • Lookups? Indexes (include clause) • Scans for larger ranges • SQL will not make use of NCLIs if a result set is comprising more than 30% of the in-row data-pages of the table • Seeks for few rows

  15. Demo 4: Data Access Operators

  16. Join Types & Operators • Nested Loop Join • Merge Join • Hash Match • Logical Operators: • Cross Joins • Inner Joins • Semi Joins • Anti-Semi Joins • Outer Joins

  17. Get row from outer table Get matching row from inner table Output composite result Loop through inner table When inner table is exhausted: loop on outer table Nested Loop Join

  18. Merge Join Get row from outer table Get next row from inner table with same key If found, output and loop on inner table If not found, loop on outer table Join Sequence Join Sequence Match & Merge

  19. Hash Match • Scan (smaller) build table • Hash build key values; store in hash table • Scan (larger) probe table • Hash probe key value; look up in hash table • If found output result Hash Table Hash Join Key Build Table Lookup in hash table Probe Table

  20. Demo 5: Joins

  21. Aggregates • Sum/Count/Avg/etc.... • Group By/ Sort • Distinct/ Top • Stream Aggregate • Hash Aggregate

  22. DEMO 6: Aggregates

  23. Parallelism • Each parallel operation is performed by all threads involved in a parallel plan • ‘Partition Parallelism’, not ‘Pipeline Parallelism’ • Look for the yellow indicator in graphical showplan • PARTITION COLUMNS are selected to allow each parallel stream to join and aggregate independently of other streams • Repartition Streams is costly, but necessary to align streams for next operation

  24. Demo 7: Parallelism

  25. Resources • Inside Microsoft SQL Server 2008: Internals • Inside Microsoft SQL Server 2008: T-SQL Querying • Inside Microsoft SQL Server 2008: T-SQL Programming • http://sqlcat.com

  26. Questions?

More Related