آشنایی با Adaptive Join

در این مقاله قصد داریم به معرفی ویژگی Adaptive Join بپردازیم. یکی از مشکلاتی که در هنگام اجرای Stored Procedure ها وجود دارد این است که Query Optimizer گاهی نیاز به انجام Index Scan دارد و در برخی موارد دیگر باید Index Seek انجام دهد. این موضوع بستگی به پارامتر ورودی پروسیجر و توزیع مقادیر مختلف داده در جداول دارد. به صورت عمومی و ساده، عملیات Scan به معنی بررسی نمودن صفحات داده از ابتدا تا انتها می باشد. عملیات Index Seek به معنی کنار رفتن بخشی از دیتا و تنها جستجو نمودن بخش دیگر داده ها می باشد.

در ادامه جهت درک مشکل مطرح شده به بررسی مثال هایی خواهیم پرداخت. در این مثال ها ما از Stack Overflow Database و SQL Server 2019 استفاده می نمائیم. دستورات زیر سطح سازگاری دیتابیس را به ۱۳۰ تغییر داده و دو ایندکس بر روی جداول Users و Posts ایجاد می نمایند:

Use master
Alter Database [StackOverflowSmall] Set Compatibility_Level = 130
GO

Use StackOverflowSmall
GO
Exec dbo.DropIndexes
Create Index IX_Reputation On dbo.Users (Reputation)

Create Index IX_OwnerUserId_Include On dbo.Posts (OwnerUserId)
Include (Title, Score)
GO

کوئری زیر را در نظر بگیرید:

Select Top 100000 u.Reputation, p.Title, p.Score
From dbo.Users u Inner Join dbo.Posts p
 On u.Id = p.OwnerUserId
Where u.Reputation = 1
Order By p.Score Desc

تصویر زیر Plan اجرائی کوئری را نمایش می‌دهد:

تصویر نشان می دهد که Index Nonclustered جدول Posts در واقع Scan شده و Query Optimizer از عملیات Merge Join استفاده نموده است. توجه نمائید که کوئری به ازای مقدار Reputation برابر با یک اجرا گردید. حالا همین کوئری را به ازای مقدار Reputation برابر با دو اجرا می نمائیم:

Select Top 100000 u.Reputation, p.Title, p.Score
From dbo.Users u Inner Join dbo.Posts p
 On u.Id = p.OwnerUserId
Where u.Reputation = 2
Order By p.Score Desc

Create Procedure USP_GetUserReputation
(@Reputation Int)
AS

Select Top 100000 u.Reputation, p.Title, p.Score
From dbo.Users u Inner Join dbo.Posts p
 On u.Id = p.OwnerUserId
Where u.Reputation = @Reputation
Order By p.Score Desc
GO

ابتدا مقدار یک را به عنوان ورودی به پروسیجر پاس می‌دهیم:

Exec USP_GetUserReputation @Reputation = 1

همانطور که انتظار داشتیم Index Nonclustered جدول Posts در واقع Scan شد و Query Optimizer از عملیات Merge Join استفاده نمود.

حالا مقدار دو را به پروسیجر پاس می‌دهیم:

Exec USP_GetUserReputation @Reputation = 2
GO

مطابق با تصویر زیر باز هم Query Optimizer در واقع Index  Nonclustered جدول Posts را Scan و از عملیات Merge Join استفاده نمود. به عبارت دیگر به ازای مقدار ورودی دو عملیات های Index Seek و Nested Loops را انجام نداد.

این مشکل Parameter Sniffing است. یعنی SQL Server به ازای اولین مقداری که به پروسیجر پاس داده می شود Plan اجرائی پروسیجر را ایجاد می نماید و در فراخوانی های بعدی پروسیجر، همان Plan را مورد استفاده قرار می دهد.

نکته مهم: تعداد کاربران بسیار زیادی در جدول Users در واقع Reputation برابر با یک دارند و تعداد بسیار کمی Reputation برابر با دو.

به صورت عمومی و ساده، هنگامی که تعداد رکوردهای مورد پردازش زیاد باشد عملیات Scan صورت می گیرد و هنگامی که تعداد رکوردهای مورد پردازش کم باشد عملیات Seek انجام می شود.

در ادامه سطح سازگاری دیتابیس را برابر با ۱۵۰ قرار داده و پروسیجر را با مقدار ورودی یک فرا می خوانیم:

Alter Database [StackOverflowSmall] Set Compatibility_Level = 150
Exec USP_GetUserReputation @Reputation = 1

تصویر زیر Plan اجرائی کوئری را نمایش می دهد، به آیکون های جدید در تصویر توجه نمائید:

در واقع تصویر نشان می دهد که به ازی مقدار ورودی یک Index Nonclustered جدول Posts در واقع Scan شده است. به تصویر بعدی دقت نمائید:

عملیات Join بین دو جریان داده به شکل Dynamic انتخاب شده است. جهت درک بهتر موضوع پروسیجر را با مقدار ورودی دو اجرا می نمائیم:

Exec USP_GetUserReputation @Reputation = 2

تصویر زیر Plan اجرائی را نمایش می دهد:

در تصویر مشاهده می نمائید که جهت بدست آوردن داده های جدول Posts از عملیات Index Seek استفاده گردیده است. تصویر بعدی نیز نشان می دهد که عملیات Join به شکل Nested Loops می‌باشد:

با استفاده از ویژگی Adaptive Join در واقع SQL Server بر اساس میزان حجم داده ورودی درRuntime یکی از عملیات های Hash Join یا Nested Loops را انتخاب می نماید. همچنین به صورت Dynamic تصمیم می گیرد که عملیات Index Seek یا Index Scan را انجام دهد.