SQL Server Internals from the practical angleDescription:There are plenty of the books and resources about SQL Server internals. While they provide the great overview of how product works, they often lack the practical angle and don’t explain how to apply that knowledge to real-life scenarios. That session helps to close that gap. It shows how various aspects of SQL Server Internals affect the systems and what needs to be taken into consideration during design and implementation stages.The session covers how SQL Server stores the data and works with indexes; how to design efficient indexing strategies; how different database objects types implemented internally and what are pros and cons of them; explains why do we have locking and blocking in the system and how to deal with concurrency issues and, finally, shows a few methods that can help with performance troubleshooting of the system. Target audienceLevel 200-300 (100-500 scale)Database Developers & Administrators who want to obtain better understanding of SQL Server Internals and looking for the practical advices rather than abstract knowledge. Plan1.       Introduction and Session overview – 10 minutes2.       Tables and Indexes – 150 minutesa.       Internal Structureb.      When and How SQL Server uses indexesc.       Index and Statistics maintenance d.      Indexing strategies and performance considerationse.      Index tuning3.       Database objects (Constraints, Views, Triggers, Functions, etc.) – 50 minutesa.       Internal implementationb.      Pros, cons and use-cases4.       General system design considerations – 15 minutesa.       Data Access Tier designb.      ORM frameworks in generalLunch Break5.       Locking, Blocking and Concurrency – 60 minutesa.       Lock types and compatibilityb.      Why we have locking and blocking in the systemc.       Locking and deadlocks troubleshootingd.      Designing transaction strategies6.       Query Execution – 30 minutesa.       Overview of Query life cycleb.      Compilation and recompilation, Plan Cache, Plan Reuse, Parameter sniffingc.       Ad-hoc and Dynamic SQL7.       Practical Troubleshooting – 60 minutesa.       SQLOS & SQL Server Execution Modelb.      Wait Statistics c.       Wait Types and performance counters overview and correlation with possible issues in the coded.      Useful DMVs8.       Workshop, Q&A and Open talk – 90 minutesAudience will be provided with the sample database and application that emulates OLTP activity against it. There will be a few problems involved, such as non-optimized queries and inefficient indexing; locking; inefficient DB schema; parameter sniffing and a few others. Audience would be asked to pin-point and fix as much problems as possible.  In addition to that, I’ll ask audience (during session overview and lunch break) about additional topics they would like to discuss. I’ll cover as much of them as possible during that time slot using either my other presentation decks or whiteboard.  BIODmitri Korotkevitch is Microsoft SQL Server MVP and Microsoft Certified Master who is working with Microsoft SQL Server for more than 11 years. He specializes in architecture, design, development and performance tuning of OLTP systems and responsible for implementation of several database backends handling thousands of transactions per second around the clock. Dmitri has Master’s degree in Computer Science and also held MCPD certificate. DBA Pre-Con 105.44