1 / 16

Mastering SQL Server 2008 Query Tuning: Strategies for Performance Enhancement

In this presentation delivered at TechDays 2011 in India, Praveen Srivatsa, a Principal SME at StudyDesk91 and Microsoft Regional Director, shares expert insights on SQL Server 2008 query tuning. The session covers essential concepts such as histogram analysis, index optimization, and troubleshooting common performance issues like excessive scanning and poor index utilization. Attendees will learn about effective strategies for optimizing queries, including the careful use of search predicates and understanding different join algorithms to enhance database performance.

kali
Télécharger la présentation

Mastering SQL Server 2008 Query Tuning: Strategies for Performance Enhancement

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. 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

  2. Query Architecture

  3. 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?

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

  5. 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)

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

  7. 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

  8. 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

  9. Understanding Joins OuterTable InnerTable JoinMethod

  10. 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

  11. 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

  12. 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

  13. 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

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

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

  16. 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”

More Related