آشنایی با Adaptive Join

آشنایی با Adaptive Join

نوشته شده توسط: مهدی قپانوری
تاریخ انتشار: ۲۸ مهر ۱۴۰۱
آخرین بروزرسانی: ۰۳ آبان ۱۴۰۲
زمان مطالعه: 10 دقیقه
۳.۸
(۶)

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

 

دوره  Performance Tuning در SQL Server

در ادامه جهت درک مشکل مطرح شده به بررسی مثال هایی خواهیم پرداخت. در این مثال ها ما از 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 را انجام دهد.

چه رتبه ای می‌دهید؟

میانگین ۳.۸ / ۵. از مجموع ۶

اولین نفر باش

title sign
دانلود مقاله
آشنایی با Adaptive Join
فرمت PDF
صفحه
حجم مگابایت
دانلود مقاله
title sign
معرفی نویسنده
مهدی قپانوری
مقالات
15 مقاله توسط این نویسنده
محصولات
1 دوره توسط این نویسنده
مهدی قپانوری

مهدی قپانوری بیش از 6 سال است که در زمینه‌های نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزه‌هایPerformance Tuning، Database Administration، Database Development و طراحی سیستم‌های OLTP می‌باشد. مهدی علاقه‌مند به R&D در حوزه‌های نوین SQL Server است.

title sign
دیدگاه کاربران

    • سلام
      مانند همیشه عالی همکار عزیزم 🙂

      ۳
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
close-image