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

Откуда: Украина
Сообщений: 81
Всем привет.
Есть база с full text search + много текстовых полей ... стоит вопрос оптимизации скорости работы, решил прооптимизировать индексы по возможности ...

нарыл вот такую информацию ...
http://technet.microsoft.com/en-us/library/ms189858.aspx

запрос к моей базе к одной из таблиц ...

DECLARE @DB_ID INTEGER
DECLARE @OBJECT_ID INTEGER
DECLARE @OBJECT_NAME NVARCHAR(255)

SET @DB_ID = (select DB_ID())

SET @OBJECT_NAME = 'Item'
SET @OBJECT_ID = (select OBJECT_ID(@OBJECT_NAME))
SELECT 
	indexStats.index_id
	, [name]
	, avg_fragmentation_in_percent
FROM 
	sys.dm_db_index_physical_stats (@DB_ID, @OBJECT_ID, NULL, NULL, NULL) AS indexStats
    JOIN sys.indexes AS sysIndexes ON indexStats.object_id = sysIndexes.object_id 
		AND indexStats.index_id = sysIndexes.index_id

в cоответствии с примером выводит вот такую штуку

1 PK_ITEM 0,491688129243737
1 PK_ITEM 0
1 PK_ITEM 0
12 IX_Item_Scheme_ID 69,4736842105263
13 IX_Item_PartNumber 98,9795918367347
47 IX_Item_Path 99,468791500664

что значит, что мне надо выполнять команды

ALTER INDEX IX_Item_PartNumber on dbo.Item
REBUILD WITH (ONLINE = ON)

ALTER INDEX IX_Item_Path on dbo.Item
REBUILD WITH (ONLINE = ON)

ALTER INDEX IX_Item_Scheme_ID on dbo.Item
REBUILD WITH (ONLINE = ON)

очень срочно ... :). Данных в таблице не очень много, тысяч 150 ... но она будет расти, и вопрос фрагментации индексов в моем случае = как по мне, очень даже критичен.

Я думаю, что неплохо было бы написать job ... который бы бегал по наиболее критичным обьектам у меня и чекал вот такие пераметры ... если значения больше попроговых, то ребилдить индексы в момент простоя ... делать это в функции, которая бы принимала на вход имя обьекта ...

А теперь, если, можно вопросы - ...
1) А может это уже есть как-то на автоматизированной уровне и мне надо просто читать мануал внимательнее?
2) Если такого нет и надо делать вот такие запросы, то как мне по грамотному забирать результаты выполнения запроса и анализировать их? сохранять во временный тейбл и бегать по строкам курсором или еще как нибудь?
3) Может есть что -то получше этого процесса?

заранее спасибо
27 окт 09, 20:20    [7846615]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
да, еще одно ... почему у меня в результате продублировались индексы (3 строки для одного и того-же индекса) 2 с 0 значениями ...?
27 окт 09, 20:24    [7846625]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
Короче, написал я вот такую нмхитрую процедурку (может кому пригодится ...)
CREATE PROCEDURE _DBManagement_MonitorIndexes
	@OBJECT_NAME varchar(255)	
AS
BEGIN
	SET NOCOUNT ON;

	--table to store temp results with  statistics for indexes
    CREATE TABLE #tempData(
	IndexID INTEGER
	, IndexName VARCHAR(255)
	, IndexFragmentation FLOAT
PRIMARY KEY CLUSTERED (
	[IndexID] ASC
))

DECLARE @DB_ID INTEGER
DECLARE @OBJECT_ID INTEGER


DECLARE @IndexID INTEGER
DECLARE @IndexFragmentation INTEGER
DECLARE @IndexName VARCHAR(255)
DECLARE @IndexCursor CURSOR

-- actual query command for execution
DECLARE @IndexOptimizationCommand VARCHAR(500)

SET @IndexCursor = 
	CURSOR FOR
		SELECT IndexID 
		FROM #tempData

SET @DB_ID = (select DB_ID())

SET @OBJECT_ID = (select OBJECT_ID(@OBJECT_NAME))

Insert Into #tempData
SELECT DISTINCT
	indexStats.index_id
	, [name] as index_name
	, SUM(avg_fragmentation_in_percent) index_fragmentation
FROM 
	sys.dm_db_index_physical_stats (@DB_ID, @OBJECT_ID, NULL, NULL, NULL) AS indexStats
    JOIN sys.indexes AS sysIndexes ON indexStats.object_id = sysIndexes.object_id 
		AND indexStats.index_id = sysIndexes.index_id
GROUP BY indexStats.index_id, [name]

OPEN @IndexCursor

	FETCH NEXT 
	FROM @IndexCursor INTO @IndexID  

	WHILE @@FETCH_STATUS = 0
    BEGIN

		SET @IndexFragmentation = 
			(SELECT IndexFragmentation 
			FROM #tempData Where IndexID = @IndexID)
			
			IF @IndexFragmentation > 5 BEGIN
				IF @IndexFragmentation > 30 BEGIN
					SET @IndexName = 
						(SELECT IndexName 
						FROM #tempData Where IndexID = @IndexID)
					SET @IndexOptimizationCommand = 'ALTER INDEX ' + @IndexName +  ' on dbo.' + @OBJECT_NAME + ' REBUILD'
					--Print(@IndexOptimizationCommand)
					EXEC(@IndexOptimizationCommand)
				END 
				ELSE BEGIN
					SET @IndexName = 
						(SELECT IndexName 
						FROM #tempData Where IndexID = @IndexID)
					SET @IndexOptimizationCommand = 'ALTER INDEX ' + @IndexName +  ' on dbo.' + @OBJECT_NAME + ' REORGANIZE'
					--Print(@IndexOptimizationCommand)
					EXEC(@IndexOptimizationCommand)					
				END
			END
		-- clear used Items
		Delete From #tempData Where IndexID = @IndexID

	FETCH NEXT FROM @IndexCursor INTO @IndexID	
	END

CLOSE  @IndexCursor
DEALLOCATE @IndexCursor

DROP TABLE #tempData
END
GO

в качестве принимаемого параметра приходит имя таблицы, которую надо анализировать, вызов происходит или с помошью job -a после активного аплоада ... либо ручками в формате

exec _DBManagement_MonitorIndexes 'Item'

что не нравится ...
1) Есть курсор - может как-то без него можно бегать по записям
2) пришлось вводить SUM для избежания повторения индексов во временной таблице - почему-то PK выводятся 2+ раза в запросе ...
3) вроде бы индексы обновились - но вот в одной таблице (там только 2700 записей) - ничего не происходит ... может надо ввести еще ограничение на кол-во записей для работы, на маленьких обьемах толку не будет, как по мне ...
4) надо ввести проверку на наличие #tempData в базе - если есть, мочить или чистить и пересоздавать

народ, делитесь плиз советами и опытом ... может я зря страдал все это время?
29 окт 09, 14:08    [7856233]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
Glory
Member

Откуда:
Сообщений: 104760
BOL - sys.dm_db_index_physical_stats
Example D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
29 окт 09, 14:14    [7856308]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33364
Блог
-- фрагментированные индексы
SELECT TOP 100
       DatbaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       i.type_desc,
       [Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),
       page_count,
       partition_number,
       'alter index [' + i.name + '] on [' + sh.name + '].['+ OBJECT_NAME(s.[object_id]) + '] REBUILD' + case
                                                                                                           when p.data_space_id is not null then ' PARTITION = '+convert(varchar(100),partition_number)
                                                                                                           else ''
                                                                                                         end + ' with(maxdop = 1,  SORT_IN_TEMPDB = on)' [sql]
  FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
  INNER JOIN sys.indexes as i ON s.[object_id] = i.[object_id] AND
                                 s.index_id = i.index_id
  left join sys.partition_schemes as p on i.data_space_id = p.data_space_id
  left join sys.objects o on  s.[object_id] = o.[object_id]
  left join sys.schemas as sh on sh.[schema_id] = o.[schema_id]
  WHERE s.database_id = DB_ID() AND
        i.name IS NOT NULL AND   -- Ignore HEAP indexes
        OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
        page_count > 100 and
        avg_fragmentation_in_percent > 10
  ORDER BY page_count DESC

Затем курсор или цикл по записям поля [sql]
29 окт 09, 15:12    [7856916]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33364
Блог
Не могу сообразить, что вы хотели сказать вот этим кодом)
SELECT DISTINCT
	indexStats.index_id
	, [name] as index_name
	, SUM(avg_fragmentation_in_percent) index_fragmentation
FROM 
29 окт 09, 15:14    [7856935]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
значит так ...
за ссылку спасибо, нашел почти готовый пример :)
по поводу процедуры ... надо немного обновить :) ... если не получится заюзать пример по ссылке
CREATE PROCEDURE _DBManagement_MonitorIndexes_Optimized
	@OBJECT_NAME varchar(255)	
AS
BEGIN
	SET NOCOUNT ON;

DECLARE @DB_ID INTEGER
DECLARE @OBJECT_ID INTEGER

DECLARE @IndexID INTEGER
DECLARE @IndexFragmentation INTEGER
DECLARE @IndexName VARCHAR(255)
DECLARE @IndexCursor CURSOR

-- actual query command for execution
DECLARE @IndexOptimizationCommand VARCHAR(500)

SET @DB_ID = (select DB_ID())
SET @OBJECT_ID = (select OBJECT_ID(@OBJECT_NAME))

SET @IndexCursor = 
	CURSOR FOR
		SELECT DISTINCT
			indexStats.index_id
			, [name] as index_name
			, SUM(avg_fragmentation_in_percent) index_fragmentation
		FROM 
			sys.dm_db_index_physical_stats (@DB_ID, @OBJECT_ID, NULL, NULL, NULL) AS indexStats
			JOIN sys.indexes AS sysIndexes ON indexStats.object_id = sysIndexes.object_id 
				AND indexStats.index_id = sysIndexes.index_id
		GROUP BY indexStats.index_id, [name]

OPEN @IndexCursor

	FETCH NEXT 
	FROM @IndexCursor INTO @IndexID, @IndexName, @IndexFragmentation  

	WHILE @@FETCH_STATUS = 0
    BEGIN

			IF @IndexFragmentation > 5 BEGIN
				IF @IndexFragmentation > 30 BEGIN
					SET @IndexOptimizationCommand = 'ALTER INDEX ' + @IndexName +  ' on dbo.' + @OBJECT_NAME + ' REBUILD'					
				END 
				ELSE BEGIN
					SET @IndexOptimizationCommand = 'ALTER INDEX ' + @IndexName +  ' on dbo.' + @OBJECT_NAME + ' REORGANIZE'
				END
				--Print(@IndexOptimizationCommand)
				EXEC(@IndexOptimizationCommand)	
			END		

	FETCH NEXT FROM @IndexCursor INTO @IndexID, @IndexName, @IndexFragmentation	
	END

CLOSE  @IndexCursor
DEALLOCATE @IndexCursor

END
GO

код ...
SELECT DISTINCT
	indexStats.index_id
	, [name] as index_name
	, SUM(avg_fragmentation_in_percent) index_fragmentation
FROM ...
GROUP BY indexStats.index_id, [name]
им я хотел сказать следующее )))

я уже писал что в первом варианте, если не группировать, то у меня вылезают повторяющиеся записи индексов, например 2 или 3 значения PK_... 1 из которых > 0, остальные = 0 ... в итоге получился эдакий извращенный способ упоминания 1 индекса в селекте :) ...
29 окт 09, 15:46    [7857257]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
2 Критик : интересный пример приведен, спасибо большое...
29 окт 09, 15:49    [7857279]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
Glory
Member

Откуда:
Сообщений: 104760
alexxUK

я уже писал что в первом варианте, если не группировать, то у меня вылезают повторяющиеся записи индексов, например 2 или 3 значения PK_... 1 из которых > 0, остальные = 0 ... в итоге получился эдакий извращенный способ упоминания 1 индекса в селекте :) ...

Они повторяются по тем полям, которые вы оставили в своем селекте. А не по всем полям, которые возвращает sys.dm_db_index_physical_stats
29 окт 09, 15:51    [7857293]     Ответить | Цитировать Сообщить модератору
 Re: Проблема оптимизации индексов, идентификаторы обьектов  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
понятно, спасибо :)
29 окт 09, 16:01    [7857384]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить