Предисловие
В интернете можно найти массу информации о дефрагментации или перестроению индексов. Однако, большинство рекомендаций направлены на базы данных, у которых есть время минимальной нагрузки (в основном ночью).
А как быть с базами данных, которые постоянно используются как для изменения данных, так и для получения информации 24 часа в сутки, 7 дней в неделю?
В данной статье приведу реализованный механизм автоматизации дефрагментации индексов в базе данных для поддержки базы данных у нас на предприятии. Этот механизм позволяет все время дефрагментировать нужные индексы, т к в системе 24x7 фрагментация индексов происходит постоянно. И часто дефрагментация даже 1 раз в день для индексов недостаточна.
Решение
Сначала общий подход:
1) создать для нужной базы данных представление, с помощью которого можно получить какие индексы и на сколько в процентах фрагментированы
2) создать таблицу для сохранения результатов дефрагментации индексов
3) создать хранимую процедуру, которая и будет анализировать и дефрагментировать выбранный индекс
4) создать представление для просмотра статистики по результатам дефрагментации индексов
5) создать задание в Агенте, которое будет запускать реализованную хранимую процедуру в п.3.
А теперь реализация:
1) создать для нужной базы данных представление, с помощью которого можно получить какие индексы и на сколько в процентах фрагментированы:
USE [Название_Базы_Данных]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [srv].[vIndexDefrag]
as
with info as
(SELECT
[object_id],
database_id,
index_id,
index_type_desc,
index_level,
fragment_count,
avg_fragmentation_in_percent,
avg_fragment_size_in_pages,
page_count,
record_count,
ghost_record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Название_Базы_Данных')
, NULL, NULL, NULL ,
N'DETAILED')
where index_level = 0
)
SELECT
b.name as db,
s.name as shema,
t.name as tb,
i.index_id as idx,
i.database_id,
idx.name as index_name,
i.index_type_desc,i.index_level as [level],
i.[object_id],
i.fragment_count as frag_num,
round(i.avg_fragmentation_in_percent,2) as frag,
round(i.avg_fragment_size_in_pages,2) as frag_page,
i.page_count as [page],
i.record_count as rec,
i.ghost_record_count as ghost,
round(i.avg_fragmentation_in_percent*i.page_count,0) as func
FROM Info as i
inner join [sys].[databases] as b on i.database_id = b.database_id
inner join [sys].[all_objects] as t on i.object_id = t.object_id
inner join [sys].[schemas] as s on t.[schema_id] = s.[schema_id]
inner join [sys].[indexes] as idx on t.object_id = idx.object_id and idx.index_id = i.index_id
where i.avg_fragmentation_in_percent >= 30 and i.index_type_desc <> 'HEAP';
GO
Данное представление выводит только те индексы, у которых процент фрагментации не меньше 30. Т е те индексы, которых нужно дефрагментировать. Выводятся только те индексы, которые не являются кучами, т к последние при дефрагментации могут дать отрицательный эффект, выражающийся либо блокировкой такой кучи, либо еще большей фрагментацией индекса.
В представлении используется важное системное представление sys.dm_db_index_physical_stats (подробнее).
2) создать таблицу для сохранения результатов дефрагментации индексов:
USE [Название_Базы_Данных]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [srv].[Defrag](
[ID] [bigint] IDENTITY(794,1) NOT NULL,
[db] [nvarchar](100) NULL,
[shema] [nvarchar](100) NULL,
[table] [nvarchar](100) NULL,
[IndexName] [nvarchar](100) NULL,
[frag_num] [int] NULL,
[frag] [decimal](6, 2) NULL,
[page] [int] NULL,
[rec] [int] NULL,
[func] [int] NULL,
[ts] [datetime] NULL,
[tf] [datetime] NULL,
[frag_after] [decimal](6, 2) NULL,
[object_id] [int] NULL,
[idx] [int] NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Defrag] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
ALTER TABLE [srv].[Defrag] ADD CONSTRAINT [DF_Defrag_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO
Главное-в этой таблице не забывать удалять данные (например, которым больше месяца или еще чаще).
Поля в таблице будут понятны по следующему пункту.
3) создать хранимую процедуру, которая и будет анализировать и дефрагментировать выбранный индекс:
USE [Название_Базы_Данных]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoDefragIndex]
AS
BEGIN
SET NOCOUNT ON;
--объявляем необходимые переменные
declare @IndexName nvarchar(100) --название индекса
,@db nvarchar(100) --название базы данных
,@Shema nvarchar(100) --название схемы
,@Table nvarchar(100) --название таблицы
,@SQL_Str nvarchar (2000) --строка для формирования команды
,@frag decimal(6,2) --% фрагментации до процесса дефрагментации
,@frag_after decimal(6,2) --% фрагментации после процесса дефрагментации
--Количество фрагментов на конечном уровне единицы распределения IN_ROW_DATA
,@frag_num int
,@func int --round(i.avg_fragmentation_in_percent*i.page_count,0)
,@page int --кол-во страниц индекса
,@rec int --общее кол-во записей
,@ts datetime --дата и время начала дефрагментации
,@tf datetime --дата и время окончания дефрагментации
--идентификатор объекта таблицы или представления, для которых создан индекс
,@object_id int
,@idx int; --ID индекса
--получаем текущую дату и время
set @ts = getdate();
--получаем очередной индекс для дефрагментации
--здесь именно важный индекс выбирается. При этом никогда не случиться, что один индекс будет
--постоянно дефрагментироваться, а все остальные не будут выбраны для дефрагментации
select top 1
@IndexName = index_name,
@db=db,
@Shema = shema,
@Table = tb,
@frag = frag,
@frag_num = frag_num,
@func=func,
@page =[page],
@rec = rec,
@object_id = [object_id],
@idx = idx
from [srv].[vIndexDefrag]
order by func*power((1.0-
convert(float,(select count(*) from SRV.[srv].[Defrag] vid where vid.db=db
and vid.shema = shema
and vid.[table] = tb
and vid.IndexName = index_name))
/
convert(float,
case when (exists (select top 1 1 from SRV.[srv].[Defrag] vid1 where vid1.db=db))
then (select count(*) from SRV.[srv].[Defrag] vid1 where vid1.db=db)
else 1.0 end))
,3) desc
--если такой индекс получен
if(@db is not null)
begin
--непосредственно реорганизация индекса
set @SQL_Str = 'alter index ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] Reorganize';
execute sp_executesql @SQL_Str;
--получаем текущую дату и время
set @tf = getdate()
--получаем процент фрагментации после дефрагментации
SELECT @frag_after = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(@db), @object_id, @idx, NULL ,
N'DETAILED')
where index_level = 0;
--записываем результат работы
insert into SRV.srv.Defrag(
[db],
[shema],
[table],
[IndexName],
[frag_num],
[frag],
[page],
[rec],
ts,
tf,
frag_after,
object_id,
idx
)
select
@db,
@shema,
@table,
@IndexName,
@frag_num,
@frag,
@page,
@rec,
@ts,
@tf,
@frag_after,
@object_id,
@idx;
--обновляем статистику для индекса
set @SQL_Str = 'UPDATE STATISTICS ['+@Shema+'].['+@Table+'] ['+@IndexName+']';
execute sp_executesql @SQL_Str;
end
END
4) создать представление для просмотра статистики по результатам дефрагментации индексов:
USE [Название_Базы_Данных]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [srv].[vStatisticDefrag] as
SELECT top 1000
[db]
,[shema]
,[table]
,[IndexName]
,avg([frag]) as AvgFrag
,avg([frag_after]) as AvgFragAfter
,avg(page) as AvgPage
FROM [srv].[Defrag]
group by [db], [shema], [table], [IndexName]
order by abs(avg([frag])-avg([frag_after])) desc;
GO
Данное представление можно использовать для ежедневного уведомления администраторов о проделанной работы автоматизации дефрагментации индексов.
5) создать задание в Агенте, которое будет запускать реализованную хранимую процедуру в п.3:
Здесь нужно подбирать время экспериментальным путем. У меня получилось где-то 5 минут, а где-то и час.
Данный алгоритм можно расширить на несколько баз данных, но тогда нужно ввести еще и п.6:
Собрать всю статистику по автоматизации дефрагментации индексов в базах данных в одном месте для последующей отправки администраторам.
А теперь хотелось бы остановиться поподробнее о уже выложенных рекомендациях по поддержке индексов:
1) дефрагментация сразу всех индексов во время минимальной нагрузки базы данных — для систем 24x7 это недопустимо, т к индексы фрагментируются постоянно и времени простоя базы данных практически не бывает.
2) перестроение индекса — данная операция блокирует таблицу или секцию (в случае секционированного индекса), что для систем 24x7 не есть хорошо. Далее, перестроение индекса в режиме реального времени поддерживается только в Enterprise-решении, а также может привести к повреждению данных.
Данный метод не является оптимальным, но успешно справляется с тем, чтобы индексы были достаточно дефрагментированы (не выше 30-40% фрагментации) для использования их оптимизатором для построения планов выполнения.
Буду очень признателен, если в комментариях появятся аргументированные плюсы и минусы данного подхода, а также проверенные альтернативные предложения.
Источники:
» Реорганизация и перестроение индексов
» sys.dm_db_index_physical_stats
Комментарии (59)
Vitoska
07.11.2016 12:34Хорошая статья.
Какой размер индекса в Ваших БД? Вопрос к тому, что большинство БД 24*7 имеют полную модель восстановления и при реорганизации больших индексов потребляется большой объем журнала транзакций, что может привести к проблемам в случае нехватки дискового пространства, т.е. приходится фильтровать по размеру индекса.
jobgemws
07.11.2016 12:42У нас простая модель восстановления по крайней мере с 2007 года так.
Мы делаем бэкапы просто раз в 3-4 часа на резервном сервере, а данные на резервный поступают через репликацию.
Размер индекса: от 2 МБ до нескольких ГБ.
Но тут еще важно кол-во страниц, занимаемых индексом.
brainfair
07.11.2016 12:42Ребилд индексов онлайн разве вызовет простой системы?
jobgemws
07.11.2016 12:43Конечно нет, но был опыт, когда после такого данные были повреждены (проверка CHECKDB)
Vitoska
07.11.2016 12:48Автор иммел ввиду, что ребилд отдельных секций индекса вызывает блокировку, но так было до 2014 SQL Server, в 2014 можно ребилдить секции онлайн.
jobgemws
07.11.2016 12:55А как происходит это онлайн? Наверняка используется временная БД типа tempDB. И все это не освобождает от риска повреждений.
А если не онлайн и не блокируется, то я в это не поверю, т к пока он перестраивает структуру индекса, то либо системе нужно поместить куда-то эту структуру, чтобы не было блокировок, а затем переключить на измененную. И вот в эти два момента блокировка будет. Да и еще-нужно куда-то изменения складывать пока перестройка идет. Важно понять как это происходит.Vitoska
07.11.2016 13:07Все верно, но эти блокировки краковременные — они не настолько существенны.
Vitoska
07.11.2016 13:08TempDB не используется, при ребилде новая структура создается прямо в файле данных, TempDB только для сортировок при перестроении.
jobgemws
07.11.2016 13:23Если в том же файле данных, то идет нагрузка на носитель информации, т е тоже не есть хорошо. Делали оценку? Поделитесь результатами
Vitoska
07.11.2016 13:40Оценку не делал, все же зависит от носителя информации. Если диски не достаточно производительные, то я согласен что производительность снизится на время перестроения индекса, но и при дефрагментации дисковый ввод-выод используется достаточно интенсивно.
jobgemws
07.11.2016 13:51Конечно при дефрагментации используется, но по задержкам не так сильно ощущается на запросы-порядка на +0,01 и менее сек.
jobgemws
07.11.2016 13:21В документации про онлайн перестроение тоже не было, что данные могут повредиться.
Вы сами пробовали, когда индекс весит много (порядка 1 ГБ) и занимает много страниц и у него несколько уровней? Сколько времени занимает?
Isiirk
07.11.2016 13:43Я замерял для себя, цифр уже не приведу, но если мы находимся в предедах рекомендации для массива по дисковым очередям, то сколь либо существенного снижения производительности общей системы не происходит
Vitoska
07.11.2016 13:44+1По поводу повреждения данных, то скорее всего это баг, например https://support.microsoft.com/ru-ru/kb/2969896
jobgemws
07.11.2016 13:53Возможно здесь Вы правы. У нас закрытые системы. И поэтому обновления ставятся только при следующей версии софта. Т е практически нет перезагрузок и остановки рабочего процесса. Т. е. поставили SP1 и так и будет жить до новой версии софта.
korus
07.11.2016 14:04+1Есть БД, которая крутится на 2005 сервере. На конкретной таблице индекс 1.5 ГБ. За последние 9 лет такой проблемы не было ни разу, запись туда каждую минуту, при этом раз в 15 минут данных сильно больше (автоматика) + пользователи хаотично ее мучают. Индекс успевает перестроиться за эти самые 15 минут, потерь данных замечено не было. Возможно это был реально баг? Надо сделать для себя пометку, покопать этот вопрос, не хотел бы я на такое напороться.
jobgemws
07.11.2016 14:37Я просто один раз напоролся-мне хватило потом)
Еще нужно и замеры сделать-пользователи могут не обратить внимания на запросы.
Но конечно проблема запросов на фоне повреждения данных просто меркнут как далекая звезда при ярком дне.
Isiirk
07.11.2016 12:45По всем БД
set quoted_identifier on; DECLARE @SQL VARCHAR(MAX) DECLARE @DB sysname DECLARE CURSDB CURSOR FORWARD_ONLY STATIC FOR SELECT [name] FROM master..sysdatabases WHERE [name] NOT IN ('model', 'tempdb') ORDER BY [name] OPEN CURSDB FETCH NEXT FROM CURSDB INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN ---------------- SELECT @SQL = CHAR(13) + '-- UPDATE FOR ' + @DB + CHAR(13) + 'USE [' + @DB +']' PRINT @SQL EXEC (@SQL) DECLARE @PageCount INT = 128 , @RebuildPercent INT = 30 , @ReorganizePercent INT = 10 , @IsOnlineRebuild BIT = 0 , @IsVersion2012Plus BIT = CASE WHEN CAST(SERVERPROPERTY('productversion') AS CHAR(2)) NOT IN ('8.', '9.', '10') THEN 1 ELSE 0 END , @IsEntEdition BIT = CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) THEN 1 ELSE 0 END , @SQL1 NVARCHAR(MAX) SELECT @SQL1 = ( SELECT ' ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s2.name) + '.' + QUOTENAME(o.name) + ' ' + CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent THEN 'REBUILD' ELSE 'REORGANIZE' END + ' PARTITION = ' + CASE WHEN ds.[type] != 'PS' THEN 'ALL' ELSE CAST(s.partition_number AS NVARCHAR(10)) END + ' WITH (' + CASE WHEN s.avg_fragmentation_in_percent >= @RebuildPercent THEN 'SORT_IN_TEMPDB = ON' + CASE WHEN @IsEntEdition = 1 AND @IsOnlineRebuild = 1 AND ISNULL(lob.is_lob_legacy, 0) = 0 AND ( ISNULL(lob.is_lob, 0) = 0 OR (lob.is_lob = 1 AND @IsVersion2012Plus = 1) ) THEN ', ONLINE = ON' ELSE '' END ELSE 'LOB_COMPACTION = ON' END + ')' FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.schemas s2 ON o.[schema_id] = s2.[schema_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.page_count > @PageCount AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V') AND s.avg_fragmentation_in_percent > @ReorganizePercent FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') PRINT @SQL1 EXEC (@SQL1) --------- FETCH NEXT FROM CURSDB INTO @DB END CLOSE CURSDB DEALLOCATE CURSDBjobgemws
07.11.2016 12:46Это если все сразу дефрагментировать, а не по одному индексу все время, и Вы используете перестроение с дефрагментацией
Isiirk
07.11.2016 13:07+1Ну в данном случае не по всем, а по тем, что рекомендует Microsoft. Если необходимости нет, то и индекс не будет перестроен или дефрагментирован в зависимости от процентра фрагментации. Очень универсальное решение позволяющее забыть на 90% систем про индексы
jobgemws
07.11.2016 13:25Увы, здесь Вы не правы. Я раньше применял рекомендацию от Microsoft. Однако, из практики оказалось, что все сразу индексы дефрагментировать хуже раз в день. Лучше делать это постоянно по одному индексу каждые 5-30 минут. Перестроение вообще показало, что в момент перестроения запросы значительно дольше выполняются, т к эта операция блокирует таблицу или секцию. При постоянной дефрагментации уходит потребность в перестроении индексов. Они будут равномерно и несильно фрагментированы всегда.
Isiirk
07.11.2016 13:38Я сторонник универсальнвх решений и перестройка тоже идёт в режиме online, запускайте мой скрипт раз в 5 минут и получите тоже самое. Только без вьюшек, хранимых процедур и т.д. При своременных мощностях можно пренебречь сверхтюнингом в угоду простоты и универсальности.
jobgemws
07.11.2016 13:49Не согласен. При больших объемах мощности могут не потянуть «универсализм». Да и на мощности у заказчиков деньги ограничены. Данный подход, изложенный в статье тоже универсальный. А если Вы хотите на разные СУБД универсальный метод, то он есть, но будет проигрывать любому методу, который будет заточен для конкретной СУБД
Isiirk
07.11.2016 13:58Я лишь говорю о том, что сама процедура не русурсозатратна, если же вы и при обычной нагрузке находитесь на пороге производительности, то возможно выход, но это уже больше на агонию похоже
jobgemws
07.11.2016 14:04Во многих системах можно дефрагментировать и даже перестраивать индексы все и сразу (по рекомендациям от Microsoft). Я лишь привел пример того, как размазать дефрагментацию в течении всего времени эксплуатации системы, у которой нет времени простоя. И да, я сначала тоже все индексы дефрагментировал и это сказалось на запросах. Так что Ваш подход к моей системе не подходит. Думаю есть и еще системы, где такой подход не подойдет. А если и подойдет, то будут небольшие зависания, о которых пользователи просто промолчат. И все будут считать, что все хорошо.
Isiirk
07.11.2016 14:19Из статьи не понятно, что мы говорим о часных случаях. Всегда есть особые решения. У меня опыта небыло на свербольших системах, но базы в пол террабайта и таблицы за 100 миллионов записей прекрасно живут с универсальными решениями, если не запускать.
jobgemws
07.11.2016 14:28Значит Вам не попадались такие проблемы)
У нас самая большая база данных, которая постоянно эксплуатируется для изменения и сбора данных-до 100 ГБ, т к все старое мы складываем в хранилище, а из базы удаляем. Т. е. записи по истечении более года не доступны для редактирования. И в данном рабочем процессе это правильно.
И если у Вас не было проблем, то никто и не просит использовать опубликованный метод.
Я лишь привел свой, который подошел, т к стандартные решения не подходили. Т. е. подходили, но существенно влияли на производительность.Isiirk
07.11.2016 14:38Возможно везло). Не сильно понимаю ограничения на редактирование, оно ни как не влияет на производительность и стараюсь не проектировать таких систем. Только если это не связано с особенностями бизнес-процесса
jobgemws
07.11.2016 14:49Не сильно понимаю ограничения на редактирование, оно ни как не влияет на производительность
Еще как влияет-объемы, в таблицах, которые используются и для модификации данных, объемы всегда будут за последние 1-2 года, а не за все время существования рабочих процессов. У нас это аж до 2000 года.
Таким образом у Вас всегда хранятся данные за последние 1-2 года, которые имеет смысл редактировать, а остальные отправляются в хранилище и уже не редактируются.Isiirk
07.11.2016 15:05Не могу согласится, поясните, как влияет изменение первой сотни или последней сотни строк в таблице на производительность даже если в ней миллионы строк?
Мы говорим не про смысл, возможно процесс этого требует, вопрос в другом, как технически отличается изменение в любом из прошлых периодов?
Или вы под хранилищем подразумеваете перенос в другие файловые группы и секционирование?jobgemws
07.11.2016 15:14под хранилищем я понимаю отдельную базу данных или кубы. В моем случае первое.
Чем меньше данных в таблицах, тем быстрее идут операции на изменения данных. Это очевидно. Не понимаю, что тут доказывать нужно.
Не понятно зачем заказчику менять данные проводок или документов, которые были проведены или закрыты более 1-2 лет назад. Может стоит его убедить в нетрогании старых проводок и документов, а не идти на поводу?Isiirk
07.11.2016 15:32Аргументируйте как объем данных в таблице влияет на обновление одной строки в любом её месте. Мне как раз очевидно, что ни как.
Или вы перешли на конкретную Бд и сейчас говорите о пересчете регистров, то это совсем из другой оперыjobgemws
07.11.2016 15:42Нет, я рассуждаю из реляционной алгебры. Чем больше записей в секции (в данном случае таблица как одна секция), тем больше времени потребуется для ее нахождения. Это очевидный факт. Даже если по индексу-все равно будет узлы перебирать. Больше данных-больше узлов, и значит логарифм по основанию кол-во ветвей в дереве все равно будет расти, немного, но расти.
Isiirk
07.11.2016 15:53Немного в текущем контексте — это не то что милисекунды, это микросекунды. Вы мне говорите же о деградации реальной, а не о теоретической. В вашем случае вопрос лишь стоит о качестве селективнлсти индексов… Ни какой деградации при достаточном кол-ве ресурсов у вас не будет. Конечно, если у вас дисковые сортировки для выполнения таких запросов или обновление по пол таблицы не происходит, но согласитесь что это уже не нормально
jobgemws
07.11.2016 15:56Вы все как-то пытаетесь спихнуть на мощное железо. Нет у наших заказчиков мощного железа. Эта розовая реальность. По теории как раз должно незначительно время увеличиваться с ростом данных. Я же вижу на практике, что с каждым +100 000 строк, а уж тем более + 1 млн строк вообще говоря время на изменение меняется уже в +0,01 сек и более
Isiirk
07.11.2016 16:19Предлагаю добавить тогда к статье теги, слабое железо. Речь ведь шла не про оптимизацию работы на слабом железе а про оптимизацию индексов, если вы занимаетесь такими вопросами сейчас, то ваша система будет требовать постоянной оптимизации. А следовательно стоимость владения её будет не соизмерима с вложением в железо. Стоимость месяца работы DBA покроет все затраты на разовую покупку железа и настройку и забыть на ближайшие пару лет…
Я не говорю что стоит железом решать вопрос, но выбор должен быть разумен…jobgemws
07.11.2016 16:29Вы опять смотрите все через розовые очки. Часто у крупных заказчиков нет денег-Вы не поверите, нет и все. Нравится-работай за хорошую ЗП, не нравится-найдут другого. И по-моему, чем крупнее фирма или Заказчик, тем больше как это сказать покультурнее выкрунтасов. Ну чего мне Вам говорить. Думаю сами знаете. Не слабое железо, а вполне распространенная проблема-чего, все богатые что ли?)
Isiirk
07.11.2016 16:05Ну и секционирование и локальные индексы не отменял ни кто
jobgemws
07.11.2016 16:10А если нет под рукой Enterprise или секция большая? И невсегда удается под каждый запрос создавать индексы.
Isiirk
07.11.2016 16:23Эти все условия должны были быть добавлены в теги, иначе м ы говорим просто об MS SQL и подразумеваем что понимаем как использовать и какие ресурсы под какие системы
jobgemws
07.11.2016 19:45Вспомнил еще важный аргумент не хранить все в одной базе данных.
Вы потом что из такой огромной БД будете резервную копию делать? А потом если понадобится ее куда скопировать? Чем больше бэкап, тем больше вероятности, что она еще и поврежденная будет. А вообще нужно разделять: оперативные данные (в самой БД)-те данные, которые пользователь может изменить (обычно это данные не старее 1-2 лет) и исторические данные-идут в хранилище данных и доступны только для чтения.
Такой подход позволяет не раздувать базы данных на серверах. Их объем всегда будет от нескольких ГБ до нескольких 10-ов ГБ. Макс=100 ГБ.
Что позволит делать быстро резервные копии и т. д. Как говорится меньше объема=меньше проблем)
А вот хранилище нужно продумать основательно, т к там будет храниться данные с самого начала сотворения рабочих процессов и до конца света)
Нужно продумать именно как быстро вытаскивать данные, а не как их менять. У нас в хранилище данные поступают каждую ночь. Как только проводке или документу с даты проведения (закрытия) прошел год. Такие данные удаляются из баз данных и остаются только в хранилище данных.
Я думаю в большинстве случаев так можно поступить, т. к. сомневаюсь, что вдруг приспичит кому-то редактировать геологически старые данные. К тому же эти данные уже были поданы в разные органы и т. д.Isiirk
08.11.2016 00:55Мы ушли от темы, мы говорим об индексах или проектировании хранилищ Бд? А что не так с Бд размером скажем 500 Гб, сети гигабитные давно, дисковые массивы работают на Гигабайтных скоростях. Бугвально неделю назад видел скорость чтения на массиве 1 ГБАЙТ в секунду на не большем таком предприятии. Я все же склонен говорить о сбалансированных системах с точки зрения ресурсов и потребностей. Собственно если мы пытаемся на десктопном железе решить задачи масштаба среднего предприятия, ну простите, как это относится к статье? А вообще экономия сомнительна на таких системах… Посчитайте ФОТ специалистов для поддержания работоспособности хотя бы за год и вы поймёте, что лучше вложиться даже в очень хорошее железо… Я уж не говорю про качество работы систем «на грани» и про простои предприятия… Выбор должен быть разумен…
jobgemws
08.11.2016 08:44Я в последнее время все больше замечаю следующую тенденцию-разрабатывают софт и БД на скорую руку, думая что железо справится и забывая математику. Скажу я Вам нет, я всегда склонен делать все максимально оптимально, т е мое решение пойдет и на проблемном железе. И более того на крупных предприятиях всегда есть 1 суперкомп, но он один. А серверов остальных много. Даже со времен 2000 года и то до сих пор работают. И Вы можете что-то там про ФОТ им говорить и что угодно. НО жизнь реальна. И алгоритмы нужно писать не с рассчетом на мощное железо, а с расчетом на минимальную нагрузку
Isiirk
08.11.2016 09:01+1Я вам не говорил об алгоритмах под мощное железо, я говорил об обычной практике, что заниматься нужно именно грамотной разработкой а не тратить время на выделение массы времени на решения, которые в продакшене и не должны возникать то… Это выглядит как вечное спасение системы
jobgemws
08.11.2016 09:06Да, отчасти здесь соглашусь)
Сначала делаются по-быстрому решения и все тесты проходят
Затем я с этим мучаюсь)
Пишу костыли на время… а что может быть более постоянным, чем написанные на время костыли?)
Часто софт, который обращается к БД, создает такие запросы, что волосы седеют.
Isiirk
07.11.2016 14:43Деградация производительности и вслески нагрузки (зависания) легко отслеживается мониторингом, если к вам с такой проблемой пришёл пользователь, то пора систему подвергать серьезному анализу…
vlivyur
07.11.2016 13:22+1Вместо такого:
можно использовать такоеUSE [Название_Базы_Данных]
По крайней мере в стандартном SSMS это заменяется во всём тексте по кнопкам Ctrl+Shift+M на нужное имя (тонкости с этим есть, но только если где-то XML используется).USE [<имя параметра, тип, значение по-умолчанию>]
Ну а по тексту вместо «дефрагментации» почти везде следует читать «фрагментации».jobgemws
07.11.2016 17:16Спасибо, что заметили опечатки-поправил
В некоторых словах вместо «дефрагмент...» нужно поставить «фрагмент...»
korus
07.11.2016 13:45+1Обычно делал это джобой или в плане обслуживания (что почти одно и то же). Расписание: когда загрузка процессора не выше определенного порога или не реже одного раза в n дней в определенное время (количество дней и время под проект). Там же скрипт проверки на «захламлённость» индекса и выполнения его реорганизации\ребилда в зависимости от ситуации. Конечно, там был еще ряд проверок на схему таблицы (от этого зависело, например, как используется таблица, чем и как часто), размер + еще пару специфичных проверок.
По сути, схема та же: смотрим на индексы, ищем те, которые нужно переделать, если ничего не мешает — делаем что нужно. Только не было никаких вьюх в продакшен базе, всё в джобе (хранимки всё-таки создавались, если проверка занимала больше 20-30 строк). Правда, я пока не сталкивался с системами, где равномерная высокая нагрузка на одни и те же модули системы в течение суток, поэтому почти все проверки базировались на времени суток.jobgemws
07.11.2016 13:47У нас все эти вьюхи и прочее сделаны в отдельной базе данных. Для простоты просто не акцентирую на это внимание. Мне план не подошел из-за того, что не бывает такого, что процессор не загружен.
ComodoHacker
07.11.2016 16:42А были ли попытки оценить эффект от этой постоянной дефрагментации? И оправдывает ли он дополнительную нагрузку на систему?
И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?jobgemws
07.11.2016 17:12И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?
Как раз с этого и начинали. И стандартные методы применяли, причем даже до меня делали.
А были ли попытки оценить эффект от этой постоянной дефрагментации?
Как раз этим и занимались статья. Результаты были хорошие, т е, как я и писал выше, всегда индексы были умеренно фрагментированы. А до этого фрагментированы одни хуже других и т. д.
И оправдывает ли он дополнительную нагрузку на систему?
Неправильный вопрос. Такой подход как раз убирает нагрузку, чем все и сразу дефрагментировать. Попробуйте сами-хуже не будет.
osipov_dv
На эту тему врят-ли стоило изобретать велосипед, можно было взять например вот это: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
jobgemws
Статья интересная. Однако, позволяет ли данное решение постоянно по одному индексу дефрагментировать, а не все сразу? Я понял на примерах, что каждые 5-30 минут запускать задание, которое вызывает хранимку, которая берет нужный индекс (анализирует) и дефрагментирует, в данном решении не реализовано. Может я и ошибаюсь, но данный метод похож на те, что я читал ранее. Оно направлено на дефрагментацию или перестройку всех индексов во время простоя системы. Я же пишу о системе, у которой времени простоя не бывает или оно настолько ничтожное по длительности в сутках, что им можно пренебречь.
crazypilot
умеет, параметр Indexes
jobgemws
Ок, тогда попробую)
Спасибо за еще одно решение)