Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 После перестроения индексов фрагментация остается  [new]
Bigzone
Member

Откуда:
Сообщений: 7
Добрый день.
На БД 30Гб
скриптом смотрю фрагментацию индексов на таблицах показывает 200 таблиц где фрагментация более 30%


Далее выполняю скрипт по перестроению индексов.
пример ниже.
после проверяю и ситуация с фрагментацией не меняется.
SQL2012 подскажите, что на это может влиять?
также выборочно на таблицах стредствами SQL смотрел фрагментацию индексов и пробовал перестроить и также % фрагментации не меняется к примеру как был 80% так и остается.

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
			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(), NULL, NULL, NULL, NULL) 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

	EXEC sys.sp_executesql @SQL

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur;
4 янв 18, 10:49    [21081292]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Bigzone
показывает 200 таблиц где фрагментация более 30%
Размером в 1 страницу?
4 янв 18, 11:23    [21081323]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Bigzone
Member

Откуда:
Сообщений: 7
да
4 янв 18, 12:01    [21081395]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Bigzone
да

Таблица в одну страницу занимает 8 Кбайт ("но это не точно" (с)), а скрипт в условии перебирает только те таблицы, где "WHERE s.page_count > 128 -- > 1 MB"...
4 янв 18, 13:47    [21081580]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Bigzone
Member

Откуда:
Сообщений: 7
Большое спасибо (Andy_OLAP)
поменял на
WHERE s.page_count > 0
дефрагментировалось 100 таблиц
осталось еще 160 таблиц, что еще можно попробовать?
5 янв 18, 04:16    [21082648]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Добрый Э - Эх
Guest
Bigzone,

попробуй почитать и осмыслить реализацию физического хранения данных в MS SQL Server-е. Чтобы не мучить ни сервер бессмысленными попытками дефрагментации того, что невозможно дефрагментировать, ни уважаемых форумчан глупыми вопросами о причинах своих неудач...
5 янв 18, 07:38    [21082723]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Bigzone
осталось еще 160 таблиц, что еще можно попробовать?
Не смотреть на таблицы, которые занимают одну страницу.

Вы думаете, зря стояло условие "s.page_count > 128 -- > 1 MB"? Думаете, это просто бага в скрипте?

Не мучайте сервер, не дефрагментируйте маленькие таблицы.

Если же начальник говорит "всё равно дефрагментируйте, пусть даже серверу будет хуже", то перестройте кластерные индексы.
5 янв 18, 12:17    [21082947]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
Bigzone
Большое спасибо (Andy_OLAP)
поменял на
WHERE s.page_count > 0
дефрагментировалось 100 таблиц
осталось еще 160 таблиц, что еще можно попробовать?


Зачем вы так агрессивно пытаетесь дефрагментировать всё, что видите?
Цель?
5 янв 18, 12:59    [21083020]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
xenix
Guest
256k,
автор
Зачем вы так агрессивно пытаетесь дефрагментировать всё, что видите?
Цель?

Шоб красиво
5 янв 18, 13:02    [21083029]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
256k
Bigzone
Большое спасибо (Andy_OLAP)
поменял на
WHERE s.page_count > 0
дефрагментировалось 100 таблиц
осталось еще 160 таблиц, что еще можно попробовать?


Зачем вы так агрессивно пытаетесь дефрагментировать всё, что видите?
Цель?
Начальник сказал?
5 янв 18, 13:24    [21083083]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Bigzone
Member

Откуда:
Сообщений: 7
либо как исключить таблицы из результат запроса который показывает таблицы к переиндексации
DECLARE @db_name varchar(50) = N'db_name',
                @table_name varchar(250) = N'db_name.dbo.tbl_name'

SELECT  IndStat.database_id, 
                IndStat.object_id, 
                QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name], 
                IndStat.index_id, 
                QUOTENAME(i.name) AS index_name,
                IndStat.avg_fragmentation_in_percent,
                IndStat.partition_number, 
                (SELECT count (*) FROM sys.partitions p
                        WHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count 
FROM sys.dm_db_index_physical_stats
    (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS IndStat
        INNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)
WHERE IndStat.avg_fragmentation_in_percent > 10 AND IndStat.index_id > 0
9 янв 18, 08:56    [21088749]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Bigzone
Member

Откуда:
Сообщений: 7
Запустил стандартный SQL отчет "Физическая статистика индекса" на БД он также показывает рекомендации перестроить либо реорганизовать.
Значит получается скрипт не все делает
DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
			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(), NULL, NULL, NULL, NULL) 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

	EXEC sys.sp_executesql @SQL

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur;
15 янв 18, 06:08    [21106278]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Bigzone, уважаемый в той статье откуда Вы брали этот скрипт внизу все написано. Ограничение в 1Мб вполне нормально и чуть не понятна суть Ваших проблем.
15 янв 18, 10:00    [21106547]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Bigzone
Member

Откуда:
Сообщений: 7
Из запроса убрал условия ограничения по перестроению индекса.
все равно остаются более 150 таблиц и фрагментироваными индексами. И стандартный отчет SQL рекомендует их перестроить.
DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON)'
			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(), NULL, NULL, NULL, NULL) s
		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

	EXEC sys.sp_executesql @SQL

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur;
15 янв 18, 10:04    [21106559]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
Добрый Э - Эх
Guest
Bigzone,

ты как Партос из мушкетеров, что ли - "дерусь, потому что - дерусь"(с)?
цель всего этого действия какая? красивые показатели в отчете?
Реальные проблемы есть, которыми можно руки занять?
Или всё остальное уже решено?
Если таблица маленькая и хранится в смешанных экстентах - ты её хоть зафрагментируйся - раньше дырку протрешь на блине диска, чем избавишься от фрагментации...
15 янв 18, 10:09    [21106576]     Ответить | Цитировать Сообщить модератору
 Re: После перестроения индексов фрагментация остается  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Bigzone,

вам уже с десяток раз ответили. Для небольших таблиц от фрагментации не измбавиться, ну или удалите индексы :)
15 янв 18, 10:11    [21106578]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить