260 likes | 399 Views
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.
E N D
T-SQL Query Tuning 20-04-2012, SQL Zaterdag MenzoSteinhorst, Senior Premier Field Engineer, Microsoft Services, menzos@microsoft.com
Agenda • Introduction to Query Optimizing • Query Plan Compilation • Analyzing Execution Plans
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>
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
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
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, …)
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!
Table Scan Clustered Index Scan Clustered Index Seek Non-Clustered Index Scan Non-Clustered Index Seek Key Lookup Rid Lookup Data Access Operators
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
Join Types & Operators • Nested Loop Join • Merge Join • Hash Match • Logical Operators: • Cross Joins • Inner Joins • Semi Joins • Anti-Semi Joins • Outer Joins
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
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
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
Aggregates • Sum/Count/Avg/etc.... • Group By/ Sort • Distinct/ Top • Stream Aggregate • Hash Aggregate
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
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