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

Откуда: Ростов-на-Дону
Сообщений: 344
Привет!
Подскажите, пожалуйста, есть задача по объединению\оптимизации индексов. Довольно большая таблица, она есть на нескольких серверах с данными разных компаний (разные клиенты на разных серверах хранятся). Для примерна на одном из серверов в таблице более 600 млн. строк. На таблице есть 2 больших индекса с include. При этом dba жалуется, что некоторые процедуры при использовании этих индексов вызывают загрузку ЦПУ до 90%, если он перекомпилирует процедуру, то она использует кластерный индекс и загрузка ЦПУ падает до 15%.
Я нашла вот этот пост, который позволяет выбрать процедуры из планов в кэше, чтобы определить какие процедуры сейчас используют эти индексы.
[url=]https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/[/url]

Вот скрипт, который используется
CREATE PROCEDURE spGetPlanUsingIndex
	@indexName NVARCHAR(128)
AS
BEGIN	
	SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 	

	-- Make sure the name passed is appropriately quoted 
	IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName); 

	WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	INSERT INTO [dbo].[LogDataFileIndexUsage]
	(
		DBName,
		SPname, 
		ScanCount,
		SeekCount,
		UpdateCount,
		RefCount,
		UseCount,
		QueryPlan,
		IndexName
	)
	SELECT
		DB_NAME(E.dbid) AS [DBName],
		object_name(E.objectid, dbid) AS [ObjectName],
		E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=sql:variable("@IndexName")])','int')  AS [ScanCount],
		E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=sql:variable("@IndexName")])','int')  AS [SeekCount],
		E.query_plan.value('count(//Update/Object[@Index=sql:variable("@IndexName")])','int') AS [UpdateCount],	
		P.refcounts AS [RefCounts],
		P.usecounts AS [UseCounts],
		E.query_plan AS [QueryPlan],
		@IndexName
	FROM sys.dm_exec_cached_plans P
	CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
	WHERE	
		E.query_plan.exist('//*[@Index=sql:variable("@IndexName")]') = 1
	OPTION(MAXDOP 1, RECOMPILE);
END


Кроме того попросила дать информацию о missing index на сервере. Список процедур получился небольшим – 10, что интересно одна и та же процедура могла использовать то один индекс то другой. На сервере включен ассинхронный режим обновления статистики. Я выбрала в каких запросах используются эти индексы в процедурах по планам из кэша, и собрала список полей, указанных в SELECT, WHERE и JOIN. А также поля , которые использовались как seek предикаты. Сделала один индекс без include полей, при тестировании вижу такую картину – его использует меньшее число процедур, но скорость работы примерно та же. Я ожидала, что вырастет загрузка CPU, но нет – все ок.
Я понимаю, что убирая include – нужно будет делать Key Lookup чтобы довыбрать нужные поля из таблицы, при анализе плана – можно видеть, что план со старым индексом несколько лучше. Но если изменения позволят сэкономить место + избавят от внезапного изменения загрузки по ЦПУ, то их стоит применить.
Буду благодарна, если поделитесь своими мыслями\опытом решения такой задачи.

К сообщению приложен файл (table + indexes.sql - 2Kb) cкачать
1 сен 16, 12:44    [19616753]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
файл с анализом процедур и запросов

К сообщению приложен файл (sp list with fields.xlsx - 10Kb) cкачать
1 сен 16, 12:44    [19616757]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
Планы с исходными индексами 20160901 with include index usage plan и с новым 20160901 v4 index usage plan.

Новый индекс

StorageId
DirectoryId
FileStatus

К сообщению приложен файл (plan.zip - 30Kb) cкачать
1 сен 16, 12:47    [19616767]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
А можно текст запроса попросить указать? В авторах SergeyS увидел, прямо заинтриговало... :)
1 сен 16, 13:14    [19616945]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
AlanDenton,

К сообщению приложен файл (spGetFiles.sql - 3Kb) cкачать
1 сен 16, 13:29    [19617050]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Владислав Колосов
Member

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

вам можно посоветовать зафиксировать "хороший" план запроса в запросе при помощи OPTION (USE PLAN = N'...') или используя руководство планов. Но это дело не слишком надежное - при изменении запроса, появлении новых индексов можно потерять оптимальность выбранного плана.
1 сен 16, 17:40    [19618465]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
komrad
Member

Откуда:
Сообщений: 5249
Nimua
При этом dba жалуется, что некоторые процедуры при использовании этих индексов вызывают загрузку ЦПУ до 90%, если он перекомпилирует процедуру, то она использует кластерный индекс и загрузка ЦПУ падает до 15%.


если процедура дергается не часто, то можно её сделать with recompile и забыть
1 сен 16, 18:06    [19618582]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
komrad,

Она выполняется каждый день. И каждый день у него с ней проблемы (на разных серверах, но все равно). Насколько я знаю бывают и другие процедуры, которые при использовании этих индексов вызывают нетипичную загрузку ЦПУ, recompile всегда помогает в этом случае.
1 сен 16, 18:21    [19618618]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
komrad
Member

Откуда:
Сообщений: 5249
Nimua
komrad,

Она выполняется каждый день. И каждый день у него с ней проблемы (на разных серверах, но все равно). Насколько я знаю бывают и другие процедуры, которые при использовании этих индексов вызывают нетипичную загрузку ЦПУ, recompile всегда помогает в этом случае.


сделайте процедуре
alter proc ... with recompile...
и забудьте про проблему
http://www.sqlservergeeks.com/sql-server-stored-procedures-use-of-recompile-clause/


если, конечно, она выполняется не несколько раз в секунду или минуту - тогда будет повышенный overhead на компиляцию
1 сен 16, 18:33    [19618653]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Nimua,

автор
На сервере включен ассинхронный режим обновления статистики.


тоесть самостоятельно вы статистику не обновляете чтоли? дак вот вам и причина.
1 сен 16, 18:37    [19618660]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
WarAnt,

Нет, обновление статистики включено
опция
Auto Update Statistics - True
Auto Update Statistics Asynchronously - True


2komrad

Хочется оптимизировать БД, которая досталась по наследству. Оба индекса точно не нужны. Можно с уверенностью грохнуть один из них. Но также видно, что Name можно не включать в индекс, но возможно есть смысл включить его в Include columns.
1 сен 16, 20:51    [19619063]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
komrad
Member

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

а что у вас с
select @@version
?
1 сен 16, 21:14    [19619124]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
komrad,

Либо 2012 либо 2012 R2, нет доступа - точно смогу сказать только завтра
1 сен 16, 22:36    [19619327]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Владислав Колосов
Member

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

заем пересчитывать какие-то статистики, если вам УЖЕ известен гарантированно хороший план запроса. Рекомпиляция тоже занятная штука, но ее эффект основан на актуальных статистиках. Рассчитать статистику для 600 млн со слабым изменением наполненияданными - значит раз в несколько дней производить полный перерасчет статистик принудительно с фуллсканом. А аппаратура у вас не резиновая. В общем, хорошо подумайте.
1 сен 16, 23:19    [19619461]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Nimua,

Здравствуйте.

Версия 2012, видно из плана (2012 R2 вообще не существует).

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

Во-первых, допросите вашего ДБА. Он точно локализовал проблему? Если говорит, что да, требуйте доказательств. Данных с PerfMon, данных с DMV, планов запросов. Простого «я рекомпилирую процедуру и все начинает летать» недостаточно. Если они не предоставляются, но проблему вешают на вас и никак иначе, то вам придется самой собирать фактуру.

1) Подтверждение и локализация проблемы

Лучше начать с подтверждения и локализации проблемы. В момент нагрузки на сервер посмотрите счетчики процессора в PerfMon, чтобы подтвердить точно, что она есть и что это точно SQL Server и далее соберите данные из:
• sys.dm_exec_query_stats
• sys.dm_exec_procedure_stats
• sys.dm_os_wait_stats

Для каждого из DMV, лучше сделать слепок во временную таблицу в начале сбора данных, подождать пока сервер поработает и сделать еще один слепок. Потом сравнить две временные таблицы. Так вы поймете, какие процедуры и запросы в них сколько потребляют и какие при этом ожидания. После этого настройте либо сессию расширенных событий, либо SQL Trace на наиболее потребляющие запросы и при следующем пике, соберите актуальные планы. Если явных кандидатов не выявится, может у вас сильные adhoc нагрузки, просто много мелких, но разных запросов – в таком случае, группируйте по query_hash или plan_hash.

На данном этапе, вам в результате нужно получить актуальные (не оценочные) планы запросов, которые потребляют больше всего ресурсов. Либо выявить множество adhoc запросов, кандидатов на параметризацию (ведь компиляция каждого из них, очень сильно может грузить процессор, да и память занимает).

2) Анализ планов

У вас уже есть планы плохих запросов и статистика, кто сколько из них потребляет ресурсов. Теперь, рекомпилируйте процедуры. Если проблема исчезает, то подтверждается диагноз вашего ДБА и дело в процедурах. В таком случае, опять собирайте актуальные планы и сравнивайте «медленные» планы и «быстрые», особое внимание бращайте на параметры, прослушивание параметров с нетипичными значениями, одна из самых частых проблем с процедурами. Если все так, то у вас хотя бы будет конкретный проблемный запрос в процедуре, куда можно добавить option(recompile) или иные методы решения проблемы прослушивания параметров. Это лучше, чем лепить на все «подозрительные» процедуры with recompile, т.к. вы можете более адресно подойти к проблеме (вдруг не те процедуры тормозят, вдруг не все запросы в них надо рекомпилировать, вдруг есть более удачное решение чем рекомпиляция, и на крайний случай option9recompile) в запросе заставляет оптимизатор применять встраивание параметров и строить более оптимальные планы чем просто рекомпиляция процедуры).

3) Встраивание решения проблемы в общую логику

Если на п.2 выяснилось, что тормоза требут, добавления каких-то новых индексов, то тут как раз самое время проанализировать существующие и прикинуть, нельзя ли не добавляя новых модифицировать старые, чтобы было выгодно всем запросам. Тут, на последнем этапе, вам и пригодится вся статистика по использованию индексов что вы собрали, где как и т.д. Я имел такой опыт, действительно, в БД, которые тянутся с давних времен, есть возможность сильно оптимизировать физический дизайн БД, путем оптимизации индексов. Например, некоторые индексы делались в эпоху когда include директивы и в помине не было.

Ну и есть другой подход. Как чинят старый телевизор. «Бац, кулаком по нему, глядишь, показывать начал! А то ишь! Ремонтера вызывать, это ж скока денег!». Т.е. можно попробовать, процедуры порекомпилить, еще кэш почистить, вдруг сработает, дешево и сердито, не надо много времени тратить. Но это не мой метод, а т.к. я тут давал свое мнение правильного направления, то придерживаюсь первого варианта пути, вне зависимости от того, какой удар кулаком по SQL Server может в итоге сработать если вы начнете пробовать все что вам предлагают =)

В довесок, вот вам еще ссылки, почитайте:
Troubleshooting SQL Server CPU Performance Issues
Troubleshooting SQL Server high CPU usage
High CPU Troubleshooting with DMV Queries
где-то даже видое у меня было, найти счас ссылку не могу, поищите по "sql server high cpu troubleshooting"
2 сен 16, 00:31    [19619660]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
SomewhereSomehow,

Спасибо! это как раз то что хотела - и много больше!
Спасибо за очень четко прописанный план действий, ушла реализовывать.
2 сен 16, 10:17    [19620168]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
Mr. X
Guest
Nimua,

"При этом dba жалуется" - мне так думается, что это задача для dba.
2 сен 16, 10:20    [19620172]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
komrad
Member

Откуда:
Сообщений: 5249
Nimua
komrad,

Либо 2012 либо 2012 R2, нет доступа - точно смогу сказать только завтра

имел ввиду, что если у вас Enterprise и есть желание сократить размер БД, то можно посмотреть на data compression
2 сен 16, 11:06    [19620346]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
o-o
Guest
komrad
Nimua
komrad,

Либо 2012 либо 2012 R2, нет доступа - точно смогу сказать только завтра

имел ввиду, что если у вас Enterprise и есть желание сократить размер БД, то можно посмотреть на data compression

экономия места это только одна сторона медали...
вы балком в compressed пробовали заливать?
скорость олала...
2 сен 16, 11:56    [19620643]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
komrad
Member

Откуда:
Сообщений: 5249
o-o
komrad
пропущено...

имел ввиду, что если у вас Enterprise и есть желание сократить размер БД, то можно посмотреть на data compression

экономия места это только одна сторона медали...
вы балком в compressed пробовали заливать?
скорость олала...


"можно посмотреть" != "это идеальное решение для экономии места"
2 сен 16, 12:39    [19620960]     Ответить | Цитировать Сообщить модератору
 Re: Замена\объединение индексов на работающей системе  [new]
o-o
Guest
komrad
"можно посмотреть" != "это идеальное решение для экономии места"

у нас не стали "смотреть", закомпрессили и все.
узнали мы об этом, потому что все...замедлилось.
они это на своем OLTP сделали,
нам же эту базу ресторят и делают READ ONLY.
так у всех наших массовых селектов сразу послетали планы:
на месте HASH JOIN-ов вдруг повылазили NESTED LOOPS, все это дело ушло в кому.
такие вот неожиданные последствия компрессии.
не знаю даже, на что гуглить.
прописываешь ему хинтом HASH JOIN, все возвращается в норму,
но он реально начал считать, что ему NL дешевле.
может, это какой-то баг 2008-ого R2, мы же сидим на SP1
2 сен 16, 12:59    [19621082]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить