Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Добрый день!

СУБД: Microsoft SQL Server Enterprise edition 2014 SP2


Помогите с грамотной организацией буферных таблиц.

В БД есть несколько высоконагруженных буферных таблиц.
Проблемы с ними следующие:

1. Таблицы пухнут, не успевают фоново удалять страницы с удаленными данными.(ghost record)
2. Таблицы пухнут по кол-ву сток(не успевают удаляться)

Таблицы организованы как кучи, с несколькими не кластерными индексами лежащие в общей файловой группе.
Основные типы ожидания на процессах работающих с данными таблицами

--writelog(waittime ~40 мс)
--pagelatch%(waittime ~10 мс)
--sos_scheduler_yield(waittime ~0мс)
Нагрузка на каждую таблицу 3к -12к записей в секунду(удаление)

На данный момент следующие мысли:
Вынести файл лога БД на SSD
Вынести буферные таблицы в отдельную файловую группу на быстрые диски.
Оценить используемость индексов и после анализа по удалить не используемые
Периодически перестраивать кучу (минус это блокировки, которые крайне не желательны)

Есть ли еще способы оптимизации работы буферных таблиц? Стоит ли разрабатывать вариант с In memory?
5 ноя 16, 11:12    [19860245]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
andrew shalaev,

Сам на эту тему маюсь, но у меня то копейки прокачиваются.
Что подтолкнуло именно к такому решению?
много записей в одной транзакции проходит?
Почему не через темпдб? Временные, либо постоянные?
5 ноя 16, 12:58    [19860399]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Mike_za
andrew shalaev,

Что подтолкнуло именно к такому решению?


Так исторически сложилось :)

Mike_za
andrew shalaev,

много записей в одной транзакции проходит?


пачки по 3к
5 ноя 16, 13:06    [19860417]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
andrew shalaev,

Как льете в таблицы? Что с блокировками? Как делите между сессиями?
5 ноя 16, 13:19    [19860436]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
aleks2
Guest
andrew shalaev
Таблицы организованы как кучи

Вот насрут серверу в душу... а потом жалуются.
5 ноя 16, 13:23    [19860446]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
У вас же Enterprise, значит можно сделать столбец типа date с датой вставки и секционировать по нему. По крайней мере, отрезать хвосты можно будет очень быстро. Даже если у вас буфер организован по другому критерию - ну увеличите buffer retention, всего-то делов. Или, как вариант, секционируйте по этому признаку.

Периодически перестраивать кучу
На 2014? Ну-ну, удачи в этом нелегком деле. Вообще, по поводу куч aleks2 уже все сказал.
5 ноя 16, 15:24    [19860540]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Ennor Tiegael,

Нормальный размер таблицы 0-1000 записей, зачем секционировать?
В пики разрастается до 50-1000к, но это уже плохо для логики.

Вы считаете что в буферной таблице необходим кластерный индекс?
5 ноя 16, 15:31    [19860547]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
andrew shalaev,

В смысле, у вас при буфере в 1 миллион строк все вешается? Смотрите на недостающие индексы, чаще делайте обновление статистики. У меня в прошлом была система, где на паре таблиц статистика обновлялась каждые 10 минут.

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

Кроме того, возможно, вам удастся сделать эти таблицы in-memory. Оно же вроде в 2014 как раз появилось?..
5 ноя 16, 15:42    [19860560]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
А на мои вопросы так и не ответили...
Что с эскалацией блокировок, как вы делите сессии? Почему не создали в темпдб- там меньше логирования
5 ноя 16, 15:45    [19860563]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Ennor Tiegael,

Да они по логике, и так в буферном кеше в памяти сидеть должны.
5 ноя 16, 15:49    [19860569]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Mike_za
А на мои вопросы так и не ответили...
Что с эскалацией блокировок, как вы делите сессии? Почему не создали в темпдб- там меньше логирования


По поводу заливки данных отвечать не стал, т.к. проблема с периодическими проблемами с запросами на update поля таблицы, после которого идет удаление данных, в редкие моменты начинается увеличение выполнения запроса update с 50 мс до 5 сек.

При этом его план один и тот же при 50 мс и 5 сек, тип ожидания всегда PAGELATCH_EX waittime 0, показатели Reads, writes 0-6, логические чтения скачут в пределач 1к-30к и их различные значения не привязаны к времени выполнения запроса.

Сейчас паралельно пикам по времени выполнения запроса собираю статистику по блокировкам, но пока не заметил что бы на этих таблицах были блокировки.
5 ноя 16, 15:55    [19860578]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Буферные таблицы секционировать, сделать возрастающий кластерник, обработку данных сделать по чекпоинтам, устаревшие обработанные данные транкейтить секциями.
5 ноя 16, 15:59    [19860583]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
andrew shalaev,

Хорошо, попробуем по другому.
Как вы вставляете вашу 1000 записей? Поштучно, булк инсерт, insert select. Что при этом с транзакциями?
5 ноя 16, 15:59    [19860584]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Ну и план хорошо бы выложить
5 ноя 16, 16:00    [19860585]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
Mike_za
Ennor Tiegael,

Да они по логике, и так в буферном кеше в памяти сидеть должны.
Прежде чем стать доступными в памяти, данные должны быть зафиксированы на диске - как минимум в журнале транзакций, а впоследствии и в файле данных. Насколько я помню, in-memory уменьшает этот футпринт. Тут сами курите доки, у меня до этого руки так и не дошли.
Mike_za
А на мои вопросы так и не ответили...
Это вы мне?

Если не можете / не хотите делать в основной базе - вынесите буфера в отдельную БД и в ней включите RCSI. Про блокировки, скорее всего, сможете забыть - ну, если у вас там не сотня соединений одновременно BULK INSERT делают.
5 ноя 16, 16:07    [19860600]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Mike_za
Ну и план хорошо бы выложить


К сообщению приложен файл. Размер - 15Kb
5 ноя 16, 16:10    [19860606]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Гавриленко Сергей Алексеевич
Буферные таблицы секционировать, сделать возрастающий кластерник, обработку данных сделать по чекпоинтам, устаревшие обработанные данные транкейтить секциями.


Это как? В смысле с той же периодичностью?
5 ноя 16, 16:12    [19860611]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
andrew shalaev
Гавриленко Сергей Алексеевич
Буферные таблицы секционировать, сделать возрастающий кластерник, обработку данных сделать по чекпоинтам, устаревшие обработанные данные транкейтить секциями.


Это как? В смысле с той же периодичностью?
Как только секция обработана полностью, чикать.
5 ноя 16, 16:13    [19860616]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Гавриленко Сергей Алексеевич,

В чем преимущество секционирования при моих небольших объёмах при корректной работе?
Единственное дисковая подсистема не будем постоянно удалять данные, но так же будет выполнять update поля с которым у меня проблемы.

При вставке в таблицу с возрастающим кластерным индексом он будет его сканировать?
5 ноя 16, 16:17    [19860621]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
andrew shalaev
Гавриленко Сергей Алексеевич
Буферные таблицы секционировать, сделать возрастающий кластерник, обработку данных сделать по чекпоинтам, устаревшие обработанные данные транкейтить секциями.


Это как? В смысле с той же периодичностью?


Здесь не правильно выделил цитату, я имел ввиду как это делать обработку данных по чекпоинтам?
5 ноя 16, 16:19    [19860626]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
andrew shalaev
В чем преимущество секционирования при моих небольших объёмах при корректной работе?
В отсутствии ghost-рекорд же.
5 ноя 16, 17:33    [19860738]     Ответить | Цитировать Сообщить модератору
 Re: Организация буферных таблиц  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Почему нельзя truncate, почему нельзя simple?
7 ноя 16, 10:33    [19864468]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить