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

Откуда: Ростов-на-Дону
Сообщений: 342
Привет!

Вкратце о том, что выбирается - это отчет по действиям юзера из архивной базы.
В нее собираются данные с нескольких серверов - каждый сервер льет данные в свою таблицу. Эти таблицы секционированы по дате по месяцу.
Все эти таблицы объединены во view.
Ключ секционирования присутствует в каждом индексе таблицы - последним полем.
При написании запроса предполагалось, что сервер будет фильтровать по дате и NodeId.
Однако фильтр используется только по дате при этом происходит index scan, если перестроить индекс и поставить дату в начало, будет происходить index seek но также без учета NodeId - что грустно.

В случае теста во временной таблице 840 записей, а в основной таблице 729 млн.

Вот схема и план

USE [DBArchive]
GO

/****** Object:  Table [dbo].[DirectoryUserAction_Archive_2_Partition]    Script Date: 12.07.2017 20:33:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DirectoryUserAction_Archive_2_Partition](
	[EventId] [bigint] NOT NULL,
	[NodeId] [int] NOT NULL,
	[EventDateUtc] [datetime] NOT NULL,
	[EventTypeId] [tinyint] NOT NULL,
	[DirId] [bigint] NULL,
	[FileName] [nvarchar](900) NULL,
	[FileId] [bigint] NULL,
	[FileLength] [bigint] NULL,
	[ParentId] [bigint] NULL,
	[OldParentId] [bigint] NULL,
	[OldName] [nvarchar](1800) NULL,
 CONSTRAINT [PK_DirectoryUserAction_Archive_2_Partition] PRIMARY KEY NONCLUSTERED 
(
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [schmUserActionMonthlyPartition]([EventDateUtc])

GO

/****** Object:  Index [IX_DirectoryUserAction_Archive_2_Partition_EventDate]    Script Date: 12.07.2017 20:33:18 ******/
CREATE CLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_EventDate] ON [dbo].[DirectoryUserAction_Archive_2_Partition]
(
	[NodeId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])
GO

/****** Object:  Index [IX_DirectoryUserAction_Archive_2_Partition_NodeId_EventId]    Script Date: 12.07.2017 20:33:18 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_NodeId_EventId] ON [dbo].[DirectoryUserAction_Archive_2_Partition]
(
	[NodeId] ASC,
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])

GO

/****** Object:  Index [IX_DirectoryUserAction_Archive_2_Partition_Type_EventDate]    Script Date: 12.07.2017 20:33:18 ******/
CREATE NONCLUSTERED INDEX [IX_DirectoryUserAction_Archive_2_Partition_Type_EventDate] ON [dbo].[DirectoryUserAction_Archive_2_Partition]
(
	[NodeId] ASC,
	[EventTypeId] ASC,
	[EventDateUtc] ASC
)
INCLUDE ( 	[DataFileId],
	[DirId],
	[EventId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])
GO


CREATE TABLE [dbo].[DirectoryUserActionEvent_Archive_2_Partition](
	[DirectoryId] [int] NOT NULL,
	[EventId] [bigint] NOT NULL,
	[EventDateUtc] [datetime] NOT NULL,
	[DirectoryEventTypeId] [tinyint] NULL,
 CONSTRAINT [PK_DirectoryUserActionEvent_Archive_2_Partition] PRIMARY KEY CLUSTERED 
(
	[DirectoryId] ASC,
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)  ON [schmUserActionMonthlyPartition]([EventDateUtc])

GO

/****** Object:  Index [IX_DirectoryUserActionEvent_Archive_2_Partition_EventId]    Script Date: 12.07.2017 20:34:22 ******/
CREATE NONCLUSTERED INDEX [IX_DirectoryUserActionEvent_Archive_2_Partition_EventId] ON [dbo].[DirectoryUserActionEvent_Archive_2_Partition]
(
	[EventId] ASC,
	[EventDateUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [schmUserActionMonthlyPartition]([EventDateUtc])


CREATE VIEW [dbo].[vwDirectoryUserActionArchive]
AS 
SELECT *, 1 AS serverId
FROM dbo.DirectoryUserAction_Archive_1_Partition
UNION ALL 
SELECT *, 2 AS serverId
FROM dbo.DirectoryUserAction_Archive_2_Partition
UNION ALL 
SELECT *, 3 AS serverId
FROM dbo.DirectoryUserAction_Archive_3_Partition
GO

CREATE VIEW [dbo].[vwDirectoryUserActionEventArchive]
AS 
SELECT *, 1 AS serverId
FROM dbo.DirectoryUserActionEvent_Archive_1_Partition
UNION ALL 
SELECT *, 2 AS serverId
FROM dbo.DirectoryUserActionEvent_Archive_2_Partition
UNION ALL 
SELECT *, 3 AS serverId
FROM dbo.DirectoryUserActionEvent_Archive_3_Partition
GO


Сам запрос
 DECLARE 
            
            @ldateBeginUtc DATETIME,
            @ldateEndUtc DATETIME,
			@lserverId INT,
			@UserDirectoryList VARCHAR(MAX)

set @ldateBeginUtc = '20160401'
set	@ldateEndUtc = '20160701'
set @lserverId = 2
			
set	@UserDirectoryList  = '<S DirectoryId="3503336" NodeId="3503336"/><S DirectoryId="3503337" NodeId="3503337"/><S DirectoryId="3503341" NodeId="3503341"/>'

DECLARE @UserDirectoryListXml XML = CAST(@UserDirectoryList AS XML);            
                
CREATE TABLE #tmp_UserDirectorys_sd
			(
				DirectoryId INT, 
				[NodeId] INT
			);

INSERT INTO #tmp_UserDirectorys_sd
(DirectoryId,
[NodeId])
SELECT t.S.value('@DirectoryId','INT'),
	t.S.value('@NodeId','INT')
FROM @UserDirectoryListXml.nodes('/S') AS t(S);

select *
from #tmp_UserDirectorys_sd

SELECT NF.EventId,
        NF.SourceUserId,
        NE.DirectoryId,
        NF.DataFileId,
        NF.EventDateUtc,                      
		NF.[NodeId],						
		NF.[FileName],						
		NF.ParentId,
		NF.OldParentId
FROM    [dbo].[vwDirectoryUserActionArchive] NF WITH (NOLOCK)
		INNER JOIN #tmp_UserDirectorys_sd VF ON (VF.[NodeId] = NF.[NodeId]) 
		INNER JOIN dbo.vwDirectoryUserActionEventArchive NE WITH (NOLOCK) ON 
			(NE.EventId = NF.EventId 
			AND NE.EventDateUtc = NF.EventDateUtc
			AND NE.DirectoryId = VF.DirectoryId)
WHERE   NF.EventDateUtc BETWEEN @ldateBeginUtc AND @ldateEndUtc
		AND NF.serverId = @lserverId
		AND NE.serverId = @lserverId
OPTION (RECOMPILE)


drop table #tmp_UserDirectorys_sd


К сообщению приложен файл (plan.zip - 24Kb) cкачать
13 июл 17, 11:28    [20639968]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Добрый Э - Эх
Guest
Nimua
каждый сервер льет данные в свою таблицу. Эти таблицы секционированы по дате по месяцу.
Все эти таблицы объединены во view.
а почему бы сразу не сделать одну таблицу, секционированную по паре "сервер - месяц"?
13 июл 17, 11:37    [20640006]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 342
Добрый Э - Эх,

В теории можно было и так, было бы даже удобнее это все сливать, так как была бы одна таблица.
13 июл 17, 11:52    [20640087]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 342
Про таблицу - я не могу переделать таблицы сейчас, но могу изменить сам запрос или индексы на таблице

Поменяла temporary table на table variable и добавила в Where фильтр по дате и для event'ов тоже

вот что получилось - теперь он делает seek на UserAction, но сканит event'ы

К сообщению приложен файл (report audit index seek on ua and index scan on event v2.zip - 12Kb) cкачать
13 июл 17, 12:20    [20640210]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Nimua
вот что получилось - теперь он делает seek на UserAction, но сканит event'ы

А с чего вы взяли, что Seek будет лучше Scan'a в данном случае и оптимизатор должен выбрать его?
Можете попробовать зафорсить Seek и Nested Loops заменив join на apply.
13 июл 17, 12:45    [20640325]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Nimua
WHERE   NF.EventDateUtc BETWEEN '2016-04-01' AND '2016-07-01'

Кстати лишнюю партицию не сканите случайно?
13 июл 17, 13:02    [20640395]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
VladimirSQL
Member

Откуда: Киев
Сообщений: 37
Remind
Nimua
вот что получилось - теперь он делает seek на UserAction, но сканит event'ы

А с чего вы взяли, что Seek будет лучше Scan'a в данном случае и оптимизатор должен выбрать его?
Можете попробовать зафорсить Seek и Nested Loops заменив join на apply.


Вероятно предполагается что данные упорядочены их их не нужно сканировать, а достаточно просто провести поиск по ним (seek).
От которого конечно бы хотелось получить меньшую ресурсоемкость и соответсвенно меньшее конечно время выполнения.
Это если опираться на классику: https://www.sql.ru/articles/mssql/2006/102501scansvsseeks.shtml

А в каком случае Scan может быть лучше Seek при работе с индексом?
13 июл 17, 13:50    [20640620]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Владислав Колосов
Member

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

При сравнимых объемах данных в обработке разных источников.
13 июл 17, 13:58    [20640665]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
Nimua
Однако фильтр используется только по дате при этом происходит index scan
На самом деле это seek только по нужным секциям - см. в свойствах значения Seek Predicates и Actual Partiton Count.
Nimua
если перестроить индекс и поставить дату в начало, будет происходить index seek
Конечно. Только по факту это тоже самое сканирование нужных секций.
Nimua
без учета NodeId - что грустно
Как вы себе представляете учет NodeId при хеш-соединении? Или обязательно нужно добиться NL при ваших-то объемах?
13 июл 17, 14:40    [20640863]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 342
Владислав Колосов,

В таблице UserAction порядка 800 млн записей, в таблице с event - немного больше (где-то на 30 млн)
в итоге в результате запроса возвращается чуть больше 1 млн записей.

to Remind,

Да, вы правы - в реальности граничная дата будет нормальной, то есть '2016-06-30 23:59:59.997'
13 июл 17, 14:40    [20640866]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Remind
Member

Откуда: UK
Сообщений: 523
invm
На самом деле это seek только по нужным секциям - см. в свойствах значения Seek Predicates и Actual Partiton Count.

Я бы все-таки назвал это scan'ом только нужных секций.
13 июл 17, 16:16    [20641503]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
aleks2
Guest
Я фсегда поражался тупости адептов секционирования.

Мало того, что они ожидают от чудесного ускорения фсего от самого факта "секционирования", дык оне еще и читать ленятся.

Толком даже секционировнное View сделать не могут.
https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx
13 июл 17, 16:19    [20641522]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 342
Привет!

В итоге прибили гвоздями - добавили хинт LOOP JOIN, в плане стал использовать nested loops.
4 сен 17, 12:36    [20769455]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
aleks222
Guest
Nimua
Привет!

В итоге прибили гвоздями - добавили хинт LOOP JOIN, в плане стал использовать nested loops.


Все в восторге!!! (с) Кот Бегемот.

ЗЫ. А как же китайские кули секционирование?!!
4 сен 17, 14:21    [20769868]     Ответить | Цитировать Сообщить модератору
 Re: Почему SQL Server не использует Index Seek в запросе  [new]
dies irae
Member

Откуда:
Сообщений: 78
а если так?
CREATE TABLE #tmp_UserDirectorys_sd
			(
				DirectoryId INT, 
				[NodeId] INT
				primary key clustered(NodeId, DirectoryId)
			);
4 сен 17, 15:54    [20770214]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить