Query and Index Tuning

This seminar teaches you how to optimize problematic queries by tuning indexes and writing efficient code. The seminar covers in detail internal data structures and index access methods, which are the fundamental building blocks that you need to be familiar with in order to apply query tuning and optimization. It describes pages, extents, table organization (heap vs. B-tree), and Nonclustered indexes.

The seminar then gets into detailed discussion of access methods, describing both the performance and consistency aspects of the various methods. Part of this discussion you will learn what are the circumstances in which reads can end up returning the same row multiple times or skip rows, and how to avoid such behavior. You will learn what are the less commonly known implications of using the NOLOCK hint/Read Uncommitted isolation. You will also learn about covering indexes, included non-key columns and filtered indexes. 

After covering fundamental building blocks and access methods, the seminar explains how to put your knowledge into action by benchmarking various indexing strategies, analyzing their performance, and choosing the most ideal design.
The seminar then describes useful dynamic management objects to get information and statistics about your indexes and queries. You will also learn about the performance related differences between temporary tables and table variables.

The seminar gets into a discussion about cursor-based solutions vs. set-based ones. It explains how to get rid of cursors when set-based solutions are most appropriate, and how to identify the uncommon cases where cursors are the last resort that will yield better performance than set-based solutions.
Finally, the seminar demonstrates how to tune and optimize your solutions by applying query revisions that can yield improvements in orders of magnitude in some cases.

Outline:

• Internals and Index Tuning 
  • Table and Index Internals 
    • Pages and Extents 
    • Table Organization 
    • Heap 
    • B-tree 
    • Nonclustered Index on a Heap 
    • Nonclustered Index on a B-tree 
  • Index Access Methods 
    • Table Scan/Unordered Clustered Index Scan 
    • Unordered Covering Nonclustered Index Scan 
    • Ordered Clustered Index Scan 
    • Ordered Covering Nonclustered Index Scan 
    • Nonclustered Index Seek + Ordered Partial Scan + Lookups 
    • Unordered Nonclustered Index Scan + Lookups 
    • Clustered Index Seek + Ordered Partial Scan 
    • Covering Nonclustered Index Seek + Ordered Partial Scan 
  • Included Non-key Columns 
  • Filtered Indexes and Statistics 
  • Analysis of Indexing Strategies 
  • Index and Query Information and Statistics 
    • Fragmentation 
    • Usage Statistics 
    • Missing Indexes 
    • Query Statistics 
    • SQL text and plan info 
• Temporary Tables 
• Sets vs. Cursors 
• Query Tuning with Query Revisions