Автоматизация дефрагментации индексов в базе данных MS SQL Server

  • Tutorial

Предисловие


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

А как быть с базами данных, которые постоянно используются как для изменения данных, так и для получения информации 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'LIMITED')
	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

    0
    На эту тему врят-ли стоило изобретать велосипед, можно было взять например вот это: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
      0
      Статья интересная. Однако, позволяет ли данное решение постоянно по одному индексу дефрагментировать, а не все сразу? Я понял на примерах, что каждые 5-30 минут запускать задание, которое вызывает хранимку, которая берет нужный индекс (анализирует) и дефрагментирует, в данном решении не реализовано. Может я и ошибаюсь, но данный метод похож на те, что я читал ранее. Оно направлено на дефрагментацию или перестройку всех индексов во время простоя системы. Я же пишу о системе, у которой времени простоя не бывает или оно настолько ничтожное по длительности в сутках, что им можно пренебречь.
        +1
        умеет, параметр Indexes
          0
          Ок, тогда попробую)
          Спасибо за еще одно решение)
      0
      Хорошая статья.
      Какой размер индекса в Ваших БД? Вопрос к тому, что большинство БД 24*7 имеют полную модель восстановления и при реорганизации больших индексов потребляется большой объем журнала транзакций, что может привести к проблемам в случае нехватки дискового пространства, т.е. приходится фильтровать по размеру индекса.

        0
        У нас простая модель восстановления по крайней мере с 2007 года так.
        Мы делаем бэкапы просто раз в 3-4 часа на резервном сервере, а данные на резервный поступают через репликацию.
        Размер индекса: от 2 МБ до нескольких ГБ.
        Но тут еще важно кол-во страниц, занимаемых индексом.
        0
        Ребилд индексов онлайн разве вызовет простой системы?
          0
          Конечно нет, но был опыт, когда после такого данные были повреждены (проверка CHECKDB)
            0
            Автор иммел ввиду, что ребилд отдельных секций индекса вызывает блокировку, но так было до 2014 SQL Server, в 2014 можно ребилдить секции онлайн.
              0
              А как происходит это онлайн? Наверняка используется временная БД типа tempDB. И все это не освобождает от риска повреждений.
              А если не онлайн и не блокируется, то я в это не поверю, т к пока он перестраивает структуру индекса, то либо системе нужно поместить куда-то эту структуру, чтобы не было блокировок, а затем переключить на измененную. И вот в эти два момента блокировка будет. Да и еще-нужно куда-то изменения складывать пока перестройка идет. Важно понять как это происходит.
                0
                Все верно, но эти блокировки краковременные — они не настолько существенны.
                  0
                  TempDB не используется, при ребилде новая структура создается прямо в файле данных, TempDB только для сортировок при перестроении.
                    0
                    Если в том же файле данных, то идет нагрузка на носитель информации, т е тоже не есть хорошо. Делали оценку? Поделитесь результатами
                      0
                      Оценку не делал, все же зависит от носителя информации. Если диски не достаточно производительные, то я согласен что производительность снизится на время перестроения индекса, но и при дефрагментации дисковый ввод-выод используется достаточно интенсивно.
                        0
                        Конечно при дефрагментации используется, но по задержкам не так сильно ощущается на запросы-порядка на +0,01 и менее сек.
                    0
                    В документации про онлайн перестроение тоже не было, что данные могут повредиться.
                    Вы сами пробовали, когда индекс весит много (порядка 1 ГБ) и занимает много страниц и у него несколько уровней? Сколько времени занимает?
                      0
                      Я замерял для себя, цифр уже не приведу, но если мы находимся в предедах рекомендации для массива по дисковым очередям, то сколь либо существенного снижения производительности общей системы не происходит
                        +1
                        По поводу повреждения данных, то скорее всего это баг, например https://support.microsoft.com/ru-ru/kb/2969896
                          0
                          Склонен к другому источнику повреждения данных, ни разу не встречал проблем с этим при перестроении, даже очень больших индексов и высокой нагрузке
                            0
                            Бывают) Ну на дисках экономят со стороны заказчика-и в этом тоже беда)
                            0
                            Возможно здесь Вы правы. У нас закрытые системы. И поэтому обновления ставятся только при следующей версии софта. Т е практически нет перезагрузок и остановки рабочего процесса. Т. е. поставили SP1 и так и будет жить до новой версии софта.
                            +1
                            Есть БД, которая крутится на 2005 сервере. На конкретной таблице индекс 1.5 ГБ. За последние 9 лет такой проблемы не было ни разу, запись туда каждую минуту, при этом раз в 15 минут данных сильно больше (автоматика) + пользователи хаотично ее мучают. Индекс успевает перестроиться за эти самые 15 минут, потерь данных замечено не было. Возможно это был реально баг? Надо сделать для себя пометку, покопать этот вопрос, не хотел бы я на такое напороться.
                              0
                              Я просто один раз напоролся-мне хватило потом)
                              Еще нужно и замеры сделать-пользователи могут не обратить внимания на запросы.
                              Но конечно проблема запросов на фоне повреждения данных просто меркнут как далекая звезда при ярком дне.
                  0
                  По всем БД

                  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 CURSDB
                  
                  
                    0
                    Это если все сразу дефрагментировать, а не по одному индексу все время, и Вы используете перестроение с дефрагментацией
                    +1
                    Ну в данном случае не по всем, а по тем, что рекомендует Microsoft. Если необходимости нет, то и индекс не будет перестроен или дефрагментирован в зависимости от процентра фрагментации. Очень универсальное решение позволяющее забыть на 90% систем про индексы
                      0
                      Увы, здесь Вы не правы. Я раньше применял рекомендацию от Microsoft. Однако, из практики оказалось, что все сразу индексы дефрагментировать хуже раз в день. Лучше делать это постоянно по одному индексу каждые 5-30 минут. Перестроение вообще показало, что в момент перестроения запросы значительно дольше выполняются, т к эта операция блокирует таблицу или секцию. При постоянной дефрагментации уходит потребность в перестроении индексов. Они будут равномерно и несильно фрагментированы всегда.
                        0
                        Я сторонник универсальнвх решений и перестройка тоже идёт в режиме online, запускайте мой скрипт раз в 5 минут и получите тоже самое. Только без вьюшек, хранимых процедур и т.д. При своременных мощностях можно пренебречь сверхтюнингом в угоду простоты и универсальности.
                          0
                          Не согласен. При больших объемах мощности могут не потянуть «универсализм». Да и на мощности у заказчиков деньги ограничены. Данный подход, изложенный в статье тоже универсальный. А если Вы хотите на разные СУБД универсальный метод, то он есть, но будет проигрывать любому методу, который будет заточен для конкретной СУБД
                            0
                            Я лишь говорю о том, что сама процедура не русурсозатратна, если же вы и при обычной нагрузке находитесь на пороге производительности, то возможно выход, но это уже больше на агонию похоже
                              0
                              Во многих системах можно дефрагментировать и даже перестраивать индексы все и сразу (по рекомендациям от Microsoft). Я лишь привел пример того, как размазать дефрагментацию в течении всего времени эксплуатации системы, у которой нет времени простоя. И да, я сначала тоже все индексы дефрагментировал и это сказалось на запросах. Так что Ваш подход к моей системе не подходит. Думаю есть и еще системы, где такой подход не подойдет. А если и подойдет, то будут небольшие зависания, о которых пользователи просто промолчат. И все будут считать, что все хорошо.
                                0
                                Из статьи не понятно, что мы говорим о часных случаях. Всегда есть особые решения. У меня опыта небыло на свербольших системах, но базы в пол террабайта и таблицы за 100 миллионов записей прекрасно живут с универсальными решениями, если не запускать.
                                  0
                                  Значит Вам не попадались такие проблемы)
                                  У нас самая большая база данных, которая постоянно эксплуатируется для изменения и сбора данных-до 100 ГБ, т к все старое мы складываем в хранилище, а из базы удаляем. Т. е. записи по истечении более года не доступны для редактирования. И в данном рабочем процессе это правильно.
                                  И если у Вас не было проблем, то никто и не просит использовать опубликованный метод.
                                  Я лишь привел свой, который подошел, т к стандартные решения не подходили. Т. е. подходили, но существенно влияли на производительность.
                                    0
                                    Возможно везло). Не сильно понимаю ограничения на редактирование, оно ни как не влияет на производительность и стараюсь не проектировать таких систем. Только если это не связано с особенностями бизнес-процесса
                                      0
                                      Не сильно понимаю ограничения на редактирование, оно ни как не влияет на производительность

                                      Еще как влияет-объемы, в таблицах, которые используются и для модификации данных, объемы всегда будут за последние 1-2 года, а не за все время существования рабочих процессов. У нас это аж до 2000 года.
                                      Таким образом у Вас всегда хранятся данные за последние 1-2 года, которые имеет смысл редактировать, а остальные отправляются в хранилище и уже не редактируются.
                                        0
                                        Не могу согласится, поясните, как влияет изменение первой сотни или последней сотни строк в таблице на производительность даже если в ней миллионы строк?

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

                                        Или вы под хранилищем подразумеваете перенос в другие файловые группы и секционирование?
                                          0
                                          под хранилищем я понимаю отдельную базу данных или кубы. В моем случае первое.
                                          Чем меньше данных в таблицах, тем быстрее идут операции на изменения данных. Это очевидно. Не понимаю, что тут доказывать нужно.
                                          Не понятно зачем заказчику менять данные проводок или документов, которые были проведены или закрыты более 1-2 лет назад. Может стоит его убедить в нетрогании старых проводок и документов, а не идти на поводу?
                                            0
                                            Аргументируйте как объем данных в таблице влияет на обновление одной строки в любом её месте. Мне как раз очевидно, что ни как.

                                            Или вы перешли на конкретную Бд и сейчас говорите о пересчете регистров, то это совсем из другой оперы
                                              0
                                              Нет, я рассуждаю из реляционной алгебры. Чем больше записей в секции (в данном случае таблица как одна секция), тем больше времени потребуется для ее нахождения. Это очевидный факт. Даже если по индексу-все равно будет узлы перебирать. Больше данных-больше узлов, и значит логарифм по основанию кол-во ветвей в дереве все равно будет расти, немного, но расти.
                                                0
                                                Немного в текущем контексте — это не то что милисекунды, это микросекунды. Вы мне говорите же о деградации реальной, а не о теоретической. В вашем случае вопрос лишь стоит о качестве селективнлсти индексов… Ни какой деградации при достаточном кол-ве ресурсов у вас не будет. Конечно, если у вас дисковые сортировки для выполнения таких запросов или обновление по пол таблицы не происходит, но согласитесь что это уже не нормально
                                                  0
                                                  Вы все как-то пытаетесь спихнуть на мощное железо. Нет у наших заказчиков мощного железа. Эта розовая реальность. По теории как раз должно незначительно время увеличиваться с ростом данных. Я же вижу на практике, что с каждым +100 000 строк, а уж тем более + 1 млн строк вообще говоря время на изменение меняется уже в +0,01 сек и более
                                                    0
                                                    Предлагаю добавить тогда к статье теги, слабое железо. Речь ведь шла не про оптимизацию работы на слабом железе а про оптимизацию индексов, если вы занимаетесь такими вопросами сейчас, то ваша система будет требовать постоянной оптимизации. А следовательно стоимость владения её будет не соизмерима с вложением в железо. Стоимость месяца работы DBA покроет все затраты на разовую покупку железа и настройку и забыть на ближайшие пару лет…
                                                    Я не говорю что стоит железом решать вопрос, но выбор должен быть разумен…
                                                      0
                                                      Вы опять смотрите все через розовые очки. Часто у крупных заказчиков нет денег-Вы не поверите, нет и все. Нравится-работай за хорошую ЗП, не нравится-найдут другого. И по-моему, чем крупнее фирма или Заказчик, тем больше как это сказать покультурнее выкрунтасов. Ну чего мне Вам говорить. Думаю сами знаете. Не слабое железо, а вполне распространенная проблема-чего, все богатые что ли?)
                                                    0
                                                    Ну и секционирование и локальные индексы не отменял ни кто
                                                      0
                                                      А если нет под рукой Enterprise или секция большая? И невсегда удается под каждый запрос создавать индексы.
                                                        0
                                                        Эти все условия должны были быть добавлены в теги, иначе м ы говорим просто об MS SQL и подразумеваем что понимаем как использовать и какие ресурсы под какие системы
                                                  0
                                                  Вспомнил еще важный аргумент не хранить все в одной базе данных.
                                                  Вы потом что из такой огромной БД будете резервную копию делать? А потом если понадобится ее куда скопировать? Чем больше бэкап, тем больше вероятности, что она еще и поврежденная будет. А вообще нужно разделять: оперативные данные (в самой БД)-те данные, которые пользователь может изменить (обычно это данные не старее 1-2 лет) и исторические данные-идут в хранилище данных и доступны только для чтения.
                                                  Такой подход позволяет не раздувать базы данных на серверах. Их объем всегда будет от нескольких ГБ до нескольких 10-ов ГБ. Макс=100 ГБ.
                                                  Что позволит делать быстро резервные копии и т. д. Как говорится меньше объема=меньше проблем)
                                                  А вот хранилище нужно продумать основательно, т к там будет храниться данные с самого начала сотворения рабочих процессов и до конца света)
                                                  Нужно продумать именно как быстро вытаскивать данные, а не как их менять. У нас в хранилище данные поступают каждую ночь. Как только проводке или документу с даты проведения (закрытия) прошел год. Такие данные удаляются из баз данных и остаются только в хранилище данных.
                                                  Я думаю в большинстве случаев так можно поступить, т. к. сомневаюсь, что вдруг приспичит кому-то редактировать геологически старые данные. К тому же эти данные уже были поданы в разные органы и т. д.
                                                    0
                                                    Мы ушли от темы, мы говорим об индексах или проектировании хранилищ Бд? А что не так с Бд размером скажем 500 Гб, сети гигабитные давно, дисковые массивы работают на Гигабайтных скоростях. Бугвально неделю назад видел скорость чтения на массиве 1 ГБАЙТ в секунду на не большем таком предприятии. Я все же склонен говорить о сбалансированных системах с точки зрения ресурсов и потребностей. Собственно если мы пытаемся на десктопном железе решить задачи масштаба среднего предприятия, ну простите, как это относится к статье? А вообще экономия сомнительна на таких системах… Посчитайте ФОТ специалистов для поддержания работоспособности хотя бы за год и вы поймёте, что лучше вложиться даже в очень хорошее железо… Я уж не говорю про качество работы систем «на грани» и про простои предприятия… Выбор должен быть разумен…
                                                      0
                                                      Я в последнее время все больше замечаю следующую тенденцию-разрабатывают софт и БД на скорую руку, думая что железо справится и забывая математику. Скажу я Вам нет, я всегда склонен делать все максимально оптимально, т е мое решение пойдет и на проблемном железе. И более того на крупных предприятиях всегда есть 1 суперкомп, но он один. А серверов остальных много. Даже со времен 2000 года и то до сих пор работают. И Вы можете что-то там про ФОТ им говорить и что угодно. НО жизнь реальна. И алгоритмы нужно писать не с рассчетом на мощное железо, а с расчетом на минимальную нагрузку
                                                        +1
                                                        Я вам не говорил об алгоритмах под мощное железо, я говорил об обычной практике, что заниматься нужно именно грамотной разработкой а не тратить время на выделение массы времени на решения, которые в продакшене и не должны возникать то… Это выглядит как вечное спасение системы
                                                          0
                                                          Да, отчасти здесь соглашусь)
                                                          Сначала делаются по-быстрому решения и все тесты проходят
                                                          Затем я с этим мучаюсь)
                                                          Пишу костыли на время… а что может быть более постоянным, чем написанные на время костыли?)
                                                          Часто софт, который обращается к БД, создает такие запросы, что волосы седеют.
                                      0
                                      Деградация производительности и вслески нагрузки (зависания) легко отслеживается мониторингом, если к вам с такой проблемой пришёл пользователь, то пора систему подвергать серьезному анализу…
                                        0
                                        Ко мне никто не приходил, я сам проанализировал с помощью следующей статьи. Повторюсь, возможно у Вас так можно делать и пользователи не ощущают замедление запросов, т к последние возможно незначительно стали медленнее в виду особенностей Вашей системы.
                            +1
                            Вместо такого:
                            USE [Название_Базы_Данных]
                            можно использовать такое
                            USE [<имя параметра, тип, значение по-умолчанию>]
                            По крайней мере в стандартном SSMS это заменяется во всём тексте по кнопкам Ctrl+Shift+M на нужное имя (тонкости с этим есть, но только если где-то XML используется).
                            Ну а по тексту вместо «дефрагментации» почти везде следует читать «фрагментации».
                              0
                              Спасибо, что заметили опечатки-поправил
                              В некоторых словах вместо «дефрагмент...» нужно поставить «фрагмент...»
                              +1
                              Обычно делал это джобой или в плане обслуживания (что почти одно и то же). Расписание: когда загрузка процессора не выше определенного порога или не реже одного раза в n дней в определенное время (количество дней и время под проект). Там же скрипт проверки на «захламлённость» индекса и выполнения его реорганизации\ребилда в зависимости от ситуации. Конечно, там был еще ряд проверок на схему таблицы (от этого зависело, например, как используется таблица, чем и как часто), размер + еще пару специфичных проверок.
                              По сути, схема та же: смотрим на индексы, ищем те, которые нужно переделать, если ничего не мешает — делаем что нужно. Только не было никаких вьюх в продакшен базе, всё в джобе (хранимки всё-таки создавались, если проверка занимала больше 20-30 строк). Правда, я пока не сталкивался с системами, где равномерная высокая нагрузка на одни и те же модули системы в течение суток, поэтому почти все проверки базировались на времени суток.
                                0
                                У нас все эти вьюхи и прочее сделаны в отдельной базе данных. Для простоты просто не акцентирую на это внимание. Мне план не подошел из-за того, что не бывает такого, что процессор не загружен.
                                0
                                А были ли попытки оценить эффект от этой постоянной дефрагментации? И оправдывает ли он дополнительную нагрузку на систему?

                                И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?
                                  0
                                  И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?

                                  Как раз с этого и начинали. И стандартные методы применяли, причем даже до меня делали.
                                  А были ли попытки оценить эффект от этой постоянной дефрагментации?

                                  Как раз этим и занимались статья. Результаты были хорошие, т е, как я и писал выше, всегда индексы были умеренно фрагментированы. А до этого фрагментированы одни хуже других и т. д.
                                  И оправдывает ли он дополнительную нагрузку на систему?

                                  Неправильный вопрос. Такой подход как раз убирает нагрузку, чем все и сразу дефрагментировать. Попробуйте сами-хуже не будет.

                                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                Самое читаемое