آشنایی با IQP در SQL Server

مقدمه

Intelligent Query Processing یا به اختصار IQP مجموعه‌ایی از ویژگی‌هایی است که تاثیر گسترده‌ایی بر بهبود عملکرد حجم کاری موجود، با کمترین میزان پیاده سازی را دارد.
با تنظیم سطح سازگاری مناسب به ازای یک Database می توان حجم کاری را به صورت اتوماتیک واجد شرایط برای IQP نمود.
به عنوان نمونه دستور زیر سطح سازگاری پایگاه داده AdventureWorks را به ۱۵۰ تنظیم می‌نماید:

Alter Database AdventureWorks Set Compatibility_Level = 150

تصویر زیر مجموعه‌ایی از ویژگی‌های مرتبط با IQP را نمایش می‌دهد:

بررسی یک نمونه از ویژگی‌های مرتبط با IQP

Scalar UDF Inlining
UDF ها یک راه استفاده مجدد از کد در میان کوئری‌های مختلف هستند، همچنین برخی از محاسبات مانند
(Business Rule‌های پیچیده) ضروری است که به شکل UDF نوشته شوند.
UDF‌ها نوشتن Logic‌های پیچیده را آسان می‌سازند، با وجود این مزایا، UDF‌ها Performance مربوط به کوئری‌ها را کاهش می‌دهند و همین موضوع باعث کاهش استفاده از آن‌ها می‌گردد.
هدف از Scalar UDF Inlining بهبود Performance مربوط به کوئری‌هایی است که در آن‌ها UDF فراخوانی می‌شود و اجرای UDF مشکل اصلی Performance کوئری است.
در ادامه تاثیر Scalar UDF Inlining بر Performance کوئری‌ها را با ارائه یک مثال بررسی می‌نماییم:
در این مثال از دیتابیس AdventureWorks استفاده نموده‌ایم و ابتدا Compatibility Level دیتابیس را برابر ۱۴۰ قرار می‌دهیم.

Use master
GO
Alter Database AdventureWorks Set Compatibility_Level = 140
GO

تصویر زیر Compatibility Level مربوط به دیتابیس AdventureWorks را نمایش می‌دهد.
سپس یک Scalar Function ایجاد می‌نماییم که ID مربوط به هر سفارش را از ورودی دریافت نموده و بعد از انجام یک عملیات ریاضی نتیجه را برمی‌گرداند.

Use AdventureWorks
GO
Create Or Alter Function dbo.GetChangeValue (@SalesOrderDetailID SmallInt)
Returns Int
AS
Begin
Declare @Value As Int
Select @Value = (OrderQty * UnitPrice) + 13  From Sales.SalesOrderDetail Where SalesOrderDetailID = @SalesOrderDetailID
Return @Value
END

کوئری زیر بدون فراخوانی Scalar Function در کسری از ثانیه اجرا می‌شود و ۲۴۴۵ رکورد را برمی‌گرداند.

Select * from Sales.SalesOrderDetail
Where ModifiedDate Between '2012-01-01 00:00:00.000' And '2012-03-10 00:00:00.000'

جهت افزایش کیفیت تصویر همه ستون‌ها در تصویر نمایش داده نشده‌اند.
اگر Scalar Function ایجاد شده را همانند یک ستون در کوئری قرار دهیم زمان اجرای آن با توجه به سخت افزار استفاده شده ۱۴ ثانیه می‌باشد.

Select *, dbo.GetChangeValue (SalesOrderDetailID ) As NewColumn from Sales.SalesOrderDetail
Where ModifiedDate Between '2012-01-01 00:00:00.000' And '2012-03-10 00:00:00.000'

تا اینجا مشخص می‌شود که Bottleneck مربوط به Performance کوئری، استفاده از Scalar Function است.
همان گونه که گفته شد IQP باعث بهبود عملکرد با کمترین میزان پیاده سازی می‌شود، در ادامه خواهیم دید که Performance مربوط به کوئری مورد بحث تنها با تغییر Compatibility Level مربوط به دیتابیس AdventureWorks به صورت چشمگیری افزایش خواهد یافت و در کسری از ثانیه اجرا می‌شود (مدت زمان اجرای قبلی ۱۴ ثانیه بوده است).
با استفاده از دستور زیر داده‌هایی را که در Cache مربوط به SQL Server قرار گرفته‌اند، بدون نیاز به Shut down و Restart سرور Clean می‌نماییم:

DBCC DropCleanBuffers
GO

با استفاده از دستور زیر سطح سازگاری پایگاه داده AdventureWorks را به ۱۵۰ تنظیم می نماییم:

Alter Database AdventureWorks Set Compatibility_Level = 150

سپس کوئری را مجدد اجرا می‌نماییم.

Select *, dbo.GetChangeValue (SalesOrderDetailID ) As NewColumn from Sales.SalesOrderDetail
Where ModifiedDate Between '2012-01-01 00:00:00.000' And '2012-03-10 00:00:00.000'

همان گونه که در تصویر زیر مشاهده می‌شود، کوئری در کسری از ثانیه اجرا شده است:

بررسی مباحث Internals

جهت بهتر نشان دادن علت مشکل Performance مربوط به استفاده از Scalar Function در کوئری‌ها، دو جدول ساده ایجاد می‌کنیم و آن ها را با تعداد کمی رکورد پر می‌نماییم.
برای این کار یک Database با نام NewDB ایجاد می¬کنیم و در ابتدا Compatibility Level آن را برابر با ۱۴۰ قرار می‌دهیم:

Use master
GO
If DB_ID (N'NewDB') Is Not Null
 Begin
  Alter Database NewDB Set Single_User With Rollback Immediate
  Drop Database NewDB
 End
Create DataBase NewDB
On Primary
(Name = N'NewDB', FileName = N'D:\Databases\Data\NewDB.mdf', Size = 128 mb, FileGrowth = 32 mb, Maxsize = Unlimited )
 Log On
(Name = N'NewDBLog', FileName = N'D:\Databases\Data\NewDB.ldf', Size = 128 mb, FileGrowth = 32 mb, Maxsize = Unlimited )
GO
Alter Database NewDB Set Compatibility_Level = 140
GO

 سپس دو جدول با نام Employees و Languages ایجاد می‌نماییم:

Use NewDB
GO
Drop Table If Exists dbo.Employees
Drop Table If Exists dbo.Languages
Create Table dbo.Languages
(LanguageID Int Not Null Primary Key, LanguageName Nvarchar(50) Not Null)
Create Table dbo.Employees
(EmployeeID Int Not Null Primary Key,
 EmployeeName Nvarchar(100)Not Null, LanguageID Int Not Null,
 Constraint FK_Employees Foreign Key(LanguageID) References dbo.Languages(LanguageID) )
 GO

دستورات زیر داده‌ها را به جداول ایجاد شده Insert می‌نمایند:

Insert Into dbo.Languages (LanguageID, LanguageName) Values (1000, N'Persian'), (2000, N'English')
Insert Into Employees Select [OBJECT_ID], [Name], CASE ABS([object_id]%2) WHEN 1 THEN 1000 ELSE 2000 END As LanguageID
 From sys.all_objects
GO

و بعد یک Scalar Function بسیار ساده ایجاد می‌نماییم که LanguageID را از ورودی دریافت نموده و LanguageName را برمی‌گرداند:

CREATE OR Alter FUNCTION dbo.GetLanguage(@LanguageID int)
RETURNS sysname
AS
BEGIN
  RETURN (SELECT LanguageName  FROM dbo.Languages WHERE LanguageID = @LanguageID);
END
GO

کوئری زیر با استفاده از Scalar Function ایجاد شده LanguageName مربوط به هر Employee را نمایش می‌دهد.

SELECT TOP (8) EmployeeID, [Language] = dbo.GetLanguage(LanguageID)
FROM dbo.Employees;

بدیهی است که به جای استفاده از UDF در این کوئری می‌توان از Join استفاده نمود. (ما جهت پیاده سازی تست از UDF استفاده نموده‌ایم).
به Actual Execution Paln کوئری توجه نمایید:جدول Languages در Plan مشاهده نمی‌شود، در حقیقت این Plan معادل آن است که ستون LanguageName مانند یک مقادر ثابت یا یک Variable در نظر گرفته شده است.
SQL Server چگونه به جدول Languages دست یافته است؟
برای Trace کوئری فوق از View سیستمی sys.dm_exec_function_stats استفاده می‌نماییم.
کوئری زیر نشان می‌دهد که فانکشن GetLanguage به ازای اجرای کوئری اصلی هشت بار فراخوانی شده است (به ازای هر Row یک بار). اما Plan مربوط به آن توسط SQL Server برگردانده نشده است

SELECT OBJECT_NAME([object_id]) as FunctionName, execution_count
FROM sys.dm_exec_function_stats
WHERE object_name(object_id) IS NOT NULL;

نمایش داده نشدن Operatorهای مربوط به کد درون Scalar UDF در Plan گرافیکی عیب یابی را دشوار می‌سازد زیرا نه تنها Operatorها در Plan نشان داده نمی‌شوند بلکه Cost مربوطه نیز نمایش داده نمی‌شود، در واقع در طی مرحله Optimization فقط به ازای Relational Operators هزینه برآورد می‌‌شود و به ازای Scalar Operators برآورد هزینه صورت نمی‌گیرد (از نظر میزان CPU هزینه اندکی در نظر گرفته می‌شود) به این موضوع اصطلاحا Lack of costing گفته می‌شود.
SQL Server 2019 اپراتورها را در Execution Plan کوئری‌هایی که در آن‌ها UDF فراخوانی می‌شود نمایش می‌دهد و این موضوع عیب یابی را آسان می‌نماید.
نکته مهم این است که اکنون Cardinality Estimates بر پایه Statistics استراتژی Join را فراهم می‌نماید، به عبارت دیگر در Execution Plan از اپراتور join استفاده می‌شود. استفاده از Strategies Join. هنگامی که در کوئری، Function به ازی هر ردیف فراخوانی می‌شود به سادگی امکان پذیر نیست.
در ادامه Compatibility Level مربوط به دیتابیس NewDB را به ۱۵۰ تنظیم می‌نماییم:

Use master
GO
Alter Database NewDB Set Compatibility_Level = 150
GO

سپس کوئری زیر را اجرا می‌نماییم:

Use NewDB
GO
SELECT TOP (8) EmployeeID, [Language] = dbo.GetLanguage(LanguageID)
FROM dbo.Employees;
GO

در اینجا Optimizer اپراتور nested loops join را انتخاب نموده است اما بسته به شرایط مختلف ممکن است استراتژی join متفاوتی را انتخاب نماید.
همچنین Scalar Function به ازای هر ردیف فراخوانی نشده است بلکه عمل Clustered Index Seek در واقع ۸ بار اتفاق افتاده است (با توجه به استفاده از Nested loops Join).
اجرا به صورت Statement-by-Statement و تکرار فراخوانی باعث ایجاد کندی می‌گردد.
در نظر گرفتن اجرای کوئری به صورت Parallelism:
به Plan کوئری‌های مثال اول که در ادامه خواهد آمد توجه نمایید:
Plan مربوط به اجرای کوئری با سطح سازگاری ۱۴۰ به ازای دیتابیس AdventureWorks:Plan مربوط به اجرای همان کوئری با سطح سازگاری ۱۵۰ به ازای دیتابیس AdventureWorks:

همان طور که مشاهده می‌نمایید Plan دوم به صورت Parallel اجرا شده است و چندین CPU Core عمل پردازش داده‌ها را انجام داده‌اند

بررسی چند نکته

۱. Scalar UDF Inlining از SQL Server 2019 در دسترس می‌باشد.
2. همه Scalar Function ها Inlineable نیستند، حتی ممکن است Inlineable باشند اما نیاز به Inlined بودن آن‌ها در هر Scenario نباشد. Inlineable بودن Scalar Function را می‌توان با استفاده از کوئری زیر چک نمود.

SELECT OBJECT_NAME([object_id]), definition, is_inlineable
FROM sys.sql_modules;

 ۳. جهت کنترل نمودن Inline در بدنه Function می‌توان از WITH INLINE = OFF استفاده نمود.
4. با استفاده از دستور زیر می توان این ویژگی را در سطح دیتابیس و جدا از Compatibility Level تنظیم نمود:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

۵. ارائه Scalar UDF Inlining در SQL Server 2019 بدین معنا نیست که هر Logic را به صورت Scalar UDF نوشته و انتظار داشته باشیم SQL Server عملکرد بهینه به ازای آن انجام دهد، اگر دیتابیسی وجود دارد که Scalar Function زیاد در آن استفاده شده است Backup آن را روی SQL Server 2019 ریستور نموده و با استفاده از DMV وضعیت Inlineable بودن را بررسی نمایید.
در مقاله‌های بعدی به بررسی بیشتر IQP در SQL Server خواهیم پرداخت