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

Откуда:
Сообщений: 852
... точнее с его отображением.

Преамбула:
Имеется Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) - 12.0.5511.0 (X64)
Aug 19 2016 14:32:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Имеется секционированная таблица-хранилище.

При попытке отобразить эстимейтед план запроса к хранилищу, причем запрос полностью укладывается в некий индекс, оптимизатор выдает предупреждение, что "хорошо бы использовать такой-то покрывающий индекс".
Причем индекс именно с такими параметрами на таблице есть. И именно он в плане запроса отображается.

Если добавить к запросу Option (recompile), то отображается точно такой же план (с точно таким же предполагаемым количеством строк и, на первый взгляд, теми же операциями на тех же индексах), но уже без зеленой подсказки "создайте индекс".

Выполняются запросы и вроде бы одинаково.

В чем может быть дело???

На таблице есть множество индексов, но все они выровнены по партициям, и для них указано STATISTICS_INCREMENTAL=ON, в т.ч. для кластерного индекса.
При подгрузке данных в хранилище данные сначала грузятся в таблицу-близнец с той же структурой индексов (но отключенных).
Потом индексы включаются (Alter index All REBUILD), потом делается swap partition, потом уже в целевой таблице на соответствующей партиции делается UPDATE STATISTICS WITH RESAMPLE для каждой инкрементальной статистики (и для индексов, и для созданных вручную статистик по полям).
Автообновление и автосоздание статистик отключено.

Чувствую, засада именно в этом.

Что я мог упустить, не подскажите?
20 окт 16, 18:15    [19805837]     Ответить | Цитировать Сообщить модератору
 Re: Странности с планом выполнения запроса на секционированной таблице  [new]
SomewhereSomehow
Member

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

18730601
20 окт 16, 18:47    [19805919]     Ответить | Цитировать Сообщить модератору
 Re: Странности с планом выполнения запроса на секционированной таблице  [new]
uaggster
Member

Откуда:
Сообщений: 852
SomewhereSomehow, спасибо, очень похоже на мой случай, особенно с учетом комментария:

http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/
Håkan Winther says:
September 6, 2016 at 11:47 pm
There is also an issue with mixed asc and desc order. The suggested indexes doesn’t specify sort order. If the Query need one column sorted in asc order and another in desc order, the suggestion keep occuring. Read more about it at:
https://ask.sqlservercentral.com/questions/46933/suggested-index-already-exists.html


Но тут всё же есть нюанс.
Я посмотрел внимательнее - планы всё же отличаются:

С (recompile):

|--Parallelism(Gather Streams)
|--Hash Match(Aggregate, HASH:([essentials].[dbo].[operations].[person_id]), RESIDUAL:([essentials].[dbo].[operations].[person_id] = [essentials].[dbo].[operations].[person_id]) DEFINE:([Expr1003]=SUM([partialagg1006]), [Expr1004]=MAX([partialagg1007]), [essentials].[dbo].[operations].[filial_id]=ANY([essentials].[dbo].[operations].[filial_id])))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([essentials].[dbo].[operations].[person_id]))
|--Stream Aggregate(GROUP BY:([essentials].[dbo].[operations].[person_id]) DEFINE:([partialagg1006]=Count(*), [partialagg1007]=MAX([Expr1005]), [essentials].[dbo].[operations].[filial_id]=ANY([essentials].[dbo].[operations].[filial_id])))
|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [essentials].[dbo].[operations].[IsChangeStateOperation]=(1) THEN [essentials].[dbo].[operations].[N] ELSE NULL END))
|--Index Seek(OBJECT:([essentials].[dbo].[operations].[ix_filial_id_person_id]), SEEK:([PtnId1000]=(7) AND [essentials].[dbo].[operations].[filial_id]=(3000)) ORDERED FORWARD)

********************************************************************
Без (recompile):

|--Parallelism(Gather Streams)
|--Hash Match(Aggregate, HASH:([essentials].[dbo].[operations].[person_id]), RESIDUAL:([essentials].[dbo].[operations].[person_id] = [essentials].[dbo].[operations].[person_id]) DEFINE:([Expr1003]=SUM([partialagg1006]), [Expr1004]=MAX([partialagg1007]), [essentials].[dbo].[operations].[filial_id]=ANY([essentials].[dbo].[operations].[filial_id])))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([essentials].[dbo].[operations].[person_id]))
|--Stream Aggregate(GROUP BY:([essentials].[dbo].[operations].[person_id]) DEFINE:([partialagg1006]=Count(*), [partialagg1007]=MAX([Expr1005]), [essentials].[dbo].[operations].[filial_id]=ANY([essentials].[dbo].[operations].[filial_id])))
|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [essentials].[dbo].[operations].[IsChangeStateOperation]=(1) THEN [essentials].[dbo].[operations].[N] ELSE NULL END))
|--Index Seek(OBJECT:([essentials].[dbo].[operations].[ix_filial_id_person_id]), SEEK:([PtnId1000] >= (1) AND [PtnId1000] <= (33) AND [essentials].[dbo].[operations].[filial_id]=[@0]) ORDERED FORWARD)

Если с recompile поиск по индексу идет точно в той партиции, в которой нужно, то без перекомпиляции - он предполагает поиск по всем партициям.

Запрос вот такой:
;With person as
(
Select [filial_id], [person_id], Count_big([N]) [N], Max(Case when [IsChangeStateOperation]=1 Then [N] Else NULL END) Actual_N
    From [dbo].[operations]
Group by [filial_id], [person_id]
)
SELECT *
  FROM person
Where [filial_id]=3000
Option (recompile)

(там на самом деле не with, а просто view, но это роли не играет, т.к. планы получаются на 100% эквивалентные (я имею ввиду для случая с СТЕ и вью, собственно, как и должно быть, если вью не материализованный, как я понимаю).

Индекс, в который он ложиться, такой:
CREATE UNIQUE NONCLUSTERED INDEX [ix_filial_id_person_id] ON [dbo].[operations]
(
        [filial_id] ASC,
	[person_id] ASC,
	[N] DESC

)
Include
([IsChangeStateOperation],[IsRegistrationOperation],[IsInformationOperation],[IsLogisticOperation],[IsModificationOperation],[operation_type_id],[actuality_date])
With (FILLFACTOR=100, STATISTICS_NORECOMPUTE=ON, STATISTICS_INCREMENTAL=ON, ONLINE=ON)
ON [sch_region_data]([filial_id])


Ну и индекс он предлагает такой:
USE [essentials]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[operations] ([filial_id])
INCLUDE ([person_id],[N],[IsChangeStateOperation])
GO


Не смотря на то, что есть такой:
CREATE NONCLUSTERED INDEX [ix_filial_id] ON [dbo].[operations]
(
	filial_id ASC
)
include
([person_id],
 [N],
 [actuality_date],
 [IsChangeStateOperation]
 )
With (FILLFACTOR=100, STATISTICS_NORECOMPUTE=ON, STATISTICS_INCREMENTAL=ON, ONLINE=ON)
ON [sch_region_data]([filial_id])
21 окт 16, 08:58    [19807038]     Ответить | Цитировать Сообщить модератору
 Re: Странности с планом выполнения запроса на секционированной таблице  [new]
SomewhereSomehow
Member

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

Не за что. Я и не думал, что планы одинаковые (у вас они отличаются хотя бы предложением индекса, что говорит о том, что они прошли разный путь оптимизации). Вполне понятно, что планы могут отличаться, есть такая штука как Parameter Embedding Optimization, посмотрите вот тут:
автор
In SQL Server 2008 RTM we introduced behaviour that allowed potentially better plans to be created when using the OPTION RECOMPILE syntax. The way this worked in principal was that if the OPTION RECOMPILE syntax was used, SQL could assume that the plan for the statement in question would not need to re-used, since it was always going to recompile. Therefore the query optimizer could safely use the actual parameter values passed to the statement when choosing a plan, as opposed to just using a parameterised template. In certain circumstances this can lead to dramatic performance gains, especially when you have parameter values which vary wildly and need different plans dependent upon the values passed.

У вас ведь вопрос был о том, почему выдается предупреждение по индексу, а не о разных планах, поэтому суть ответа в том, что если такой индекс есть и успешно используется, то на предупреждение можно не обращать внимания.
21 окт 16, 09:31    [19807154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить