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

Откуда:
Сообщений: 65
Здравствуйте.
Не занимались расчисткой БД совсем, несколько лет хранили всё, и сейчас ситуация следующая:
database_size: 65530 MB
unallocated_space: 7168 MB
reserved: 59536944 KB
data: 21540368 KB
index_size: 37946336 KB
unused: 50240 KB
в основных таблицах по 20-30 млн строк.

Есть утилита для расчистки(от поставщика ПО, с которым мы работаем), но она крайне примитивная, ей задаётся дата и всё что ДО этой даты - уничтожается. Но нам такой вариант не подходит. Есть определённые данные, которые мы хотим хранить.
Написали свой скрипт, который отбирает нужные и не нужные данные и через хранимую процедуру удаляет только те записи, которые не нужны. Опыты показывают, что 50-80% данных будут уничтожаться.
Поставили скрипт исполняться в нерабочее время и по ночам он начал потихонечку расчищать БД: сначала выбирает необходимые id и потом удаляет их из множества таблиц.
Вот лог последнего выполнения по основной таблице, значение ДО/ПОСЛЕ выполнения:
rows: 22931608/22877252 (удалил 54356 записей)
reserved: 11118952/11119144 (увеличилось на 192)
data: 2453136/2453192 (увеличилось на 56)
index_size: 8659424/8658760 (уменьшилось на 664)
unused: 6392/7192 (увеличилось на 800)
уточню, что это результаты только по основной таблице, помимо неё удаление происходит ещё в ~5 связанных таблицах.

Под утро выполняется план обслуживания БД:
1) бекап
2) индексирование
3) шринк


Забыл сказать причину расчистки: приложение которое работает с базой начало жутко виснуть, выдавать ошибки, профайлером посмотрели запросы, нашли запросы, которые БД исполняет очень долго, а программа ждёт ответ в течение 30 секунд и потом выдаёт ошибку(timeout).
Вот прямо сейчас вижу запрос, который исполняется 44 секунды, а программа через 30 секунд ожидания выдала ошибку и всё.

Вопросы:
1) Надо ли ещё что-нибудь делать в нашем случае?
2) Не совсем понял момент по значению data - оно не уменьшилось после удаления записей, а наоборот выросло, так и должно быть?
3) Вечером перезагружаем сервер, после полного запуска SQL съедает 15-25% ОЗУ, утром приходим(ночью отработал скрипт расчистки, потом бекап, потом индексация, потом шринк) и съедено 70-90% ОЗУ, в течение дня доходит до ~97%. Так и должно быть?
18 май 18, 11:36    [21419758]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
iiyama
Member

Откуда:
Сообщений: 642
Serg58,
re>database_size: 65530 MB

Это "пятничный" юмор такой?
18 май 18, 11:52    [21419815]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Konst_One
Member

Откуда:
Сообщений: 11518
ну может это sql2000 на офисной машинке с дохлым цпу и маленькими дисками?
18 май 18, 11:55    [21419822]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Меня скоро передергивать от слова шринк будет, в особености после перестроения индексов.
Шринкуй, перезагружай, доминируй!
18 май 18, 11:57    [21419828]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Serg58
Вопросы:
1) Надо ли ещё что-нибудь делать в нашем случае?
2) Не совсем понял момент по значению data - оно не уменьшилось после удаления записей, а наоборот выросло, так и должно быть?
3) Вечером перезагружаем сервер, после полного запуска SQL съедает 15-25% ОЗУ, утром приходим(ночью отработал скрипт расчистки, потом бекап, потом индексация, потом шринк) и съедено 70-90% ОЗУ, в течение дня доходит до ~97%. Так и должно быть?
1.а Шринк срочно уберите, он раз за разом замедляет работу базы.
1.б Пытайтесь оптимизировать работу приложения, например, модифицируйте его, если возможно, или попытайтесь построить недостающие индексы.
2. Вообще должно уменьшаться, раз удаляете.
3. Нормально. Если на сервере запущены ещё какие то приложения, кроме MSSQL, то можете ограничить ему память. Он забирает столько памяти, сколько дадут.
18 май 18, 14:05    [21420320]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
Serg58,

если у вас переиндексация - регулярная операция, запаситесь дисковым пространством. Ничего шринкать не надо. Оно для другого предназначено.
18 май 18, 14:24    [21420397]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
StarikNavy
Member

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

65 гигов, это "ни о чем" по современным меркам. поэтому надо доделать главную работу:
"рофайлером посмотрели запросы, нашли запросы, которые БД исполняет очень долго, "
а теперь проанализировать эти запросы и понять что на самом деле причина их тормозов
18 май 18, 15:05    [21420540]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Serg58
Member

Откуда:
Сообщений: 65
iiyama, нет) я серьёзно. Прикладываю скриншот.

Konst_One, SQL Server 2012 (x64) в стоечном сервере IBM с 32 ГБ ОЗУ.

aleksrov, ;) полагаю, мы делаем, что то не правильно, я здесь и пытаюсь разобраться. Сам я НЕ обслуживаю БД, я с ней работаю, тот кто обслуживает говорит, что планы настроил в соответствии с инструкцией которую получил от поставщика ПО.

alexeyvg,
alexeyvg
1.а Шринк срочно уберите, он раз за разом замедляет работу базы.

Сейчас уберу. То есть оставляю только: бекап и индексацию?
И ещё момент: шринк вообще отключать на совсем? или не делать его ежедневно?
Со слов технаря он отключал шринк, но тогда лог вырастает до 200Гб.

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

Приложение не наше, не доступно для изменения, и относительно часто обновляется, поэтому даже доступные для изменения хранимые процедуры, которые использует приложение не хочется трогать, т.к. в случае будущих обновлений можно что-либо упустить. И изменять структуру БД уж совсем не хочется.

alexeyvg
2. Вообще должно уменьшаться, раз удаляете.

а вообще после обширного удаления данных что необходимо сделать? переиндексацию?

alexeyvg
3. Нормально. Если на сервере запущены ещё какие то приложения, кроме MSSQL, то можете ограничить ему память. Он забирает столько памяти, сколько дадут.

Понял! Нет, сервер исключительно под БД, пусть забирает всю ОЗУ.


Параллельно веду переписку с разработчиком ПО, они говорят, что база огромна и надо расчищать :-\ только после расчистки надо оценить поможет или нет и смотреть дальше.
Будем расчищать, но процесс не быстрый, во время работы это не возможно делать, пользователи жалуются на тормоза.

К сообщению приложен файл. Размер - 6Kb
18 май 18, 15:29    [21420664]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
@база 65Гб

@тормозит

@половина базы влазит в память

@разработчиком ПО, они говорят, что база огромна

что это за зверь
18 май 18, 15:34    [21420690]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Konst_One
Member

Откуда:
Сообщений: 11518
интересно было бы посмотреть текущие настройки вашей базы:

SELECT * FROM sys.databases WHERE name = 'ваше имя базы'
18 май 18, 15:39    [21420709]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Serg58
Member

Откуда:
Сообщений: 65
так, сейчас ещё раз всё проверил лично по обслуживанию, оказывается включен в плане на текущий момент только бекап и за ним шринк. Индексацию выключали и не помнят когда.

Если быть точным то вот так:
1) Ежедневное (разностное) копирование - ежедневно кроме понедельника в 02:30 (проверка целостности, резервное копирование БД(разностное), очистка после обслуживания, очистка журнала)
2) Еженедельное полное копирование - по понедельникам в 02:30 (проверка целостности, резервное копирование БД(полное), очистка после обслуживания, очистка журнала)
3) шринк - каждые 6 часов начиная с 04:00 и до 23:59 (резервное копирование (журнал транзакций), сжатие бд, очистка журнала, очистка после обслуживания(удаление резервного копирования бд), очистка после обслуживания(отчёт плана обслуживания)).

Вижу план на выполнение команд:
use BDname;
DBCC SHRINKDATABASE(BDname)
exec sp_msforeachtable 'dbcc dbreindex(''?'')'
exec sp_msforeachtable 'update statistics ?'


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

На сколько я понимаю, по этому плану БД уходит в монопольный режим и надо ждать её выполнения, но это слишком долго.

Подскажете как быть? Мне приходит в голову идея сначала очищать базу до максимально возможного и на ночь включать пересоздание индексов. Только вот очищать базу мы будем примерно месяц.
Или сейчас, пока идёт очистка, делать обновление статистики (exec sp_msforeachtable 'update statistics ?') и дефрагментацию индексов(exec sp_msforeachtable 'dbcc INDEXDEFRAG (BDname, ''?'')' ?

Ещё вопрос: а нормально ли это, что БД весит 65Гб, из которых почти половину занимает именно index_size ?
18 май 18, 17:11    [21420985]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Serg58
Member

Откуда:
Сообщений: 65
Konst_One
интересно было бы посмотреть текущие настройки вашей базы:
SELECT * FROM sys.databases WHERE name = 'ваше имя базы'

для удобства чтения транспонировал:
+
database_id 8
source_database_id NULL
owner_sid 0x01
create_date 14.10.2017 15:02
compatibility_level 110
collation_name Cyrillic_General_CI_AS
user_access 0
user_access_desc MULTI_USER
is_read_only 0
is_auto_close_on 0
is_auto_shrink_on 1
state 0
state_desc ONLINE
is_in_standby 0
is_cleanly_shutdown 0
is_supplemental_logging_enabled 0
snapshot_isolation_state 0
snapshot_isolation_state_desc OFF
is_read_committed_snapshot_on 0
recovery_model 1
recovery_model_desc FULL
page_verify_option 2
page_verify_option_desc CHECKSUM
is_auto_create_stats_on 1
is_auto_update_stats_on 1
is_auto_update_stats_async_on 0
is_ansi_null_default_on 0
is_ansi_nulls_on 0
is_ansi_padding_on 0
is_ansi_warnings_on 0
is_arithabort_on 0
is_concat_null_yields_null_on 0
is_numeric_roundabort_on 0
is_quoted_identifier_on 0
is_recursive_triggers_on 0
is_cursor_close_on_commit_on 0
is_local_cursor_default 0
is_fulltext_enabled 1
is_trustworthy_on 0
is_db_chaining_on 0
is_parameterization_forced 0
is_master_key_encrypted_by_server 0
is_published 0
is_subscribed 0
is_merge_published 0
is_distributor 0
is_sync_with_backup 0
service_broker_guid 72CB2F71-261E-4A89-A054-5F1AB23A5A24
is_broker_enabled 0
log_reuse_wait 2
log_reuse_wait_desc LOG_BACKUP
is_date_correlation_on 0
is_cdc_enabled 0
is_encrypted 0
is_honor_broker_priority_on 0
replica_id NULL
group_database_id NULL
default_language_lcid NULL
default_language_name NULL
default_fulltext_language_lcid NULL
default_fulltext_language_name NULL
is_nested_triggers_on NULL
is_transform_noise_words_on NULL
two_digit_year_cutoff NULL
containment 0
containment_desc NONE
target_recovery_time_in_seconds 0
18 май 18, 17:15    [21420998]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
iiyama
Member

Откуда:
Сообщений: 642
Serg58,

recovery_model_desc FULL + единственный полный бэкап раз в сутки = пятница удалась
18 май 18, 17:26    [21421046]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
Serg58,

для начала сразу:
alter database <databasename>
set auto_shrink off


Из плана обслуживания нафиг уберите shrink, это довольно вредная операция.
У вас что там в базе постоянно большой объем данных прибавляется и одновременно чистится?
18 май 18, 17:41    [21421085]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
хм. сорян поспешил не дочитав первый пост.

Если вам базу надо почистить а объем удаляемых данных превышает объемы данных для хранения имеет смысл перелить нужные данные в новые таблицы, удалить старые, переименовать новые названиями старых таблиц. после этого разово сделать shrink для базы.

Порционно удаляя данные и делая каждую ночь shrink вы будете наблюдать картину "торможения" приложения еще очень долго.
18 май 18, 17:50    [21421126]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Serg58
Member

Откуда:
Сообщений: 65
iiyama, тот неловкий момент, но я не понял шутку) полагаю у нас не правильный бекап, с ним пока не разбирался даже.
И, кстати, у нас настроено полное зеркалирование БД на аналогичный сервер.


felix_ff,
felix_ff
для начала сразу:
alter database <databasename>
set auto_shrink off

Выполнение команд успешно завершено

felix_ff
Из плана обслуживания нафиг уберите shrink, это довольно вредная операция.

в плане обслуживания шринк ОТКЛЮЧИЛ лично, появился красная стрелочка вниз у иконки.

felix_ff
У вас что там в базе постоянно большой объем данных прибавляется и одновременно чистится?

Прибавляется - да.
И вот только пару дней как я запустил свой скрипт расчистки, теперь и удаляется примерно в 2 раза больше чем добавляется.
18 май 18, 17:54    [21421155]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Serg58,

извиняюсь за несколько грубую аналогию, но ваша ситуация с базой напоминает старый анекдот про козла, который "сильно задрочен, но жить будет"(с).
Вам бы нужно "остричь" вашего "козла", "помыть" и "причесать", а вы его - доить пытаетесь...

Теперь несколько мыслей по существу:

1) Бэкапы. В общем случае принято делать не только полный бэкап базы, но и периодические бэкапы транзакт-лога (между полными бэкапами). Периодичность выбирать исходя из интенсивности изменения данных и желаемого размера транзакт-лога. В этом случае размер лога не будет выходить "за рамки приличия". А его шринки станут невостребованной операцией.

2) Статистика. У MS SQL Server хороший и сильный оптимизатор запросов, но он основан не на "телепатии", а на "стоимости операций", для расчета которой требуется актуальная информация по таблицам, индексам и распределению данных в них. Поэтому задача по своевременному обновлению устаревших статистик - одна из важных при обслуживании базы. Необязательно это делать каждый день, для всех таблиц и полным сканированием. Но делать это надо.

3) Индексы. Их отсутствие может приводить к печальным результатам в производительности. Но наличие "неподходящих" или плохо обслуживаемых "деградировавших" индексов - не намного лучше.


Начните с малого: список проблемных запросов вам известен. Посмотрите их планы; обновите статистику по таблицам, участвующим в этих запросах; убедитесь, что схема индексирования таблиц удовлетворяет условиям фильтрации и соединения данных в запросах.
Сриптов по поиску и обновлению устаревшей статистики, поиску отсутствующих индексов, поиску сильно фрагментированных индексов- в интернете пруд-пруди.
18 май 18, 18:19    [21421233]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Serg58,

Судя по приведенным параметрам - автообновление статистики у вас включено. Возможно, таблицы растут не настолько быстро, чтобы успел сработать автосбор, но достаточно быстро, чтобы планы успели "поехать".
Меня "сейчас, должно быть, будут убивать"(с) но считаю, что на вашей версии сервера вполне себе целесообразно активировать trace flag 2371, и, возможно, перевести параметр is_auto_update_stats_async_on в true
18 май 18, 18:51    [21421307]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Serg58,

Если честно очень сложно обьяснить как должно быть когда человек полный 0 (это не камень в ваш огород, просто факт). Правда, почитайте про то что такое шринк, виды резервного копирования, модели восстановление и по многому озарение придет само.
У меня везде включены флаги 1117, 1118, 2371.
Выгоняйте нахрен вашего технаря который говорит что если не шринковать лог то будет капец.
Почему НЕ НАДО делать шринк, здесь
https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Про шринк лога
https://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log
У вас 50% базы в оперативе, вы счастливый человек. Дело не в кол-во данных, а в корявых запросах, ищите и оптимизируйте.

Анна - "Периодичность выбирать исходя из интенсивности изменения данных и желаемого размера транзакт-лога". Периодичность выбирают исходя из RPO а не из интенсивности изменения данных.
18 май 18, 19:05    [21421325]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1367
Serg58,

кстати да Щукина Анна правильно подметила.
У вас сейчас модель восстановления базы FULL, соответственно вам необходимо настроить периодически что бы делались бэкапы лога транзакций, иначе он неизбежно будет расти.

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

При этом если по каким то сакральным причинам вам необходимо отдать освободившееся место системе (к примеру размер файла БД подходит к границам диска а добавлять место вам по политическим наставлениям запрещает Папа Римский)
То ваши действия должны быть такими
+

1) переливаем нужные данные в новую таблицу (желательно что бы новая таблица размещалась в другой файловой группе)
2) делаем drop table или truncate старых таблиц
3) делаем shrinkfile файлов данных объем которых необходимо уменьшить
4) переименовываем новые таблицы в старые
5) настраиваем правильное обслуживание статистики и индексов


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

https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
18 май 18, 19:08    [21421328]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
aleksrov
Периодичность выбирают исходя из RPO а не из интенсивности изменения данных.
те же "яйца", только в профиль....
18 май 18, 19:08    [21421329]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Serg58
Параллельно веду переписку с разработчиком ПО, они говорят, что база огромна и надо расчищать :-\ только после расчистки надо оценить поможет или нет и смотреть дальше.
К сожалению большинство разработчиков ленивые и мало что понимают в оптимизации баз данных. А еще у них нет вашей копии базы. Им даже просто не хочется тратить время на выяснение почему там у пары клиентов что-то тормозит, больше денег они за это все равно не получат. На их тестовых базах в 10МБ все летает, так что да, конечно же проблема в том что база разрослась. Поэтому советы по производительности от вендоров обычно сводятся к обновлению статистики и перестроению индексов.
Так что самая реальная возможность что то исправить это смотреть на медленные запросы и планы самому, если сами не понимаете, выкладывайте планы сюда. Ну а там обычно 2 варианта: отсутсвуют индексы или нужно переписывать запрос. Если индексы, то просто создаете их, вряд ли разрабы накатывают апдейты путем пересоздания таблиц. Ну или можете попробовать пропихнуть это в их обновления. Разницы особой нет. Если же нужно переписывать запрос/процедуру, тогда придется долго и упорно вести переписку с разрабами о том почему их код кривой и как можно все исправить, может они согласятся и включат ваши фиксы в следующие обновления.
Serg58
rows: 22931608/22877252 (удалил 54356 записей)
Это работа скрипта за ночь? Меньше 1% строк? У вас наверное новые данные быстрее добавляются. Возможно ваш скрипт неопимизирован. Удаляет по одной строке и индексов на внешних ключах вообще нет?
18 май 18, 20:39    [21421509]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Serg58,
Начните с недостающих индексов. Запустите вот этот скрипт на вашей базе.
+
DECLARE @lastDays INT
SET @lastDays = 3

SELECT
  table_name = OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) + '.' + OBJECT_NAME(mid.object_id, mid.database_id),
  advantage = CONVERT(decimal(15, 3), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)), 
  mid.index_handle,
  migs.last_user_seek,
  equality_columns = ISNULL(mid.equality_columns, ''), 
  inequality_columns = ISNULL(mid.inequality_columns, ''), 
  included_columns = ISNULL(mid.included_columns, ''),
  migs.avg_user_impact,
  migs.user_seeks, 
  avg_total_user_cost = CONVERT(decimal(15, 3), migs.avg_total_user_cost), 
  migs.unique_compiles, 
  recommended_index = 
    'CREATE INDEX [' + 'IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' +
    replace(replace(replace(ISNULL(mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND  mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END
    + ISNULL(mid.inequality_columns,''), '[', ''), ']', ''), ', ', '_')
    + ']' + CHAR(13) + CHAR(10) + 'ON '
    + mid.statement
    + '(' + ISNULL(mid.equality_columns,'')
    + case 
      when mid.equality_columns is not null and mid.inequality_columns is not null 
        then ', '
      else ''
      end
    + ISNULL(mid.inequality_columns,'') +
    + case 
      when mid.included_columns is null 
        then ')'
      else ')' + CHAR(13) + CHAR(10) + 'INCLUDE (' + ISNULL(mid.included_columns,'') + ')'
      end 
FROM sys.dm_db_missing_index_group_stats AS migs
  INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() 
AND migs.last_user_seek >= DATEADD(DAY, -@LastDays, GETDATE())
ORDER BY advantage DESC
18 май 18, 20:53    [21421542]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Serg58
И ещё момент: шринк вообще отключать на совсем? или не делать его ежедневно?
Со слов технаря он отключал шринк, но тогда лог вырастает до 200Гб.
Да, насовсем. Файл должен вырасти до некоторого размера, а потом стабилизироваться.
Скорее всего, для выполнения некоторых действий (например, реиндексации) нужен запас пространства, вот файл и растёт. после выполнения этих действий место в файле освобождается. Так зачем сокращать файл, если он потом всё равно расширится?
Шринк - это аварийная разовая операция, которая делается для устранения последствий каких либо ошибок в эксплуатации базы, делать его нужно не по расписанию, а обдуманно, и вместе с устранением этих ошибок.

Serg58
а вообще после обширного удаления данных что необходимо сделать? переиндексацию?
Да, можно перестроить индексы, после завершения всего процесса удаления (не очередной порции)
Serg58
Приложение не наше, не доступно для изменения, и относительно часто обновляется, поэтому даже доступные для изменения хранимые процедуры, которые использует приложение не хочется трогать, т.к. в случае будущих обновлений можно что-либо упустить. И изменять структуру БД уж совсем не хочется.
Да, это понятно, ну вот, на этот случай я добавил про "индексы". Проанализируйте профайлером долгие запросы, посмотрите рекомендации сервера (он их показывает при просмотре планов), и возможно либо решите проблему созданием индексов, либо обновлением статистики, либо хотя бы передадите конкретику разработчикам.
Serg58
Параллельно веду переписку с разработчиком ПО, они говорят, что база огромна и надо расчищать :-\
Это яркий и однозначный признак непрофессионализма разработчиков. Не должны старые данные влиять на скорость выполнения оперативных транзакций.
Serg58
полагаю, мы делаем, что то не правильно, я здесь и пытаюсь разобраться. Сам я НЕ обслуживаю БД, я с ней работаю, тот кто обслуживает говорит, что планы настроил в соответствии с инструкцией которую получил от поставщика ПО.
И к тем, кто обслуживает, тот же самый вопрос. Почему разбираетесь вы, а не они, почему не они задают тут вопросы, почему не они дают вам эти советы, и вообще, просто не решают проблемы, что бы вы и пользователи их даже не заметили? "Планы настроил в соответствии с инструкцией"? Он кто, секретарша-машинистка, тексты набирает по инструкции, или специалист по MSSQL?
Serg58
Индексацию выключали и не помнят когда.
Вообще говоря, перестроение индекса нужно, что бы устранить последствия шринка (и для других подобных случаев). Для нормального использования индексов их не нужно перестраивать.
Достаточно обновлять статистику, да и то можно не каждый день.

Serg58
Мне приходит в голову идея сначала очищать базу до максимально возможного и на ночь включать пересоздание индексов. Только вот очищать базу мы будем примерно месяц.
Вот через месяц всё это и сделаете.

Уберите шринк, сделаете его вручную после очистки, тогда же сделаете и переиндексацию
В ежедневных джобах оставьте бакап и статистику, последнюю можно попробовать перенести на выходные, если не будет хватать времени.

Serg58
3) шринк - каждые 6 часов начиная с 04:00 и до 23:59 (резервное копирование (журнал транзакций), сжатие бд, очистка журнала, очистка после обслуживания(удаление резервного копирования бд), очистка после обслуживания(отчёт плана обслуживания)).
:-) В общем, напихали всё, про что прочитали :-)
18 май 18, 22:32    [21421684]     Ответить | Цитировать Сообщить модератору
 Re: подскажите по расчистке БД и последующему обслуживанию  [new]
Serg58
Member

Откуда:
Сообщений: 65
Огромное спасибо всем за ответы!

iiyama,
iiyama
Serg58,
recovery_model_desc FULL + единственный полный бэкап раз в сутки = пятница удалась

полный бекап у нас раз в неделю по понедельникам, а ежедневно - разностный.
Я уже понял, что это не правильно, с этим буду разбираться позже, сейчас разобраться с тормозами.


Щукина Анна,
Щукина Анна
Serg58,
извиняюсь за несколько грубую аналогию, но ваша ситуация с базой напоминает старый анекдот...

всё именно так, полностью с Вами согласен. Я работаю с базой как её пользователь, обслуживать её вообще не моя работа, не мой отдел. Но жутко надоели тормоза, технари говорят, что всё в соответствии как указано разработчиком ПО, а тех.поддержка говорит присылайте логи, очищайте базу и т.д.
Решил сам разобраться.

Щукина Анна
1) Бэкапы. В общем случае принято делать не только полный бэкап базы, но и периодические бэкапы транзакт-лога (между полными бэкапами). Периодичность выбирать исходя из интенсивности изменения данных и желаемого размера транзакт-лога. В этом случае размер лога не будет выходить "за рамки приличия". А его шринки станут невостребованной операцией.

Про бекап, повторюсь, буду разбираться отдельно. Сейчас у нас полное зеркалирование БД идёт в 2 сервер.
И вот эти бекапы, о которых писал выше.


Щукина Анна
2) Статистика. У MS SQL Server хороший и сильный оптимизатор запросов, но он основан не на "телепатии", а на "стоимости операций", для расчета которой требуется актуальная информация по таблицам, индексам и распределению данных в них. Поэтому задача по своевременному обновлению устаревших статистик - одна из важных при обслуживании базы. Необязательно это делать каждый день, для всех таблиц и полным сканированием. Но делать это надо.

3) Индексы. Их отсутствие может приводить к печальным результатам в производительности. Но наличие "неподходящих" или плохо обслуживаемых "деградировавших" индексов - не намного лучше.

Вооот! Сейчас у меня начинает вырисовываться то что у нас произошло: база заполнялась, не чистилась(СОВСЕМ), не обслуживалась(только бекап и шринк).
Тут мы запустили расчистку, удалилось множество данных и после этого запрос который до этого выполнялся - стал выполнятся в разы дольше.
Думается мне, что если запустить обновление статистики и дефрагментацию индексов запрос будет выполнятся в разы быстрее.
18 май 18, 22:55    [21421703]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить