SQL Sat Pre-Con: SQL Server Internals from the practical angle by Dmitri Korotkevitch
Friday, March 1, 2013 from 8:30 AM to 5:00 PM (EST)
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.
Level 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.
1. Introduction and Session overview – 10 minutes
2. Tables and Indexes – 150 minutes
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, etc.) – 50 minutes
a. Internal implementation
b. Pros, cons and use-cases
4. General system design considerations – 15 minutes
a. Data Access Tier design
b. ORM frameworks in general
5. Locking, Blocking and Concurrency – 60 minutes
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 – 30 minutes
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 – 60 minutes
a. SQLOS & SQL Server Execution Model
b. Wait Statistics
c. Wait Types and performance counters overview and correlation with possible issues in the code
8. Workshop, Q&A and Open talk – 90 minutes
Audience 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.
Dmitri 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.
When & Where
Tampa Bay SQL Users Groups
These are two user groups interested in sharing information about Microsoft SQL Server and related topics.
The Pinellas User group meets at the Juvenile Welfare Board at 6:30pm on the third Tuesday of every month except December. The address is 14155 58th Street North, Clearwater, FL 33760.
The Hillsborough User group meet at Computer Associates at 6:30pm on the last Thursday of every month except December. The address is 10210 Highland Manor Dr, Suite 300, Tampa, FL 33610.
We look forward to seeing you there.