پس از
آشنایی مقدماتی با نحوه‌ی خواندن یک Query Plan، اکنون نوبت به بررسی عملگرهایی است که در آن مشاهده می‌شوند و همچنین تغییرات در کوئری‌ها چگونه بر روی آن‌ها تاثیر گذاشته و آن‌ها را تغییر می‌دهند و این تغییرات چه تاثیری را بر روی کارآیی خواهند داشت.
عملگرهای Scans و Seeks
در حالت کلی می‌توان دو نوع جدول بدون و با ایندکس را درنظر گرفت. در حالت جداول بدون ایندکس، برای جستجوی اطلاعات نیاز به Table Scan وجود دارد و برعکس آن شامل یک Clustered index scan خواهد بود. گاهی از اوقات Clustered index scanها بهترین روش دریافت اطلاعات هستند و گاهی از اوقات خیر و نیاز به بررسی بیشتری دارند. بنابراین قانون کلی، حذف آن‌ها به محض مشاهده، نیست.
نوع دیگر عملگرهای دسترسی به داده‌ها، Seeks هستند که شامل Clustered index seeks و Non-clustered index seeks می‌شوند. در بسیاری از موارد عنوان می‌شود که Seeks کارآیی بهتری را به همراه دارند. هرچند این مورد نیاز به بررسی بیشتری دارد که در ادامه با مثال‌هایی آن‌ها را مرور خواهیم کرد.
بررسی عملگر Table scan در یک Query Plan
در ادامه تعدادی از عملگرهای مرتبط با data access را از لحاظ نحوه‌ی انتخاب و تغییر آن‌ها توسط بهینه ساز کوئری‌های SQL Server بررسی می‌کنیم. برای این منظور ابتدا در management studio از منوی Query، گزینه‌ی Include actual execution plan را انتخاب می‌کنیم. سپس کوئری‌های زیر را اجرا می‌کنیم:
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO
SELECT *
INTO [Sales].[Copy_Orders]
FROM [Sales].[Orders];
GO
SELECT
[CustomerID],
[OrderID],
[OrderDate]
FROM [Sales].[Copy_Orders]
WHERE [CustomerID] > 550;
GO
در اینجا در ابتدا، تمام رکوردهای جدول [Sales].[Orders]، به جدول [Sales].[Copy_Orders] کپی می‌شوند. سپس یک کوئری را بر روی این جدول کپی، اجرا کرده‌ایم.
همانطور که مشاهده می‌کنید، برای برآورده کردن قسمت where این کوئری، یک Table Scan صورت گرفته‌است؛ چون این جدول کپی، به همراه هیچ ایندکسی نیست. به همین جهت برای یافتن رکوردهای مدنظر، راه دیگری بجز اسکن کل جدول بانک اطلاعاتی وجود ندارد که بسیار ناکارآمد است.
همچنین اگر به برگه‌ی messages دقت کنیم، با توجه به روشن بودن STATISTICS IO، میزان logical reads نیز قابل مشاهده‌است:
(33035 rows affected)
Table 'Copy_Orders'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
به علاوه اجرای آن نیز کمی بیشتر از نیم ثانیه، طول کشیده‌است:
SQL Server Execution Times:
CPU time = 79 ms, elapsed time = 762 ms.
بررسی عملگر Index Seek در یک Query Plan
اکنون سؤال اینجا است که آیا می‌توان این وضعیت را بهبود بخشید؟
بله. برای این منظور یک NONCLUSTERED INDEX را بر روی جدول کپی، ایجاد می‌کنیم؛ به نحوی که CustomerID لحاظ شده‌ی در قسمت where کوئری را پوشش دهد:
CREATE NONCLUSTERED INDEX [IX_Copy_Orders_CustomerID]
ON [Sales].[Copy_Orders] (
[CustomerID]
)
INCLUDE (
[OrderID], [OrderDate]
);
GO
چون مطابق کوئری، [OrderID] و [OrderDate] در قسمت where ذکر نشده‌اند، در اینجا INCLUDE شده‌اند.
در ادامه مجددا همان کوئری را اجرا می‌کنیم:
SELECT
[CustomerID],
[OrderID],
[OrderDate]
FROM [Sales].[Copy_Orders]
WHERE [CustomerID] > 550;
GO
که سبب تولید کوئری پلن زیر می‌شود:
اینبار عملگر Table Scan قبلی به یک عملگر Index Seek بر روی NONCLUSTERED INDEX تعریف شده، تغییر کرده‌است و اگر به آمار I/O آن دقت کنیم، logical reads 106 قابل مشاهده‌است که بهبود قابل ملاحظه‌ای است نسبت به عدد 689 قبلی.
بررسی عملگر Clustered index scan در یک Query Plan
در ادامه همین کوئری را بر روی جدول [Sales].[Orders] اصلی اجرا می‌کنیم:
SELECT
[CustomerID],
[OrderID],
[OrderDate]
FROM [Sales].[Orders]
WHERE [CustomerID] > 550;
GO
که به صورت پیش‌فرض شامل این ایندکس‌ها است:
اجرای کوئری فوق، چنین کوئری پلنی را تولید میکند:
جدول [Sales].[Orders]، یک CLUSTERED INDEX را بر روی [OrderID] دارد و یک NONCLUSTERED INDEX را بر روی [CustomerID].
در کوئری پلن تولید شده، یک Clustered index scan مشاهده می‌شود. علت اینجا است که هرچند در جدول [Sales].[Orders] یک NONCLUSTERED INDEX بر روی [CustomerID] تعریف شده‌است:
CREATE NONCLUSTERED INDEX [FK_Sales_Orders_CustomerID] ON [Sales].[Orders]
(
[CustomerID] ASC
)
اما قسمت INCLUDE ایندکس قبلی را که تعریف کردیم، ندارد و به همراه [CustomerID] و [OrderDate] نیست. به همین جهت اینبار logical reads 692 است.
بنابراین وجود عملگر Clustered index scan در یک کوئری پلن، یعنی نیاز به خواندن و اسکن کل جدول وجود دارد. برای اثبات آن، همین کوئری قبلی را که بر روی [Sales].[Orders] انجام دادیم، اینبار بدون قسمت where آن اجرا کنید. یعنی کوئری بر روی کل جدول انجام شود:
SELECT
[CustomerID],
[OrderID],
[OrderDate]
FROM [Sales].[Orders]
سپس به برگه‌ی messages مراجعه کرده و عدد logical reads آن‌را مشاهده کنید. این عدد دقیقا با عدد logical reads کوئری where دار، یکی است؛ که بیانگر اسکن کامل جدول در حالت Clustered index scan است.
سؤال: آیا Clustered index scan همواره کل یک جدول را اسکن میکند؟
پاسخ: خیر. اگر یک کوئری برای مثال دارای top/min/max باشد، کل جدول اسکن نخواهد شد:
SELECT TOP 10
[CustomerID],
[OrderID],
[OrderDate]
FROM [Sales].[Orders]
WHERE [CustomerID] > 550;
تفاوت این کوئری با کوئری‌های قبلی، در داشتن یک top 10 است. اگر آن‌را اجرا کنیم، به کوئری پلن زیر خواهیم رسید:
هرچند در اینجا هم یک Clustered index scan صورت گرفته، اما اگر به برگه‌ی messages آن مراجعه کنیم، آمار I/O آن بیانگر تنها logical reads 5 است که معادل اسکن کل جدول نیست:
(10 rows affected)
Table 'Orders'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
مقایسه‌ی عملگرهای Index Scan و Index Seek
ابتدا کوئری زیر را اجرا می‌کنیم:
SELECT
[CustomerID],
[OrderID]
FROM [Sales].[Orders]
WHERE [OrderID] > 30000;
این کوئری با کوئری قبلی از لحاظ قسمت select اندکی متفاوت بوده و در آن OrderDate حذف شده‌است. در قسمت where نیز کوئری بر روی OrderID صورت گرفته‌است.
در این جدول ایندکسی بر روی CustomerID وجود دارد و همچنین کلید اصلی جدول، OrderID است.
پس از اجرای این کوئری، به کوئری پلن زیر خواهیم رسید:
که بیانگر یک Index Scan است و نکته‌ی جالب آن، استفاده‌ی از ایندکس FK_Sales_Orders_CustomerID می‌باشد (نام این شیء، ذیل آیکن عملگر، مشخص است). یعنی SQL Server در اینجا از یک non-clustered index تعریف شده‌ی بر روی CustomerID استفاده کرده‌است.
اکنون اگر OrderID را تغییر دهیم چه اتفاقی رخ می‌دهد؟
SELECT
[CustomerID],
[OrderID]
FROM [Sales].[Orders]
WHERE [OrderID] > 60000;
اینبار به یک clustered index seek رسیدیم که بر روی کلید اصلی جدول یا همان PK_Sales_Orders که ذیل عملگر مشخص شده، رخ داده‌است:
در این مثال با دو ورودی مختلف، دو کوئری پلن مختلف تولید شده‌است؛ که مرتبط است با میزان اطلاعاتی که قرار است بازگشت داده شود.
اگر این دو کوئری را با هم اجرا کنیم (در طی یک batch)، به پلن مقایسه‌ای زیر خواهیم رسید که در آن هزینه‌ی Index Scan بیشتر است از clustered index seek:
به همراه آمار CPU و I/O ای به صورت زیر که اولی مرتبط است با index scan و دومی با clustered index seek:
(43595 rows affected)
Table 'Orders'. Scan count 1, logical reads 191, physical reads 1, read-ahead reads 182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 754 ms.
(13595 rows affected)
Table 'Orders'. Scan count 1, logical reads 131, physical reads 0, read-ahead reads 127, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 276 ms.
به همین جهت است که عنوان می‌شود، scanها خوب نیستند و seekها بهترند.
