14 декабря 2016 в 17:31

Администрирование → Ахиллесова пята супер процессоров SQL сервера или борьба с PAGELATCH

Бывает время, когда большие базы данных и большое количество обращений к ней вынуждают делать вертикальное масштабирование серверов путем наращивания процессорной мощности и других основных характеристик. Но бывают ситуации, когда после этого прирост производительности почти отсутствует как в нашем случае. В этом посте я хочу рассказать как была решена эта проблема у нас путем добавления пустого поля в таблицу. Заинтриговал?

Все началось с жалоб на сервер базы данных о зависании программы у клиентов при запуске больших расчетов. Проанализировав загрузку железа, была выявлена нехватка процессорной мощности в пики нагрузки. Было принято решение обновления железа. Результат — увеличение количества мощности CPU более чем в 20! раз. Но при первом же пики нагрузки было обнаружено следующее:

  • Скорость отклика программы у пользователей оставалась такая же как и до модификации железа, а многие говорили что стало хуже.
  • В самый пик нагрузки максимальная загрузка процессоров не превышала 20%.

Чтение sys.sysprocesses показало что 90% пользователей висели с типом ожидания PAGELATCH. Рекомендации Microsoft по выставления флага T1118 однородных экстентов ни к чему не привели. Также было замечено что все легковесный блокировки идут на одну страницу памяти. С помощь выставления флага DBCC TRACEON (3604) инструкции DBCC PAGE была установлена виновная таблица TABLE1 в базе tempdb.

Логика архитектуры данных на сервере, допускала работу нескольких предприятий в одной БД. Данные в таблицах фильтровались по коду организации, который присваивался пользователям при входе в программу и хранились они все в табличке TABLE1. По сути эта таблица состояла из 2-3 колонок, в которой каждому SPID пользователя соответствовал код организации, который он выбрал. Несмотря на то, что вся база tempdb была размещена на RAM диске, эта таблица оказалась самым узким местом.

После более глубокого анализа оказалось, что практически все строки таблицы в момент наибольшего количества соединений занимали около 8кб. А следовательно они размещались на одной странице памяти, на которую и накладывалась блокировка PAGELATCH. Для решения данной проблемы было решено добавить в таблицу TABLE1 поле char(4000). А так на страницах памяти хранятся строки целиком, то есть без переноса, каждой новой строке соответствовала новая страница.

После это проблема с PAGELATCH на этой таблице ушла, и нагрузка перешла на процессоры. Они больше не простаивали и выполняли расчеты.