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

Откуда:
Сообщений: 297
Всем привет, вопрос собсвенно по сабжу). Есть один запрос, как ни бился, пишет у итераторов(Index Seek Nonclustered, Lookup) что Estimated Number of Rows = 1. Статистику обновлял, все индексы перестроил. В чем может быть или это не проблема вовсе?
16 июл 13, 11:56    [14572212]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35369
Блог
kalimba,

а если к одной из табличек запроса применить sp_recompile?
16 июл 13, 12:02    [14572245]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Критик
kalimba,

а если к одной из табличек запроса применить sp_recompile?
Не помогло
16 июл 13, 12:07    [14572281]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
SomewhereSomehow
Member

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

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

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

У вас отличается реальное число строк от оценочного и если да, то насколько сильно? Если нет - то это, обычно, не проблема.
16 июл 13, 12:47    [14572620]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

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

Спасибо за развернутый ответ) Табличные переменные не используются, план прилагаю)

К сообщению приложен файл (tmp60C8_Anonymized.sqlplan - 46Kb) cкачать
16 июл 13, 12:53    [14572649]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Гость333
Member

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

Расхождение начинает идти от первого же index seek (по индексу Table1.Index1).
Там используется условие поиска:
table1.column13 = ScalarString12 and
table1.column14 > ScalarString13 and table1.column14 < ScalarString14

ScalarString12, ScalarString13, ScalarString14 — что это?
Сколько строк в таблице table1?
Какие estimated количества показываются в планах запросов:
select * from table1 where column13 = ScalarString12
и
select * from table1 where column14 > ScalarString13 and column14 < ScalarString14
?
16 июл 13, 13:47    [14573001]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
kalimba
Всем привет, вопрос собсвенно по сабжу). Есть один запрос, как ни бился, пишет у итераторов(Index Seek Nonclustered, Lookup) что Estimated Number of Rows = 1. Статистику обновлял, все индексы перестроил. В чем может быть или это не проблема вовсе?

у тебя просто неверная статистика. точнее имеющаяся статистика не может точно предугадать количество строк при выборке
16 июл 13, 13:52    [14573042]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
SomewhereSomehow
Member

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

Посмотрел план. Да, оптимизатор очень плохо оценил число строк: 1 vs 22313. Нужно понять причину таких плохих оценок. Согласно вашему анонимизированному плану при оценке используются предикаты:
Seek:
	Prefix: Database1.Schema1.Table1.Column13 = Scalar Operator(ScalarString12); 
	Start: Database1.Schema1.Table1.Column14 > Scalar Operator(ScalarString13); 
	End: Database1.Schema1.Table1.Column14 < Scalar Operator(ScalarString14)
Residual:
	ScalarString15

Т.е. оптимизатору, для вычисления селективности, необходимо скомбинировать как минимум 4 статистики. На первый взгляд думается, что:
- Либо он где-то ошибается на протяжении процесса комбинации вследствие ограничения модели.
- Либо, статистика нерепрезентативна/не по всем полям/мешают какие-то выражения - проверьте что статистики есть для каждого поля, обновите их все with fullscan (не только статистику по индексу), проверьте что вам не портит картину то, что у вас закодировано в Residual предикате в виде ScalarString15 - ибо, что это в анонимном виде - непонятно...
- Либо все вместе =)

Не буду гадать, т.к. не вижу запроса, но по структуре плана, похоже, что у вас есть некая cte/derived table с константами, которые в зависимости от неких параметров передаются в условия джойна. Я бы начал с того, что локализовал проблему, уменьшив план только до области в которой возникают неправильные оценки, т.е. где идет соединение константной таблицы с индексом. По сути оставить только константы и таблицу с индексом из которой выбираются только поля что есть в индексе, чтобы не было лукапов и всего остального. После этого, может быть выложить полный план или попробовать сделать репро. Тогда можно будет говорить более предметно.
16 июл 13, 14:04    [14573130]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,
автор
необходимо скомбинировать как минимум 4 статистики

Имеется ввиду конечно не 4 статистики, а 4 вероятности, чтобы посчитать общую вероятность попадания строки под это условие.
16 июл 13, 14:08    [14573154]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Гость333,

Условия поиска такие:
WHERE is_suspected = 0 AND CAST(creation_time as date) >=  DATEADD(day, -31, CAST(GETDATE() as date))

Используется этот индекс:
CREATE NONCLUSTERED INDEX [IX_is_suspected_creation_time] ON [dbo].[order_view]
(
	[is_suspected] ASC,
	[creation_time] ASC
)
INCLUDE (id, ...)

Кол-во строк - 1.8млн
Первый запрос - Index Seek, расхождение очень маленькое (меньше 10к строк из 1.7млн).
Второй запрос - IndexScan, 1vs97000, ну я так понимаю это нормально, потому что это поле в индексе стоит на 2 месте
SELECT id
FROM dbo.Table1 WITH(INDEX(IX_is_suspected_creation_time))
WHERE is_suspected = 0

SELECT id
FROM dbo.Table2 WITH(INDEX(IX_is_suspected_creation_time))
WHERE cast(creation_time as date) >=  DATEADD(day,-31,cast(getdate() as date))
16 июл 13, 14:27    [14573292]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Сделал запрос, получилось 1vs91k... Что-то я я вно не понимаю)
SELECT id
FROM dbo.Table1
WHERE
	is_suspected = 0
	AND creation_time >=  DATEADD(day,-31,cast(getdate() as date))
16 июл 13, 14:44    [14573392]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
kalimba
CAST(creation_time as date)
- называется "скажи статистике до свидания".
16 июл 13, 14:49    [14573425]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Ennor Tiegael,

Даже если и так, то почему в запросе выше приблизительное кол-во строк = 1, если я cast() убрал?
16 июл 13, 14:53    [14573471]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Ennor Tiegael
Member

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

А creation_date какого типа, сама по себе?
16 июл 13, 14:56    [14573507]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Ennor Tiegael,

datetime (первый, не 2)
16 июл 13, 14:57    [14573516]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
kalimba
Второй запрос - IndexScan, 1vs97000

Уточню: estimated number of rows = 1, actual number of rows = 97000?
16 июл 13, 15:01    [14573546]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Гость333,

Да
16 июл 13, 15:01    [14573552]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Ennor Tiegael
kalimba
CAST(creation_time as date)
- называется "скажи статистике до свидания".

Не факт, MSSQL умеет применять функции к значениям диапазонов статистики, и в целом это у него получается неплохо.
16 июл 13, 15:03    [14573564]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Я предполагал, что у ТС в запросе используется некая табличка констант, для универсальности, часто универсальные запросы имеют проблемы с оценками, но после публикации стало понятно что имеет место быть несоответствие типов, хотя в случае с datetime vs date сиквел обычно умеет с этим справляться при помощи функции GetRangeWithMismatchedTypes. Есть хорошая статья на эту тему Dynamic Seeks and Hidden Implicit Conversions.
Вот выдержка из нее:
автор
Why is this a bad thing if you get a seek anyway? The problem is that these hidden implicit conversions can result in inaccurate cardinality and distribution estimations at any stage of the plan. So, even if you get a seek, the plan might be way off overall. If that isn’t persuasive enough, consider this: will having hidden nested range calculations improve your chances of getting a good query plan? Probably not, no. Be very aware of types, and in particular of the types returned by functions and expressions. If in doubt, use SELECT INTO to materialize the results of an expression or query, and check the types of the columns produced.

Однако, в моем простом репро оценки получились хорошие, видимо, в вашем случае, это не так. Попробуйте создать вычисляемую колонку/привести типы/задавать интервал дня явно.
16 июл 13, 15:27    [14573793]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

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

Я вот буквально только что задал дату явно ('2013-06-15') и всё чудесно заработало... Иду читать статью.. )
16 июл 13, 15:30    [14573819]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
Вот такая забавушка (тип createin_time - datetime2(0)):
-- Actual Number of Rows: 91219
-- Est: 1
SELECT id
FROM dbo.order_view
WHERE
	is_suspected = 0
	AND creation_time >= DATEADD(DAY, -31, CAST(SYSDATETIME() as date))

-- Est: 514442
DECLARE @date date = DATEADD(DAY, -31, CAST(SYSDATETIME() as date))
SELECT id
FROM dbo.order_view
WHERE
	is_suspected = 0
	AND creation_time >= @date

-- Est: 21498
SELECT id
FROM dbo.order_view
WHERE
	is_suspected = 0
	AND creation_time >= '2013-06-15'
16 июл 13, 15:39    [14573885]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
Exproment
Member

Откуда:
Сообщений: 416
kalimba, я так понимаю, что у вас проблема неявного преобразования типов. Из-за которого не может адекватно оцениваться кардинальность. Я не понял ваших типов данных, однако посмотрите тут на их приоритеты. С учетом того, что типы с меньшим приоритетом преобразуются к типам с высшим.
16 июл 13, 16:16    [14574083]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
SomewhereSomehow
Member

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

1. В первом случае у вас получается сильное расхождение из-за неявных преобразований по диапазону, тот механизм, который сервер использует и который описан в статье.

2. Во втором случае, локальная переменная заставляет сервер делать догадку в 30%, плюс вносится какая-то лепта за счет селективности предиката is_suspected = 0, получается примерно 1790790*(0.3+S) ~ 514442
(по-идее, если тут добавить опцию recompile - должно заработать также плохо как вариант 1 =))

3. В третьем случае, есть неявное преобразование строки в datetime (просто константы, без динамических диапазонов), и там уже просто по гистограмме считается сколько значений попадает в интервал, высчитывается селективность этого предиката и умножается на селективность предиката is_suspected = 0 и на число строк. Получается чуть меньше чем реальность. Последний вариант должен работать приемлемо, несмотря на ошибку в оценке, т.к. она уже не такая сильная. Но если нет, можете попробовать создать фильтрованную статистику, типа такого:
create statistics fs on mytable(creation_time) where is_suspected  = 0 with fullscan;

Кроме того, в последнем варианте, чтобы не полагаться на порядок приведения я бы явно сделал: =convert(datetime, '20130615'). Либо, если нужно текущую дату =convert(datetime, convert(date,getdate()));
16 июл 13, 16:20    [14574109]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

Откуда:
Сообщений: 297
SomewhereSomehow,
После вопроса Ennor Tiegael о типе данных поменял тип данных creation_time с datetime на datetime2(0), перестроил индексы, но забыл статистику обновить. После обновления варианты 2 и 3 стали равнозначны, планы прилагаю. Спасибо за статью, а то я всю голову уже сломал)

К сообщению приложен файл (tmp5171.sqlplan - 17Kb) cкачать
16 июл 13, 16:27    [14574145]     Ответить | Цитировать Сообщить модератору
 Re: В каких случаях опатимизатор может не использовать статистику?  [new]
kalimba
Member

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

После обновления статистики Est. Rows стал близок к правде (91k vs 96k), на планах видно в общем)
16 июл 13, 16:29    [14574151]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить