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

Откуда:
Сообщений: 137
Добрый день!

Есть задача которая звучит очень просто
-Нужно удалить ненужные индексы, и добавить нужные

Побродив в просторах интернете выяснил что с помощью Database Engine Tuning Advisor можно на основе статистики понять чего не хватает в бд (какие индексы нужны).
Там склоняется все к тому что надо профайлером записать в файл все что выполняется в бд (писать должно дней 5) и потом файл загрузить в Database Engine Tuning Advisor

Записав в профайлером в файл минут 10 работы сервера , адвизор выдает что нужно создать (статистику , индексы), но ни слова что удалить, наверное не так как то смотрю или статистика не показывает.
Вообщем хочу узнать насколько данный метод актуален, возможно есть более продвинутый метод.

Короче поделитесь опытом, кто как ищет ненужные индексы, и те которых нехватает , буду благодарен :).
2 июл 18, 11:53    [21536306]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
TaPaK
Member

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

как-то так... статистика использования
но по теме разгребаем только существенные индексы на больщих объектах
SELECT 
	object_name(i.object_id) AS TableName, 
	i.name AS [Unused Index],
	SUM(d.[used_page_count]) * 8/1024. AS IndexSizeMb,
	SUM(ISNULL(user_seeks,0)) as user_seeks,
	SUM(ISNULL(user_scans,0)) as user_scans,
	SUM(ISNULL(user_lookups,0)) as user_lookups,
	SUM(ISNULL(user_updates,0)) as user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
      AND i.index_id = s.index_id
      AND s.database_id = db_id()
LEFT JOIN
	 sys.dm_db_partition_stats	d
ON
	d.[object_id] = i.[object_id]
    AND d.[index_id] = i.[index_id]	
WHERE 
	i.Type <> 1	AND
	objectproperty(i.object_id, 'IsIndexable') = 1 AND 
	objectproperty(i.object_id, 'IsIndexed') = 1 AND
	(s.user_updates > 0 and s.user_seeks = 0 AND OR s.user_scans = 0 ) AND ... ) AND
	--object_name(i.object_id) LIKE '%'
GROUP BY 
	i.object_id,
	i.name
ORDER BY IndexSizeMB DESC
2 июл 18, 11:56    [21536314]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
aleks222
Member

Откуда:
Сообщений: 855
SELECT top(100)   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]
       , I.[NAME] AS [INDEX NAME]
       , USER_SEEKS
       , USER_SCANS
       , USER_LOOKUPS
       , USER_UPDATES 
  FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
       WHERE S.[OBJECT_ID] = OBJECT_ID('[Costa].[PriceCruiseCategory]')


OBJECT NAME	INDEX NAME	USER_SEEKS	USER_SCANS	USER_LOOKUPS	USER_UPDATES
PriceCruiseCategory	PriceCruiseCategory_pk	0	13	0	13
PriceCruiseCategory	FUX_CruCode_CatCode	26	12	0	13
PriceCruiseCategory	FUX_CruCode_CatCode(prices)	0	1	0	13


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

Короче, чем длиннее период наблюдения - тем точнее сведения.
2 июл 18, 11:58    [21536319]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ну и понимать что
автор
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
2 июл 18, 11:58    [21536320]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
kolyady
Member

Откуда:
Сообщений: 137
Спасибо !

Так как новичек в данном вопросе подскажите правильно ли я все понял

1) По last_USER_SEEK по минимальному значению этого поля можно понять с какого времени ведется статистика а меня min(last_USER_SEEK) и другие 3 поля показывают минимальную дату '2018-06-21 13:29:23.367', тобиш статистика у меня началась вестить с этого числа, хватит ли полторы недели для того чтобы уже делать какието выводы о нужности или ненужности индекса?

2) По колонкам ваших запросов :
- USER_SEEKSтут вроде все понятно каким количеством запросов юзается данный индекс (если не так то поправте).
- USER_SCANS , USER_LOOKUPS , USER_UPDATES не совсем понятно на что здесь обращать внимание ?

Для наглядности запрос вернул такие данные:

К сообщению приложен файл. Размер - 35Kb
2 июл 18, 12:24    [21536398]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
TaPaK
Member

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

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

если все(Seek + SCAN + Lookup) ноль, то индексом никогда не пользовались, но это не значить что и не будут :)
2 июл 18, 12:28    [21536410]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1466
kolyady
хватит ли полторы недели для того чтобы уже делать какието выводы о нужности или ненужности индекса?
системы бывают разные.... в некоторых системах период активности запроса может определяться "периодами бухгалтерской отчетности": ежечасно, посуточно, неделя/декада, месяц, квартал, полугодие, год. Вы уверены, что за 1.5 недели ВСЕ виды нагрузок вашей системы успели отработать не менее одного раза? А то снесёте индекс, нужный лишь для сдачи годовой отчетности и будете удивляться в конце отчетного периода, что "как же так - раньше всегда работало быстро, а теперь ушло на сутки в себя"...
2 июл 18, 12:29    [21536411]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
kolyady
Member

Откуда:
Сообщений: 137
Ясно тогда еще недельку подождем (как раз закрытие месяца идет) :).

Все таки по колонкам можно уточнить
USER_SCANS
, USER_LOOKUPS
, USER_UPDATES

Почитал в доках описание этих полей чесно говоря до конца не понял что в них накапливается и нужно ли это учитывать
Если кто в знает как простонароду обьяснить что в них и насколько это нужно учитывать, буду признателен :).
2 июл 18, 12:55    [21536472]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
kolyady
Member

Откуда:
Сообщений: 137
Люди добрые помогите отличить одно от другого в доке пишется
user_seeks BIGINT Количество запросов по пользовательским запросам.
user_scans BIGINT Количество сканирований по пользовательским запросам, которые не использовали предикат «искать».
user_lookups BIGINT Количество запросов по закладкам по пользовательским запросам.
user_updates BIGINT Количество обновлений по пользовательским запросам. Сюда входят Insert, Delete и Updates,
представляющие количество операций, которые не были затронуты. Например, если вы удаляете 1000 строк
в одном выражении, это число увеличивается на 1

user_seeks - это непосредственно сами запросы пользователей (тобиш пользовательские запросы затрагивают данный индекс).

user_scans и user_lookups что показывает ? в каких случаях что то туда попадает?

user_updates - наверное отображает любые операции по добавлению апдейту и делейту операции.

помогите понять что такое user_scans и user_lookups желательно на примере
2 июл 18, 14:42    [21536836]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
TaPaK
Member

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

https://docs.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017
2 июл 18, 14:46    [21536849]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
Idol_111
Member

Откуда:
Сообщений: 598
kolyady,

ну в принципе Вы уже все и написали.

Сканирование - это когда читается весь индекс без использования поиска. К примеру, у вас индекс (date, id), а вам нужно найти date where id=17.

Lookups - это когда каких то данных не хватает в индексе и запрос после поиска лезет в кластерный индекс (или кучу), чтобы взять их. К примеру, то же индекс, но нужно найти id, city where date =2017. Вот после поиска по индексу запрос полезет (lookups) в кластерный за city. И будет у вас seek =+1 и lookup=+1.
10 июл 18, 04:54    [21558151]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация БД - путем удаления ненужных индексов и добавлением нужных  [new]
otets1988
Member

Откуда:
Сообщений: 21
В приницпе со скриптами уже разобрались, осталось только голову подключить, но раз тему подняли, то я оставлю еще парочку скриптов

SELECT 'Identify the missing indexes (TOP 10), across ALL databases.'
/* Purpose: Identify the missing (or incomplete indexes) (TOP 10), for ALL databases.				*/
/* Notes : 1. Could combine above with number of reads/writes a DB has since reboot, but this takes */
/*		   into account how often index could have been used, and estimates a 'realcost'			*/
SELECT  TOP 10 
		[Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
		, avg_user_impact -- Query cost would reduce by this amount, on average.
		, TableName = statement
		, [EqualityUsage] = equality_columns 
		, [InequalityUsage] = inequality_columns
		, [Include Cloumns] = included_columns
FROM		sys.dm_db_missing_index_groups g 
INNER JOIN	sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
INNER JOIN	sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;


SELECT 'Identify which indexes are not being used, across ALL databases.'
/* Purpose: Identify which indexes are not being used, for a given database.							*/
/* Notes: 1. These will have a deterimental impact on any updates/deletions.							*/
/*		  Remove if possible (can see the updates in user_updates and system_updates fields)			*/
/*		  2. Systems means DBCC commands, DDL commands, or update statistics - so can typically ignore.	*/
/*		  3. The template below uses the sp_MSForEachDB, this is because joining on sys.databases		*/
/*			gives incorrect results (due to sys.indexes taking into account the current database only).	*/ 	

-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in following step.
SELECT TOP 1
		DatabaseName = DB_NAME()
		,TableName = OBJECT_NAME(s.[object_id])
		,IndexName = i.name
		,user_updates	
		,system_updates	
		-- Useful fields below:
		--, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
	AND	user_seeks = 0
	AND user_scans = 0 
	AND user_lookups = 0
	-- Below may not be needed, they tend to reflect creation of stats, backups etc...
--	AND	system_seeks = 0
--	AND system_scans = 0
--	AND system_lookups = 0
	AND s.[object_id] = -999  -- Dummy value, just to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB	'USE [?]; 
-- Table already exists.
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10	
		DatabaseName = DB_NAME()
		,TableName = OBJECT_NAME(s.[object_id])
		,IndexName = i.name
		,user_updates	
		,system_updates	
		-- Useful fields below:
		--, *
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
	AND	user_seeks = 0
	AND user_scans = 0 
	AND user_lookups = 0
    AND i.name IS NOT NULL	-- I.e. Ignore HEAP indexes.
	-- Below may not be needed, they tend to reflect creation of stats, backups etc...
--	AND	system_seeks = 0
--	AND system_scans = 0
--	AND system_lookups = 0
ORDER BY user_updates DESC
;
'

-- Select records.
SELECT TOP 10 *  FROM #TempUnusedIndexes ORDER BY [user_updates]  DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes


Разумеется TOP управляем на свое усмотрение
10 июл 18, 08:14    [21558220]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить