SQL Server Performance Tuning Real-World Scenarios

2020

TL; DR

In this SQL Server Performance Tuning Pre-Con, we will go through various real-world performance tuning challenges I have experienced during my last 10 years of Consulting Engagements and how I overcome each of them.

Session Details

I have been working as SQL Server Performance Tuning Consultant for over the last 10 years and I have seen many different complicated problems while helping my clients. I have been maintaining a repository of the problems and their solutions to me. In this year’s pre-con, I am going to open my business secrets to everyone. We will see various complicated real-world problems that you see in the real world and their resolutions.

Before the beginning of the session, we will see the show list content of the pre-con to everyone. Every attendee will get the opportunity to vote on the topic they want to see and we will build our learning path together. Once we have a learning path build, we will immediately jump on each topic and learn them together.

Here are the major topics which we will cover in the Pre-Con.

All demonstrations will use SQL Server 2019 version

Accidently slowing down database servers
Randomly slow running queries
Frequently restart fixing the SQL Server performance
Identifying workload patterns causing resource bottlenecks

CPU
Memory
IO

Slow performing Views and Stored Procedures
Index strategies

Index slowing down SELECTs
Index slowing down INSERT, UPDATE, DELETES
Query skipping the best-matched Index
Irrelevance of Fragmentations for Indexes
Cardinality and Index Selection

SQL Wait Stats

CXPACKET
WRITELOG
RESOURCE_SEMAPHORE
… many more

Locking and Troubleshooting
Analyzing SQL Server Execution Plans

Extended Events
Query Store
DMVs

SQL Server 2017 and SQL Server 2019 Enhancements

We will exclusively focus on the real-world scenarios and also, include a few demonstrations which you face every day. This is fasted faced 100% demonstration-oriented pre-conference.