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

Откуда:
Сообщений: 1196
Всем привет!
Есть таблица

CREATE TABLE [DW].[Availability](
	[ProductId] [int] NOT NULL,
	[MonthLastDay] [date] NOT NULL,
	[Availability] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Availability] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC,
	[MonthLastDay] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [DWByDatePS]([MonthLastDay])
) ON [DWByDatePS]([MonthLastDay])
GO
CREATE NONCLUSTERED INDEX [IX_Availability_MonthLastDay] ON [DW].[Availability]
(
	[MonthLastDay] ASC,
	[ProductId] ASC
)
INCLUDE ( 	[Availability]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [DWByDatePS]([MonthLastDay])


Запросы
DECLARE	@BeginDate date = '2013-04-30',
		@EndDate date = '2014-04-30';

SELECT COUNT(*)
FROM            DW.Availability AS SM_1 

SELECT COUNT(*)
FROM            DW.Availability AS SM_1 
WHERE        (SM_1.MonthLastDay BETWEEN @BeginDate AND @EndDate)


Оба запроса возвращают одно и то же число 50172870, т.е. @BeginDate и @EndDate перекрывают весь диапазон MonthLastDay.
Статистика самая свежая.

Планы выполнения
 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))
       |--Stream Aggregate(DEFINE:([globalagg1005]=SUM([partialagg1004])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1004]=Count(*)))
                      |--Index Scan(OBJECT:([WebCrawler3DW].[DW].[Availability].[IX_Availability_MonthLastDay] AS [SM_1]))


|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))
       |--Stream Aggregate(DEFINE:([globalagg1005]=SUM([partialagg1004])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1004]=Count(*)))
                      |--Index Seek(OBJECT:([WebCrawler3DW].[DW].[Availability].[IX_Availability_MonthLastDay] AS [SM_1]), SEEK:([PtnId1000] >= RangePartitionNew([@BeginDate],(1),'2009-10-01','2010-05-01','2010-12-01','2011-04-01','2011-08-01','2011-11-01'

Стоимость первого запроса составляет 278, а второго 25.
Вопрос: почему во втором запросе оптимизатор выбрал поиск по индексу, а не сканирование?
Дело в секционировании? Как проверить?
18 апр 14, 15:06    [15901657]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
SomewhereSomehow
Member

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

А разница по производительности-то есть?

Если коротко, то оптимизатор выбирает поиск только когда есть что искать. Если нет, то он не может применить правило, которое сгенерирует поиск в плане.
18 апр 14, 16:08    [15902185]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

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

Первый запрос выполняется 20 сек, второй 22.
18 апр 14, 16:35    [15902339]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
SomewhereSomehow
Member

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

Первый запрос выполняется 20 сек, второй 22.

За что боремся в таком случае? За две секунды?

Для общего времени в 20 секунд, в рамках погрешности, в принципе. Сколько раз измерения проводили, всегда 2 секунды разница? Если так, то может быть какие-то накладные расходы на обеспечение динамического исключения секций, которого не происходит.

Я, в общем, к чему и спросил про реальную производительность, что разницы особой быть не должно.
Или это просто любопытство?
18 апр 14, 16:46    [15902387]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

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

Боремся за то, что реально эта таблица используется в большом запросе, который не завершился за 18 часов.
И смутило, что вместо логичного сканирования индекса стоит поиск по индексу.
18 апр 14, 17:02    [15902476]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8485
Почему смутило, Вы же используете фильтр во втором запросе. Индекс покрывает условие фильтра вот и происходит поиск.
18 апр 14, 17:22    [15902579]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8485
18 часов - это что-то неверно делаете. Может большое количество физических чтений, смотрите план запроса.
18 апр 14, 17:23    [15902587]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
SomewhereSomehow
Member

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

В таком случае, причину искать лучше в том плане запроса, который вызывает проблемы. Иначе, получается как в анекдоте: ищу не там где потерял, а там где светлее. Выдрав кусок запроса, вы получили новый запрос, который может быть оптимизирован по-другому.

А про динамическое исключение секций, если интересно, почитайте тут, зачем оно и как работает: Introduction to Partitioned Tables

Если запрос, и план соответственно, слишком сложный для вашего анализа, есть еще такой общий метод, когда стараются исключить все лишнее, оставив только "костяк" медленного запроса. Описан в книге "Рефакторинг SQL-приложений" (книга не по сиквелу). Смысл в том, чтобы убрать все лишнее, оставив только необходимое. Только нужно понимать, что делаешь. Например, убрав из предложения select одно поле, можно сделать какой-то индекс покрывающим и запрос будет выполняться быстро. Т.е. нужно уметь видеть причинно-следственные связи от своих действий. В результате вы либо упростите запрос так, что станет понятна причина, либо наткнетесь на нее пока будете убирать условия этакий, метод "проб и ошибок", либо запутаетесь, что на что влияет.

Но лучше конечно план посмотреть. Еще ожидания посмотрите, чего запрос ждет когда выполняется 18 часов.
18 апр 14, 17:24    [15902594]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Планчик вот, буду благодарен за советы.
А основное ожидание PAGEIOLATCH_EX, думаю, тут ничего не поделаешь.

К сообщению приложен файл (ExecutionPlan6.sqlplan.zip - 15Kb) cкачать
18 апр 14, 17:37    [15902654]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Владислав Колосов
Почему смутило, Вы же используете фильтр во втором запросе. Индекс покрывает условие фильтра вот и происходит поиск.

Фильтр не всегда приводит к Seek.
18 апр 14, 17:40    [15902665]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
SomewhereSomehow
Member

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

Планчик-то желательно действительный (увидимся через 18 часов?=))

В целом, что можно сказать по форме плана. Кругом Nested Loops (NL), что обычно не является самым хорошим выбором для соединения больших объемов. Но это общие слова, трудно сказать не видя действительного плана. Очень-очень нехорошо выглядит сканирование таблицы [PK_SkuByMonth] на внутренней стороне NL, в попытке оптимизировать это безобразие сервер строит Index Spool, который предполагается избавит от многомилионных сканирований этой таблицы. Эта операция тоже достаточно тяжелая, и кто его знает, сколько там строк спулится на самом деле?

Возникает вопрос - почем сервер выбрал такой порядок таблиц. Что там за условия (текст запроса truncated), какое реальное число строк по сравнению с оценкой? Что с реальным числом строк в четырех джойнах что предшествуют ему? Очень странная оценка соединения по ProductID в одну строку, в соединяемых таблицах SKU и Product все в порядке со статистикой? Может была добавлена какая-то большая порция данных (в SKU, например) и это не отражено в статистике, отсюда такие оценки.

Далее, вы используете SELECT INTO, который стал работать в параллель только с 2014 версии, попробуйте заменить на insert .. select. Возможно сервер сочтет более дешевым параллельный план, который он любит использовать совместно с HAsh Join-ами.

Ну и многочисленные агрегаты в CTE (судя по началу текста запроса), может индексированное представление по ним (хотя там столько ограничений, что я и не помню их все, может и не подойдет).
18 апр 14, 18:09    [15902810]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
SomewhereSomehow
Member

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

Аб и еще, забыл главное-то, индекс сделайте, чтоб хотя бы охоту к спулу отбить:
create index ix_ProductId_MonthLastDay on SkuByMonth(ProductId, MonthLastDay) include (Price, Promo, CurrencyId, List)
18 апр 14, 18:28    [15902870]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

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

Текст запроса вот.
Действительный план представить не могу - запрос оборвал на 19 часу, так как понятно было, что что-то не так.
А индексик такой у меня есть.
CREATE NONCLUSTERED INDEX [IX_SkuByMonth_ProductId] ON [DW].[SkuByMonth]
(
	[ProductId] ASC,
	[MonthLastDay] ASC
)
INCLUDE ( 	[Price],
	[Promo Price],
	[CurrencyId],
	[List Price]) ON [DWByDatePS]([MonthLastDay])


К сообщению приложен файл (SQLQuery77a.sql - 8Kb) cкачать
18 апр 14, 18:39    [15902904]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Почему у меня подозрения насчёт таблицы DW.Availability возникли -
в плане оценочное количество возвращаемых строк 11539801, Тогда как должно быть 50172870.

Аналогично, в плане запроса
SELECT COUNT(*)
FROM            DW.Availability AS SM_1 

Index Scan возвращает 50172900,
а в
SELECT COUNT(*)
FROM            DW.Availability AS SM_1 
WHERE        (SM_1.MonthLastDay BETWEEN @BeginDate AND @EndDate)

Index Seek возвращает 4515560, что неверно.
18 апр 14, 18:55    [15902954]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Это ожидаемое число строк, действительное , конечно совпадает в обеих запросах.
18 апр 14, 18:57    [15902959]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
автор
Действительный план представить не могу - запрос оборвал на 19 часу, так как понятно было, что что-то не так.


Вот для таких случаев и было бы полезно представление sys.dm_exec_query_profiles, которое появилось в 2014 и позволяет смотреть что происходит в процессе выполнения до получения плана запроса (если конечно, был включен сбор действительного плана).

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

Типа такого:
CREATE NONCLUSTERED INDEX [IX_SkuByMonth_ProductId] ON [DW].[SkuByMonth]([MonthLastDay] ASC,[ProductId] ASC) 
INCLUDE ([Price], [Promo Price], [CurrencyId], [List Price]) ON [DWByDatePS]([MonthLastDay])


автор
Почему у меня подозрения насчёт таблицы DW.Availability возникли - в плане оценочное количество возвращаемых строк 11539801, Тогда как должно быть 50172870.


Давайте посмотрим, откуда такая оценка:
Картинка с другого сайта.

При оценке Nested Loops Join нужно умножать возвращаемое число строк с одной итерации на количество итераций, Plan Explorer (откуда картинка) по умолчанию делает это и там легко следить сколько что возвращает.

Смотрим на сам поиск (оператор 1), там планируется возвращать 1 ряд на одной итерации, всего соединение планирует 11 млн. итераций (оператор 2), смотрим откуда на внешней стороне соединения 11 млн. строк. Это дает выход предыдущего соединения (оператор 3). Которое в свою очередь является продуктом поиска (оператор 4) и нижней ветки. С нижней веткой - разговор отдельный, откуда в поиске (оператор 4) 9 млн. Планируется по одной строке на итерацию, всего 9 млн итераций согласно верхнему соединению (оператор 5). Посмотрим откуда там берется 9 млн. Это результат оценки соединения двух базовых таблиц (одна из которых, судя по всему, индексированное представление): Crawler и SKU (View). Если оценка неточна, нужно искать причину в оценке соединения этих таблиц - все ли в порядке со статистикой на этих таблицах. Кроме того, посмотрите соединение с таблицей Product. Я в общем-то уже писал это выше, но так и не понял, что там - на всех таблицах вся статистика полная и обновленная?

Еще есть вариант, раз у вас там вью, попробуйте указать подсказку NOEXPAND, иногда, сервер когда раскрывает представление в текст, а потом схлопывает обратно - оценки кое где могут "съехать", обычно это бывает в тех местах, которые не распространяются далее по плану, но как знать - попробуйте.

Т.е. причину 11 млн в таблице Availability следует искать выше, в таблицах "драйверах", которые заставляют выполняться поиск по Availability 11 млн раз.

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

Я еще писал про select into и параллелизм, hash join-ы и т.д.
20 апр 14, 14:06    [15907174]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы со статистикой  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
SomewhereSomehow,
Спасибо за подробный ответ.

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

Типа такого:
CREATE NONCLUSTERED INDEX [IX_SkuByMonth_ProductId] ON [DW].[SkuByMonth]([MonthLastDay] ASC,[ProductId] ASC) 
INCLUDE ([Price], [Promo Price], [CurrencyId], [List Price]) ON [DWByDatePS]([MonthLastDay])



У меня кластерный индекс по полям [MonthLastDay], [ProductId].

SomewhereSomehow
Смотрим на сам поиск (оператор 1), там планируется возвращать 1 ряд на одной итерации

В том то и дело, что там на одну итерацию должно возвращать от 0 до 13 строк.
Статистика перед выполнением запроса была собрана с FULLSCAN.

SomewhereSomehow
Еще есть вариант, раз у вас там вью, попробуйте указать подсказку NOEXPAND, иногда, сервер когда раскрывает представление в текст, а потом схлопывает обратно - оценки кое где могут "съехать", обычно это бывает в тех местах, которые не распространяются далее по плану, но как знать - попробуйте.

Пробовал, стоимость запроса возрастает на 50%.

Поэкспериментирую ещё с внешними ключами, их там нет вообще.
23 апр 14, 11:40    [15921186]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить