یکی از مشکلات در روند بهینه سازی بازدهی یا همان Performance یک پایگاه داده بسیار حجیم، ساختن Index بر روی جداول آن است که باعث پائیین آمدن روند بهینه سازی و در مواردی باعث خارج شدن پایگاه داده از ارائه خدمات به نرم افزارهای مرتبط می شود. اصولا مدیران پایگاه داده برای بهینه سازی بازدهی پایگاه داده، اشیاء Index را ساخته و بعد دستورات Query را مورد بررسی و ارزیابی قرار می دهند که البته این نکته قابل ذکر است که متخصصان حرفه ای پایگاه داده قبل از ساخت اشیاء Index در پایگاه داده، آنها را با Query ها ارزیابی کرده و بعد از به نتیجه رسیدن منطقی به صورت فیزیکی آن Index را پیاده سازی می کنند.
در این مقاله می خواهیم به یکی از روشهای حرفه ای در بهینه سازی بازدهی پایگاه داده بپردازیم. یکی از روش های معمول در بین متخصصین، روش Hypothetical Index است که روند بهینه سازی پایگاه داده را به مقدار قابل توجه ای افزایش می دهد. در ذهن داشته باشید که تمامی دستورات در این مقاله از طرف شرکت مایکروسافت به صورت رسمی ارائه نشده و استفاده از آنها در پایگاه داده های اصلی یا Production بدون داشتن دانش فنی بالا پیشنهاد نمی شود.
توجه: نویسنده و انتشار دهنده این مقاله هیچگونه مسئولیتی در قبال خرابی احتمالی پایگاه داده توسط اجراء این دستورات ندارد.
Hypothetical Index نوعی از Index است که هیچگونه داده ای را به صورت فیزیکی ذخیره نمی کند و تنها ساختار Index در SQL Server ذخیره شده، فقط توسط SQL Server Optimizer Engine قابل دسترسی و استفاده است. این نوع از Indexها می تواند به دو صورت، با Statistics و بدون Statistics ساخته شود. توجه داشته باشید که ساختن این نوع Index با سرعت بسیار بالایی انجام می شود و قابل استفاده در SQL Server 2005 ، SQL Server 2008 ، SQL Server 2008R2 ، SQL Server 2012 و SQL Server 2014 است. برای شروع استفاده از این روش حرفه ای، یک جدول با نام TempData در پایگاه داده Tempdb با استفاده از دستور زیر می سازیم و آن را از داده پر می کنیم.
use tempdb; go Create Table dbo.TempData (ID bigint Identity(1,1),Padding binary(8) Default 0xFF, CPadding Char(2) Default 'AA'); go Insert Into dbo.TempData Default Values Go 1000
بعد از ساخت جدول TempData، با استفاده از دستورات زیر داده های جدول را واکشی کرده و در انتها نوع عملیات در Execution Plan را در تصویر زیر مشاهده می کنیم.
Select ID,Padding From dbo.TempData Where ID = 500; Select ID From dbo.TempData Where ID = 500 ; Select * from dbo.TempData Where ID = 500;
همانطور که در تصویر بالا مشاهده می کنید تمامی دستورات از عملیات فیزیکی Table Scan برای بازیابی داده ها استفاده می کند که این نوع عملیات برای SQL Server بسیار هزینه بردار است. حال برای بهینه سازی این دستورات ما نیاز به ساخت Index برای پشتیبانی از Query ها داریم ولی به دلیل حجم زیاد داده، ساخت Index زمانبر و ممکن است پایگاه داده را از دسترس نرم افزارهای مرتبط خارج کند. در این شرایط از Hypothetical Index استفاده می کنیم. دستورات زیر سه (3) Index از نوع Hypothetical با نام های FARDIX, FARDIX1 و FARDCIX بر روی جدول TempData میسازد.
Create Index FARD_IX on dbo.TempData (ID) INCLUDE (PADDING) With Statistics_Only ; Create Index FARD_IX_1 on dbo.TempData (ID) With Statistics_Only ; Create Clustered Index FARD_CIX on dbo.TempData (ID) With Statistics_Only ;
ساختن این Index ها 1 ثانیه به طول انجامید که در مقایسه با ساختن Index های معمولی بسیار سریعتر است. در قسمت بعدی می خواهیم بخشی از ساختار Index های ساخته شده را مشاهده کنیم. دستورات زیر اطلاعاتی را از قبیل نام و شماره انحصاری جدول TempData و Index های ساخته شده به ما می دهد.
Select object_id,name,index_id,is_hypothetical from sys.indexes where Name IN ('FARD_IX','FARD_IX_1','FARD_CIX');
Select O.object_id,O.name,A.data_pages from sys.allocation_units A
Inner Join sys.partitions P on P.partition_id = A.container_id
Inner Join sys.objects O on O.object_id = P.object_id AND O.name IN ('FARD_IX','FARD_IX_1','FARD_CIX','TempData');
همانطور که در تصویر زیر مشاهده می کنید، تمامی Index ها از نوع Hypothetical بر جدول TempData ساخته شده است.
در مرحله بعدی برای استفاده از Hypothetical Index ها، شما نیاز دارید تا از دستور DBCC AUTOPILOT استفاده نمائید. این دستور SQL Server Optimizer Engine را طوری تنظیم می کند که از Hypothetical Index ها در Estimated Execution Plan ها استفاده کند. با استفاده از این دستور شما می توانید از طرز استفاده از Index ها قبل از ساخت فیزیکی آنها مطلع شوید. دستور DBCC AUTOPILOT چندین پارامتر را برای تنظیم SQL Server Optimizer Engine نیاز دارد که در جدول زیر مشخصات آنها ذکر شده است.
ردیف نام پارامتر مقدار
1 TYPE_ID 0 : با استفاده از NonClustered Index
2 DB_ID شماره انحصاری پایگاه داده ای که Hypothetical Index ها در آن موجود است.
3 OBJECT_ID شماره انحصاری جدول مورد نظر.
4 INDEX_ID شماره انحصاری Hypothetical Index در جدول مورد نظر.
دستورات زیر SQL Server Optimizer Engine را برای استفاده از Hypothetical Index ها در جدول TempData در پایگاه داده Tempdb تنظیم می کند.
-- DBCC AUTOPILOT (TYPE_ID,DB_ID,OBJECT_ID,INDEX_ID); DBCC AUTOPILOT(0,2,389576426,4); DBCC AUTOPILOT(0,2,389576426,5); DBCC AUTOPILOT(6,2,389576426,6);
در پایان نیاز به اجراء دستورات SET AUTOPILOT ON در قبل و SET AUTOPILOT OFF در بعد ازQuery برای مشاهده نوع عملیات واکشی داده در Estimated Execution Plan داریم. دستورات زیر نوع عملیات واکشی داده از جدول TempData را با استفاده از Hypothetical Index ها را نشان می دهند. در نظر داشته باشید که Estimated Execution Plan با اجراء Query بدون استفاده از دستورات SET AUTOPILOT تماما نوع عملیاتی Table Scan را نشان خواهد داد.
SET AUTOPILOT ON; go Select ID,Padding From dbo.TempData Where ID = 500; Select ID From dbo.TempData Where ID = 500 ; Select * from dbo.TempData Where ID = 500; go SET AUTOPILOT OFF;
همانطور که در تصویر زیر مشاهده می کنید، عملیات واکشی داده بعد از استفاده از Hypothetical Index ها به طور کلی تغییر کرده و نشان می دهد که با ساختن فیزیکی این Index ها می توان سرعت بازدهی پایگاه داده را با اطمینان خاطر بالا برد.