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

Откуда: Moscow
Сообщений: 610
Доброго времени суток!
СУБД: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Есть запрос:
select top 10 f.TypeForm
, p1.PrName as NameForm
from mRQForm f (nolock/* index = XPKmRQForm*/)
join mSMProperties p1(nolock) on p1.IdProperty = f.TypeForm  


На таблице mRQForm есть индекс:

+

CREATE UNIQUE NONCLUSTERED INDEX [XPKmRQForm] ON [dbo].[mRQForm] 
(
	[IDForm] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]




Несмотря на наличие индекса оптимизатор предпочитает делать полный скан таблицы и запрос выполняется 10 сек.
Если писать хинтом использование индекса до запрос выполняется 0 сек.

статистика io без хинта индекса:

Table 'mRQForm'. Scan count 1, logical reads 487231, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mSMProperties'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

статистика io с использованием хинта индекса:

Table 'mSMProperties'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mRQForm'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

На обоих таблицах состояние индексов и статистики отличное.

Прикладываю план запроса без хинта

К сообщению приложен файл (без хинта индекса.sqlplan - 6Kb) cкачать
3 апр 14, 11:42    [15826098]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
План запроса с хинтом

К сообщению приложен файл (хинт с индексом.sqlplan - 10Kb) cкачать
3 апр 14, 11:43    [15826102]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
Glory
Member

Откуда:
Сообщений: 104760
andrew shalaev
Несмотря на наличие индекса оптимизатор предпочитает делать полный скан таблицы

А как этот индекс поможет сделать соединение таблиц ?
3 апр 14, 11:50    [15826159]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Glory,

Соединение не поможет, но в итоге сканирование по нему выходит быстрее, чем сканирование всей таблици.
3 апр 14, 11:53    [15826180]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
кстати если построить кластреный уникальный индекс вместо некластреного, то запрос в любом случае будет выполнятся 10 сек.
3 апр 14, 11:54    [15826186]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
Glory
Member

Откуда:
Сообщений: 104760
andrew shalaev
но в итоге сканирование по нему выходит быстрее

А нафиг его сканировать, если в нет нужного для соединения поля ?
3 апр 14, 11:55    [15826194]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
SomewhereSomehow
Member

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

У вас в запросе есть top, когда используется top оптимизатор задействет механизм row goal (целевое число строк), т.е. он заранее знает сколько ему нужно получить.

По этому, рассматривая сканирование как вариант доступа, он предполагает, что чтобы получить всего 10 строк нужно сканировать не всю таблицу, а только ее часть, предполагая равномерное распределение он вычисляет эту часть. Далее сравнивает ее стоимость со стоимостью случайного доступа, а поскольку последовательный дешевле чем случайны, часто выбирается сканирование.
Если распределение неравномерное, то происходит такая картина:
Картинка с другого сайта.
Подробнее описывал тут.
Я бы на вашем месте, сделал бы индекс по TypeForm: create index ix_TypeForm on mRQForm(TypeForm);

П.С.
Но что более интересно, если посмотреть на ваши планы - почему у вас нету атрибута TableCardinality на терминальных узлах? Невозможно понять, сколько строк в таблицах, в частности в таблице [mRQForm]. Если у вас UNIQUE NONCLUSTERED INDEX [XPKmRQForm] ON [dbo].[mRQForm], в плане с хинтом - "Index Scan" по "[mRQForm].[XPKmRQForm]", при этом нет предикатов, выражение в top больше чем оценочное и реальное число строк и при этом оценивается и возвращается одна строка.

Т.е. что, в таблице одна строка? Опять же - почему нет свойства TableCardinality? Почему тогда в другом плане другое число строк, показывает по миллиону строк на одну итерацию внутри NL. Кроме-того, в плане есть пропущенные ИД узлов, как будто не все узлы в плане.

Вощем странные какие-то планы...руками не редактировались? Или мож утилитой какой доставались или обрабатывались? Версия сервера, кстати, какая?
3 апр 14, 12:42    [15826534]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
SomewhereSomehow
Member

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

А все, версию сервера увидел, в самом начале, проглядел. Может странности плана ей объясняются, не знаю, нет инстанса 2005 под рукой, чтобы проверить...

Кстати глядя на запрос, возникает вопрос - а вам вообще нужен там join?
Мож просто:
select top 10 p1.IdProperty, p1.PrName as NameForm from mSMProperties p1(nolock)
3 апр 14, 12:58    [15826681]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
SomewhereSomehow
Кстати глядя на запрос, возникает вопрос - а вам вообще нужен там join?

Join нужен, я привел упрощенный запрос.

SomewhereSomehow
Т.е. что, в таблице одна строка?

В таблице 1,7 млн строк

SomewhereSomehow
Вощем странные какие-то планы...руками не редактировались? Или мож утилитой какой доставались или обрабатывались?

Планы реальные, не изменялись. Сделаны в managemant studio

SomewhereSomehow
Я бы на вашем месте, сделал бы индекс по TypeForm

Так в итоге и сделал.
3 апр 14, 13:38    [15826917]     Ответить | Цитировать Сообщить модератору
 Re: Непонятная работа оптимизатора  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
andrew shalaev
В таблице 1,7 млн строк
Планы реальные, не изменялись. Сделаны в managemant studio

Пытался репро сделать, не получается, чтобы в таблице было миллион строк, а давалась оценка без предикатов и с топ(10) - всего одна строка - видимо зависит от версии, или какой-то вашей очень особенной статистики. "Странности" плана видимо версией же и объясняются (кроме "пропущенных" ИД узлов).
Ну, главное корень проблемы понятен.
3 апр 14, 13:53    [15827062]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить