Product Description
You’re a developer or database administrator, and you’re curious.
You love learning about the internals of the tools you use. You’re comfortable writing queries and creating tables, but you don’t always understand why a particular index gets used, and another one does not. You’re not sure when SQL Server chooses to reuse an existing query plan, versus when it decides to create a new one.
You’ve been to a few 1-hour “how to tune indexes” or “how to tune queries” sessions at your local user group, SQLSaturdays, or online, and you’re ready to dig deeper. You’ve run common industry tools like sp_WhoIsActive, sp_BlitzIndex, and sp_BlitzCache, but now you want to learn where those tools get their data from.
You’re ready for SQL Server Internals 201.
This class includes extensive demonstrations that illustrate the details of SQL Server internals: metadata, physical storage, query processing, query plans, recompilation, plan cache management, and distribution statistics. This course is presented on SQL Server 2017, but the vast majority of content is relevant to earlier versions, too. To see the full list of modules, click the Modules and Agenda tab.
About the instructor: Kalen Delaney has been a Microsoft MVP for SQL Server since 1993. She is the author of several highly regarded books, including Microsoft SQL Server 2012 Internals, SQL Server Concurrency, SQL Server MVP Deep Dives, and SQL Server Internals of In-Memory OLTP.
This is an online class – Kalen hosts it in GoToWebinar, live on webcam, taking questions as she goes through the material. Audio can come through either your computer audio, or by dialing into a US phone number – headset recommended either way. For more information, check out the Training Logistics PDF for our online classes.
You can watch again later with Instant Replay – as soon as you register, you can stream the online recordings for a year. Watch early to get ready, then after your live class, brush up on stuff you missed or revisit your favorite topics. It’s the best of both worlds: live training with us, plus reminders.
This class is not scheduled for delivery, but you can contact Kalen Delaney to schedule a class at your company.
Module 1: Metadata
- What is metadata and what do you need to know about it?
- What are schemas?
- What kinds of metadata are available and how can you find out what’s out there?
- What’s the difference between compatibility views and catalog views?
- What’s the big deal about DMVs?
- What’s the secret backdoor into SQL Server?
- Where is the secret room where all the metadata definitions are stored?
Module 2: Physical Storage
How is your data organized in the database files?
What’s the difference between a heap and b-tree?
IAMs: How does SQL Server keep track of all your objects?
How can you actually see what’s stored on your database pages?
What’s the difference between clustered and nonclustered indexes?
How do indexes get fragmented and how bad can fragmented indexes be?
How do you know if your indexes are actually used?
Module 3: Query Processing and Query Plans
- How do you know what options to use for viewing your query plans?
- What is the difference between an estimated and an actual query plan?
- What is the real difference between a SEEK and a SCAN and why do you see SEEK when SQL Server is reading all the rows in the table?
- What is SQL Server really doing when the plan says LOOP JOIN? Is that always the best JOIN option?
- What is happening when SQL Server is doing a MERGE JOIN or a HASH JOIN?
- What’s the difference between STREAM and HASH Aggregation?
- When does SQL Server have to sort your data and where does that happen?
- What do you have to be aware of when SQL Server is modifying your data?
- What’s the difference between inserting into a heap and inserting into a table with a clustered index?
- What are ghost records and how can you see them? When do they go away?
- How is TRUNCATE TABLE different than deleting every row?
- When is an update performed in-place?
- What are the first things you should look for in the query plan for a poorly performing query?
Module 4: Recompilation and Plan Cache Management
- What happens during query processing?
- What does SQL Server’s optimizer actually do?
- What metadata is available to look inside SQL Server’s plan cache?
- How do you clear the cache?
- What are the different types of compiled plans?
- When is an ad hoc plan reused?
- What is a prepared plan? When and how is it reused?
- Why is parameter sniffing such a big deal?
- How can you control reuse of a stored procedure plan?
- When is a query plan automatically recompiled?
- What is threshold for updating statistics?
- How can you see XML plans and why are they useful?
- What information can you get by directly accessing the cache metadata?
Module 5: Distribution Statistics Internals
- What information is contained inside the statistics?
- What metadata is available for observing statistics details?
- What tools are available for you to manage the distribution statistics?
Lori Fry –
Kalen is a very good teacher and helped me understand the WHY of query processing, plans and indexes and then HOW to increase performance using what I learned. She gave us lots of useful tools/queries to see what the metadata looks like and the section on Plan Cache Management was eye opening. I’m using my new skill set to troubleshoot performance issues at work and sharing with my developers. I recommend this class to anyone who wants to learn more about performance tuning as a DBA or developer.