Indexes from Every Angle - Internals, Statistics, Performance, Maintenance
There is no other area within SQL Server where you can get better performance gains than with indexes. Yet almost ironically, this is also the most misunderstood and improperly maintained area of database design/tuning. In this fast paced full day seminar, we will look at the internal structures of indexes, how the optimizer chooses indexes (i.e. statistics) and what types of queries benefit from which types of indexes. Focus will be placed on how indexes are used for seeks and scans as well as how to best index for SARGs, joins and aggregations. Indexes are created with the intention of improving performance but if left unmanaged and unmaintained, they will lose their effectiveness over time. To close this session we will cover how to see fragmentation - manually and programmatically as well as how to appropriately rebuild and automate index maintenance. Finally, if you think your knowledge will be lost when SQL Server 2005 arrives you'll be pleasantly surprised. Most of the principles we discuss are true for both SQL Server 2000 and SQL Server 2005 yet where there are differences, they will be discussed!
If you want to understand index internals, statistics, how the optimizer chooses indexes, strategies for index coverage, and optimization plans - and just want a better understanding of the SQL Server environment...this fast-paced and rare event is the place to be!
M1: Index Internals
· Index Concepts
· Table Structure
· Index Internals
o Heaps
o Why Cluster
o Table Usage
o Employee Table Case Study
· Why Cover Internals?
· Key Points…
M2: Statistics
· Query Specific Index Usage – How?
· Data Access Patterns
· Statistics
o What do they look like?
o What are they telling us?
· Statistics and Interpolation
· Statistics
o How do you see them
o When/how do they get created
o When/how do they get updated
· Interpolation – When it goes wrong
· Even better… Indexes!
M3: SARGs and Joins
· Selectivity
· How to Improve Queries with Varying Search Arguments (SARGs)
o Indexing for AND
o Indexing for OR
· How to Improve Joins
o Phase I
o Phase II
o Phase III
M4: Aggregations and Indexed Views
· Indexing for Aggregations
· Views & Indexes
· Indexed Views
o Defined
o Power of Indexed Views
· Creation and Implementation
· Requirements
M5: Index Maintenance
· How Fragmentation Occurs
· What Fragmentation Means
· How to See Fragmentation
· How to Minimize Fragmentation
· Rebuilding an Index
o Why
o When
o How
· Automating Index Rebuilds
· Understanding Index Usage
Registration 9.00 Bacon/egg/sausage butties, Tea and Coffee
Seminar Start 9.30
Break 1 11.00 - 11.20 Muffins, Tea and Coffee
Lunch 12.45 - 13.30 Picnic bag lunch (sandwich, crisps, fruit, chocolate bar and can of drink)
Break 2 14.45 - 15.05 Cookies, Tea and Coffee
Q & A 16.45
Seminar End 17.00
To book a place fill out our registration form or call Tony Rogerson on 01582 768450 or email him at tonyrogerson@torver.net. There are discounts for block bookings.