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

Откуда:
Сообщений: 424
Есть некоторые тумманые знания что нужно что-то делать с базой. Но хотелось бы осознанно делать то что нужно и когда нужно. Соответственно надо уметь диагностировать проблему и уметь устранить её.
Пока выяснил что нужно делать:
1. Обновление статистики
2. Дефрагментация индексов
3. Реиндексация таблиц
Скорей всего нужно ещё что-то делать. Подскажите.
Начнем с обновления статистики. Как определить когда нужно её обновлять?
Пока нашёл что можно использовать
exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'
И есть рекомендации делать это раз в сутки.
По дефрагментации как определить когда пора дефрагментировать? Почему только индексы, таблицы тоже фрагментируются?
Делаю вот так
sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'
Как часто надо делать непонятно.
7 ноя 14, 11:20    [16810590]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Glory
Member

Откуда:
Сообщений: 104751
leonix
Пока выяснил что нужно делать:

Откуда вы выяснили, что это нужно конкретно вам ?
У вас сильная дефрагментация ?

leonix
Скорей всего нужно ещё что-то делать. Подскажите.

Прежде всего нужно делать бэкапы

leonix
И есть рекомендации делать это раз в сутки.

От кого рекомендация?

leonix
Пока нашёл что можно использовать
exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

Откройте для себя MaintenancaPlan

leonix
Делаю вот так
sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'
Как часто надо делать непонятно.

BOL - Reorganizing and Rebuilding Indexes
7 ноя 14, 11:27    [16810633]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
leonix
Начнем с обновления статистики. Как определить когда нужно её обновлять?
И есть рекомендации делать это раз в сутки.

Обновлять статистики нужно индивидуально для каждого объекта исходя из того, как часто в таблице меняются данные (какой %...автоматическое обновление статистики использует 20%+500)


leonix
По дефрагментации как определить когда пора дефрагментировать? Почему только индексы, таблицы тоже фрагментируются?
Как часто надо делать непонятно.

та же история...всё индивидуально...смотрите: sys.dm_db_index_physical_stats
кроме того...дефрагментация может быть перманентна для каких-то индексов и бороться не имеет смысла, только систему нагружать...

leonix
Почему только индексы, таблицы тоже фрагментируются?

кластерный индекс - это и есть таблица, с кучами немного другая история
7 ноя 14, 11:31    [16810663]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Есть некоторые тумманые знания что нужно что-то делать с базой.

Прежде всего настройте резервное копирование баз.

1. Обновление статистики
2. Дефрагментация индексов
3. Реиндексация таблиц


Это следует выполнять только, если требуется. Как правило, требуется только п.1, но на SQL 2014, например, и это не требуется, если обновление статистик автоматическое. Обновляйте статистики, если таблицу огромные, а добавляемый изменяемый объем данных небольшой и принципиально разнородный.
7 ноя 14, 12:39    [16811194]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
leonix
Member

Откуда:
Сообщений: 424
Извиняюсь, забыл добавить про резервное копирование.
Бэкапы это святое. С этим разобрался в первую очередь.
Полная модель восстановления. В случае краха базы и лога, потери будут максимум за 5 минут работы пользователей.
Настроил фулл каждый день, дифф через 2 часа и лог через каждые 5 минут.
Бэкапы тестировал и себе восстанавливаю почти каджый день по несколько раз на определенный момент времени.
7 ноя 14, 12:45    [16811245]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
leonix
Member

Откуда:
Сообщений: 424
Glory
leonix
Пока выяснил что нужно делать:

Откуда вы выяснили, что это нужно конкретно вам ?
У вас сильная дефрагментация ?


Неправильно выразился. Возможно нужно мне делать. Как определить надо ли делать?

Glory
leonix
Скорей всего нужно ещё что-то делать. Подскажите.

Прежде всего нужно делать бэкапы


Делается.

Glory
leonix
И есть рекомендации делать это раз в сутки.

От кого рекомендация?

Гдето на просторах интернета встречал. А на этом форуме хотел бы уточнить.

Glory
leonix
Пока нашёл что можно использовать
exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'

Откройте для себя MaintenancaPlan

Чем плоха эта команда? Про планы обслуживания я знаю.

автор
leonix
Делаю вот так
sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'
Как часто надо делать непонятно.

BOL - Reorganizing and Rebuilding Indexes

Спасибо. Почитаю.
7 ноя 14, 13:21    [16811663]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
leonix
Member

Откуда:
Сообщений: 424
Knyazev Alexey
leonix
Начнем с обновления статистики. Как определить когда нужно её обновлять?
И есть рекомендации делать это раз в сутки.

Обновлять статистики нужно индивидуально для каждого объекта исходя из того, как часто в таблице меняются данные (какой %...автоматическое обновление статистики использует 20%+500)

Наверно это можно автоматизировать? Не вручную же каждый день анализировать это.


автор
leonix
По дефрагментации как определить когда пора дефрагментировать? Почему только индексы, таблицы тоже фрагментируются?
Как часто надо делать непонятно.

та же история...всё индивидуально...смотрите: sys.dm_db_index_physical_stats
кроме того...дефрагментация может быть перманентна для каких-то индексов и бороться не имеет смысла, только систему нагружать...


Вот похоже этот скрип подходит.

-- 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


Как часто его запускать? Если каждый день по ночам, это нормально будет?

автор
leonix
Почему только индексы, таблицы тоже фрагментируются?

кластерный индекс - это и есть таблица, с кучами немного другая история
[/quote]

А что с кучами делать? Они же тоже фрагментируются.
7 ноя 14, 13:34    [16811777]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Какая разница - фрагментирована куча или нет. Также никакой нет разницы и с индексами, но по другой причине.
7 ноя 14, 14:05    [16812012]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
leonix
Member

Откуда:
Сообщений: 424
Владислав Колосов
Какая разница - фрагментирована куча или нет. Также никакой нет разницы и с индексами, но по другой причине.


Для меня фрагментация это когда данные раскиданы далеко друг от друга по разным страницам и соотв. серверу требуется больше манипуляций для считывания данных. А если дефрагментировать, то всё будет рядышком и считывается будет быстрее.
7 ноя 14, 14:08    [16812041]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Glory
Member

Откуда:
Сообщений: 104751
leonix
Для меня фрагментация это когда данные раскиданы далеко друг от друга по разным страницам и соотв. серверу требуется больше манипуляций для считывания данных.

И у вас действительно присутствует дефрагментация ? Причем в такой степени, что реально заставлеяет сервер делать больше манипуляций ?
7 ноя 14, 14:11    [16812052]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
leonix
Member

Откуда:
Сообщений: 424
Glory
leonix
Для меня фрагментация это когда данные раскиданы далеко друг от друга по разным страницам и соотв. серверу требуется больше манипуляций для считывания данных.

И у вас действительно присутствует дефрагментация ? Причем в такой степени, что реально заставлеяет сервер делать больше манипуляций ?


Не знаю. Как можно узнать если ли такая проблема у меня?
7 ноя 14, 14:21    [16812120]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Glory
Member

Откуда:
Сообщений: 104751
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 index_id > 0;
7 ноя 14, 14:27    [16812167]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Владислав Колосов
Как правило, требуется только п.1, но на SQL 2014, например, и это не требуется, если обновление статистик автоматическое.
Это вы о чем простите? В 2014 уже придумали опцию all_statistics_is_always_updated = 1?
7 ноя 14, 21:19    [16814336]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
Mind
Владислав Колосов
Как правило, требуется только п.1, но на SQL 2014, например, и это не требуется, если обновление статистик автоматическое.
Это вы о чем простите? В 2014 уже придумали опцию all_statistics_is_always_updated = 1?


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


У вас опечатка: all_statistics_are_always_updated
7 ноя 14, 22:28    [16814655]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
a_voronin
У вас опечатка: all_statistics_are_always_updated
Я плохо читал документацию
8 ноя 14, 00:38    [16815164]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8839
Mind
Владислав Колосов
Как правило, требуется только п.1, но на SQL 2014, например, и это не требуется, если обновление статистик автоматическое.
Это вы о чем простите? В 2014 уже придумали опцию all_statistics_is_always_updated = 1?

Там же допилили анализатор обновления статистик как раз для старых много - новых мало. Или я пропустил что-то?
9 ноя 14, 16:46    [16818427]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Владислав Колосов
Member

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

в самом плохом случае,который можно создать только искусственно, заполнение страниц будет 50%, соответственно, сервер произведет вдвое больше логических чтений при полном сканировании таблицы. Но это уж очень запущенный случай.
Разницы нет - в каком порядке находятся страницы, если оборудование подобрано правильно. в этом случае попаданий в кэш оперативной памяти будет 99.8% и не будет никакой разницы во времени доступа к разным данным.

Гораздо важнее своевременное обновление статистик, например, после ежемесячных расчетов счетов и т.п.
9 ноя 14, 16:51    [16818438]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
leonix
Не знаю. Как можно узнать если ли такая проблема у меня?
Посмотреть в лог телефона и/или электронную почту.
Были звонки от потребителей/пользователей с жалобами - есть проблема. Не было звонков - не проблемы.
У меня индексы по таблице с 2.000.000.000 записей с фрагментацией больше 90% есть - а проблемы нет. ;)
11 ноя 14, 06:07    [16825898]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
DeColo®es
leonix
Не знаю. Как можно узнать если ли такая проблема у меня?
Посмотреть в лог телефона и/или электронную почту.
Были звонки от потребителей/пользователей с жалобами - есть проблема. Не было звонков - не проблемы.
У меня индексы по таблице с 2.000.000.000 записей с фрагментацией больше 90% есть - а проблемы нет. ;)

Какие то странные советы дают старожилы новичкам...

А я масло в машине не меняю уже 5 лет. Непонятно зачем его рекомендуют менять каждые 10 тыщ пробега, проблемы то нет! Вот как начнутся проблемы, тогда и поменяю.
12 ноя 14, 21:19    [16836976]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Mind
Какие то странные советы дают старожилы новичкам...
А я масло в машине не меняю уже 5 лет. Непонятно зачем его рекомендуют менять каждые 10 тыщ пробега, проблемы то нет! Вот как начнутся проблемы, тогда и поменяю.


да тут в другом смысл...

Дефрагментация - это плоха? ДА!
Всегда ли нужно бороться с ней и на всех объектах? А вот не факт!
13 ноя 14, 08:30    [16837771]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Knyazev Alexey
Дефрагментация - это плоха? ДА!


по Фреду...Фрагментация, конечно же
13 ноя 14, 08:41    [16837795]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Knyazev Alexey
Mind
Какие то странные советы дают старожилы новичкам...
А я масло в машине не меняю уже 5 лет. Непонятно зачем его рекомендуют менять каждые 10 тыщ пробега, проблемы то нет! Вот как начнутся проблемы, тогда и поменяю.


да тут в другом смысл...

Дефрагментация - это плоха? ДА!
Всегда ли нужно бороться с ней и на всех объектах? А вот не факт!
Т.е. вы хотите сказать, что Best practice: никогда не дефрагментируйте индексы пока не выясните что фрагментация является проблемой?

DeColo®es
Посмотреть в лог телефона и/или электронную почту.
Были звонки от потребителей/пользователей с жалобами - есть проблема. Не было звонков - не проблемы.
Если это была не шутка, то админов, которые узнают о проблемах только от пользователей, вообще нужно выгонять...
13 ноя 14, 21:57    [16843019]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Mind
Best practice: никогда не дефрагментируйте индексы пока не выясните что фрагментация является проблемой?


опять всё перековеркали...
фрагментация - это всегда плохо, но делать её нужно не на всех объектах!
Некоторые объекты в нормальном состоянии всегда фрагментированы...и накладные расходы на дефрагментацию могут вывести систему из привычного равновесия...после чего объект за короткое время опять станет сильно фрагментирован...
вот в таких случаях просто нет смысла в их дефрагментации
14 ноя 14, 10:02    [16844256]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
leonix
Member

Откуда:
Сообщений: 424
Зайдём с другой стороны.
Если бы вам предложили провести аудит БД на качество проведения регламентных процедур, то что бы вы смотрели?
13 янв 15, 15:19    [17111416]     Ответить | Цитировать Сообщить модератору
 Re: Какие регламентные процедуры необходимо выполнять с БД и когда?  [new]
Glory
Member

Откуда:
Сообщений: 104751
leonix
Если бы вам предложили провести аудит БД на качество проведения регламентных процедур, то что бы вы смотрели?

На предварительно составленные и утвержденные критерии, которые должны быть достигнуты этими регламентными процедурами.
13 янв 15, 16:16    [17111881]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить