Tuning High
Performance
SQL Server Databases
From Design to Maintenance
3-Day Workshop
Presented by Kimberly L. Tripp
Taken from a series of popular shorter sessions and workshops – including sessions that have been top rated at conferences around the world – this three day Master Class combines materials to create a cohesive and interesting three day course. Fast-paced and packed full of information, Kimberly’s sessions are always interesting. In 2003 Kimberly was the top rated speaker at virtually every US event at which she lectured. In fact, at the US Microsoft Tech Ed event Kimberly delivered 5 sessions [out of 380] and all 5 were rated among the top 12 – including the number one spot! A common comment made is that Kimberly has the gift of being able to communicate technical materials and make subjects that would otherwise be dull – somewhat interesting. Kimberly’s looking forward to this new combination workshop which pulls top sessions, top recommendations and some of the most effective ways to improving performance – both quickly and effectively.
If you want to understand index internals, statistics, how the optimizer chooses indexes, strategies for index coverage, procedure plans, caching and recompilation, design techniques to improve cache utilization and minimize lock contention and just want a better understanding of the SQL Server environment…this exclusive master class is the place to be!
Here’s the comprehensive agenda:
Overview
The Goals in Achieving Optimal Performance
Methodology
l Where can performance be improved?
l Our Focus – SQL Server
Ø Designing for Performance
Ø Known Problems
Ø Finding the Unknown Problems
§ Long Running Queries
§ Frequently Executed Queries
l Tools for “fishing”
l Keeping the performance optimal
l Where to go for more information!
Designing for Performance
l Database Structure
Ø The Anatomy of a Data Modification (re: db structures)
Ø Data v. Log
Ø Transactions and Logging
Ø Understanding Autogrowth
Ø Minimizing VLFs
Ø Filegroups
l Design Techniques
Ø Table Design
Ø Partitioning
Ø Constraints
Ø Redundant Keys
l Indexing for Performance
Index Internals
l Index Concepts
l Table Structure
l Index Internals
Ø Heaps
Ø Why Cluster
Ø Table Usage
Ø Employee Table Case Study
l Why Cover Internals?
l Key Points…
Statistics
l Query Specific Index Usage – How?
l Data Access Patterns
l Statistics
Ø What do they look like?
Ø What are they telling us?
l Statistics and Interpolation
l Statistics
Ø How do you see them
Ø When/how do they get created
Ø When/how do they get updated
l Interpolation – When it goes wrong
l Even better… Indexes!
Indexing for Performance – Part I
l Selectivity
l How to Improve Queries with Varying Search Arguments (SARGs)
Ø Indexing for AND
Ø Indexing for OR
l How to Improve Joins
Indexing for Performance – Part II
l Indexing for Aggregations
Ø HASH Aggregations
Ø Stream Aggregations
l Views & Indexes
l Indexed Views
Ø Defined
§ Indexed View with Joins
§ Indexed View with Computations
§ Indexed View with Aggregations
Ø Another Option
Ø Power of Indexed Views
l Creation
l Maintenance
Index Maintenance
l How Fragmentation Occurs
l What Fragmentation Means
l How to See Fragmentation
l How to Minimize Fragmentation
l Rebuilding an Index
Ø Why
Ø When
Ø How
l Automating Index Rebuilds
Optimizing Procedural Code
Ø Resolution
Ø Compilation/Optimization
Ø Execution/Recompilation
l Recompilation Issues
Ø When do you want to Recompile?
Ø Options for Recompilation?
Ø What to Recompile?
l Stored Procedure Best Practices
Ø Naming Conventions
Ø Avoiding Excessive Recompiles
Ø Writing Solid Code
Ø Detecting Excessive Recompiles
Transactions – Locks, Logging, Locking and Blocking
l Transactions
Ø Nesting
Ø The Anatomy of a Data Modification (re: locking)
l Lock Granularity/Escalation
l Lock Compatibility
l Lock Isolation Levels
Ø Types
Ø Concerns (re: Lock Duration v. Consistency)
l Lock Hints
Ø Query Level
Ø Session Level
l Locking
Ø Livelocks
Ø Deadlocks
l Minimizing Blocking
l Investigating Locks/Locking
Wrap-up