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

Откуда:
Сообщений: 9
Для небольшой БД (10 Гб), написал план обслуживания:

1. Backup БД
2. Проверка целостности БД
3. Rebuild/Reorganize таблицы индексов
4. Обновление статистики БД
5. Очистка процедурного кеша

Все это через management studio добавил в план обслуживания разово. Ранее с базой делались только бекапы.

Подскажите, как можно узнать степень дефрагментации индексов. Что скажите про мой план вообще?
7 мар 13, 13:32    [14024379]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
3. для всех индексов, даже для тех, которым не нужно обновление ?
4. автообновление выключено ?
5. зачем ?
7 мар 13, 13:34    [14024391]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
В общем, сам я только осваиваю СУБД, а обслуживать базу данных надо) План написал исходя из статей инета.
3. Дополнение: надо делать именно реорганизацию, чтобы не было простоя доступа к БД (в базу постоянно пишется небольшой поток данных). Как выяснить каким индексам нужно это обновление? И всем ли таблицам? стандартным таблицам тоже?
4. Не проверял. Сейчас проверю.
5. По рекомендациям интернета после всех операций надо кеш чистить.

По рекомендуйте, пожалуйста, как с умом подойти к вопросу обслуживания БД
7 мар 13, 14:15    [14024606]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
3. просмотр на наличие фрагментации индексов таблицы
DBCC SHOWCONTIG ('tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS;
7 мар 13, 14:21    [14024655]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
4. Автообновление статистики, для выбранных баз включено. Получается не нужно делать? А для основных баз?
7 мар 13, 14:21    [14024656]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
arturv2011
4. Автообновление статистики, для выбранных баз включено. Получается не нужно делать? А для основных баз?


Хуже не будет, но это время...

Что значит постоянно идет поток данных ? И ночью ? Нет технологического окна ?

Скрипт для выборочной реиндексации\реоганизации.

/*Использование представления sys.dm_db_index_physical_stats в скрипте для перестроения или реорганизации индексов

В следующем примере автоматически реорганизуются или перестраиваются все секции в базе данных со средней степенью 
фрагментации более 10 процентов.Для выполнения этого запроса необходимо разрешение VIEW DATABASE STATE.
В данном примере в качестве первого параметра указывается DB_ID без определения имени базы данных.
Если уровень совместимости текущей базы данных составляет 80 или ниже, будет сформирована ошибка.
Чтобы исправить эту ошибку, замените вызов функции DB_ID() действительным именем базы данных.
Дополнительные сведения об уровнях совместимости баз данных см. 
в разделе Уровень совместимости инструкции ALTER DATABASE (Transact-SQL).
http://msdn.microsoft.com/ru-ru/library/ms188917.aspx
*/

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
7 мар 13, 14:40    [14024786]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
Спасибо, а можно это сделать средствами managment studio? Все-таки, выполняя скрипт по коду, надо хорошо знать и понимать синтаксис и назначение всех команд, а я новичок еще) На начальном пути, так сказать...
7 мар 13, 16:11    [14025206]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
arturv2011
Спасибо, а можно это сделать средствами managment studio? Все-таки, выполняя скрипт по коду, надо хорошо знать и понимать синтаксис и назначение всех команд, а я новичок еще) На начальном пути, так сказать...


нет, такого функционала нет в ssms
7 мар 13, 16:31    [14025290]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Konst_One
3. просмотр на наличие фрагментации индексов таблицы
DBCC SHOWCONTIG ('tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS;
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sys.dm_db_index_physical_stats instead.
7 мар 13, 22:11    [14026335]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
arturv2011
Спасибо, а можно это сделать средствами managment studio? Все-таки, выполняя скрипт по коду, надо хорошо знать и понимать синтаксис и назначение всех команд, а я новичок еще) На начальном пути, так сказать...
SQL Server Index and Statistics Maintenance by Ola Hallengren
7 мар 13, 22:14    [14026352]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
arturv2011,

Почитайте, возможно Вам может пригодиться.
Там есть две процедуры по обслуживанию индексов
dbatools.sp_smart_cluster_reindex
dbatools.sp_smart_reindex


Они достаточно интеллектуальны. Так же можно воспользоваться и функциями для просмотра степени фрагментации по индексам.
dbatools.fn_cluster_index_for_smart_rebuild
dbatools.fn_index_for_smart_rebuild
7 мар 13, 22:35    [14026408]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
Спасибо всем за подсказки))

По поводу своего плана:

1. Backup БД
2. Проверка целостности БД
3. Реорганизация таблицы индексов
4. Обновление статистики БД (нужно ли, если в необходимых базах включено автообновление статистики?)
5. Очистка процедурного кеша.

Правильный ли порядок обслуживания? Информацию по данному плану вычитал на страницах интернета? А как вы обслуживаете свои базы данных?
11 мар 13, 07:52    [14033383]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
arturv2011
4. Обновление статистики БД (нужно ли, если в необходимых базах включено автообновление статистики?)
5. Очистка процедурного кеша.
4. не повредит
5. зачем?
11 мар 13, 09:43    [14033582]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
arturv2011
Спасибо всем за подсказки))

По поводу своего плана:

1. Backup БД
2. Проверка целостности БД
3. Реорганизация таблицы индексов
4. Обновление статистики БД (нужно ли, если в необходимых базах включено автообновление статистики?)
5. Очистка процедурного кеша.

Правильный ли порядок обслуживания? Информацию по данному плану вычитал на страницах интернета? А как вы обслуживаете свои базы данных?

1) ну вообще-то гораздо логичнее сначала проверять БД на целостность, а уже потом делать её бэкап
2) ты что за истину в последней инстанции воспринимаешь всё что в инете написано ? У тебя уже спросили зачем ты чистишь процедурный кэш ?
11 мар 13, 09:50    [14033602]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
А на что еще полагаться, если нет опыта? Вот и нагуглил... Цитирую, например: "Частота очистки процедурного КЭШа (буфер плана запроса) должна совпадать с частотой обновления статистики. Так как MS SQL кэширует планы запроса для их повторного выполнения, это делается для экономии времени. И вполне возможна такая ситуация, когда после обновление статистики в КЭШе останется устаревшая информация о плане запроса, что приведет, к неоптимальном его выполнении."
11 мар 13, 12:51    [14034795]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
arturv2011
А на что еще полагаться, если нет опыта? Вот и нагуглил... Цитирую, например: "Частота очистки процедурного КЭШа (буфер плана запроса) должна совпадать с частотой обновления статистики. Так как MS SQL кэширует планы запроса для их повторного выполнения, это делается для экономии времени. И вполне возможна такая ситуация, когда после обновление статистики в КЭШе останется устаревшая информация о плане запроса, что приведет, к неоптимальном его выполнении."


автор
4. Автообновление статистики, для выбранных баз включено.


Отсюда делаем вывод, что кэш постоянно надо сбрасывать ? :)
11 мар 13, 12:54    [14034815]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
Хотите сказать, что раз включено автообновление статистики, в очистке процедурного кеша нет необходимости что ли? Сам что ли чистится?)) Или его очистка никоим образом не повлияет на производительность БД?)
11 мар 13, 12:57    [14034825]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
arturv2011
Хотите сказать, что раз включено автообновление статистики, в очистке процедурного кеша нет необходимости что ли? Сам что ли чистится?)) Или его очистка никоим образом не повлияет на производительность БД?)

Я хочу сказать, что это ближе к экстренным операциям, а не к плановым :)
11 мар 13, 13:01    [14034849]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
а реорганизацию советуют делать раз в неделю) это как вы считаете? норма?
11 мар 13, 13:20    [14034984]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Был такой Ералаш: Мальчик заходит в автобус. Покупает один билет, затем второй. бабулька спрашивает типа, зачем тебе второй билет, мальчик ?
М: Ну как, а если первый потеряю.
Б: А если и второй потеряешь:
М: на этот случай у меня есть проездной.
Я к тому, что если реально с планами, будут возникать проблемы, то можно и сделать. Но опять же, этого не должно быть в стандартной ситуации, имхо.
11 мар 13, 13:24    [14035008]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
arturv2011
Хотите сказать, что раз включено автообновление статистики, в очистке процедурного кеша нет необходимости что ли? Сам что ли чистится?)) Или его очистка никоим образом не повлияет на производительность БД?)

а зачем его чистить? если статистика изменилась и сервер обнаружил, что предыдущий план не оптимален, происходит перекомпиляция. а неиспользуемые планы из кеша постепенно вытесняются
11 мар 13, 13:24    [14035010]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
arturv2011
а реорганизацию советуют делать раз в неделю) это как вы считаете? норма?


Ну опять же, везде надо смотреть конкретную ситуацию. Кому то и раз в месяц нормально, кому то раз в сутки или чаще. Поставьте пока раз в сутки и не все индексы, а только не обходимые. А вообще, надо наблюдать и подбирать оптимально именно в Вашей ситуации. Нет универсального рецепта.
11 мар 13, 13:26    [14035017]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
Shakill
arturv2011
Хотите сказать, что раз включено автообновление статистики, в очистке процедурного кеша нет необходимости что ли? Сам что ли чистится?)) Или его очистка никоим образом не повлияет на производительность БД?)

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


Ну бывают ситуации, особенно при серьезных обновлениях и сброс помогает. Но опять же, это разовая, а не плановая операция.
11 мар 13, 13:27    [14035023]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
arturv2011
Member

Откуда:
Сообщений: 9
Понятно) Благодарю) А как вычислить индексы, нуждающиеся в реорганизации? И что будет, если я данную процедуру назначу для всей базы?
11 мар 13, 13:44    [14035125]     Ответить | Цитировать Сообщить модератору
 Re: Обслуживание БД  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
arturv2011
Понятно) Благодарю) А как вычислить индексы, нуждающиеся в реорганизации? И что будет, если я данную процедуру назначу для всей базы?

Вам выше предлагали варианты.
В этой процедуре они уже вычисляются, посмотрите условие

30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

Но вообще, возьмите в привычку, все сначала на тестовой базе (лучше на тестовом сервере) прогонять.

Если накатите ее то он реорганизует индексы с фрагментацией меньше 30, перестроит с фрагментацией больше 30
11 мар 13, 13:50    [14035171]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить