alert audit backup bashhistory bat centos email hp hyper-v ilo info job jobs linux log mail mssql prtg script shiping sms sql SQL Server Profiler sqript symantec Symantec Backup Exec tar tempdb windows xml архивирование бэкап задание индекс индексы лог размер реиндекс сеть смс таблица трасировка фрагментация хабр хранимая процедура

Июль 2017
Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Июн
	1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

реиндекс отмеченных сообщений

Как сформировать скрипт для реиндекса индексов mssql. Необходимо для каждого индекса с генерировать соответствующую ALTER INDEX команду

20.09.2016, Categories Mssql Comments Нет комментариев

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	PRINT ''Перестройка  [' + i.name + N'] начато ''  + Cast(GETDATE() as varchar);
	
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)
			
	PRINT ''Перестройка  [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) '
ELSE 'REORGANIZE'
		END + ';'
		
		


	FROM (
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(DB_ID('ax_molniya'), OBJECT_ID('dbo.INVENTTRANS'), NULL, NULL, 'LIMITED') s
		WHERE s.page_count > 128 -- > 1 MB
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 5
		GROUP BY s.[object_id], s.index_id
	) s
	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

	print (@SQL);

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

SELECT '

PRINT ''Перестройка [' + i.name + N'] начато '' + Cast(GETDATE() as varchar);

ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +

CASE WHEN s.avg_fragmentation_in_percent > 30

THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)

PRINT ''Перестройка [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) '

ELSE 'REORGANIZE'

END + ';'

FROM (

SELECT

s.[object_id]

, s.index_id

, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)

FROM sys.dm_db_index_physical_stats(DB_ID('ax_molniya'), OBJECT_ID('dbo.INVENTTRANS'), NULL, NULL, 'LIMITED') s

WHERE s.page_count > 128 -- > 1 MB

AND s.index_id > 0 -- <> HEAP

AND s.avg_fragmentation_in_percent > 5

GROUP BY s.[object_id], s.index_id

) s

JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

print (@SQL);

FETCH NEXT FROM cur INTO @SQL

END

CLOSE cur

DEALLOCATE cur

Что значит строка (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON) :

[stextbox id=»info» caption=»FILLFACTOR =»]Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0.[/stextbox]

[stextbox id=»info» caption=»PAD_INDEX «]Определяет разреженность индекса. Значение по умолчанию — OFF.
ON
Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.
OFF или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью. При этом остается достаточно места по крайней мере для одной строки максимального размера, которого может достигать индекс, в зависимости от набора ключей в промежуточных страницах.
Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL). [/stextbox]

[stextbox id=»info» caption=»STATISTICS_NORECOMPUTE «]Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.
ON
Устаревшие статистики не пересчитываются автоматически.
OFF
Автоматическое обновление статистических данных включено.
Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE. [/stextbox]

[stextbox id=»info» caption=» ALLOW_ROW_LOCKS «]Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы.
Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц. Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса. [/stextbox]

[stextbox id=»info» caption=»ALLOW_PAGE_LOCKS»]Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.
ON
Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке страниц определяет компонент Database Engine.
OFF
Блокировки страниц не используются.
ПримечаниеПримечание
Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.[/stextbox]

[stextbox id=»info» caption=»SORT_IN_TEMPDB»]Указывает, следует ли сохранять результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.
ON
Промежуточные результаты сортировки, которые используются при индексировании, хранятся в базе данных tempdb. Это может сократить время, требуемое для создания индекса, если база данных tempdb размещена на иных дисках, нежели пользовательская база данных. Однако это увеличивает использование места на диске, которое используется при индексировании.
OFF
Промежуточные результаты сортировки хранятся в той же базе данных, где и индекс.
Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается. [/stextbox]

[stextbox id=»info» caption=»ONLINE «]Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.
ПримечаниеПримечание
Операции с индексами в сети доступны только в выпусках SQL Server Enterprise, Developer и Evaluation.
ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в режиме в сети либо, происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.
OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) для этой таблицы. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT. [/stextbox]

Сбор статистики по заполнению фрагментации индекса на отдельных таблицах

11.05.2016, Categories Mssql Comments Нет комментариев

Первым делом создадим табличку куда будет собираться статистика.

 CREATE TABLE kmosk.dbo.reindeks_SALESLINE
  ( [database_id] VARCHAR(max)
      ,[object_id] VARCHAR(max)
      ,[index_id] VARCHAR(max)
      ,[partition_number] VARCHAR(max)
      ,[index_type_desc] VARCHAR(max) 
      ,[alloc_unit_type_desc] VARCHAR(max) 
      ,[index_depth] VARCHAR(max) 
      ,[index_level] VARCHAR(max) 
      ,[avg_fragmentation_in_percent] VARCHAR(max) 
      ,[fragment_count] VARCHAR(max) 
      ,[avg_fragment_size_in_pages] VARCHAR(max) 
      ,[page_count] VARCHAR(max) 
      ,[avg_page_space_used_in_percent] VARCHAR(max) 
      ,[record_count] VARCHAR(max) 
      ,[ghost_record_count] VARCHAR(max) 
      ,[version_ghost_record_count] VARCHAR(max) 
      ,[min_record_size_in_bytes] VARCHAR(max) 
      ,[max_record_size_in_bytes] VARCHAR(max) 
      ,[avg_record_size_in_bytes] VARCHAR(max) 
      ,[forwarded_record_count] VARCHAR(max)
    , vreme datetime NOT NULL DEFAULT GETDATE())

CREATE TABLE kmosk.dbo.reindeks_SALESLINE

( [database_id] VARCHAR(max)

,[object_id] VARCHAR(max)

,[index_id] VARCHAR(max)

,[partition_number] VARCHAR(max)

,[index_type_desc] VARCHAR(max)

,[alloc_unit_type_desc] VARCHAR(max)

,[index_depth] VARCHAR(max)

,[index_level] VARCHAR(max)

,[avg_fragmentation_in_percent] VARCHAR(max)

,[fragment_count] VARCHAR(max)

,[avg_fragment_size_in_pages] VARCHAR(max)

,[page_count] VARCHAR(max)

,[avg_page_space_used_in_percent] VARCHAR(max)

,[record_count] VARCHAR(max)

,[ghost_record_count] VARCHAR(max)

,[version_ghost_record_count] VARCHAR(max)

,[min_record_size_in_bytes] VARCHAR(max)

,[max_record_size_in_bytes] VARCHAR(max)

,[avg_record_size_in_bytes] VARCHAR(max)

,[forwarded_record_count] VARCHAR(max)

, vreme datetime NOT NULL DEFAULT GETDATE())

После этого создадим задание которое будет наполнять табличьку

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'ax_molniya');
SET @object_id = OBJECT_ID(N'ax_molniya.[dbo].[SALESLINE]');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;

 
   insert INTO kmosk.dbo.reindeks_SALESLINE
   ([database_id]
      ,[object_id]
      ,[index_id]
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count])
     
   SELECT 
   [database_id]
      ,[object_id]
      ,[index_id]
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count]
      
    FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')
    END;
    
    
 --INSERT INTO    kmosk.dbo.test2 (DATA)
  --VALUES (GETDATE());

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

SET @db_id = DB_ID(N'ax_molniya');

SET @object_id = OBJECT_ID(N'ax_molniya.[dbo].[SALESLINE]');

IF @db_id IS NULL

BEGIN;

PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

PRINT N'Invalid object';

END;

ELSE

BEGIN;

insert INTO kmosk.dbo.reindeks_SALESLINE

([database_id]

,[object_id]

,[index_id]

,[partition_number]

,[index_type_desc]

,[alloc_unit_type_desc]

,[index_depth]

,[index_level]

,[avg_fragmentation_in_percent]

,[fragment_count]

,[avg_fragment_size_in_pages]

,[page_count]

,[avg_page_space_used_in_percent]

,[record_count]

,[ghost_record_count]

,[version_ghost_record_count]

,[min_record_size_in_bytes]

,[max_record_size_in_bytes]

,[avg_record_size_in_bytes]

,[forwarded_record_count])

SELECT

[database_id]

,[object_id]

,[index_id]

,[partition_number]

,[index_type_desc]

,[alloc_unit_type_desc]

,[index_depth]

,[index_level]

,[avg_fragmentation_in_percent]

,[fragment_count]

,[avg_fragment_size_in_pages]

,[page_count]

,[avg_page_space_used_in_percent]

,[record_count]

,[ghost_record_count]

,[version_ghost_record_count]

,[min_record_size_in_bytes]

,[max_record_size_in_bytes]

,[avg_record_size_in_bytes]

,[forwarded_record_count]

FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')

END;

--INSERT INTO kmosk.dbo.test2 (DATA)

--VALUES (GETDATE());

Задание будет наполнять табличку в 8 утра и 8 вечера

AdminBD

Мета

Categories

Свежие записи

Свежие комментарии

реиндекс отмеченных сообщений

Как сформировать скрипт для реиндекса индексов mssql. Необходимо для каждого индекса с генерировать соответствующую ALTER INDEX команду

Сбор статистики по заполнению фрагментации индекса на отдельных таблицах