Home Remote DBA Service Consulting and Performance Server Health Checks Training Resources Calendar Past Customers What our Students say About SQLskills.com		IE1: Immersion Event on Internals and Performance   Overview This 5-day class is focused on the core fundamentals and foundation for SQL Server experts. While many folks are just looking for 'the answer', attendees of this class will learn how to derive the answer for themselves. The more you know about how SQL Server works the easier you will be able to respond to performance, design, and corruption problems - as well as solve them! In addition to internals, this class covers some of the most important elements of good design, indexing and isolation – items core to database scalability and performance. This class provides an excellent knowledge base for all other Immersion Events and the information within it should be considered a pre-requisite for the other courses (although attending this class is not a pre-requisite for attending the others). Please see our Immersion Events Schedule for class dates and our comprehensive Immersion Events FAQ for class costs and other frequently asked questions. Instructors: Paul S. Randal, Kimberly L. Tripp Module List Module 1: Database Structures The fundamental building block of knowledge is how SQL Server stores data on disk and this module will cover all structures in a database. Topics covered include: Records Pages Extents Allocation bitmaps Allocation units and IAM pages Using DBCC PAGE FILESTREAM Data Compression Module 2: Data File Internals and Maintenance Although this may seem like a simple topic, there are many things to consider around data file configuration and management, especially for tempdb. Topics covered include: Data file and filegroup layout Allocation algorithms Data file growth and shrinking Instant file initialization Tempdb Monitoring and troubleshooting Module 3: Transactions and Locking All operations performedd by SQL Server are done in the context of transactions. And all transactions involve locking to some degreee. This module will explain how transactions and locking work, plus explore blocking and deadlocks and the performance and application problems that can result. Topics covered include: Anatomy of a transaction Transaction madness Locking Lock escalation Blocking Deadlocks Monitoring and troubleshooting Module 4: Snapshot Isolation In many systems today, real-time analysis is required – often at the expense of OLTP activity. And, when significant OLTP activity is present, real-time analysis is prone to inconsistencies. After discussing locking and blocking, we will move to discussing statement-level and transaction-level inconsistencies, when they can occur, how to minimize/eliminate them using locking (at the expense of concurrency) and then how to use versioning to get the best of both worlds (concurrency and accuracy). But, at what expense? The primary focus of this module is to discuss when using these isolation levels is appropriate, what the possible trade-offs are, and what are the best practices for using versioning. Topics covered include: Mixed workload challenges Understanding isolation levels Controlling isolation levels Statement-level read consistency Transaction-level read consistency Module 5: Logging, Recovery, and Log File Internals and Maintenance Logging and recovery underpins SQL Server's ability to recover after a crash and all high-availability and disaster recovery technologies. It can also have a profound effect in the performance of a workload. This module will explain these topics in great depth. Topics covered include: Logging and log records Analyzing transaction log contents Checkpoints and crash recovery Transaction log logical and physical architecture Recovery models and their effects Log file maintenance Monitoring and troubleshooting Module 6: Table Design Strategies After having spent time on internals, we will apply them directly to best practices in table design. Topics covered include: Understanding row width (vertical partitioning) Character data, overflow and LOB LOB considerations Application inconsistencies in types Module 7: Index Internals Taking your design further, we will now consider our base table strategies in indexes. This is fundamentally one of the most misunderstood and improperly handled areas of SQL Server. This first module will explain the internals of both the clustered and nonclustered indexes and will be the foundation for the remainder of the class. Topics covered include: Index concepts Table structure Heaps Clustered indexes Nonclustered indexes ColumnStore overview Index internals case study Module 8: Index Fragmentation Even with the most carefully-designed indexing strategy, fragmentation is unavoidable. This module will explain the causes and effects of fragmentation, as well as the trade-offs between the various methods of removing it. Topics covered include: How the Storage Engine uses an index structure Forms of index fragmentations Causes and effects of index fragmentation Detecting index fragmentation Removing index fragmentation – REORGANIZE vs REBUILD Case study: GUIDs Module 9: Internals and Data Access Understanding the options that SQL Server has for accessing data from within indexes is critical to creating the RIGHT indexes. In this module we’ll continue our internals discussion with primary focus on how some of the special features (INCLUDE and filters) enhance your indexing options. Topics covered include: Data access patterns Covering Using INCLUDE (SQL Server 2005+) Using filters (SQL Server 2008+) Interval subsumption Module 10: Statistics Now that we know how SQL Server uses indexes and the different options available, how does SQL Server know which index to choose? Topics covered include: Cost-based optimization Data access patterns Statistics: What they look like What they are telling us How to see them When/how they get created When/how they get updated Problems/solutions with statistics Steps in histogram Filtered statistics Uneven distribution Module 11: Indexing Strategies What kinds of indexes are best to create? In what order should the columns be and should they be in the key or only in the leaf-level? This module focuses on a variety of real queries and then debates all of the options that should be considered and above all emphasizes consolidation. Topics covered include: Indexing for AND (highly-selective queries, index intersection, covering) Indexing for OR (tuning, re-writing) Indexing for joins (join types, which strategies use what types of indexes, 3 phases of tuning) Indexing for aggregates (hash aggregates, stream aggregates) Indexed views (overview/key points) Module 12: Partitioning Primarily we will discuss the use and benefits of vertical partitioning as well as horizontal partitioning. Topics covered include: Horizontal partitioning strategies Partitioned views Partitioned tables Implementing the sliding window scenario Partitioning design techniques combined Partition-aligned indexed views Optimizing sets Other features and partitioning Partitioning vs. filtering Questions? If you have any questions about the training classes that SQLskills.com provides, please don't hesitate to send us email!
Privacy Policy. All Rights Reserved. Copyright 2011 SQLskills.com