slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
DAT34 2 SQL Server Performance Series – Part 3 – Query and Index Tuning PowerPoint Presentation
Download Presentation
DAT34 2 SQL Server Performance Series – Part 3 – Query and Index Tuning

Loading in 2 Seconds...

play fullscreen
1 / 15

DAT34 2 SQL Server Performance Series – Part 3 – Query and Index Tuning - PowerPoint PPT Presentation


  • 163 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'DAT34 2 SQL Server Performance Series – Part 3 – Query and Index Tuning' - junius


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
dat34 2 sql server performance series part 3 query and index tuning

DAT342SQL Server Performance Series – Part 3– Query and Index Tuning

Maciej Pilecki

Consultant, SQL Server MVP

Project Botticelli Ltd.

maciej.pilecki@projectbotticelli.com

about me
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”
agenda for today
Agenda for today
  • Indexes in SQL 2008
  • Missing index DMVs
  • Database Tuning Advisor
  • Understanding Execution Plans
  • Query Optimization and Parameterization
indexes in sql 2008
Indexes in SQL 2008
  • Clustered
  • Non-clustered
  • XML
  • Spatial
  • Full-text
  • Coming up in Denali:
    • Columnstore indexes
missing index dmvs
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…
database engine tuning advisor
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
some fundamental questions
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?
execution plans statistics io and profiler
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…
query optimization
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...
controlling plan caching
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
summary
Summary
  • Indexes in SQL 2008
  • Missing index DMVs
  • Database Tuning Advisor
  • Understanding Execution Plans
  • Query Optimization and Parameterization
what s next
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
session evaluations

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.

slide15

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