SQL Server Internals for Performance

We all know that the answer to almost any SQL Server tuning question is “It Depends!”

In this seminar, I’ll tell you what you need to know in order to figure out what it actually depends on! How can you answer these “It Depends” questions in your own environment? We’ll look at what kinds of tuning changes can be made to enhance your system or application performance, and what areas of SQL Server Internals you need to understand to make the best choice for you when trying to answer the following questions:

• Should we build a clustered, nonclustered or columnstore index?

We’ll compare the structure of these different kinds of indexes and looks and the best practices for using each type.

• Is reusing cached query plans a good thing?

We’ll look at the different ways SQL Server can cache and reuse plans and discuss how to tell when reuse is desirable. We’ll explore how to encourage SQL Server to recompile a plan when that that is the best option.

• Should Read Committed Snapshot Isolation be enabled?

We’ll explore what exactly happens internals when RCSI is enabled and that the uses cases are for this option. We’ll also talk about situations in which you might not want to use this.

• How many files should tempdb be configured with?

To understand why tempdb has concurrency considerations that aren’t applicable to other databases, we need to understand some of SQL Server’s internal allocation algorithms. We’ll look at how pages are allocated for temp tables and what changed in SQL Server 2016. With this information, we’ll be able to consider the best practices recommendations and determine what is best in your environment.

If time allows, their might be a few other questions we could discuss!

About Kalen:

Kalen Delaney has been working with SQL Server for over 30 years, and provides advanced SQL Server training to clients around the world. She has been a SQL Server MVP since 1993 and has been writing about SQL Server almost as long. Kalen has spoken at dozens of technical conferences, including every PASS conference in the US and multiple TechEd events in both the US and Europe. Kalen is the author or co-author of many books on SQL Server, the most recent being SQL Server 2016 Internals: In-memory OLTP, from Red Gate.