SQLSaturday Kiev 2015 PreCon: SQL Server Internals from the practical angle

There are plenty of the books and resources with the information about SQL Server. They provide the great overview of how product work; however, they often lack the practical angle and do not explain how to apply that knowledge to real-life scenarios. That pre-con helps to close that gap. It shows how different SQL Server components work under the hood, how they communicate with each other and, most importantly, how it affects you and your systems.

The pre-con is based on “Pro SQL Server Internals” book (http://www.amazon.com/dp/1430259620). It shows the large number of demos and discusses various topics, including the following:

1.How SQL Server stores the data and works with indexes.

2.Internal implementation of the various database objects and when they should or should not be used.

3.SQL Server Concurrency Model and concurrency issues troubleshooting.

4.SQL Server Query Execution and most common execution plan caching related issues.

5.Performance troubleshooting

Target audience

Level 250-300 (100-500 scale)

Plan

1.Introduction and Session overview

2.Tables and Indexes

a.Internal Structure

b.When and How SQL Server uses indexes

c.Index and Statistics maintenance

d.Indexing strategies and performance considerations

e.Index tuning

3.Database objects (Constraints, Views, Triggers, Functions, CLR, etc.)

a.Internal implementation

b.Pros, cons and use-cases

4.General system design considerations (Slides will be provided but part is not delivered in 1-day course)

a.Data Access Tier design

b.ORM frameworks in general

5.Locking, Blocking and Concurrency

a.Lock types and compatibility

b.Why we have locking and blocking in the system

c.Locking and deadlocks troubleshooting

d.Designing transaction strategies

6.Query Execution

a.Overview of Query life cycle

b.Compilation and recompilation, Plan Cache, Plan Reuse, Parameter sniffing

c.Ad-hoc and Dynamic SQL

7.Practical Troubleshooting

a.SQLOS & SQL Server Execution Model

b.Wait Statistics

c.Wait Types and performance counters overview and correlation with possible issues in the code

d.Useful DMVs

Speaker: Dmitri Korotkevitch

Dmitri is a Microsoft SQL Server MVP, Microsoft Certified Master (SQL Server 2008) and author of “Pro SQL Server Internals” (http://www.amazon.com/dp/1430259620) with 20 years of IT experience including over 14 years of experience working with Microsoft SQL Server as an Application and Database Developer, Database Administrator, and Database Architect.

Dmitri regularly speaks at various events, including PASS Summit, 24HOP, SQL Rally and Microsoft TechEd. He specializes in the design, development, and performance tuning of complex OLTP systems that handle thousands of transactions per second around the clock.