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

Откуда: Иваново
Сообщений: 5
Доброго времени суток.

Есть вопрос по планированию обслуживания БД.
Имеем: База данных под управлением MSSQL 2008 R2 Standart. В настоящее время (через месяц после введения БД в эксплуатацию) ее размер составляет порядка 650 Мб. Из них 95% это одна таблица с данными (назовем ее DATA), которая и будет в основном наращиваться в течении последующих пяти лет. Сейчас в ней примерно около 3 млн. записей. По предварительным расчетам (при существующем количестве объектов для сбора данных) за 5 лет размер БД вырастет до размеров 80-100 Гб (возможно так же расширение всего проекта в несколько (не более 10) раз). После этого старые данные будут постепенно удалятся.
Все это хозяйство размещается на 2х процессорном сервере HP, 16 гигов оперы(не маловато ли?), 900 гиговый рейд 5 (4 диска по 300 гб).

1. Каким образом лучше организовать хранение данных:
1.1. есть ли смысл выносить индексы в отдельный файл (файловую группу)? (где почитать?)
1.2. если ли смысл добавлять файлы (файловый группы) для данных?
1.3. как можно разбить таблицу с данными БД на различные файловые группы? будет ли существенен прирост в производительности? (и вообще можно ли так сделать). Например, есть мысль для каждого года создавать отдельный файл и хранить данные таблицы DATA? (где про это можно почитать?)

2. Каким образом лучше организовать обслуживание БД (в настоящее время сделан ежедневный полный бекап с хранением файлов в течении 1 месяца, таким образом всегда есть 30 полных бэкапов):
2.1. Как часто (и нужно ли вооще) делать ребилд (или реорганизацию) индексов? (где почитать поподробнее?)
2.2. То же со статистикой?
2.3. Может чтото еще??

Буду раз выслушать любые комментарии по поводу всего вышесказанного...
28 окт 12, 18:09    [13387436]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Самое главное не написано, как вы эти данные будете использовать? Если просто складировать в БД и никогда не читать, то можно вообще больше ничего не делать.
Yacudzer
16 гигов оперы(не маловато ли?)
Еще раз, зависит от того как вы будете эти данные читать.
Yacudzer
1.1. есть ли смысл выносить индексы в отдельный файл (файловую группу)? (где почитать?)
1.2. если ли смысл добавлять файлы (файловый группы) для данных?
Я не вижу смысла. Чего вы хотите этим добиться?
Yacudzer
1.3. как можно разбить таблицу с данными БД на различные файловые группы? будет ли существенен прирост в производительности? (и вообще можно ли так сделать). Например, есть мысль для каждого года создавать отдельный файл и хранить данные таблицы DATA? (где про это можно почитать?)
Это называется partitioning и доступно только в Enterprise. Для вашей редакции можно только сохранять каждый год в отдельную таблицу, а потом объединять все это в одно view.
Yacudzer
2.1. Как часто (и нужно ли вооще) делать ребилд (или реорганизацию) индексов? (где почитать поподробнее?)
Настолько часто чтобы не было сильной фрагментации. Читать BOL.
Yacudzer
2.2. То же со статистикой?
Настолько часто, чтобы оптимизатор запросов мог строить правильные планы на основе этой статистики.
Yacudzer
2.3. Может чтото еще??
DBCC CHECKDB. Ну и бэкап логов было бы неплохо, если у вас база в FULL.
28 окт 12, 21:56    [13387921]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Yacudzer
Member

Откуда: Иваново
Сообщений: 5
Mind
Самое главное не написано, как вы эти данные будете использовать? Если просто складировать в БД и никогда не читать, то можно вообще больше ничего не делать.
Yacudzer
16 гигов оперы(не маловато ли?)
Еще раз, зависит от того как вы будете эти данные читать.

насчет чтения: автоматическая ежедневная выборка данных, ежемесячная выборка за предыдущий месяц ну и по запросу оператора (опять же, не часто, раз в сутки)

Mind
Yacudzer
1.1. есть ли смысл выносить индексы в отдельный файл (файловую группу)? (где почитать?)
1.2. если ли смысл добавлять файлы (файловый группы) для данных?

Я не вижу смысла. Чего вы хотите этим добиться?

Ну, в случае выноса индексов в отдельный файл - как минимум уменьшение фрагментации. Если в будущем будет расширение рейда, то, возможно, вынос их на отдельный раздел - увеличение производительности. Хотя, такими объемами впредь оперировать не приходилось - не знаю на сколько поможет.
Mind
Yacudzer
1.3. как можно разбить таблицу с данными БД на различные файловые группы? будет ли существенен прирост в производительности? (и вообще можно ли так сделать). Например, есть мысль для каждого года создавать отдельный файл и хранить данные таблицы DATA? (где про это можно почитать?)
Это называется partitioning и доступно только в Enterprise. Для вашей редакции можно только сохранять каждый год в отдельную таблицу, а потом объединять все это в одно view.
это не поможет - ПО, которое работает с БД - не поймет. Разработка не наша, к сожалению.
А в 2012 SQL это тоже в Enterprise только реализовано? Может имеет смысл проапгрейдить СУБД?
Mind
DBCC CHECKDB. Ну и бэкап логов было бы неплохо, если у вас база в FULL.

БД в Simple. Опять же обусловлено ПО сторонних разработчиков.
28 окт 12, 22:21    [13387972]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Yacudzer
Ну, в случае выноса индексов в отдельный файл - как минимум уменьшение фрагментации. Если в будущем будет расширение рейда, то, возможно, вынос их на отдельный раздел - увеличение производительности. Хотя, такими объемами впредь оперировать не приходилось - не знаю на сколько поможет.
Пока у вас и система и все данные с журналами лежат на одном единственном массиве, да еще и RAID5, всякие манипуляции с файлами и группами не имеют смысла.
Yacudzer
БД в Simple. Опять же обусловлено ПО сторонних разработчиков.
Т.е. эти замечательные сторонние разработчики определили за вас стратегию резервного копирования, а отвественность осталась ваша? Круто.
Да и вообще не понятно, каким боком модель восстановления влияет на работоспособность клиентского ПО.
28 окт 12, 23:02    [13388085]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
В простых случаях обслуживание базы можно ограничить следующими операциями (в строго таком порядке):
1) SHRINK DATABASE
DBCC SHRINKDATABASE ('Имя базы', 10)

2) Перестройка (REBUILD) индексов. Важно чтобы она выполнялась _после_ сжатия базы
EXEC sp_msforeachtable N'ALTER INDEX ALL ON ? REBUILD'
Удобная системная процедура sp_msforeachtable позволяет выполнить один запрос для всех таблиц базы (подставляет вместо "?" по очереди имена всех таблиц).

3) Обновление статистики
EXEC sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
29 окт 12, 14:34    [13391119]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Alan008
В простых случаях обслуживание базы можно ограничить следующими операциями (в строго таком порядке):
1) SHRINK DATABASE
DBCC SHRINKDATABASE ('Имя базы', 10)

2) Перестройка (REBUILD) индексов. Важно чтобы она выполнялась _после_ сжатия базы
EXEC sp_msforeachtable N'ALTER INDEX ALL ON ? REBUILD'
Удобная системная процедура sp_msforeachtable позволяет выполнить один запрос для всех таблиц базы (подставляет вместо "?" по очереди имена всех таблиц).

3) Обновление статистики
EXEC sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'


1. - зло. Зачем это надо в постоянке делать?
2. - т.е. ребилдеть все и пофиг надо оно или нет ?
3. - Зачем, если сделали ребилд индексов ? Обновлять только по таблицам...
29 окт 12, 14:39    [13391167]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
"1" не зло, если после него все равно делается ребилд всех индексов. "1" позволяет место экономить, когда база активно модифицируется (может разрастаться до неприличия).

По поводу "ребилдеть всё", "обновлять всю статистику": если эти задачи на конкретной базе выполняются менее чем за 5 минут, почему бы их не делать каждый день?
29 окт 12, 14:50    [13391281]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Alan008
"1" не зло, если после него все равно делается ребилд всех индексов. "1" позволяет место экономить, когда база активно модифицируется (может разрастаться до неприличия).

Ерунду пишите. После таких манипуляций ни место не экономится, ни производительность не увеличивается
29 окт 12, 14:55    [13391326]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
автор
"1" не зло, если после него все равно делается ребилд всех индексов. "1" позволяет место экономить, когда база активно модифицируется (может разрастаться до неприличия).


1. Зачем нагружать сервер бесполезными действиями ?
2. Шринк применяется по необходимости, а не для планового обслуживания.
3. Он не позволяет сэкономить место, а исправить грабли, после неверных настроек сервера. Т.е. если модель базы FULL, а бэкапы логов не делаются и тп. при правильных настройках баз и их обслуживания, сервер все равно возьмет столько, сколько ему необходимо места. А операция приращения довольно затратная. Не издевайтесь над сервером!
4. Исходя из пункта 3, надо планировать СХД , настройки баз данных, их обслуживания так. что бы не нуждаться в шринке.

автор
По поводу "ребилдеть всё", "обновлять всю статистику": если эти задачи на конкретной базе выполняются менее чем за 5 минут, почему бы их не делать каждый день?


Опять же, если да кабы.. Привыкать надо к хорошему и правильному. Дабы потом не выяснять, откуда ночью блокировки пошли..
29 окт 12, 14:59    [13391377]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Alan008,

Интересный подход.
Т.е. если у автомашины случится прокол колеса - вы будете советовать купить новую автомашину?
29 окт 12, 14:59    [13391378]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
>>Дабы потом не выяснять, откуда ночью блокировки пошли
Согласен, можно оговориться, что желательно, чтобы данные операции выполнялись в период отсутствия (или снижения) рабочей нагрузки на базу. Т.е. к примеру, если с базой работают только днем, а ночью не работают, то мне абсолютно пофиг, как сильно ночью нагружается сервер.
29 окт 12, 15:05    [13391444]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Glory
Member

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

Эти операции НЕ должны выполняться в такой последовательности. От такой последовательности только ВРЕД.
29 окт 12, 15:08    [13391472]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Alan008
>>Дабы потом не выяснять, откуда ночью блокировки пошли
Согласен, можно оговориться, что желательно, чтобы данные операции выполнялись в период отсутствия (или снижения) рабочей нагрузки на базу. Т.е. к примеру, если с базой работают только днем, а ночью не работают, то мне абсолютно пофиг, как сильно ночью нагружается сервер.

Таких оговорок можно найти массу, поверьте. Но если Вам так проще - дай Бог. Только придерживаться такой философии ТС я бы не рекомендовал..
29 окт 12, 15:09    [13391480]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
2Glory:
посоветуйте Ваш вариант последовательности.

Я исходил из логики, что сначала пакуем базу, потом перестриваем индексы, чтобы избежать фрагментации, которая могла возникнуть при выполнении операции SHRINK
29 окт 12, 15:16    [13391571]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Alan008
2Glory:
посоветуйте Ваш вариант последовательности.

Я исходил из логики, что сначала пакуем базу, потом перестриваем индексы, чтобы избежать фрагментации, которая могла возникнуть при выполнении операции SHRINK

Не делать шринк БЕЗ НЕОБХОДИМОСТИ. Еще раз это не плановое обслуживание.

Давайте еще кучу ненужностей засунем и последующими операциями будем исправлять последствия этих ненужностей...
29 окт 12, 15:19    [13391607]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Alan008
Я исходил из логики, что сначала пакуем базу, потом перестриваем индексы, чтобы избежать фрагментации, которая могла возникнуть при выполнении операции SHRINK

Ага, сначала пакуем, потом раздуваем.
Вы думаете, что фрагментация уменьшится, если при перестроении индекса понадобится выделить новые страницы ?
29 окт 12, 15:23    [13391651]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
Вот тут проблема описана чуть подробнее:

The major problem with the Shrink operation is that it increases fragmentation of the database to very high value. Higher fragmentation reduces the performance of the database as reading from that particular table becomes very expensive.

One of the ways to reduce the fragmentation is to rebuild index on the database.

Правда далее автор замечает, что Rebuild приводит к разрастанию базы. Может быть это происходит при каких-то конкретных условиях. Лично я такого "неоправданного" увеличения размера рабочих баз не наблюдал, хотя описанные выше операции выполняются на них уже несколько лет с регулярностью один раз в 1-2 недели (объем баз от 10 до 100 ГБ).
29 окт 12, 15:36    [13391755]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Centraloff
Member

Откуда: Екатеринбург
Сообщений: 138
Alan008,

Скажите а на сколько уменьшаются размеры ваших баз до и после шринка, какого размера они стают после ребилда, сколько стоит значение увеличение размера файла БД
29 окт 12, 15:51    [13391873]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
>>на сколько уменьшаются размеры ваших баз до и после шринка
Сходу сказать не могу, нужно собрать эту статистику. Но что уменьшается - это факт. Дело в том, что есть базы, хранящие в себе гигабайты блобов (там внутри базы хранятся десятки тысяч файлов, причем эти файлы постоянно обновляются, добавляются и удаляются, база постоянно испытывает OLTP-нагрузки, это приводит к ненужному разрастанию размеров базы, избавиться от которого помогает SHRINK). Я не хочу сказать, что SHRINK - это хорошо, просто он помогает экономить место. Побочные эффекты шринка (фрагментацию) вынужден решать ребилдом индексов.
29 окт 12, 15:58    [13391929]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Alan008
>>на сколько уменьшаются размеры ваших баз до и после шринка
Сходу сказать не могу, нужно собрать эту статистику. Но что уменьшается - это факт. Дело в том, что есть базы, хранящие в себе гигабайты блобов (там внутри базы хранятся десятки тысяч файлов, причем эти файлы постоянно обновляются, добавляются и удаляются, база постоянно испытывает OLTP-нагрузки, это приводит к ненужному разрастанию размеров базы, избавиться от которого помогает SHRINK). Я не хочу сказать, что SHRINK - это хорошо, просто он помогает экономить место. Побочные эффекты шринка (фрагментацию) вынужден решать ребилдом индексов.


Блин.... Вы туда сюда файл фигачите... После шринка начинается прирост, что очень затратно. Ну как Вы этого не понимаете ?...
29 окт 12, 16:02    [13391976]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Alan008
Member

Откуда: г. Иваново
Сообщений: 52
2 Ozerov.
Пожалуйста, выражайтесь более аргументированно. "Туда сюда фигачите" - это не язык технического специалиста.
Я вам поясняю: через базу проходит в день, допустим 200 МБ данных (т.е. 200 МБ - это дельта, столько удаляется старых данных и приходит новых, примерно 50/50). За 2 недели соответственно 200 МБ*14=2.7 ГБ примерно. 2.7 ГБ дельты! Если SHRINK позволит мне хотя бы 1 ГБ из них освободить, я уже буду рад.
29 окт 12, 16:10    [13392043]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Alan008
2 Ozerov.
Пожалуйста, выражайтесь более аргументированно. "Туда сюда фигачите" - это не язык технического специалиста.
Я вам поясняю: через базу проходит в день, допустим 200 МБ данных (т.е. 200 МБ - это дельта, столько удаляется старых данных и приходит новых, примерно 50/50). За 2 недели соответственно 200 МБ*14=2.7 ГБ примерно. 2.7 ГБ дельты! Если SHRINK позволит мне хотя бы 1 ГБ из них освободить, я уже буду рад.

А затем они типа не используются для новых вставок Вы хотите сказать ? так и лежат мертвым грузом все такие обидевшиеся и неиспользованные ?
Вы заставляете сервер ужать базу. Потом (уже при ребилде) начинается процесс приращения. Затем, когда идут новые вставки, процесс приращения повторяется. Оно Вам зачем ?
А насчет языка, дык Вам объясняют, а Вы, пардон, уперлись и не хотите понимать...
29 окт 12, 16:17    [13392096]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
секционируйте табличку "DATA". только критерий подберите поудобнее. и все ваши "танцы" (при удачном выборе критерия) сведутся к обслуживанию 1 секции этой таблицы. прикладному ПО этого "понимать" не нужно совершенно
в крайнем случае можно попробовать "секционированное представление", если ваше ПО не будет сильно "сопротивляться", а жаба задушит на EE редакцию уходить. чуть больше накладные расходы, зато проще в обслуживании
хотя.. при озвученных объемах как-то не очень понятно, для чего вообще весь этот "цирк" затевать. а что, за 5 лет вы не сможете разработчиков "уболтать" поддержать "секции данных" на прикладном уровне? или это будет дороже покупки EE лицензии на сиквел?
29 окт 12, 16:32    [13392213]     Ответить | Цитировать Сообщить модератору
 Re: Планирование обслуживания базы данных.  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Alan008
Если SHRINK позволит мне хотя бы 1 ГБ из них освободить, я уже буду рад.
Вот Вы, как "технический специалист" сначала узнайте - что там удаляет shrink.
Если в вашей БД нужно 1Гб места под хранение данных - Вам никакой shrink не поможет.
Мосто под данные можно освободить:
  • командой delete (естественно, удалив какие-то другие данные)
  • перестроением индекса с указанием fillfactor меньше, чем фактический (за счет более "оптимального" использования выделенных страниц данных)
  • перестроив индекс с использованием сжатия (ну это разово поможет, конечно)

    shrink только осовобождает место на диске, занятое файлом данных, естественно, уменьшая свободное место ВНУТРИ файла данных.
    Никакого места, доступного для хранения данных, эта команда не добавляет и не уменьшает - для данных доступно (при наличии настройки авто-величения размера файла) место по формуле:
    FreeForData = DiskSize-OtherFilesOnDisk-DBDataSize
    Размер файла данных тут вообще роли не играет, он отвечает только за то, сколько можно выделить на диске под какие-то другие задачи. Его размер - это (условно DataSize+ReservedSpace)

    Это все равно как Вы бы продавали неиспользуемое в вашей квартире место перед уходом из дома, покупая его назад, покупая его назад вечером, (и естественно - платя комиссию. )
  • 29 окт 12, 16:33    [13392226]     Ответить | Цитировать Сообщить модератору
     Re: Планирование обслуживания базы данных.  [new]
    Alan008
    Member

    Откуда: г. Иваново
    Сообщений: 52
    >>А затем они типа не используются для новых вставок
    Может и используются, но видимо не всегда, т.к. SHRINK обычно все-таки ужимает базу (если бы он ее разращивал, это было бы заметно и мы бы не стали им пользоваться). И кстати я выше приводил пример, где у меня SHRINK оставляет резерв свободного места 10% от базы, так что не совсем верно говорить, что база "сразу начнет разрастаться".

    А зачем тогда вообще может понадобиться SHRINK, если все освобожденные страницы SQL Server всегда сам эффективно использует? Желат-но дать ссылку на BOL, где это явно прописано. Шринк кстати еще и файл лога шринкает, что в SIMPLE модели вроде актуально (т.к. никаких специальных операций по бэкапу лога и т.п. не делается).
    29 окт 12, 16:39    [13392264]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить