alexeyvgSerg58а вообще после обширного удаления данных что необходимо сделать? переиндексацию?Да, можно перестроить индексы, после завершения всего процесса удаления (не очередной порции)
Правильно ли я понял, что после порционного удаления можно делать обновление статистики (exec sp_msforeachtable 'update statistics ?') а когда расчистим базу сильно сделать переиндексацию(exec sp_msforeachtable 'dbcc dbreindex(''?'')')
Два вопроса:
1) когда нужно использовать дефрагментацию индексов(exec sp_msforeachtable 'dbcc INDEXDEFRAG (BDname, ''?'')' ?
2) сейчас у нас стоит цель из 22млн(основной таблицы) расчистить не нужные нам данные, это где то 50-80%, и мы выйдем на ориентировочно следующий цикл: в день поступает ~30-50 тысяч новых записей, и примерно столько же будет удаляться старых(которые отлежали примерно год в базе), в таком случае какой принцип обслуживания, когда что делать?

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

alexeyvgИ к тем, кто обслуживает, тот же самый вопрос. Почему разбираетесь вы, а не они, почему не они задают тут вопросы, почему не они дают вам эти советы, и вообще, просто не решают проблемы, что бы вы и пользователи их даже не заметили? "Планы настроил в соответствии с инструкцией"? Он кто, секретарша-машинистка, тексты набирает по инструкции, или специалист по MSSQL?
Ой, это отдельная и очень сложная тема. Я ему не начальник, совершенно другой отдел, просто мне не всё равно. Вопрос этот очень обширный и совсем не по теме.

alexeyvgУберите шринк, сделаете его вручную после очистки, тогда же сделаете и переиндексацию
В ежедневных джобах оставьте бакап и статистику, последнюю можно попробовать перенести на выходные, если не будет хватать времени.
шринк уже убрал, и в планах его отключил, и командой что дали здесь выше.
Статистику и дефрагментацию индексов я сегодня вечером прогнал на более мелких базах - всё ок.
В понедельник у меня будет возможность запустить обновление статистики и на основной самой большой базе

MindSerg58rows: 22931608/22877252 (удалил 54356 записей)Это работа скрипта за ночь? Меньше 1% строк? У вас наверное новые данные быстрее добавляются. Возможно ваш скрипт неопимизирован. Удаляет по одной строке и индексов на внешних ключах вообще нет?
сейчас скрипт работает в режиме теста, он забирает из базы 100 тысяч записей (на это уходит от 0 до 6 секунд) и в цикле проверяет каждую строку, если она удовлетворяет условиям - вызывается хранимая процедура(которую делал не я, а разработчики ПО) и удаляет передаваемое ей значение(id записи). Удаляет процедура и в нескольких взаимосвязанных таблицах, во всех них есть столбец с этим id который я передаю ей.
Тот результат что я давал удалил из 100 тысяч - 54356 записи только в основной таблице, в смежных таблицах ещё больше, ибо несколько таблиц много записей к одной id из основной.
Работал этот скрипт 64 минуты, скрипт на питоне.

alexeyvg, большое Вам спасибо за комментарии!

alexeyvgЕсли у вас модель полная, и нет бакапа лога, то разобраться с этим - первоочередная задача, потому что все изменения, которые вы делаете с данными (в том числе удаляемые старые данные) остаются в базе.
хм...у нас сейчас по понедельникам полный бекап, а каждый следующий день - разностный бекап. Этого не достаточно?


ещё я почитал несколько статей, тем и составил себе следующий план, прокомментируйте, пожалуйста, не ошибся ли я:
1.отключить шринк, совсем - сделано
2.в связи с отключением шринка посмотреть сколько будет весить журнал(лог), если он будет слишком огромный просчитать вариант перехода на 2-3 полных бекапа в неделю, вместо одного, либо вообще рассмотреть вариант отказа от разностного(дифференцированного) варианта и перейти на бекап лога (журнал транзакций).
3.завтра(в понедельник) сделать обновление статистики (exec sp_msforeachtable 'update statistics ?') и дефрагментацию индексов(exec sp_msforeachtable 'dbcc INDEXDEFRAG (BDname, ''?'')' и проверить как будет выполняться тот долгий запрос.
4.расчистить БД от ненужных данных (ориентирочно база должна уменьшится на 50-70%)
5.после окончания расчистки, сделать её ежедневной, что бы не копилось ненужное.
6.после окончания расчистки сделать переиндексацию (exec sp_msforeachtable 'dbcc dbreindex(''?'')')
7.настроить раз в неделю обновление статистики (exec sp_msforeachtable 'update statistics ?') (вопрос: её делать ПЕРЕД полным бекапом или ПОСЛЕ бекапа? )
8.воспользоваться скриптами для определения фрагментации индексов, и определиться нужно ли делать дефрагментацию индексов(exec sp_msforeachtable 'dbcc INDEXDEFRAG (BDname, ''?'')', либо просто раз в месяц поставить.

смотреть как работает БД, если проблемы с тормозами не решатся, то профайлером смотреть долгие запросы, их планы и разбираться с конкретными запросами, возможно, действительно не хватает каких-либо индексов.

вот пока всё.

ещё прочитал про очистку процедурного кэша (DBCC FREEPROCCACHE), но пока не понял, надо ли оно.

alexeyvgВ полной модели лог хранится вечно , он не удаляется дифф или полными бакапами, шринком и т.д.
Освободить место в файле лога можно только бакапом лога, и никак иначе.
Либо вообще ничего там не хранить, переведя базу с полной модели на простую.
я запутался) Теперь всё ясно. Лог мы не бекапили совсем, только база полностью + разностная.
Сегодня настроим бекап лога и посмотрим как будет очищаться. Вновь, спасибо большое за разъяснение.

Щукина АннаalexeyvgЛибо вообще ничего там не хранить, переведя базу с полной модели на простую.Судя по тому, как ТС притворяет рекомендации с форума в жизнь своей системы - не мешало бы уточнить, чем чревато держать базу в режиме Simple. Кроме того, ТС пару раз упоминал о зеркалировании. Непонятно, что за зеркалирование и на чем построено. Может там лог-шиппинг, несовместимый с простым режимом или ещё что-то....
простая модель нам не подойдёт, наш вариант зеркалирования только на полной работает. Про зеркалирование я пока вообще не скажу, но это рядом стоящий аналогичный сервер, куда зеркально копируется БД. Пару раз меняли их местами, если не ошибаюсь из-за гибели жёсткого диска, в тот момент я вообще не касался обслуживания, но ребята, на сколько я знаю просто меняют имя хоста, запускают какой-то скрипт и зеркало становится основным сервером прямо за считанные минуты.

за выходные скрипт по очистке бд отработал (пятница/сейчас):

rows: 22868517/21984800 (удалил 883717 записей)
reserved: 11132904/11185256 (увеличилось на 52352)
data: 2455856/2466800 (увеличилось на 10944)
index_size: 8669640/8698808 (увеличилось на 29168)
unused: 7408/19648 (увеличилось на 12240)
уточню, что это результаты только по основной таблице, помимо неё удаление происходит ещё в ~5 связанных таблицах.

Не совсем понимаю результаты. Почему reserved и data увеличились? Если данные удалились?!

Сейчас сделали обновление статистики (exec sp_msforeachtable 'update statistics ?') выполнялся 6 минут. После этого пропала проблема с timeout в программе с тем долгим запросом.
Сейчас запустили дефрагментацию (exec sp_msforeachtable 'dbcc INDEXDEFRAG (BDname, ''?'')) уже 20 минут, ждём. Вываливает статистику, есть и pages moved, есть и pages removed. Из того что вижу сейчас, примерно 1-3% removed в большинстве результатов.


Щукина АннаВо-первых, непонятно - что и как считает Ваш скрипт.
Сначала пишет в лог данные из запроса exec sp_spaceused N'table_name'
потом удаляет выборочно данные хранимой процедурой от разработчика ПО
в завершение своей работы снова пишет значения из exec sp_spaceused N'table_name', их я и цитирую.

Щукина АннаВо-вторых, никто не обещал, что освободившееся место после удаления данных будет использоваться для повторный вставок. Особенно, если у Ваших таблиц есть монотонно-растущий кластерный индекс, а процесс удаления данных - не освобождает страницы полностью, а лишь удаляет с них часть данных.
Понял, спасибо. Тогда просто продолжаем чистить, игнорируя цифры статистики, а БД сама разберётся. За дисковым пространством мы особо не гонимся, главное что бы быстро работало.

Щукина Анна, спасибо, всё больше и больше начинаю осознавать происходящее и выстраивать путь к совершенству ;)

К сожалению, дефрагментация (exec sp_msforeachtable 'dbcc INDEXDEFRAG (BDname, ''?'')) НЕ ВЫПОЛНИЛАСЬ! Примерно 50 минут делалась и выдало:
Произошла ошибка при выполнении пакетного файла. Сообщение об ошибке: Неустранимая ошибка подключения. Состояние ошибки: 15, Токен: 54

Щукина Аннаselect @@version
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 15:14:48 Copyright (c) Microsoft Standart Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Щукина АннаСразу напрашивается оптимизация - выбирать в обработку только те данные, что удовлетворяют условиям обработки, а не все подряд :).
То есть, перенести условия проверки из цикла обработки - в процесс отбора записей...
Изначально так и было, наоборот переделал под тот вариант, как сейчас. По 2 причинам:
1.хочу видеть статистику сколько записей было удалено (хотя это можно реализовать и с условием в sql)
2.когда я использовал условие в where запроса, сам запрос выполнялся слиииишком долго. Если выгрузить всё подряд от 0 до 6 секунд, то с условием выходило больше минуты.

Вероятно сейчас, после обновления статистики, будет лучше и быстрее с where. Но это не первоочередная задача.

Щукина АннаSerg58,
в целом - прогресс в сторону увеличения производительности системы наметился?
ДА! В основном помогло за 5 минут обновление статистики. Тот запрос, который вообще сподвиг меня заняться всем этим выполнялся 45 секунд, а программа прикладная ждёт всего 30 секунд и потом выдаёт timeout и собственно не работает. Мы отключали этот функционал и работали без него. Вот утром сегодня обновили статистику и сейчас этот запрос выполняется просто моментально.

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

Правильно я разобрался? схема следующая:
1. в ночь на понедельник делаем полное резервное копирование базы данных (Full Backup) (проверка целостности БД, обновление статистики, резервное копирование БД(полное), очистка плана обслуживания)
2. в каждый другой день делаем (проверка целостности БД, резервное копирование БД(разностное), резервное копирование БД (журнал транзакций), очистка после обслуживания)

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

TaPaKи скорее всего в индексы в не попадаете
Вот эту фразу не совсем понял.

fullscan статистики в ночь включу.