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

Откуда:
Сообщений: 633
Помогите разобраться пожалуйста.

При каких условиях (почему) Сервер, используя нужный индекс при запросе, запущенном вручную,
не использует его (нужный индекс) при запуске того же запроса приложением?
Параметры одинаковые.

Дополнение:
SQL trace показывает перекомпиляцию запроса в случае запуска запроса приложением.
(SQL Server 9.0.4309)
3 дек 10, 06:56    [9878891]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
Запрос типа:
SELECT MAX(keys)
FROM messager 
WHERE
	outputs                               = @P1
AND request_id COLLATE Latin1_General_CS_AS = @P2
AND request_id                              = @P3

AND statusr COLLATE Latin1_General_CS_AS <> @P4
AND statusr                              <> @P5

AND participant COLLATE Latin1_General_CS_AS = @P6
AND participant                              = @P7
"keys" - varchar
3 дек 10, 07:02    [9878898]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
лолл
Member

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

что значит "перекомпиляция"? двойная компиляция? ибо в любом случае будет как минимум одна компиляция динамического запроса. Вы уверены, что планы одинаковы в обоих случаях? крайнее решение в качестве "заплатки" - with(index(..))
3 дек 10, 10:28    [9879620]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
лолл
Idol_111,

что значит "перекомпиляция"? двойная компиляция? ибо в любом случае будет как минимум одна компиляция динамического запроса. Вы уверены, что планы одинаковы в обоих случаях? крайнее решение в качестве "заплатки" - with(index(..))

SQL trace отлавливает state - Recompiled, что и означает рекомпиляция.

Есстественно планы разные, если в одном случае индекс используется, а в другом нет.

Изменить запрос невозможно (в смысле нельзя). Нужно играть индексами и статистикой.
5 дек 10, 22:53    [9890009]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Idol_111
AND request_id COLLATE Latin1_General_CS_AS = @P2
AND request_id                              = @P3
Если есть индекс по request_id, то из-за COLLATE и не используется.
Прежде, чем сопоставить значение с @P2, серверу нужно перебрать все записи, чтобы проверить, что будет с каждой строкой после преобразования в нужный COLLATION.
И зачем сравнивать дважды - с COLLATION и без?
6 дек 10, 00:21    [9890166]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
лолл
Member

Откуда:
Сообщений: 450
кстати да, имеет смысл привести параметр к коллейшену поля а не наоборот, если на то уж пошло... но сам запрос все еще не вполне ясен.
6 дек 10, 01:30    [9890259]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
DeColo®es
Если есть индекс по request_id, то из-за COLLATE и не используется.
Прежде, чем сопоставить значение с @P2, серверу нужно перебрать все записи, чтобы проверить, что будет с каждой строкой после преобразования в нужный COLLATION.

Я использую индекс:
CREATE NONCLUSTERED INDEX [IX_forMAX] ON [dbo].[messager] 
(
	[request_id] ASC,
	[statusr] ASC,
	[outputs] ASC,
	[participant] ASC
)
INCLUDE ( [keys])
Но почему, когда я запускаю этот же запрос вручную из MSСтудии, индекс используется?

DeColo®es
И зачем сравнивать дважды - с COLLATION и без?
Без понятия, программа не моя.
У базы другой collation (SQL_Latin1_General_CP1_CI_AS), может поэтому.
6 дек 10, 01:51    [9890276]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
Когда запрос запускается программой, происходит сканирование по индексу, который содержит только "keys" (причем Estimated number of rows = 4, а Actual number of rows > 100 000) и predicate - "keys" is not null. Ну а потом уже вытаскивает остальные данные по lookup, объединяет и фильтрует.

Когда запрос запускается вручную, идет преобразование констант и поиск по индексу (IX_forMAX см выше) (Estimated number of rows = 800, а Actual number of rows = 40 000), затем объединение и суммирование.
6 дек 10, 02:16    [9890296]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Александр Третьяков
Member

Откуда: Украина, г. Тернополь
Сообщений: 549
в двух словах...
если ты используеш запрос выполненный в КвериАналайзер, то оптимизатор анализирует и ПРАВИЛЬНО строит план выполнения.
если ты запускаеш этот же код из хранимой процедуры, и параметры хранимой ПРОЦЕДУРЫ есть ПАРАМЕТРАМИ запроса, то оптимизатор может НЕ ПРАВИЛЬНО построить план, для того чтобы он правильно сделал план нужно передать данные параметров локальным параметрам процедуры.

Это называется параметер сниффинг.

Я тоже мучился почему тут индекс берется, а тут опа... и перестал браться, я уже явно его прибивал хинтами, все равно оптимизатор его игнорировал. Можешь посмотреть по последним мои топикам.
6 дек 10, 10:30    [9890937]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
_djХомяГ
Guest
автор
(причем Estimated number of rows = 4, а Actual number of rows > 100 000)


А со статистикой то всё в порядке?
6 дек 10, 12:44    [9891886]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
Александр Третьяков,
Спасибо Вы правы, совсем вылетело это из головы.
После детальной проверки оптимизатору понравился чуть измененный индекс. Улучшение только на 34% вместо предполагаемых 86, но в любом случае исчезает сканирование (что более важно в данном случае).
CREATE NONCLUSTERED INDEX [IX_forMAX] ON [dbo].[messager] 
(
	[outputs] ASC,
	[request_id] ASC,
	[statusr] ASC,
	[participant] ASC
)
INCLUDE ( [keys])
Как я уже упоминал, код менять нельзя, так что надеюсь на реале новый индекс оптимизатору понравиться тоже.
7 дек 10, 03:59    [9896079]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
_djХомяГ
А со статистикой то всё в порядке?

По случайному стечению обстоятельств, вся статистика обновляется за час до этого в принудительном порядке. :)
7 дек 10, 04:02    [9896080]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
К сожалению, на реале работать не стал. Опять сканирование по полной программе.
Возможно что-нибудь еще предпринять без изменения кода?
Готов проверить любые идеи :).

Может кто подскажет как залезть глубже в SQL server и проверить, почему он выбирает такой план (может через расширенные процедуры или какой тул есть)?
7 дек 10, 23:04    [9902141]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
Idol_111
Готов проверить любые идеи :).
Может, так попробовать
CREATE NONCLUSTERED INDEX [IX_forMAX] ON [dbo].[messager] 
(
	[outputs] ASC,
	[request_id] ASC,
	[participant] ASC,
	[statusr] ASC
)
INCLUDE ( [keys])
Idol_111
(может через расширенные процедуры или какой тул есть)?
Посмотрите sys.dm_db_missing_index_details и sys.dm_db_missing_index_groups.

Хотя они вроде к 2008 относятся...
7 дек 10, 23:36    [9902225]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
alexeyvg
Может, так попробовать
В прошлый раз, я поменял индекс, т.к. запрос завернутый в хранимую процедуру выбирал именно его по каким-то причинам.
alexeyvg
Посмотрите sys.dm_db_missing_index_details и sys.dm_db_missing_index_groups.

Хотя они вроде к 2008 относятся...

Уже смотрел, ничего. (они есть в 2005).
8 дек 10, 01:40    [9902428]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Mr Index
Guest
Idol_111

CREATE NONCLUSTERED INDEX [IX_forMAX] ON [dbo].[messager] 
(
	[outputs] ASC,
	[request_id] ASC,
	[statusr] ASC,
	[participant] ASC
)
INCLUDE ( [keys])
.


Этот постройте -
CREATE NONCLUSTERED INDEX [IX_forkey] ON [dbo].[messager] (keys,request_id,participant,outputs)
8 дек 10, 01:51    [9902437]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
max44
Member

Откуда: МОСКВА
Сообщений: 280
To Idol_111
Если у вас есть удачный (где используются индекс) план, то попробуйте заставить сервер выполнять запрос по этому плану, подставив ему его ...

sp_create_plan_guide 

посмотреть в BOL: Optimizing Queries in Deployed Applications by Using Plan Guides
(Оптимизация запросов в используемых приложениях с помощью руководств планов )
8 дек 10, 07:33    [9902632]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
Mr Index
Этот постройте -
CREATE NONCLUSTERED INDEX [IX_forkey] ON [dbo].[messager] (keys,request_id,participant,outputs)

Намек понял, построил, но чуть другой. Сканирование не исчезло, но улучшение на лицо.

Решил остановиться на этом индексе, так как это позволяет заменить другой индекс.
9 дек 10, 01:21    [9908229]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
max44,
Спасибо, попробую, но позже.
Времени нет, да и опыт у меня с этой фичей не очень позитивный. Как-то коряво она работала.
9 дек 10, 01:24    [9908234]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Mr Index 2
Guest
Idol_111
Намек понял, построил, но чуть другой. .


Вы хоть не додумайтесь statusr в индекс пихать
9 дек 10, 01:29    [9908238]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Idol_111
Member

Откуда:
Сообщений: 633
Mr Index 2
Idol_111
Намек понял, построил, но чуть другой. .


Вы хоть не додумайтесь statusr в индекс пихать

почему нет? так и сделал. только в этом и суть (полностью покрывающий индекс).
9 дек 10, 01:43    [9908248]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
iljy
Member

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

CREATE NONCLUSTERED INDEX [IX_forMAX] ON [dbo].[messager] 
(
	[request_id] ASC,
	[outputs] ASC,
	[participant] ASC,
        [keys] DESC
)
INCLUDE ([statusr])
9 дек 10, 02:09    [9908261]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Это хорошо известная проблема. в QA/SSMS значение параметра заранее известно - поэтому оптимизатор принимает решение что некий индекс _для_данного_значения_ будет полезен. Когда же вы засовываете это же запрос в SP или prepared SQL - план генерится исходят из предположения что переменная может иметь любое значение. А в таком случае индекс будет неэффективен. Пример

create table Product (Product_id INT IDENTITY, Product_name CHAR(8000), Product_type bigint) 
GO
CREATE CLUSTERED INDEX [PK_index] ON [dbo].Product (Product_id ASC) 
GO
CREATE NONCLUSTERED INDEX [MyLovelyIndex] ON [dbo].Product (Product_type ASC)
GO
insert into Product (Product_name, Product_type )VALUES('a', 1)
GO 5
insert into Product (Product_name, Product_type )VALUES('a', 2)
GO 500000 -- ОБРАТИТЕ ВНИМАНИЕ НА ЭТО!!! *************************************
insert into Product (Product_name, Product_type )VALUES('a', 3)
GO 5
insert into Product (Product_name, Product_type )VALUES('a', 4)
GO 5
insert into Product (Product_name, Product_type )VALUES('a', 5)
GO 5
insert into Product (Product_name, Product_type )VALUES('a', 6)
GO 5
CREATE PROCEDURE SP_TEST1 (@w int)
AS
select Product_name from Product where Product_type = @w

Теперь посмотрите что будет с вызовами:


SET STATISTICS TIME ON
GO
EXEC SP_TEST1 2
EXEC SP_TEST1 1
EXEC SP_TEST1 3
EXEC SP_TEST1 4
GO
DBCC FREEPROCCACHE
GO 
DBCC DROPCLEANBUFFERS
GO
EXEC SP_TEST1 1
EXEC SP_TEST1 2
EXEC SP_TEST1 3
EXEC SP_TEST1 4

Правда интересно?

А вот как бы я создал индексы и переписал бы SP и индексы на 2008

CREATE NONCLUSTERED INDEX [MyLovelyIndex_filtred1] ON [dbo].Product (Product_type ASC) WHERE Product_type = 1
CREATE NONCLUSTERED INDEX [MyLovelyIndex_filtred2] ON [dbo].Product (Product_type ASC) WHERE Product_type > 2 and Product_type < 6
CREATE NONCLUSTERED INDEX [MyLovelyIndex_filtred3] ON [dbo].Product (Product_type ASC) WHERE Product_type >= 6


CREATE PROCEDURE SP_TEST2 (@w bigint) 
AS 
IF 
	@w = 1 
	select Product_name from Product where Product_type = @w AND Product_type = 1
ELSE 
	IF (@w > 2 AND @w < 6)
	select Product_name from Product where Product_type = @w AND Product_type > 2 AND Product_type < 6
	ELSE 
		IF @w >= 6
		select Product_name from Product where Product_type = @w AND Product_type >= 6
		ELSE 
			select Product_name from Product where Product_type = @w 

Вот это бы работало устойчиво ВСЕГДА и жрало бы минимальное количество памяти как на диске так и в RAM
9 дек 10, 06:48    [9908398]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 532
Ойц.. пример вырвал из другого примера :) Конечно же вот это должно быть:


CREATE NONCLUSTERED INDEX [MyLovelyIndex_filtred1] ON [dbo].Product (Product_type ASC) WHERE Product_type = 1
CREATE NONCLUSTERED INDEX [MyLovelyIndex_filtred2] ON [dbo].Product (Product_type ASC) WHERE Product_type > 2 

CREATE PROCEDURE SP_TEST2 (@w bigint) 
AS 
IF 
	@w = 1 
	select Product_name from Product where Product_type = @w AND Product_type = 1
ELSE 
	IF @w > 2 
                 select Product_name from Product where Product_type = @w AND Product_type > 2 
		ELSE 
	                    select Product_name from Product where Product_type = @w

Кстати, почему я добавил AND Product_type = 1 и AND Product_type > 2 - ответ для любознательных в БОЛ
9 дек 10, 06:59    [9908407]     Ответить | Цитировать Сообщить модератору
 Re: Не используется нужный индекс  [new]
Александр Третьяков
Member

Откуда: Украина, г. Тернополь
Сообщений: 549
хоть и прошло больше года.
я любознательный, и ненашел в БОЛ ответ
Product_type > 2 - ответ для любознательных в БОЛ
22 сен 11, 11:07    [11317366]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить