اشتراکها
اشتراکها
استفاده از SVN به همراه SQL Server
مطالب
بررسی کارآیی کوئریها در SQL Server - قسمت ششم - بررسی عملگرهای دسترسی به دادهها در یک Query Plan
پس از آشنایی مقدماتی با نحوهی خواندن یک 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 را انتخاب میکنیم. سپس کوئریهای زیر را اجرا میکنیم:
در اینجا در ابتدا، تمام رکوردهای جدول [Sales].[Orders]، به جدول [Sales].[Copy_Orders] کپی میشوند. سپس یک کوئری را بر روی این جدول کپی، اجرا کردهایم.
همانطور که مشاهده میکنید، برای برآورده کردن قسمت where این کوئری، یک Table Scan صورت گرفتهاست؛ چون این جدول کپی، به همراه هیچ ایندکسی نیست. به همین جهت برای یافتن رکوردهای مدنظر، راه دیگری بجز اسکن کل جدول بانک اطلاعاتی وجود ندارد که بسیار ناکارآمد است.
همچنین اگر به برگهی messages دقت کنیم، با توجه به روشن بودن STATISTICS IO، میزان logical reads نیز قابل مشاهدهاست:
به علاوه اجرای آن نیز کمی بیشتر از نیم ثانیه، طول کشیدهاست:
بررسی عملگر Index Seek در یک Query Plan
اکنون سؤال اینجا است که آیا میتوان این وضعیت را بهبود بخشید؟
بله. برای این منظور یک NONCLUSTERED INDEX را بر روی جدول کپی، ایجاد میکنیم؛ به نحوی که CustomerID لحاظ شدهی در قسمت where کوئری را پوشش دهد:
چون مطابق کوئری، [OrderID] و [OrderDate] در قسمت where ذکر نشدهاند، در اینجا INCLUDE شدهاند.
در ادامه مجددا همان کوئری را اجرا میکنیم:
که سبب تولید کوئری پلن زیر میشود:
اینبار عملگر Table Scan قبلی به یک عملگر Index Seek بر روی NONCLUSTERED INDEX تعریف شده، تغییر کردهاست و اگر به آمار I/O آن دقت کنیم، logical reads 106 قابل مشاهدهاست که بهبود قابل ملاحظهای است نسبت به عدد 689 قبلی.
بررسی عملگر Clustered index scan در یک Query Plan
در ادامه همین کوئری را بر روی جدول [Sales].[Orders] اصلی اجرا میکنیم:
که به صورت پیشفرض شامل این ایندکسها است:
اجرای کوئری فوق، چنین کوئری پلنی را تولید میکند:
جدول [Sales].[Orders]، یک CLUSTERED INDEX را بر روی [OrderID] دارد و یک NONCLUSTERED INDEX را بر روی [CustomerID].
در کوئری پلن تولید شده، یک Clustered index scan مشاهده میشود. علت اینجا است که هرچند در جدول [Sales].[Orders] یک NONCLUSTERED INDEX بر روی [CustomerID] تعریف شدهاست:
اما قسمت INCLUDE ایندکس قبلی را که تعریف کردیم، ندارد و به همراه [CustomerID] و [OrderDate] نیست. به همین جهت اینبار logical reads 692 است.
بنابراین وجود عملگر Clustered index scan در یک کوئری پلن، یعنی نیاز به خواندن و اسکن کل جدول وجود دارد. برای اثبات آن، همین کوئری قبلی را که بر روی [Sales].[Orders] انجام دادیم، اینبار بدون قسمت where آن اجرا کنید. یعنی کوئری بر روی کل جدول انجام شود:
سپس به برگهی messages مراجعه کرده و عدد logical reads آنرا مشاهده کنید. این عدد دقیقا با عدد logical reads کوئری where دار، یکی است؛ که بیانگر اسکن کامل جدول در حالت Clustered index scan است.
سؤال: آیا Clustered index scan همواره کل یک جدول را اسکن میکند؟
پاسخ: خیر. اگر یک کوئری برای مثال دارای top/min/max باشد، کل جدول اسکن نخواهد شد:
تفاوت این کوئری با کوئریهای قبلی، در داشتن یک top 10 است. اگر آنرا اجرا کنیم، به کوئری پلن زیر خواهیم رسید:
هرچند در اینجا هم یک Clustered index scan صورت گرفته، اما اگر به برگهی messages آن مراجعه کنیم، آمار I/O آن بیانگر تنها logical reads 5 است که معادل اسکن کل جدول نیست:
مقایسهی عملگرهای Index Scan و Index Seek
ابتدا کوئری زیر را اجرا میکنیم:
این کوئری با کوئری قبلی از لحاظ قسمت select اندکی متفاوت بوده و در آن OrderDate حذف شدهاست. در قسمت where نیز کوئری بر روی OrderID صورت گرفتهاست.
در این جدول ایندکسی بر روی CustomerID وجود دارد و همچنین کلید اصلی جدول، OrderID است.
پس از اجرای این کوئری، به کوئری پلن زیر خواهیم رسید:
که بیانگر یک Index Scan است و نکتهی جالب آن، استفادهی از ایندکس FK_Sales_Orders_CustomerID میباشد (نام این شیء، ذیل آیکن عملگر، مشخص است). یعنی SQL Server در اینجا از یک non-clustered index تعریف شدهی بر روی CustomerID استفاده کردهاست.
اکنون اگر OrderID را تغییر دهیم چه اتفاقی رخ میدهد؟
اینبار به یک clustered index seek رسیدیم که بر روی کلید اصلی جدول یا همان PK_Sales_Orders که ذیل عملگر مشخص شده، رخ دادهاست:
در این مثال با دو ورودی مختلف، دو کوئری پلن مختلف تولید شدهاست؛ که مرتبط است با میزان اطلاعاتی که قرار است بازگشت داده شود.
اگر این دو کوئری را با هم اجرا کنیم (در طی یک batch)، به پلن مقایسهای زیر خواهیم رسید که در آن هزینهی Index Scan بیشتر است از clustered index seek:
به همراه آمار CPU و I/O ای به صورت زیر که اولی مرتبط است با index scan و دومی با clustered index seek:
به همین جهت است که عنوان میشود، scanها خوب نیستند و seekها بهترند.
عملگرهای 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
همانطور که مشاهده میکنید، برای برآورده کردن قسمت 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
در ادامه مجددا همان کوئری را اجرا میکنیم:
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 )
بنابراین وجود عملگر Clustered index scan در یک کوئری پلن، یعنی نیاز به خواندن و اسکن کل جدول وجود دارد. برای اثبات آن، همین کوئری قبلی را که بر روی [Sales].[Orders] انجام دادیم، اینبار بدون قسمت where آن اجرا کنید. یعنی کوئری بر روی کل جدول انجام شود:
SELECT [CustomerID], [OrderID], [OrderDate] FROM [Sales].[Orders]
سؤال: آیا Clustered index scan همواره کل یک جدول را اسکن میکند؟
پاسخ: خیر. اگر یک کوئری برای مثال دارای top/min/max باشد، کل جدول اسکن نخواهد شد:
SELECT TOP 10 [CustomerID], [OrderID], [OrderDate] FROM [Sales].[Orders] WHERE [CustomerID] > 550;
هرچند در اینجا هم یک 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;
در این جدول ایندکسی بر روی CustomerID وجود دارد و همچنین کلید اصلی جدول، OrderID است.
پس از اجرای این کوئری، به کوئری پلن زیر خواهیم رسید:
که بیانگر یک Index Scan است و نکتهی جالب آن، استفادهی از ایندکس FK_Sales_Orders_CustomerID میباشد (نام این شیء، ذیل آیکن عملگر، مشخص است). یعنی SQL Server در اینجا از یک non-clustered index تعریف شدهی بر روی CustomerID استفاده کردهاست.
اکنون اگر OrderID را تغییر دهیم چه اتفاقی رخ میدهد؟
SELECT [CustomerID], [OrderID] FROM [Sales].[Orders] WHERE [OrderID] > 60000;
در این مثال با دو ورودی مختلف، دو کوئری پلن مختلف تولید شدهاست؛ که مرتبط است با میزان اطلاعاتی که قرار است بازگشت داده شود.
اگر این دو کوئری را با هم اجرا کنیم (در طی یک 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.
با توجه به کامنتی که در متد doRefreshToken نوشته شده:
"گرفتن یک توکن جدید توسط refresh_token فقط زمانی باید صورت پذیرد که id_token منقضی شده است."
بنابراین هر زمان که نیاز به token میباشد، باید منقضی شدن یا نشدن آن بررسی گردد و بسته به آن یا تقاضای token جدید شود یا token فعلی که معتبر است، استفاده گردد. این فرض صحیح است؟
تصمیم گیری که token منقضی شده یا خیر چگونه باید انجام شود؟ با ارسال درخواستی به server و بررسی token ذخیره شده در دیتابیس یا ذخیرهی تاریخ انقضا در مرورگر و اتخاذ تصمیم بر اساس آن؟
فرض کنید که میخواهیم خروجی از جدول خود را به صورت XML نمایش یا از طریق وب سرویس در برنامه مان استفاده نماییم. اولین راهی که به ذهنمان میرسد خودمان رشته xml را با حلقه ای ایجاد نماید یا استفاده از فضای نام System.Xml و کلاسهای نوشته شده برای این کار . اما خود Sql Server امکانات ویژه ای برای کار با ساختار xml مهیا نموده که براحتی میتوانید خروجی xml از داده هایتان ایجاد نمایید.
برای این کار از عبارت For XML در Select میتوان استفاده نمود. برای مثال برای بدست آوردن ساختار ساده از For Xml Auto استفاده نمایید
SELECT BusinessEntityID, PersonType, Title, FirstName, MiddleName, LastName FROM Person WHERE BusinessEntityID = 10001 FOR XML AUTO
که خروجی بصورت node attribute زیر میباشد:
اما اگر بخواهیم خروجی به صورت node Elements باشد کافیست از پارامتر Elements استفاده نمایید
SELECT BusinessEntityID, PersonType, Title, FirstName, MiddleName, LastName FROM Person WHERE BusinessEntityID = 10001 FOR XML AUTO, ELEMENTS
خروجی بصورت زیر میباشد:
اگر بخواهیم node attributes و node elements با هم ترکیب کنیم بصورت زیر عمل میکنیم:
SELECT BusinessEntityID AS '@ID', PersonType, Title, FirstName, MiddleName, LastName FROM Person WHERE BusinessEntityID = 10001 FOR XML ELEMENTS
خروجی بصورت زیر است:
حال میخواهیم همه nodeها را یک node ریشه قرار دهیم برای این کار از پارامتر ROOT در کنار AUTO به صورت زیر استفاده نمایید:
SELECT * FROM Person WHERE BusinessEntityID = 15291 FOR XML AUTO , ROOT('Persons')
اما اگر بخواهیم نام جدول را با نام دلخواه خود تغییر دهیم از پارامتر PATH به جای AUTO به صورت زیر استفاده نمایید:
SELECT * FROM Person WHERE BusinessEntityID = 15291 FOR XML PATH('P') , ROOT('Persons')
در یک hash join، اطلاعات از دو ورودی نامرتب، دریافت و join میشوند که نسبت به merge join، عملیات سنگینتری است. برای اینکار، یک hash table را از دیتاست خارجی و یک نمونهی دیگر را بر اساس دیتاست درونی ساخته و سپس کار انطباق ردیفها را انجام میدهد.
بررسی عملگر hash join
ابتدا در management studio از منوی Query، گزینهی Include actual execution plan را انتخاب میکنیم. سپس کوئریهای زیر را اجرا میکنیم:
در اینجا اطلاعات دو جدول StockItems و OrderLines بر روی ستون StockItemID با هم Join شدهاند و اجرای آن یک چنین کوئری پلنی را تولید میکند:
دیتاست بالایی که ضخامت پیکان خارج شدهی از آن کمتر است، تعداد ردیفهای کمتری را نسبت به دیتاست درونی دارد (227 ردیف، در مقابل بیش از 231 هزار ردیف).
با حرکت اشارهگر ماوس بر روی هر کدام از ایندکسها، میتوان با دقت کردن به Output List آنها، دقیقا دریافت که هرکدام، چه ستونهایی از کوئری نهایی را تامین میکنند:
دیتاست بالایی که از PK_Warehouse_StockItems تامین میشود:
دیتاست درونی که از NCCX_Sales_OrderLines تامین میشود و یک COLUMNSTORE INDEX است:
بهبود کارآیی hash join با فشرده سازی ایندکسهای آن
ایندکس NCCX_Sales_OrderLines که در کوئری فوق مورد استفاده قرار گرفته، همانطور که در قسمتی از تعریف آن نیز مشخص است، تعداد ستونهای بیشتری را از آنچه ما نیاز داریم، در بر دارد. در این حالت آیا اگر ایندکس مناسبتری را با تعداد ستون کمتری ایجاد کنیم، از آن استفاده میکند؟
این ایندکس جدید، نیازهای واقعی کوئری نوشته شده را پوشش میدهد و تعداد ستون کمتری را به همراه دارد.
در این حالت اگر کوئری زیر را اجرا کنیم:
در کوئری پلن نهایی تفاوتی مشاهده نمیشود و باز هم SQL Server، همان COLUMNSTORE INDEX را به ایندکس جدید ترجیح دادهاست. علت اینجا است که ماهیت COLUMNSTORE INDEXها فشرده شدهاست؛ در مقابل NONCLUSTERED INDEXها معمولی که به صورت پیشفرض غیر فشرده شده هستند و یک row store میباشند.
یک نکته: در این کوئری علت استفادهی از RECOMPILE، وادار کردن SQL server به محاسبهی مجدد کوئری پلن جاری است.
اکنون اگر نگارش فشرده شدهی ایندکسی را که ایجاد کردیم، با ذکر گزینهی DATA_COMPRESSION = PAGE تعریف کنیم، چه اتفاقی رخ میدهد؟
پس از آن مجددا همان کوئری قبلی را که به همراه RECOMPILE است، اجرا میکنیم. اینبار به کوئری پلنی خواهیم رسید که از این ایندکس جدید استفاده میکند.
یک نکته: اگر علاقمند بودید تا هزینهی این کوئریها را نسبت به یکدیگر محاسبه و مقایسه کنید، چون یک کوئری معمولی، همواره از آخرین پلن محاسبه شده استفاده میکند، اینکار میسر نیست. اما میتوان با ذکر صریح ایندکس مدنظر توسط راهنمای WITH INDEX، بهینه ساز کوئریها را وارد کرد تا از ایندکسی که ذکر میشود، بجای ایندکسی که فکر میکند بهتر است، استفاده کند. بنابراین اجرای هر 4 کوئری زیر با هم، 4 کوئری پلن متفاوت را بر اساس ایندکسهای متفاوتی، محاسبه کرده و نمایش میدهد:
بررسی عملگر compute scalar
کار عملگر compute scalar، ارزیابی و محاسبهی یک عبارت است و خروجی آن نیز یک مقدار scalar است؛ مانند functions در SQL Server. مشکلی که با این عملگر وجود دارد این است که هزینهی انجام آن عموما در کوئری پلن ظاهر نمیشود (و یا با تخمین نادرستی ظاهر میشود) که میتواند گمراه کننده باشد. همچنین پلن حاصل، اشیایی را که توسط یک function مورد استفاده قرار میگیرند، لحاظ نمیکند.
برای نمونه اگر پلن دو کوئری زیر را با هم مقایسه کنیم:
تقریبا یکی هستند:
از این جهت که (*)COUNT در SQL server به (*)COUNT_BIG تفسیر شده و اجرا میشود. به همین جهت آنچنان تفاوتی در اینجا قابل مشاهده نیست.
اما اگر function زیر را تعریف کنیم:
و سپس پلن کوئری که از آن استفاده میکند را بررسی نمائیم:
مشاهده خواهیم کرد که در actual execution plan آن، هزینهی فراخوانی این تابع صفر است و همچنین جزئیاتی از اشیایی که توسط آن فراخوانی شدهاند نیز ذکر نشدهاست:
یک روش محاسبهی هزینهی فراخوانی این تابع، استفاده از extended events است. روش دیگر آن استفاده از اشیاء DMO's میباشد:
این کوئری اطلاعات logical_reads مرتبط با تابع فراخوانی شده را گزارش میدهد که ... صفر نیست:
بنابراین compute scalar صورت گرفته دارای هزینهای است که در actual execution plan ظاهر نمیشود.
اکنون اگر از منوی Query، گزینهی Include actual execution plan را انتخاب نکنیم و بجای آن گزینهی Display estimated execution plan را انتخاب کنیم، به تصویر زیر خواهیم رسید:
در نیمهی پایینی آن، جزئیات دسترسیهای تابع فراخوانی شده نیز ذکر میشوند. بنابراین استفادهی از estimated execution planها در حین کار با توابع، بسیار مفید است.
بررسی عملگر 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
دیتاست بالایی که ضخامت پیکان خارج شدهی از آن کمتر است، تعداد ردیفهای کمتری را نسبت به دیتاست درونی دارد (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
یک نکته: در این کوئری علت استفادهی از 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
یک نکته: اگر علاقمند بودید تا هزینهی این کوئریها را نسبت به یکدیگر محاسبه و مقایسه کنید، چون یک کوئری معمولی، همواره از آخرین پلن محاسبه شده استفاده میکند، اینکار میسر نیست. اما میتوان با ذکر صریح ایندکس مدنظر توسط راهنمای 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;
یک روش محاسبهی هزینهی فراخوانی این تابع، استفاده از 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];
بنابراین compute scalar صورت گرفته دارای هزینهای است که در actual execution plan ظاهر نمیشود.
اکنون اگر از منوی Query، گزینهی Include actual execution plan را انتخاب نکنیم و بجای آن گزینهی Display estimated execution plan را انتخاب کنیم، به تصویر زیر خواهیم رسید:
در نیمهی پایینی آن، جزئیات دسترسیهای تابع فراخوانی شده نیز ذکر میشوند. بنابراین استفادهی از estimated execution planها در حین کار با توابع، بسیار مفید است.
- مقدار AccessTokenExpirationMinutes را در فایل تنظیمات برنامه تغییر دهید.
- مفهوم refresh token در اینجا شبیه به پیاده سازی sliding expiration برای کوکیها است. اطلاعات بیشتر: «معرفی JSON Web Token» + مثال به روز رسانی خودکار توکن منقضی شده با یک تایمر سمت کلاینت، در سری «احراز هویت و اعتبارسنجی کاربران در برنامههای Angular» عمیقتر بررسی شدهاست.