وحید نصیری وحید نصیری
بررسی کارآیی کوئری‌ها در SQL Server - قسمت هشتم - بررسی عملگرهای Hash Join و Compute Scalar در یک Query Plan
اندازه‌ی قلم متن
تخمین مدت زمان مطالعه‌ی مطلب: شش دقیقه
در یک hash join، اطلاعات از دو ورودی نامرتب، دریافت و join می‌شوند که نسبت به merge join، عملیات سنگین‌تری است. برای اینکار، یک hash table را از دیتاست خارجی و یک نمونه‌ی دیگر را بر اساس دیتاست درونی ساخته و سپس کار انطباق ردیف‌ها را انجام می‌دهد.


بررسی عملگر hash join

 ابتدا در management studio از منوی Query، گزینه‌ی Include actual execution plan را انتخاب می‌کنیم. سپس کوئری‌های زیر را اجرا می‌کنیم:
USE [WideWorldImporters];
GO

SET STATISTICS IO ON;
GO


/*
Query with a hash join
*/
SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Warehouse].[StockItems] [si]
    JOIN [Sales].[OrderLines] [ol]
    ON [si].[StockItemID] = [ol].[StockItemID];
GO
در اینجا اطلاعات دو جدول StockItems و OrderLines بر روی ستون StockItemID با هم Join شده‌اند و اجرای آن یک چنین کوئری پلنی را تولید می‌کند:


دیتاست بالایی که ضخامت پیکان خارج شده‌ی از آن کمتر است، تعداد ردیف‌های کمتری را نسبت به دیتاست درونی دارد (227 ردیف، در مقابل بیش از 231 هزار ردیف).
با حرکت اشاره‌گر ماوس بر روی هر کدام از ایندکس‌ها، می‌توان با دقت کردن به Output List آن‌ها، دقیقا دریافت که هرکدام، چه ستون‌هایی از کوئری نهایی را تامین می‌کنند:
دیتاست بالایی که از PK_Warehouse_StockItems تامین می‌شود:
ALTER TABLE [Warehouse].[StockItems] ADD  CONSTRAINT [PK_Warehouse_StockItems] PRIMARY KEY CLUSTERED
(
   [StockItemID] ASC
)


دیتاست درونی که از NCCX_Sales_OrderLines تامین می‌شود و یک COLUMNSTORE INDEX است:
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCX_Sales_OrderLines] ON [Sales].[OrderLines]
(
[OrderID],
[StockItemID],
[Description],
[Quantity],
[UnitPrice],
[PickedQuantity]
)



بهبود کارآیی hash join با فشرده سازی ایندکس‌های آن

ایندکس NCCX_Sales_OrderLines که در کوئری فوق مورد استفاده قرار گرفته، همانطور که در قسمتی از تعریف آن نیز مشخص است، تعداد ستون‌های بیشتری را از آنچه ما نیاز داریم، در بر دارد. در این حالت آیا اگر ایندکس مناسب‌تری را با تعداد ستون کمتری ایجاد کنیم، از آن استفاده می‌کند؟
CREATE NONCLUSTERED INDEX [IX_OrderLines_StockItemID]
ON [Sales].[OrderLines](
[StockItemID] ASC,
[PickedQuantity] ASC,
[OrderID])
ON [PRIMARY];
GO
این ایندکس جدید، نیازهای واقعی کوئری نوشته شده را پوشش می‌دهد و تعداد ستون کمتری را به همراه دارد.
در این حالت اگر کوئری زیر را اجرا کنیم:
SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol]
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID]
OPTION
(RECOMPILE);
GO
در کوئری پلن نهایی تفاوتی مشاهده نمی‌شود و باز هم SQL Server، همان COLUMNSTORE INDEX را به ایندکس جدید ترجیح داده‌است. علت اینجا است که ماهیت COLUMNSTORE INDEX‌ها فشرده شده‌است؛ در مقابل NONCLUSTERED INDEXها معمولی که به صورت پیش‌فرض غیر فشرده شده هستند و یک row store می‌باشند.

یک نکته: در این کوئری علت استفاده‌ی از RECOMPILE، وادار کردن SQL server به محاسبه‌ی مجدد کوئری پلن جاری است.

اکنون اگر نگارش فشرده شده‌ی ایندکسی را که ایجاد کردیم، با ذکر گزینه‌ی DATA_COMPRESSION = PAGE تعریف کنیم، چه اتفاقی رخ می‌دهد؟
CREATE NONCLUSTERED INDEX [IX_OrderLines_StockItemID_Compressed]
ON [Sales].[OrderLines](
[StockItemID] ASC,
[PickedQuantity] ASC,
[OrderID])
WITH (DATA_COMPRESSION = PAGE)
ON [PRIMARY];
GO
پس از آن مجددا همان کوئری قبلی را که به همراه RECOMPILE است، اجرا می‌کنیم. اینبار به کوئری پلنی خواهیم رسید که از این ایندکس جدید استفاده می‌کند.

یک نکته: اگر علاقمند بودید تا هزینه‌ی این کوئری‌ها را نسبت به یکدیگر محاسبه و مقایسه کنید، چون یک کوئری معمولی، همواره از آخرین پلن محاسبه شده استفاده می‌کند، اینکار میسر نیست. اما می‌توان با ذکر صریح ایندکس مدنظر توسط راهنمای WITH INDEX، بهینه ساز کوئری‌ها را وارد کرد تا از ایندکسی که ذکر می‌شود، بجای ایندکسی که فکر می‌کند بهتر است، استفاده کند. بنابراین اجرای هر 4 کوئری زیر با هم، 4 کوئری پلن متفاوت را بر اساس ایندکس‌های متفاوتی، محاسبه کرده و نمایش می‌دهد:
SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol]
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID]
OPTION
(RECOMPILE);
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_Sales_OrderLines_Perf_20160301_02))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_OrderLines_StockItemID))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_OrderLines_StockItemID_Compressed))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO


بررسی عملگر compute scalar

کار عملگر compute scalar، ارزیابی و محاسبه‌ی یک عبارت است و خروجی آن نیز یک مقدار scalar است؛ مانند functions در SQL Server. مشکلی که با این عملگر وجود دارد این است که هزینه‌ی انجام آن عموما در کوئری پلن ظاهر نمی‌شود (و یا با تخمین نادرستی ظاهر می‌شود) که می‌تواند گمراه کننده باشد. همچنین پلن حاصل، اشیایی را که توسط یک function مورد استفاده قرار می‌گیرند، لحاظ نمی‌کند.

برای نمونه اگر پلن دو کوئری زیر را با هم مقایسه کنیم:
SELECT COUNT(*)
FROM [Sales].[Orders];

SELECT COUNT_BIG (*)
FROM [Sales].[Orders];
تقریبا یکی هستند:


از این جهت که (*)COUNT در SQL server به (*)COUNT_BIG تفسیر شده و اجرا می‌شود. به همین جهت آنچنان تفاوتی در اینجا قابل مشاهده نیست.

اما اگر function زیر را تعریف کنیم:
CREATE FUNCTION dbo.CountProductsSold (
@SalesPersonID INT
) RETURNS INT

AS

BEGIN
    DECLARE @SoldCount INT;

    SELECT @SoldCount = COUNT(DISTINCT [ol].[StockItemID])
    FROM [Sales].[Orders] [o]
        JOIN [Sales].[OrderLines] [ol]
        ON [o].[OrderID] = [ol].[OrderID]
    WHERE [o].[SalespersonPersonID] = @SalesPersonID

    RETURN (@SoldCount);

END
و سپس پلن کوئری که از آن استفاده می‌کند را بررسی نمائیم:
SELECT
    [FullName] AS [SalesPerson],
    [dbo].[CountProductsSold]([PersonID]) AS [NumberOfProductsSold]
FROM [Application].[People]
WHERE [IsSalesperson] = 1;
مشاهده خواهیم کرد که در actual execution plan آن، هزینه‌ی فراخوانی این تابع صفر است و همچنین جزئیاتی از اشیایی که توسط آن فراخوانی شده‌اند نیز ذکر نشده‌است:


یک روش محاسبه‌ی هزینه‌ی فراخوانی این تابع، استفاده از extended events است. روش دیگر آن استفاده از اشیاء DMO's می‌باشد:
SELECT
    [fs].[last_execution_time],
    [fs].[execution_count],
    [fs].[total_logical_reads]/[fs].[execution_count] [AvgLogicalReads],
    [fs].[max_logical_reads],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_function_stats [fs]
CROSS APPLY sys.dm_exec_sql_text([fs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([fs].[plan_handle]) [p];
این کوئری اطلاعات logical_reads مرتبط با تابع فراخوانی شده را گزارش می‌دهد که ... صفر نیست:


بنابراین compute scalar صورت گرفته دارای هزینه‌ای است که در actual execution plan ظاهر نمی‌شود.
اکنون اگر از منوی Query، گزینه‌ی Include actual execution plan را انتخاب نکنیم و بجای آن گزینه‌ی Display estimated execution plan را انتخاب کنیم، به تصویر زیر خواهیم رسید:


در نیمه‌ی پایینی آن، جزئیات دسترسی‌های تابع فراخوانی شده نیز ذکر می‌شوند. بنابراین استفاده‌ی از estimated execution planها در حین کار با توابع، بسیار مفید است.
‫۵ سال و ۲ ماه قبل، یکشنبه ۱۶ تیر ۱۳۹۸، ساعت ۲۳:۵۰
۲۰۰۵
۰
SQL Server
مطالب مشابه