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

Откуда:
Сообщений: 2
Привет всем.

Есть локальная база данных.

Есть скрипт. Есть покрывающий индекс по таблице [Temp_Player_Points_ATP_1990_0.21].
Как видно в плане выполнения запроса идет Index Scan. Почему не Index Seek?

Ниже представлены:
Выполняемый скрипт
Ключи к таблицам из скрипта
План выполнения запроса в текстовом виде
Таблица к плану выполнения запроса

Помогите, пожалуйста.

Выполняемый скрипт
SET STATISTICS PROFILE ON 
go

select g.id_t_g, g.id_r_g, g.id1_g, g.id2_g, g.points_c1_p, g.points_c2_p, g.points_r1_p, g.points_r2_p
	, p1.pointsC_avg_all as pc1, p2.pointsC_avg_all as pc2, p1.pointsR_avg_all as pr1, p2.pointsR_avg_all as pr2 
from [win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21] g 
	inner join [win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21] p1 on g.npp = p1.npp and g.id1_g = p1.id_p 
	inner join [win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21] p2 on g.npp = p2.npp and g.id2_g = p2.id_p
go

SET STATISTICS PROFILE OFF
go



Ключи для таблицы [Games_ATP_1990_0.21]
ALTER TABLE [dbo].[Games_ATP_1990_0.21] ADD  CONSTRAINT [pk_Games_ATP_1990_0.21] PRIMARY KEY CLUSTERED 
(
,[npp] ASC
)WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF SORT_IN_TEMPDB = OFF IGNORE_DUP_KEY = OFF ONLINE = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_1] ON [dbo].[Games_ATP_1990_0.21]
(
,[npp] ASC,
,[id2_g] ASC
)
INCLUDE ([id_t_g],
,[id_r_g],
,[id1_g],
,[points_c1_p],
,[points_c2_p],
,[points_r1_p],
,[points_r2_p]) 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 [PRIMARY]




Ключи для таблицы [Temp_Player_Points_ATP_1990_0.21]
ALTER TABLE [dbo].[Temp_Player_Points_ATP_1990_0.21] ADD  CONSTRAINT [pk_Temp_Player_Points_ATP_1990_0.21] PRIMARY KEY CLUSTERED 
(
,[id_p] ASC,
,[npp] ASC
)WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF SORT_IN_TEMPDB = OFF IGNORE_DUP_KEY = OFF ONLINE = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_1] ON [dbo].[Temp_Player_Points_ATP_1990_0.21]
(
,[npp] ASC,
,[id_p] ASC
)
INCLUDE ([pointsC_avg_all],
,[pointsR_avg_all]) 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 [PRIMARY]



Поле StmtText. Текстовый план выполнения запроса. Дополнительные поля к плану выполнения смотрите ниже
1.	select g.id_t_g, g.id_r_g, g.id1_g, g.id2_g, g.points_c1_p, g.points_c2_p, g.points_r1_p, g.points_r2_p
		, p1.pointsC_avg_all as pc1, p2.pointsC_avg_all as pc2, p1.pointsR_avg_all as pr1, p2.pointsR_avg_all as pr2 
	from [win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21] g 
		inner join [win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21] p1 on g.npp = p1.npp and g.id1_g = p1.id_p 
		inner join [win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21] p2 on g.npp = p2.npp and g.id2_g = p2.id_p
2.	  |--Parallelism(Gather Streams)
3.		   |--Hash Match(Inner Join, HASH:([p2].[npp], [g].[id1_g])=([p1].[npp], [p1].[id_p]), RESIDUAL:([win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p2].[npp]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p1].[npp] AND [win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[id1_g] as [g].[id1_g]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p1].[id_p]))
4.				|--Bitmap(HASH:([p2].[npp], [g].[id1_g]), DEFINE:([Bitmap1004]))
5.				|    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([p2].[npp], [g].[id1_g]))
6.				|         |--Hash Match(Inner Join, HASH:([g].[npp], [g].[id2_g])=([p2].[npp], [p2].[id_p]), RESIDUAL:([win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[npp] as [g].[npp]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p2].[npp] AND [win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[id2_g] as [g].[id2_g]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p2].[id_p]))
7.				|              |--Bitmap(HASH:([g].[npp], [g].[id2_g]), DEFINE:([Bitmap1003]))
8.				|              |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([g].[npp], [g].[id2_g]))
9.				|              |         |--Index Scan(OBJECT:([win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[IDX_1] AS [g]))
10.				|              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([p2].[npp], [p2].[id_p]))
11.				|                   |--Index Scan(OBJECT:([win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[IDX_1] AS [p2]),  WHERE:(PROBE([Bitmap1003],[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p2].[npp],[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p2].[id_p])))
12.				|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([p1].[npp], [p1].[id_p]))
13.					 |--Index Scan(OBJECT:([win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[IDX_1] AS [p1]),  WHERE:(PROBE([Bitmap1004],[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p1].[npp],[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p1].[id_p])))


Дополнительные поля к плану выполнения
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
70039611.110NULLNULLNULLNULL700396NULLNULLNULL15.97539NULLNULLSELECT0NULL
70039612.121ParallelismGather StreamsNULLNULL70039600.28546877715.97539[g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p] [p1].[pointsC_avg_all] [p1].[pointsR_avg_all] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
700396643.132Hash MatchInner JoinHASH:([p2].[npp] [g].[id1_g])=([p1].[npp] [p1].[id_p]) RESIDUAL:([win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p2].[npp]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p1].[npp] AND [win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[id1_g] as [g].[id1_g]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p1].[id_p])NULL70039602.3072327715.68992[g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p] [p1].[pointsC_avg_all] [p1].[pointsR_avg_all] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
700396644.143BitmapBitmap CreateHASH:([p2].[npp] [g].[id1_g])[Bitmap1004]70039600.2827602719.979099[g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p] [p2].[npp] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
700396645.154ParallelismRepartition StreamsPARTITION COLUMNS:([p2].[npp] [g].[id1_g])NULL70039600.2827602719.979099[g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p] [p2].[npp] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
700396646.165Hash MatchInner JoinHASH:([g].[npp] [g].[id2_g])=([p2].[npp] [p2].[id_p]) RESIDUAL:([win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[npp] as [g].[npp]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p2].[npp] AND [win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[id2_g] as [g].[id2_g]=[win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p2].[id_p])NULL70039602.130882719.69634[g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p] [p2].[npp] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
700396647.176BitmapBitmap CreateHASH:([g].[npp] [g].[id2_g])[Bitmap1003]70039600.2465147614.161869[g].[npp] [g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p]NULLPLAN_ROW11
700396648.187ParallelismRepartition StreamsPARTITION COLUMNS:([g].[npp] [g].[id2_g])NULL70039600.2465147614.161869[g].[npp] [g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p]NULLPLAN_ROW11
700396649.198Index ScanIndex ScanOBJECT:([win-win.sys.Tennis].[dbo].[Games_ATP_1990_0.21].[IDX_1] AS [g])[g].[npp] [g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p]7003963.8912730.02408102613.915354[g].[npp] [g].[id_t_g] [g].[id_r_g] [g].[id1_g] [g].[id2_g] [g].[points_c1_p] [g].[points_r1_p] [g].[points_c2_p] [g].[points_r2_p]NULLPLAN_ROW11
7054266410.1106ParallelismRepartition StreamsPARTITION COLUMNS:([p2].[npp] [p2].[id_p])NULL140079200.2500768253.403581[p2].[id_p] [p2].[npp] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
7054266411.11110Index ScanIndex ScanOBJECT:([win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[IDX_1] AS [p2]) WHERE:(PROBE([Bitmap1003][win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p2].[npp][win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p2].[id_p]))[p2].[id_p] [p2].[npp] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]14007923.1053470.04815713253.153504[p2].[id_p] [p2].[npp] [p2].[pointsC_avg_all] [p2].[pointsR_avg_all]NULLPLAN_ROW11
7054846412.1123ParallelismRepartition StreamsPARTITION COLUMNS:([p1].[npp] [p1].[id_p])NULL140079200.2500768253.403581[p1].[id_p] [p1].[npp] [p1].[pointsC_avg_all] [p1].[pointsR_avg_all]NULLPLAN_ROW11
7054846413.11312Index ScanIndex ScanOBJECT:([win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[IDX_1] AS [p1]) WHERE:(PROBE([Bitmap1004][win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[npp] as [p1].[npp][win-win.sys.Tennis].[dbo].[Temp_Player_Points_ATP_1990_0.21].[id_p] as [p1].[id_p]))[p1].[id_p] [p1].[npp] [p1].[pointsC_avg_all] [p1].[pointsR_avg_all]14007923.1053470.04815713253.153504[p1].[id_p] [p1].[npp] [p1].[pointsC_avg_all] [p1].[pointsR_avg_all]NULLPLAN_ROW11
6 окт 19, 13:03    [21987594]     Ответить | Цитировать Сообщить модератору
 Re: Почему Index Scan, а не Index Seek.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36450
Расставьте forceseek в ожидаемых местах поиска по индексу и сравните стоимость.
6 окт 19, 13:29    [21987612]     Ответить | Цитировать Сообщить модератору
 Re: Почему Index Scan, а не Index Seek.  [new]
Gerros
Member

Откуда: Харьков
Сообщений: 471
Win-Win.Sys
Как видно в плане выполнения запроса идет Index Scan. Почему не Index Seek?
Потому что для Hash Match нужны все значения (Index Scan), а не некоторые (Index Seek).
6 окт 19, 14:48    [21987635]     Ответить | Цитировать Сообщить модератору
 Re: Почему Index Scan, а не Index Seek.  [new]
PaulWist
Member

Откуда:
Сообщений: 2184
Win-Win.Sys,


1. В табличке Temp_Player_Points_ATP_1990_0.21 содержится 1 млн 400.792 под условие (p1 on g.npp = p1.npp and g.id1_g = p1.id_p и p2 on g.npp = p2.npp and g.id2_g = p2.id_p) попадает 705 тыс 484 записей как для алиаса p1 так и для p2.

Т.е. ровно половина от таблички! Какой тут может быть Index Seek!

2. И наконец, результирующая выборка отбирает 700 тыс 396 записей, те убирает всего 5100 записей.

Вполне логичный план.
7 окт 19, 08:26    [21987909]     Ответить | Цитировать Сообщить модератору
 Re: Почему Index Scan, а не Index Seek.  [new]
Win-Win.Sys
Member

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

Пошел изучать матчасть))
7 окт 19, 13:28    [21988176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить