Вход | Регистрация

Информационные технологии ::

Метки: 

sql server тупит после плана обслуживания

Я
   onetone
 
15.02.18 - 21:38
Всем привет, в общем, проблема описана в САБЖе.

Есть план обслуживание, выполняющие, перестроение, фрагментацию индексов, а так же проверка базы данных, бэкап и отправка письма. Так вот, после этого плана обслуживание, 1С на след день жутко тупит(план выполняется в 3 ночи), достаточно сделать рестарт sql service, и все начинает летать, или отключаешь план обслуживание, тоже все начинает работать стабильно.

Подскажите куда копать?
 
  Рекламное место пустует
   NorthWind
 
1 - 15.02.18 - 21:50
убирайте из плана задачи по одной такие, которые касаются непосредственно БД. И смотрите, что влияет. Еще полезно понять, не происходят ли какие-то операции слишком долго, а то, может, у вас просто что-то не завершается к утру, потому база и тупит. Перенесите на пораньше, посмотрите какой даст эффект.
   onetone
 
2 - 15.02.18 - 21:51
(1) Задачи убрать попробую, план точно заканчивает выполнение, так как приходит письмо об успешном выполнении
   Cyberhawk
 
3 - 15.02.18 - 21:52
   Cyberhawk
 
4 - 15.02.18 - 21:52
Заодно расскажешь нам во сколько обошлось и какие результаты
   onetone
 
5 - 15.02.18 - 22:48
(3) СУпер совет
   onetone
 
6 - 15.02.18 - 22:49
(4) Я тогда уж лучше просто бантик напишу с рестартом sql. Надеялся что кто то сталкивался с подобным.
   Fram
 
7 - 15.02.18 - 22:51
план то покажешь?.. или нам опять телепатов надо включать?
   onetone
 
8 - 15.02.18 - 22:56
https://yadi.sk/i/IhuW0FXT3SSjcD

-- Параметры скрипта
declare @database_names as nvarchar(max) = N''; -- имена баз задавать через запятую, если не заданы, то все несистемные базы
                                          -- пока парсер примитивный - строка просто делится по запятым и обрезаются крайние пробелы
                                          -- (если в имени базы будет запятая или в начале или конце имени пробел, то система не работает)
                                          -- если указано "-ИмяБазы", то база будет исключена,
declare @index_size_threshhold as int = 1024;   -- минимальный размер в КБ для перестраиваемого индекса. Нет смысла перестраивать индексы на десяток страниц
declare @index_rebuild_threshhold as numeric(5,2) = 25; -- показатель фрагментации, начиная с которого происходит перестроение индекса
declare @index_defrag_threshhold as numeric(5,2) = 12;  -- показатель фрагментации, начиная с которого происходит дефрагментация индекса
declare @index_rebuild_space_used_threshhold as numeric(5,2) = 50; -- процент заполненности страниц меньше которого требуется перестроение индекса
declare @timeout as int = 7200; -- максимальное время работы скрипта
declare @max_size as bigint = 536870912; -- максимальный суммарный обрабатываемый размер в КБ (чтобы не нагенерировать логов на терабайты) -- 512*1024*1024 КБ = 0,5 ТБ
declare @is_emulate as bit = 0; -- 0 - выполнять, 1 - только вывести команды

set nocount on;
use master;

declare @indexes as table (
    database_name nvarchar(128) not null,
    schema_name nvarchar(128) not null,
    table_name nvarchar(128) not null,
    index_name nvarchar(128) not null,
    is_clustered bit not null,
    size_kb bigint not null,
    fragmentation numeric(5,2) not null
    );
declare @database_names_table as table (
    name nvarchar(128) not null primary key
    );
if object_id('tempdb..#index_stats') is not null
    drop table #index_stats;
create table #index_stats (
    database_id smallint not null,
    object_id int not null,
    index_id int not null,
    index_type_desc nvarchar(60) not null,
    avg_fragmentation_in_percent float not null,
    page_count bigint not null,
    avg_page_space_used_in_percent float not null,
    record_count bigint not null,
    index_name nvarchar(128),
    table_name nvarchar(128),
    schema_name nvarchar(128),
    db_name nvarchar(128)
    )

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Поиск баз данных для обслуживания'
declare @timeout_datetime datetime = dateadd(second, @timeout, getdate());


-- Создание списка обслуживаемых БД по @database_names
with database_name_table(database_names_tail, database_name) as
    (
        select
            substring(@database_names, nullif(charindex(',', @database_names, 1), 0) + 1, len(@database_names) + 1),
            rtrim(ltrim(left(@database_names, isnull(nullif(charindex(',', @database_names, 1), 0) - 1, len(@database_names)))))
        where
            @database_names is not null
        union all
        select
            substring(database_names_tail, nullif(charindex(',', database_names_tail, 1), 0) + 1, len(database_names_tail) + 1),
            rtrim(ltrim(left(database_names_tail, isnull(nullif(charindex(',', database_names_tail, 1), 0) - 1, len(database_names_tail)))))
        from database_name_table db
        where
            database_names_tail is not null
    ),
database_names_with_indicator(database_name, indicator) as
    (
        select
            db_name(db_id(case when database_name like '-%' then rtrim(ltrim(substring(database_name, 2, len(database_name)))) else database_name end)),
            case when database_name like '-%' then 1 else 0 end
        from database_name_table db
        where db_name(db_id(case when database_name like '-%' then rtrim(ltrim(substring(database_name, 2, len(database_name)))) else database_name end)) is not null
    )
insert @database_names_table (name)
select name
from sys.databases db
where
db.name not in ('master', 'model', 'tempdb', 'msdb') -- системные базы данных обычно не требуется переиндексировать
and db.name not in (select dbi.database_name from database_names_with_indicator dbi where indicator = 1)
and ((select top 1 dbi.database_name from database_names_with_indicator dbi where indicator = 0) is null or
    db.name in (select dbi.database_name from database_names_with_indicator dbi where indicator = 0))
;
print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- найдено ' + convert(nvarchar(max), @@rowcount) + ' баз данных для обслуживания'

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Поиск индексов для обслуживания'
-- курсором обходим выбранные БД и ищем индексы и данные по их фрагментации
declare @database_cursor as cursor;
declare @current_database as nvarchar(128);
set @database_cursor = cursor forward_only for
select name from @database_names_table;
open @database_cursor;
fetch @database_cursor into @current_database;

while (@@FETCH_STATUS = 0)
begin
    
    insert #index_stats
        (database_id, object_id, index_id, index_type_desc, avg_fragmentation_in_percent,
        page_count, avg_page_space_used_in_percent, record_count)
    select
        database_id, object_id, index_id, index_type_desc, max(avg_fragmentation_in_percent),
        sum(page_count), sum(avg_page_space_used_in_percent*page_count)/isnull(nullif(sum(page_count),0),1), sum(record_count)
    from
        sys.dm_db_index_physical_stats( db_id(@current_database), null, null, null, 'DETAILED') ips
    where
        ips.index_id>0 -- убираем кучи (heap)
        and ips.index_type_desc in (N'CLUSTERED INDEX', N'NONCLUSTERED INDEX') -- всякие хитрые индексы не обрабатываем
        and ips.alloc_unit_type_desc = N'IN_ROW_DATA' -- обрабатываем только по "обычным" записям
        and ips.index_level = 0
    group by database_id, object_id, index_id, index_type_desc
    having sum(page_count)*8 >= @index_size_threshhold

    exec ('use [' + @current_database + '];
    update i
    set
        i.db_name = db_name(),
        i.table_name = t.name,
        i.schema_name = s.name,
        i.index_name = ci.name
    from #index_stats i
    left join sys.tables t on i.object_id = t.object_id
    left join sys.schemas s on t.schema_id = s.schema_id
    left join sys.indexes ci on i.object_id = ci.object_id and i.index_id = ci.index_id
    where
    i.database_id = db_id();');

    fetch @database_cursor into @current_database;
end;

use master;
close @database_cursor;
deallocate @database_cursor;

declare @WithOptionsRebuild nvarchar(100) = 'WITH (SORT_IN_TEMPDB = ON); '; -- в Enterprise/Developer можно добавить в скобки ", ONLINE = ON"

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Обработка найденных индексов'
-- Курсором обходим выбранные индексы и ищем те, которые надо обслуживать в порядке убывания размера (без упорядочнивания по БД!)
declare @index_cursor as cursor;
set @index_cursor = cursor forward_only for
select
    'ALTER INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] ' +
    case
        when @index_rebuild_threshhold <= i.avg_fragmentation_in_percent then 'REBUILD ' + @WithOptionsRebuild
        when @index_rebuild_space_used_threshhold >= i.avg_page_space_used_in_percent then 'REBUILD ' + @WithOptionsRebuild
        when @index_defrag_threshhold <= i.avg_fragmentation_in_percent then 'REORGANIZE '
    end sql_command,
    case -- оценка влияния на журнал транзакций (неточная!)
        when @index_rebuild_threshhold <= i.avg_fragmentation_in_percent then i.page_count*8
        when @index_rebuild_space_used_threshhold >= i.avg_page_space_used_in_percent then i.page_count*8
        when @index_defrag_threshhold <= i.avg_fragmentation_in_percent then i.page_count*8*4*i.avg_fragmentation_in_percent/100
    end size
from #index_stats i
where
    @index_rebuild_threshhold <= i.avg_fragmentation_in_percent or
    @index_defrag_threshhold <= i.avg_fragmentation_in_percent or
    @index_rebuild_space_used_threshhold >= i.avg_page_space_used_in_percent
order by i.page_count desc

declare @database_id as smallint;
declare @object_id as int;
declare @index_id as int;
declare @partition_number as int;
declare @sql nvarchar(max);
declare @size numeric(20,4);

open @index_cursor;
fetch @index_cursor into @sql, @size;

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Начало обновления индексов'

while (@@FETCH_STATUS = 0)
begin
    set @max_size = @max_size - @size;

    print '';
    print '-- ' + convert(nvarchar(max), getdate(), 121);
    print @sql;
    print '-- Размер индекса: ' + cast(@size as nvarchar(max));
    print '-- Остаток @max_size: ' + cast(@max_size as nvarchar(max));

    if (@is_emulate = 0)
        exec(@sql);

    if (@timeout_datetime<getdate())
    begin
        print '-- Выполнение прекращено по таймауту!';
        break;
    end;
    if (@max_size<0)
    begin
        print '-- Достигнут предел обслуживаемого размера, выполнение прекращено!';
        break;
    end;
        
    fetch @index_cursor into @sql, @size;
end;

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Окончание обновления индексов'

close @index_cursor;
deallocate @index_cursor;

-- обновление частотных статистик
declare @dbstat_cursor as cursor;
set @dbstat_cursor = cursor forward_only for
select 'use [' + d.name + ']; exec sp_updatestats @resample = ''resample'';'
from @database_names_table d

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Начало обновления частотных статистик'

open @dbstat_cursor;
fetch @dbstat_cursor into @sql;

while (@@FETCH_STATUS = 0)
begin
    
    print '';
    print '-- ' + convert(nvarchar(max), getdate(), 121);
    print @sql;
        
    if (@is_emulate = 0)
        exec(@sql);

    if (@timeout_datetime<getdate())
    begin
        print '-- Выполнение прекращено по таймауту!';
        break;
    end;
    fetch @dbstat_cursor into @sql;
end;

print '-- ' + convert(nvarchar(max), getdate(), 121) + ' -- Окончание обновления частотных статистик'

if (@is_emulate = 1)
  select * from #index_stats i order by i.avg_fragmentation_in_percent desc
  
  
drop table #index_stats;

это из t-sql. остальное видно на скрине
   Fram
 
9 - 15.02.18 - 23:47
(8) скрипт точно рабочий?
1С сервер на том же сервере?
максимальная память ограничена у sql?
проверка целостности средствами sql вам точно нужна?
   Fram
 
10 - 16.02.18 - 00:00
 
  Рекламное место пустует
   onetone
 
11 - 18.02.18 - 02:40
(9)  точно рабочий
Нет
Да
Нужна
   onetone
 
12 - 18.02.18 - 02:40
(10) в скрипте тоже самое,  только с условиями.
   Fram
 
13 - 18.02.18 - 03:25
(12) (1) пробовал уже?
   mexanik_96
 
14 - 18.02.18 - 10:37
(0) у тебя из кеша вытесняются страницы часто используемые, и сбрасываются на диск. чтобы это проверить после плана обслуживания смотри на утро в мониторе ожидания, вангую что там латчи будут(поднятие страниц с диска)
   vde69
 
15 - 18.02.18 - 10:55
1. полное перестроение индекса не нужно выполнять часто, поставь раз в неделю
2. последней операцией поставь обновление статистики
   ildary
 
16 - 18.02.18 - 14:10
(15) извините что встреваю - получается надо делать два задания - одно - раз в неделю (полное перестроение индекса), а второе - каждую ночь (все остальные задачи)?
   vde69
 
17 - 18.02.18 - 14:46
(16) вообще делают три задания
1. дневное (например раз в час, два), в него например диф бекап, может быть обновление статистики
2. ночное (ежедневный бекап) и т.д.
3. недельный/месячный индексы, шринки короче всякая оптимитизация...
   Cool_Profi
 
18 - 18.02.18 - 14:52
(17) А статистику обновлять будет Есенин? Таки я вам скажу - Есенин её обновлять не будет, он уже давно умер...


Список тем форума
Рекламное место пустует   Рекламное место пустует
Кaк может человек ожидaть, что его мольбaм о снисхождении ответит Тот, кто превыше, когдa сaм он откaзывaет в милосердии тем, кто ниже его? Петр Трубецкой
ВНИМАНИЕ! Если вы потеряли окно ввода сообщения, нажмите Ctrl-F5 или Ctrl-R или кнопку "Обновить" в браузере.
Тема не обновлялась длительное время, и была помечена как архивная. Добавление сообщений невозможно.
Но вы можете создать новую ветку и вам обязательно ответят!
Каждый час на Волшебном форуме бывает более 2000 человек.
Рекламное место пустует