Практический кейс настройки производительности MS SQL Server

Практический кейс настройки производительности MS SQL Server

Полный курс по настройке и оптимизации производительности от Пинал Дэйва

Я предлагаю очень популярную услугу комплексной проверки работоспособности базы данных. В то время как я помогаю организациям улучшить их работу, я лично получаю знания. Я в значительной степени видел все возможные ошибки при работе с SQL Server. Я видел администраторов баз данных, которые настраивали свою систему таким образом, что они мгновенно снижали производительность сервера. Я также встречал разработчиков SQL, которые пишут сценарии, которые могут отключить всю систему всего за несколько секунд.

Лично я не люблю называть это тренингом, я думаю, что тренировка - это не то слово, которое мы делаем. Я думаю, что это больше похоже на обмен бизнес-секретами. На этом опыте я объясню и поделюсь всеми хитростями, сценариями и секретами, которые я практикую для своих клиентов в моем бизнесе.

Этот семинар основан на моем многолетнем опыте работы с более чем 400 клиентами, чтобы помочь им создать надежную систему, которая работает очень быстро.

Курс состоит из 4 модулей:
Модуль 1: Индексация, худшие практики и решения
Модуль 2: WhichIndex - интерактивная игра с предложением WHERE и многоколоночными индексами
Модуль 3: Не меняйте код - интерактивная игра с функциями, представлениями и индексами
Модуль 4: Ваш сервер, ваши вопросы

Складчина курс в формате видеозаписи курса с предоставлением скриптов и пояснений.

Язык курса: английский

Спойлер: Оригинальное описание

What Makes this Training Workshop Unique?

I offer a very popular Comprehensive Database Performance Health Check service. While I help organizations to improve their performance, I personally gain knowledge as well. I have pretty much seen every possible error while working with SQL Server. I have seen DBAs configuring their system such a way that they will instantly kill server’s performance. I have also come across SQL Developers who write scripts that can shut an entire system down in just a few seconds.

I personally do not like to call it training, I think training is not the right word for what we do. I think it is more like Sharing Business Secrets. In this experience, I will explain and share all the tricks, scripts and secrets that I practice for my customers in my business.

This workshop is crafted from my many years of experience working with over 400 customers to help them build a robust system that runs extremely fast.

What is Covered in this Training?
This is extremely sharp, fast-paced 3–4 hours training, where we learn various real-world performance troubleshooting scenarios and their resolutions.

The training is divided into three major parts.

Module 1: Indexing, Worst Practices, and Solutions (70 minutes)

Indexes are often considered a silver bullet to solve performance problems, but the reality is far from the age-old myth. The right index can help improve performance, but the wrong indexes often play party spoiler and reduce your server’s performance. Identifying which indexes to create and which indexes to delete can be cumbersome (or near impossible) if we do not know how to do workload analysis.

In this module of the workshop, we will see some neat tricks related to how to create useful indexes which last longer. We will also build a preventive auto-healing system which can help you focus on more complicated performance issues.

Comfort break – 15 minutes

Module 2: WhichIndex – An Interactive Game with WHERE clause and Multi-Column Indexes (70 minutes)

WhichIndex is the interactive game that gives a new experience to teach you how to understand how multi-column indexes work with queries with multiple conditions.

This game follows the format of open book exams where every user begins with answers to the questions. Once we understand our solutions, we start our journey to this interactive game. At a different interval of this game, users have to answer the question WhichIndex to the quiz master.

In this self-scored game, the winner is the person who gets the maximum answers wrong. If you have not understood the basics of indexes and performance tuning concept, you will solidify that concept while we play this game.

On a side note – cheating is not only allowed but is heavily encouraged as well!

At the end of this game, users will have an understanding of three essential rules related to Indexes, which YOU will never forget.

Comfort break – 15 minutes

Module 3: Don’t Change Code – An Interactive Game with Functions, Views, and Indexes (50 minutes)

Any good singer needs the support of the decent sound system and cooperative band to be successful. Similarly for any index to be successful, it requires a supportive environment. It is incredibly critical to learn how indexes selection takes shape when functions are used in the query.

We start with a problem where there is a query and an index. The query is not using the index. The challenge which we have to solve in this game is to make the necessary modification, so our query uses our index. However, there is one condition – Don’t Change Code. We are not allowed to change the query.

While I struggle to solve this puzzle, your responsibility will be to help me out with various ideas and also help me honor the one condition of the game – Don’t Change Code.

The game is indeed a very fast paced but at every twist and turns we will take a few moments to understand the secrets of indexes.

Comfort break – 15 minutes

Module 4: Your Server, Your Questions

In the final part, we usually discuss the performance problems you are facing with your server. If you do not have additional questions, we usually spend the rest of the time looking at various real-world scenarios related to SQL Server Query Execution Plans and Performance. We often spend time discussing following in this module.

• Server Configurations for Performance
• Instance Configuration for Performance
• SQL Server DBCC Best Practices
• Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs)
• Hardware Discussions
• Index Analysis and Optimization
• I/O Distribution Analysis
• Effective Query Writing
• SQL Server Resource Wait Stats Analysis
• SQL Server Best Practices

We will learn together, how to solve real-world performance problems systematically and scientifically.