This demo-oriented a day-long session will revolve around a story line of an organization where various performance tuning issues shows up. We will assume a role of newbie and discussion 21 essential scripts which can come handy while working with SQL Server performance issues.
The basic philosophy behind this session is that every day should have a personal favorite script toolbox which they can use it when they face adverse situations. We will go over about various details about how to build such a scripts which are database server independent.
A very common workflow for fixing any performance problem is in two steps:
Part 1: Run Diagnostic Queries
Part 2: Run Problem Resolving Queries
We will take the above workflow and play a role of new employee in the organization. This day-long session will be divided in these two major sections
1) How to Solve Common Performance Issues - Reactively?
a) Resource Bottlenecks – IO, CPU, Memory
b) TempDB and Contention
c) Identifying Slow Running Queries
d) Indexing Issues and Quick Fixes
e) Wait Stats and Queries
f) Statistics and Execution Plans
g) Live Plans *
2) How to Solve Common Performance Issues – Proactively?
a) Index Strategies *
b) Database File Deployments
c) Resource Governor and Alerts
d) In-Memory OLTP Optimization *
e) Query Store *
f) Temporal Tables *
g) Azure Enhancements *
The list item with * will discuss SQL Server 2017/2019 concepts.
We will end the session with a hands-on exercise assigned to attendees.
The session will have a unique role-playing style of presentation. We will have a big story arc with two sub story lines.
