SQL Server. Наладка и оптимизация для профессионалов

Предисловие....17

Для кого эта книга....18

Структура книги....18

Условные обозначения....20

Использование исходного кода примеров....21

Как связаться с автором....22

Благодарности....22

От издательства....23

Глава 1. Установка и настройка SQL Server....24

Аппаратное обеспечение и операционная система....24

Центральный процессор....25

Оперативная память....25

Дисковая подсистема....26

Сеть....27

Операционные системы и приложения....28

Виртуализация и облачные технологии....29

Настройка SQL-сервера....29

Версия SQL Server и уровень обновления....30

Мгновенная инициализация файлов....30

Настройка базы tempdb....32

Флаги трассировки....33

Параметры сервера....35

Настройка баз данных....38

Настройки базы данных....39

Настройки журнала транзакций....40

Файлы данных и файловые группы....41

Анализ журнала ошибок SQL Server....43

Консолидация экземпляров и баз данных....46

Эффект наблюдателя....47

Резюме....50

Чек-лист устранения неполадок....50

Глава 2. Модель выполнения SQL Server и статистика ожидания....51

SQL Server: высокоуровневая архитектура....51

SQLOS и модель выполнения....53

Статистика ожидания....56

Динамические административные представления, связанные с моделью выполнения....61

sys.dm_os_wait_stats....61

sys.dm_exec_session_wait_stats....62

sys.dm_os_waiting_tasks....63

sys.dm_exec_requests....64

sys.dm_os_schedulers....66

Обзор регулятора ресурсов....67

Резюме....69

Чек-лист устранения неполадок....69

Глава 3. Производительность дисковой подсистемы....70

Как устроена подсистема ввода/вывода SQL Server....70

Планирование и ввод/вывод....71

Чтение данных....73

Запись данных....75

Подсистема хранения: целостный обзор....76

Представление sys.dm_io_virtual_file_stats....77

Счетчики производительности и метрики ОС....81

Виртуализация, хост-адаптер шины и уровни хранения....86

Настройка контрольных точек....87

Ожидания ввода/вывода....91

Ожидание ASYNC_IO_COMPLETION ....91

Ожидание IO_COMPLETION ....91

Ожидание WRITELOG ....92

Ожидание WRITE_COMPLETION ....92

Ожидания PAGEIOLATCH....92

Резюме....94

Чек-лист устранения неполадок....94

Глава 4. Неэффективные запросы....95

Чем плохи неэффективные запросы....95

Статистика выполнения на основе кэша планов....96

Расширенные события и трассировки SQL....109

Хранилище запросов....115

Отчеты хранилища запросов в SSMS ....119

Работа с динамическими административными представлениями хранилища запросов....122

Сторонние инструменты....128

Резюме....129

Чек-лист устранения неполадок....130

Глава 5. Хранение данных и настройка запросов....131

Хранение данных и схемы доступа....131

Таблицы на основе строк....132

Индексы на основе B-деревьев....135

Составные индексы....140

Некластеризованные индексы....141

Фрагментация индекса....146

Статистика и оценка количества элементов....150

Как вести статистику....153

Модели оценки количества элементов....155

Анализ плана выполнения....157

Построчный и пакетный режим выполнения....157

Динамическая статистика запросов и профилирование статистики выполнения....160

Характерные проблемы при настройке запросов....164

Неэффективный код....164

Неэффективный поиск по индексу....168

Неправильный тип соединения....171

Избыточный поиск по ключу....180

Индексирование данных....182

Резюме....185

Чек-лист устранения неполадок....186

Глава 6. Загрузка процессора....188

Неоптимизированные запросы и код T-SQL....188

Неэффективный код T-SQL....189

Сценарии для контроля за загрузкой ЦП....190

Шаблоны неоптимизированных запросов....192

Компиляция запросов и кэширование планов....193

Планы, чувствительные к параметрам....194

Независимость от значений параметров....200

Компиляция и параметризация....203

Автоматическая параметризация....205

Простая параметризация....206

Принудительная параметризация....207

Параллелизм....210

Резюме....212

Чек-лист устранения неполадок....213

Глава 7. Проблемы с оперативной памятью....214

Использование и конфигурация памяти в SQL Server....214

Настройка памяти SQL Server....217

Сколько нужно памяти?....219

Выделение памяти....219

Клерки памяти....222

Команда DBCC MEMORYSTATUS....232

Выполнение запросов и предоставление памяти....232

Оптимизация запросов, интенсивно использующих память....236

Обратная связь по предоставлению памяти....242

Управление объемом предоставленной памяти....243

In-Memory OLTP и устранение неполадок....244

Резюме....248

Чек-лист устранения неполадок....248

Глава 8. Блокировки и конкурентный доступ....249

Типы блокировок и их поведение....250

Основные типы блокировок....251

Совместимость блокировок....255

Уровни изоляции транзакций и поведение блокировок....256

Проблемы блокирования....260

Устранение неполадок блокирования в реальном времени....261

Работа с отчетом о заблокированном процессе....266

Уведомления о событиях и Blocking Monitoring Framework....270

Взаимные блокировки (Deadlocks)....271

Устранение взаимных блокировок....272

Блокировки и индексы....276

Оптимистичные уровни изоляции....277

Уровень изоляции READ COMMITTED SNAPSHOT ....280

Уровень изоляции SNAPSHOT....281

Блокировки схемы....282

Укрупнение блокировок....284

Устранение неполадок с укрупнением блокировок....287

Ожидания, связанные с блокировками....290

Тип ожидания LCK_M_U ....290

Тип ожидания LCK_M_S ....291

Тип ожидания LCK_M_X ....291

Типы ожидания LCK_M_SCH_S и LCK_M_SCH_M ....292

Типы ожидания интентной блокировки LCK_M_I* ....293

Типы ожидания блокировки диапазона LCK_M_R* ....293

Резюме....294

Чек-лист устранения неполадок....295

Глава 9. Работа с базой данных tempdb и ее производительность....296

Временные объекты в tempdb....296

Временные таблицы и табличные переменные....297

Кэширование временных объектов....303

Параметры с табличным значением....306

Обычные таблицы в базе данных tempdb и протоколирование транзакций....307

Внутренние компоненты, использующие tempdb....309

Хранилище версий....309

Переносы (spills)....312

Распространенные проблемы с tempdb....315

Состязания за доступ к системным страницам....318

Нехватка места....322

Конфигурация базы данных tempdb....325

Резюме....326

Чек-лист устранения неполадок....327

Глава 10. Кратковременные блокировки....328

Введение в кратковременные блокировки....328

Кратковременные блокировки страниц....331

Решение проблем с горячими точками: параметр индекса OPTIMIZE_FOR_SEQUENTIAL_KEY....334

Решение проблем с горячими точками: хеш-разбиение....336

Решение проблем с горячими точками: In-Memory OLTP....338

Другие типы кратковременных блокировок....339

Резюме....342

Чек-лист устранения неполадок....343

Глава 11. Журнал транзакций....344

Внутреннее устройство журнала транзакций....344

Модификация данных и протоколирование транзакций....345

Явные и автоматически фиксируемые транзакции и накладные расходы журнала....350

Отложенная устойчивость....353

Протоколирование транзакций In-Memory OLTP ....354

Виртуальные файлы журналов....355

Конфигурация журнала транзакций....358

Проблемы с усечением журнала....359

Ожидание повторного использования журнала LOG_BACKUP ....361

Ожидание повторного использования журнала ACTIVE_TRANSACTION ....362

Ожидание повторного использования журнала AVAILABILITY_REPLICA ....363

Ожидание повторного использования журнала DATABASE_MIRRORING....363

Ожидание повторного использования журнала REPLICATION....364

Ожидание повторного использования журнала ACTIVE_BACKUP_OR_RESTORE....364

Другие стратегии решения проблем с усечением журнала....365

Ускоренное восстановление базы данных....365

Пропускная способность журнала транзакций....366

Резюме....368

Чек-лист устранения неполадок....369

Глава 12. Группы доступности AlwaysOn....370

Обзор групп доступности AlwaysOn....370

Очереди групп доступности....372

Синхронная репликация и опасное ожидание HADR_SYNC_COMMIT....377

Расширенные события групп доступности....380

Асинхронная репликация и доступные для чтения вторичные реплики....385

Особенности доступных для чтения вторичных реплик....386

Параллельный повтор....390

Устранение неполадок аварийного переключения....391

Группы доступности и отказоустойчивая кластеризация Windows Server....391

Устранение неполадок аварийного переключения....394

Когда аварийное переключение не происходит....395

Резюме....396

Чек-лист устранения неполадок....397

Глава 13. Другие примечательные типы ожиданий....398

Ожидания ASYNC_NETWORK_IO ....398

Ожидания THREADPOOL ....400

Ожидания, связанные с резервным копированием....405

Повышение производительности резервного копирования....405

Параметры BUFFERCOUNT и MAXTRANSFERSIZE....406

Частичные резервные копии базы данных....407

HTBUILD и другие ожидания с префиксом HT*....407

Вытесняющие ожидания....408

Тип ожидания PREEMPTIVE_OS_WRITEFILEGATHER ....408

Тип ожидания PREEMPTIVE_OS_WRITEFILE ....409

Типы ожидания, связанные с аутентификацией....409

Ожидания OLEDB ....410

Типы ожидания: подводим итоги....410

Резюме....411

Чек-лист устранения неполадок....412

Глава 14. Анализ схемы базы данных и индексов....413

Анализ схемы базы данных....413

Таблицы-кучи....414

Индексы с типом данных uniqueidentifier....417

Широкие и неуникальные кластеризованные индексы....418

Недоверенные внешние ключи....421

Неиндексированные внешние ключи....422

Избыточные индексы....424

Высокие значения идентификаторов....428

Анализ индекса....431

Представление sys.dm_db_index_usage_stats....432

Представление sys.dm_db_index_operational_stats....439

Целостное представление: sp_Index_Analysis....443

Резюме....445

Чек-лист устранения неполадок....446

Глава 15. SQL Server в виртуализированных средах....447

Виртуализировать или не виртуализировать — вот в чем вопрос....447

Настройка SQL Server в виртуализированных средах....449

Планирование мощности....449

Конфигурация ЦП....451

Память....457

Хранилище....458

Сеть....460

Управление виртуальными дисками....461

Стратегия и инструменты резервного копирования....462

Устранение неполадок в виртуальных средах....463

Недостаточная пропускная способность процессора....463

Нехватка памяти....468

Производительность дисковой подсистемы....469

Резюме....471

Чек-лист устранения неполадок....472

Глава 16. SQL Server в облаке....473

Облачные платформы с высоты птичьего полета....473

Надежность платформы....474

Лимитирование ресурсов....474

Топология....475

Связь и обработка случайных ошибок....476

Доступ к экземпляру базы данных....476

Случайные ошибки....477

SQL Server в облачных виртуальных машинах....478

Настройка ввода/вывода и производительность....478

Настройка высокой доступности....480

Межрегиональная задержка....480

Управляемые службы Microsoft Azure SQL....482

Рекомендации по архитектуре и проектированию служб....482

Подходы к устранению неполадок....486

Amazon SQL Server RDS....489

CloudWatch....490

Performance Insights....491

Google Cloud SQL....493

Резюме....494

Чек-лист устранения неполадок....495

Приложение. Типы ожиданий....496

Об авторе....510

Иллюстрация на обложке....511

Исчерпывающий обзор лучших практик по устранению неисправностей и оптимизации производительности Microsoft SQL Server. Специалисты по базам данных, в том числе разработчики и администраторы, научатся выявлять проблемы с производительностью, системно устранять неполадки и расставлять приоритеты при тонкой настройке, чтобы достичь максимальной эффективности.