1 / 15

DAT34 2 SQL Server Performance Series – Part 3 – Query and Index Tuning

DAT34 2 SQL Server Performance Series – Part 3 – Query and Index Tuning. Maciej Pilecki Consultant, SQL Server MVP Project Botticelli Ltd. maciej.pilecki@projectbotticelli.com. About me. Microsoft Certified Trainer since 2001 SQL Server MVP since Jan 2006

junius
Download Presentation

DAT34 2 SQL Server Performance Series – Part 3 – Query and Index 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. DAT342SQL Server Performance Series – Part 3– Query and Index Tuning Maciej Pilecki Consultant, SQL Server MVP Project Botticelli Ltd. maciej.pilecki@projectbotticelli.com

  2. About me • Microsoft Certified Trainer since 2001 • SQL Server MVP since Jan 2006 • Specializing in SQL Server database development and administration • Delivering training and consulting services around the world • Frequent speaker at many international conferences and UG meetings • „Dr. House of SQL”

  3. Agenda for today • Indexes in SQL 2008 • Missing index DMVs • Database Tuning Advisor • Understanding Execution Plans • Query Optimization and Parameterization

  4. Indexes in SQL 2008 • Clustered • Non-clustered • XML • Spatial • Full-text • Coming up in Denali: • Columnstore indexes

  5. Missing index DMVs • Query optimizer can suggest an index • Stores the „missing” index information in: • sys.dm_db_missing_index_details • sys.dm_db_missing_index_groups • sys.dm_db_missing_index_group_stats • Mind the limitations: • Single query only (no info on the cost of the index!) • No exact information on the order of the columns • Only last 500 missing indexes stored • See BOL for more…

  6. Database Engine Tuning Advisor • Full-blown tuning tool • Can analyze workload from: • Scripts • Profiler trace (table or file) • Can recommend: • Indexes • Indexed Views • Partitioning • Can run against test server • Can estimate data growth impact

  7. Some fundamental questions • What should be your clustered index? • It’s your PK by default • But is that right? • Think about what kind of queries are being executed against your table • Do you do range scans against your PK? • Is there a better clustering key? • How many clustered indexes can you have? • One is the correct answer, but… • What if I create non-clustered index and include all columns? • How is it different than the clustered index?

  8. Execution Plans, STATISTICS IO and Profiler • Execution plan is nice to understand what your query does • But not what is the cost of it • It’s an ESTIMATED cost that shows up in the Execution Plan • NEVER use Execution Plan alone in query tuning! • STATISTICS IO is better but… • That will also miss some info sometimes • Profiler is you only true friend – it never lies…

  9. Query Optimization • Query Optimization is good - improves query performance • But: • It is expensive (memory, CPU, time) • It is throttled at the server level (see Optimization section in KB 907877) • It can timeout • We need plan caching...

  10. Controlling plan caching • Application-side parameterization • Stored Procedures • Forced parameterization • Database-level option • More aggressive in parameterizing ad-hoc SQL • Optimize for ad hoc workloads • New server option in SQL Server 2008 • Only a stub is cached on first execution • Full plan cached after second execution

  11. Summary • Indexes in SQL 2008 • Missing index DMVs • Database Tuning Advisor • Understanding Execution Plans • Query Optimization and Parameterization

  12. What’s next • Follow-up: Meet me at TLC with your questions today from 12:00 – 14:00 – SQL Server Mission Critical stand • Or email me at maciej@projectbotticelli.com

  13. Session Evaluations Tell us what you think, and you could win! All evaluations submitted are automatically entered into a daily prize draw*  Sign-in to the Schedule Builder at http://europe.msteched.com/topic/list/ * Details of prize draw rules can be obtained from the Information Desk.

  14. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related