SQL Server. Оптимизация и Анализ производительности

Оптимизация запросов - один из самых интересных и самых сложных аспектов работы с SQL Server. Спроектировать систему, которая будет работать идеально - невозможно, мы все это знаем. Поэтому, чаще всего, фазы оптимизации чередуются с фазами разработки. В этом случае, как правило, есть какая-то нагрузка, в контексте которой можно проводить анализ и оптимизацию. В этом докладе мы на один час попадём в такую фазу Оптимизации, и пройдём путь от определения узких мест к оптимизации конкретных запросов. Минимум теории и много практики. Будем мониторить базу данных под нагрузкой, определять узкие места, анализируюя нагрузку, смотреть планы запросов, которые влияют на производительность системы, и оптимизировать их.

Докладчик: Денис Резник

[MisterGenix]

Это лучшее, что я посмотрел на ютубе за последний год! Спасибо

[wakeuphuman-]

Большое спасибо Денису! Приятно слушать специалиста.

[Das.Kleine.Krokodil]

Настройка Perfomance monitor
00:07:22 Sample interval (примерно 5 сек)
00:08:34 Processor
00:09:31 System / Processor Queue Length (желательно без длительного пика)
00:10:14 Memory / Avalible MBytes (желательно > 100-500)
00:10:58 Paging file (желательно = 0)
00:11:26 PhysicalDisk/
- Avg. Disk sec/Read (желательно < 100 мс)
- Avg. Disk sec/Write (желательно < 100 мс)
- Disk Reads/sec (наблюдаем за трендами)
- Disk Writes/sec (наблюдаем за трендами)
00:12:47 SQLServer:Buffer Manager / Page life expectancy (желательно > 5 мин или чем > тем лучше)
00:15:12 SQLServer:General Statistics / User connections
00:15:39 SQLServer:Memory Manager /
Memory Grants Pending (желательно = 0)
Target Server Memory (смотрим чтобы Target была > Total)
Total Server Memory
00:16:56 SQLServer:SQL Statistics /
- Batch requests/sec (наблюдаем за трендами)
- SQL Compilations/sec (соотносим с нагрузкой на процессор)
- SQL Re-compilations/sec (соотносим с нагрузкой на процессор)

[Das.Kleine.Krokodil]

Теория
00:27:24 Немного про анализ данных, про сведение данных Perfomance monitor и профайлера
00:29:10 Поиск в куче
00:29:26 Поиск в кластерном индексе: поиск, сканирование, сканирование диапазона.
00:31:52 Статистика

[Das.Kleine.Krokodil]

Настройка профайлера
00:19:57 Настройка профайлера на основе шаблона Tuning: добавить столбцы Start time, End time, CPU, Reads, Row count, Writes.

[Das.Kleine.Krokodil]

01:00:13 Итоги выступления
01:01:10 Рекомендуемые книги и сайты

[Das.Kleine.Krokodil]

Сбор данных
00:23:44 Запуск рабочей нагрузки и сбора данных
00:25:46 Остановка сбора данных. Сохранение трассы профайлера.
00:26:14 "Собирать трассу и счетчики можно по сети с другого сервера"

[Das.Kleine.Krokodil]

Сведение данных Perfomance monitor и профайлера
00:33:35 Загрузка данных Perfomance monitor в профайлер

[Das.Kleine.Krokodil]

Анализ длительного запроса
00:34:46 Анализ корреляции данных профайлера и Perfomance monitor
00:36:12 Нагружен процессор и большое количество чтений
00:37:18 Выполнили этот запрос в Management studio - выполнился быстро, мало чтений
00:38:06 Выполнили этот запрос опять в приложении - опять выполнился медленно, много чтений
00:38:47 Выяснение причин разных планов запроса.
00:40:25 Причина: Parameter sniffing
00:44:14 Причина: SET ARITHABORT
00:47:16 Выявлена нагрузка на процессор из за компиляции запросов
00:48:27 Анализ тяжелых запросов
00:50:10 Большое различие между _Actual number of Rows_ и _Estimated number of Rows_ - признак невалидной устаревшей статистики
00:55:52 Анализ различных показателей из Perfomance monitor.
00:58:10 О счетчиках ожиданий
00:59:12 Почему динамический SQL-запрос это плохо, а параметры хорошо.

[Das.Kleine.Krokodil]

00:00:00 План выступления
00:02:24 Работа MS SQL: процессор, память, диск. Процессы