Full Day Performance Tuning Pre-con

Hello Dear Potential Attendee! Welcome to your opportunity to learn all about SQL Server! Come join SQL MVP Jorge Segarra and SQL Server DBA Bradley Ball for a full day of Performance Tuning. There is a lot to cover and a lot to learn.

Introduction

Congratulations! You’ve got a Server – The first thing that you have to learn in Troubleshooting is how to ask the questions. We have two types of systems we will maintain. Those we see every day and those we have just discovered because of a problem thats been brought to our attention. There are questions we ask every day when we have the luxury of time and there are questions we ask quickly to figure out what’s going on. • Topics Covered: Baselines, Waits and Queues, Extended Events, Error Logs, DMV’s

Configuration & Best Practices

As Consultants a lot of the problems that we see are caused because the system has not been configured properly. There are things to configure for the Hardware, things to configure for the OS, things to configure AFTER you’ve installed SQL Server. We’ll discuss not just how you should set things, but why!

Topics Covered: Hardware (CPU, Memory, Physical Disk), Pre Configuration Checklist (Instant Database File Initialization, Service Account rights, OS Patching, Power Configuration), Post Configuration Checklist (Tempdb, Max Memory, Max DOP, Affinity, NUMA, Trace Flags).

Indexes, the Optimizer, and the Plan Cache

When it comes to making your queries run you need to understand how SQL Server reads a query and how the Optimizer works and return our physical data. Physical data is stored in structures such as tables and indexes, and understanding how to structure tables and indexes to work with our queries is critical. But what ties the Optimizer to the physical structures is the query plan. To find the query plan we’ll dive into the plan cache and cover query tuning from A to Z.

Topics Covered: Query Optimizer, Indexes, Page Splits, Plan Cache, Execution Plans and operators.

Locking, Blocking, Deadlocks, Latches, and Spinlocks

This topic sounds scary, but it’s not. Regardless of whether you have a small database with only a few users, or if you have a large multi-tenant system you have Locking, Blocking, Latches, and Spinlocks. Understanding what they are and how they work is the first step in understanding how to troubleshoot them. Deadlocks are a problem, but they are a problem we can fix. In this module we will explain and investigate how to find and fix the problems that we see in our database every day.

Topics Covered: Locking, Blocking, Deadlocks, Latches, and Spinlocks.