virtual techdays

INDIA │ 9-11 February2011 virtual techdays SQL 2008 Query Tuning Praveen Srivatsa │Principal SME – StudyDesk91 │Director, AsthraSoft Consulting │Microsoft Regional Director, Bangalore │MVP, ASP.NET

Query Architecture

Query optimizer and statistics • SQL Server always used a statistic based query optimizer • Statistical information is used to chose the optimal index for a particular query • The most important information for the optimizer is: • Selectivity or density: How many different values exist in this column of the table? • Histogram over the values of a column: How often does one particular value exist?

Analyzing Factors • Application • Database Design • Microsoft SQL Server • Operating System • Hardware

SQL Coding Issues • Excessive Scanning • Poor use of indexes/missing Indexes • Out of Date Table Statistics • Non-selective Triggers • ‘Where’ clause not limiting record set enough • Excessive Recompiles • Long blocking durations (multi-connections)

Where • Limit number of Columns • Avoid <>, OR, and not • Indexed fields • Avoid calculated columns • Avoid functions • Avoid redundant criteria

Equality Predicate SELECT * FROM CUSTOMER WHERE CUSTOMER_NAME = 'CUSTOMER#000000047' Range Predicate SELECT * FROM CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 AND 200 Conjunctions SELECT * FROM ORDER_ITEM WHERE SHIP_DATE = '1998-07-02' AND SUPPLIER_ID = 350 AND PRODUCT_ID > 80000 Search arguments useful in driving index seeks Search Arguments

Not Predicates SELECT * FROM CUSTOMER WHERE CUSTOMER_ID <> 100 Like pattern on left of column SELECT * FROM CUSTOMER WHERE CUSTOMER_NAME LIKE ‘%47‘ Function on column SELECT * FROM ORDER_ITEM WHERE YEAR(SHIP_DATE) = 1998 Non-search arguments may still be evaluated in an index In Where clause of Index Scan or Index Seek Operator Non-Search Arguments

Understanding Joins OuterTable InnerTable JoinMethod

Nested Loop Algorithm • Get Row From Outer Table (1st input to Join operator in showplan) • Get Matching Row From Inner Table (2nd input to Join operatorin showplan) • Output Composite Result • Loop Through Inner Table • When Inner Table Exhausted ,Loop on Outer Table OuterTable InnerTable

Merge Join Algorithm • Get next 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 OuterTable InnerTable Join Sequence Join Sequence Match andMerge

Hash Join Algorithm • Scan Smaller (Build) Table • Hash Build Key Values; Store in Hash Table • 1st input to Join operator in SHOWPLAN • Scan Larger (Probe) table • Hash Probe Key Value; Look Up in Hash Table • If Found, Output Result • 2nd input to Join operator in SHOWPLAN Hash Join Key Hash Table Build Table Lookup in Hash Table Hash Join Probe Table Key

Troubleshooting Join Performance • Is join predicate missing? • Indexes on join column(s)? • Join columns exact data type match? • Examine Statistics Profile for estimates versus actual • Hash Join: EstimateRows for child operatorproducing Build input • Nested Loop: EstimateExecute for inner table (2nd input) • Merge Scan: EstimateRows for Sort Operator (if present) • Examine STATISTCS IO • SET STATISTICS IO ON • If Hash Join, are workfiles present • Indicate Build tables have spilled to tempdb • Last Resort: Join Hints

Stored Procedures • Execution Plan Usage • Keep them small • Recompiles • Causes • Avoiding • Analysis

Intelligent Indexing • Minimizing result sets to columnsneeded (Covering index approach)instead of select * • Avoid indexing fields that change frequently • Compound Indexes– Unique >>>>Non-Unique

How do you do Partitioning? • Before a table is partitioned two things have to be created, a partition Function and Schema • Partition Function • Each row of an index/table is assigned to a partition (numbered 1, 2, 3, ...) by using a “Partition Function” • SQL Server 2005 supports Range partitioning only on a single column • User defines the key column, the number of partitions, and the partition boundary points • Partition Scheme • Each partition for a partition function is mapped to a physical storage location (Filegroup) through a “Partition Scheme”

virtual techdays