در توسینسو تدریس کنید

و

با دانش خود درآمد کسب کنید

بهینه سازی بازدهی پایگاه داده SQL Server با Hypothetical Index

یکی از مشکلات در روند بهینه سازی بازدهی یا همان 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;

بهینه سازی بازدهی پایگاه داده SQL Server با Hypothetical Index

همانطور که در تصویر بالا مشاهده می کنید تمامی دستورات از عملیات فیزیکی 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 ساخته شده است.

بهینه سازی بازدهی پایگاه داده SQL Server با Hypothetical Index

در مرحله بعدی برای استفاده از 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

  • 5 : شروع دوباره Session و یا پاکسازی تمامی دستورات قبلی
  • 6 : فقط با استفاده از Clustered 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 ها می توان سرعت بازدهی پایگاه داده را با اطمینان خاطر بالا برد.

بهینه سازی بازدهی پایگاه داده SQL Server با Hypothetical Index

حمید ج. فرد

متخصص پایگاه داده SQL Server

منبع: Fard Solutions

#کاربرد_hypothetical_index_در_sql_سرور #بالا_بردن_performance_در_اوراکل #بالا_بردن_سرعت_query_در_sql #بالا_بردن_performance_در_sql_سرور #بالا_بردن_سرعت_query_گرفتن #نحوه_استفاده_از_hypothetical_index #hypothetical_index_چیست #انواع_index_در_sql_سرور #performance_tunning_در_sql_سرور
عنوان
1 آیا NonClustered Columnstore Index گزینه مناسبی است؟ رایگان
2 مقایسه فنی Stream Aggregate و Hash Match رایگان
3 بهینه سازی بازدهی پایگاه داده SQL Server با Hypothetical Index رایگان
4 بازیابی داده های سالم از پایگاه داده خراب رایگان
5 تغییر در Optimizer Engine برای غیر فعال کردن مرتب سازی JOIN ها رایگان
6 اهمیت اجراء دستور DBCC CheckDB قبل از گرفتن فایل پشتیبان رایگان
7 اهمیت استراتژی پشتیبانگیری و بازیابی پایگاه داده در SQL Server رایگان
8 محدودیتهای بازدهی و راه حل آنها در SQL Server رایگان
9 مستلزمات برای طراحی Index در SQL Server رایگان
زمان و قیمت کل 0″ 0
6 نظر
prober

سرم گیج رفت نکتتون خیلی حرفه ای هست احسنت

فکر می کنم در حد مقاله است تا نکته

محمد نصیری

واقعا لذت میبرم مقاله ها و نکته هاتون رو میخونم ، از اینکه در کنار ما هستید خیلی خوشحالیم..

حمید ج. فرد

خواهش می کنم. امیدوارم همه از این مطلب استفاده مفید برده باشند.

fotros

سلام؛

بسیار عالی بود....

ممنون اقای مهندس که دانش و تجربیات خودتون رو در اختیار ما میگذارید. این روش خیلی برای من کارامد خواهد بود.قصد تست روی دیتابیس های سنگینم رو دارم.

امیدوارم باز هم مقالاتی پیرامون Performance Tunning داشته باشیم.

فرشید علی اکبری

سلام

خیلی عالی بود و تشکر از مقاله خوبتون.

حسن ضرابی

با سلام خدمت استاد عزیزم جناب آقای مهندس HamidJFard

جناب آقای مهندس از مقاله بسیار بسیار بسیار بسیار عالی شما ممنونم واقعا عالی بود

مقاله های شما بی نظیر هست واقعا استاد هستید

ممنونم که دانش خود را در اختیار ما کاربران قرار می دهید

موفق باشید

با تشکر از شما دوست عزیز

نظر شما
برای ارسال نظر باید وارد شوید.
از سرتاسر توسینسو
تنظیمات حریم خصوصی
تائید صرفنظر
×

تو می تونی بهترین نتیجه رو تضمینی با بهترین های ایران بدست بیاری ، پس مقایسه کن و بعد خرید کن : فقط توی جشنواره پاییزه می تونی امروز ارزونتر از فردا خرید کنی ....