Comments 35
А исходная проблема-то разрешилась? Т.е. обновление статистики не помогало, а теперь всё летает?
хотел бы вас попросить сопровождение, на двух бд прошло успешно
на одной словил ошибку
Executing query "SET QUOTED_IDENTIFIER ON; if DB_ID('profiler') ...".: 100% complete End Progress Error: 2024-12-07 08:57:34.52 Code: 0xC002F210 Source: Execute T-SQL Statement Task 1 Execute SQL Task Description: Executing the query "DECLARE @FramentationReportTable_1 VARCHAR(255) ..." failed with the following error: "Incorrect syntax near 'bad_indexes_1'.".
Можно имя БД?
GoldenHome_Treasure_NEW_2012
В базе профайлер таблицы с именами такого вида Fragmentation_GoldenHome_Treasure_NEW_2012дата ?
Вот этот запрос выдает результат или ошибку?
DECLARE @FramentationReportTable_1 VARCHAR(255)
SET @FramentationReportTable_1 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES WHERE table_Name LIKE 'Fragmentation_GoldenHome_Treasure_NEW_2012'+REPLACE(convert(varchar, getdate(), 102), '.', '_') + '%')
print @FramentationReportTable_1
exec ('
select
frag.db_name,
frag.schema_name,
frag.table_name,
frag.index_name,
frag.partition_num,
case
when frag.record_size16 <= 403 then 95 when frag.record_size16 <= 806 then 90
when frag.record_size*16 <= 1209 then 85
else 80
end as suggested_fillfactor
from Profiler.dbo.'+ @FramentationReportTable_1 +' frag
where frag.page_count > 24 and frag.fragmentation >= 5 and frag.num = 1 and frag.index_type <>''HEAP''
order by frag.fragmentation desc')
Пока как предположение, без полного запроса. Что дата в таблице в базе profiler идет с датой от вчера. и по этому курсор не смог задаться (так как ищет от сегодня данные). Если это так то запрос должен выдать ошибку, и получить данные если будет SET @FramentationReportTable_1 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES WHERE table_Name LIKE 'Fragmentation_GoldenHome_Treasure_NEW_2012'+REPLACE(convert(varchar, getdate()-1, 102), '.', '_') + '%')
Fragmentation_GoldenHome_Treasure_NEW_2012_2024_12_07_1
Msg 208, Level 16, State 1, Line 3
Invalid object name 'Profiler.dbo.frag'.
Ой я тогда в запросе, который кинул _ забыл после имени БД 'Fragmentation_GoldenHome_Treasure_NEW_2012_' нужно
база проблемная
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
обе где успешно
Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) Nov 14 2023 18:33:19 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)
все три разных хоста
ща проверим
Fragmentation_GoldenHome_Treasure_NEW_2012_2024_12_07_1
Msg 207, Level 16, State 1, Line 3
Invalid column name 'record_size16'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'record_size16'.
Completion time: 2024-12-07T10:29:30.5579878+03:00
Кажется в запросе где знаки потерялись при копирование у меня
DECLARE @FramentationReportTable_1 VARCHAR(255)
SET @FramentationReportTable_1 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES WHERE table_Name LIKE 'Fragmentation_GoldenHome_Treasure_NEW_2012_'+REPLACE(convert(varchar, getdate(), 102), '.', '_') + '%')
print @FramentationReportTable_1
exec ('
select
frag.db_name,
frag.schema_name,
frag.table_name,
frag.index_name,
frag.partition_num,
case
when frag.record_size*16 <= 403 then 95
when frag.record_size*16 <= 806 then 90
when frag.record_size*16 <= 1209 then 85
else 80
end as suggested_fillfactor
from Profiler.dbo.'+ @FramentationReportTable_1 +' frag
where frag.page_count > 24 and frag.fragmentation >= 5 and frag.num = 1 and frag.index_type <>''HEAP''
order by frag.fragmentation desc')
А пришли, весь запрос который у тебя получился в скрипте, который не работает (насколько я понимаю это номер 2 должен быть что у меня в статье)
DECLARE @FramentationReportTable_1 VARCHAR(255)
SET @FramentationReportTable_1 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES WHERE table_Name LIKE 'Fragmentation_GoldenHome_Treasure_NEW_2012_'+REPLACE(convert(varchar, getdate(), 102), '.', '_') + '%')
print @FramentationReportTable_1
exec ('
DECLARE bad_indexes_1 CURSOR FOR
select
frag.db_name,
frag.schema_name,
frag.table_name,
frag.index_name,
frag.partition_num,
case
when frag.record_size16 <= 403 then 95 when frag.record_size16 <= 806 then 90
when frag.record_size*16 <= 1209 then 85
else 80
end as suggested_fillfactor
from Profiler.dbo.'+ @FramentationReportTable_1 +' frag
where frag.page_count > 24 and frag.fragmentation >= 5 and frag.num = 1 and frag.index_type <>''HEAP''
order by ROUND ([fragmentation],0 ) desc, [rows_count] desc')
-- Open the cursor.
OPEN bad_indexes_1
DECLARE @db_name nvarchar(130);
DECLARE @schema_name nvarchar(130);
DECLARE @table_name nvarchar(130);
DECLARE @index_name nvarchar(130);
DECLARE @fragmentation bigint;
DECLARE @suggested_fillfactor int;
DECLARE @partition_num bigint;
DECLARE @partitionOption nvarchar(130);
DECLARE @fillfactorOption nvarchar(130);
DECLARE @object_name nvarchar(1000);
DECLARE @command nvarchar(1000);
DECLARE @time nvarchar(130)
WHILE (1=1)
BEGIN
FETCH NEXT
FROM bad_indexes_1
INTO @db_name, @schema_name, @table_name, @index_name, @partition_num, @suggested_fillfactor;
IF @FETCH_STATUSS < 0 BREAK
IF @partition_num > 1
begin
SET @partitionOption = N' PARTITION=' + CAST@partition_numm AS nvarchar(10));
set @fillfactorOption = N''
end
else
begin
SET @partitionOption = N''
set @fillfactorOption = N' FILLFACTOR=' + CAST@suggested_fillfactorr as nvarchar(10)) + N', '
end
SET @object_name = QUOTENAME@db_namee) + N'.' + QUOTENAME@schema_namee) + N'.' + QUOTENAME@table_namee)
BEGIN TRY
SET @command = N'ALTER INDEX ' + QUOTENAME(@index_name) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption + N'maxdop = 0, ONLINE = ON, SORT_IN_TEMPDB = ON, MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = BLOCKERS)'
set @time = CURRENT_TIMESTAMP;
print @time
RAISERROR@commandd, 0, 1)
EXEC@commandd)
set @time = CURRENT_TIMESTAMP;
RAISERROR(N'DONE', 0, 1)
END TRY
BEGIN CATCH
SET @command = N'ALTER INDEX ' + QUOTENAME@index_namee) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption + N'maxdop = 0, SORT_IN_TEMPDB = ON)'
set @time = CURRENT_TIMESTAMP;
print @time
RAISERROR@commandd, 0, 1)
EXEC@commandd)
set @time = CURRENT_TIMESTAMP;
RAISERROR(N'DONE', 0, 1)
print @time
END CATCH
END
close bad_indexes_1
deal
Тоже смотрю у тебя часть запроса потерялась при копирование.
должно быть IF @@FETCH_STATUS < 0 BREAK
а у тебя IF @FETCH_STATUSS < 0 BREAK
Кстати можешь попробовать на сервере выполнить запрос (корректный) просто закоментив обе строки с переиндексацией вот эти SET @command = N'ALTER INDEX ' .... и тогда будет проще увидеть где ошибка
ща попробуем запрос проверить
и за коменнтить
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 12:32:42 PM Progress: 2024-12-07 12:32:43.29 Source: {3F75105D-FFED-468F-8903-4844FFB5DE7E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2024-12-07 12:32:50.71 Source: Execute T-SQL Statement Task Executing query "SET QUOTED_IDENTIFIER ON; if DB_ID('profiler') ...".: 100% complete End Progress Error: 2024-12-07 12:32:50.76 Code: 0xC002F210 Source: Execute T-SQL Statement Task 2 Execute SQL Task Description: Executing the query "DECLARE @FramentationReportTable_2 VARCHAR(255) ..." failed with the following error: "Incorrect syntax near 'bad_indexes_2'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2024-12-07 12:32:50.76 Code: 0xC002F210 Source: Execute T-SQL Statement Task 1 Execute SQL Task Description: Executing the query "DECLARE @FramentationReportTable_1 VARCHAR(255) ..." failed with the following error: "Incorrect syntax near 'bad_indexes_1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2024-12-07 12:32:50.76 Code: 0x80019002 Source: Subplan_1 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:32:42 PM Finished: 12:32:50 PM Elapsed: 8.078 seconds. The package execution failed. The step failed.
Это больше похоже на ошибку из лога. Я имел ввиду new quary весь 2ой или 3й шаг из статьи на сервере выполнить. закометив строку с переиндексации.
скрипт отработал. спасибо за сопровождение. копировал через Ctr+a , почему образалось в оба скрипта с двух разных вкладок не ясно.
мое почтение.
промах ---
А зачем вам успевать в техокно?, у меня самая большая база в терабайт сейчас, работает 2 скрипта, один занимается индексированием, второй сбором статистики, всё выполняется online, в скриптах для снижения нагрузки реализован механизм проверки, не выполняется ли сейчас бэкап в базе... Работает уже пол года, полет отличный, пару таблиц есть в базе, которые нельзя инлексировать online, но практика показывает, что проблем не возникает из за этого.
Индексация выполняется только тех, которые действительно надо, то же самое со статистикой, а не всё подряд
А как ваш скрипт учитывает плотность страниц, о которой вы пишете в тексте? Ну, не считая того, что уменьшает её, уменьшая для всех таблиц fill factor?
А зачем мне учитывать плотность страниц при перестройке индекса в общем случае без точной специфики работы базы. Ведь априори, если уменьшая фрагментацию, мы увеличиваем плотность данных на страницах. Вопрос изначальный в статье о том, что переиндексация нужна. А оставлять fill factor 0 чревато тем, что при любой вставке будет проходить разбиение и фрагментация будет расти слишком быстро. Но при этом и не желательно делать слишком много пустого пространства, из за которого плотность будет меньше. а общее условие suggested_fillfactor позволяет найти компромисс от полного заполнения станиц и как следствие каждодневное повышение фрагментации и сохранить плотность на должном уровне.
Ведь априори, если уменьшая фрагментацию, мы увеличиваем плотность данных на страницах
Это ложное утверждение. C fill factor < 100 вы скорее её уменьшаете, но поскольку не отслеживаете - подтвердить или опровергнуть это не получится. Однако люди, которые запустят ваши скрипты, могут сильно удивиться, что индексы выросли, и памяти теперь нужно больше, чем до дефрагментации.
suggested_fillfactor позволяет найти компромисс от полного заполнения станиц и как следствие каждодневное повышение фрагментации и сохранить плотность на должном уровне
А кем он suggested? Зачем он безльтернативно suggested для всех таблиц с фрагментацией больше 5%? А если это историческая таблица с индексом по дате, в которой все изменения происходят в последнем месяце?
Не думаю что есть люди которые берут чужие скрипты и пихаюх их в прод без проверки. Так же после 2и3 скрипта я указал про моменты что есть - fillfactor, есть проверка по партиция (и переиндексация только фрагментированных партиций) и про условия. так же было указано что это общая light версия.
А ложное утверждение это может быть только с отличных fillfactor от стандартного 0.(100). "А зачем мне учитывать плотность страниц при перестройке индекса в общем случае без точной специфики работы базы. Ведь априори, если уменьшая фрагментацию, мы увеличиваем плотность данных на страницах." тут не говорилось для разных таблиц с разной спецификой работы с ними.
Предложенный мной и частью других статей (https://www.sqlservercentral.com/только там чаще про 80% говорят) но также везде сказано что нужно смотреть производительность.
Так же я ни где не указал что этот скрипт истина в последней инстанции. Написано о определенной проблеме и предпосылках. так же в конце статьи написано про разные настройки.
MSSQL Переиндексация в несколько потоков