مقایسه هزینه اجرای دو کوئری در sql server که Result یکسانی دارند

چهار روش مختلف جهت مقایسه سرعت و هزینه اجرای دو کوئری وجود دارد که آنها را بررسی می نماییم . این چهار روش عبارتند از :

1)    استفاده از Execution Plan :
2)    استفاده از Statistics IO ON
3)    استفاده از Statistics Time ON
4)    استفاده از sys.dm_exec_sessions

1)    استفاده از Execution Plan :

Execution Plan یا همان نقشه فیزیکی اجرای کوئری ، ترتیب اجرای فیزیکی دستورات را مشخص می کند ، به ما کمک می کند تا دریابیم:

الف) آیا این کوئری از کوئری دوم سریعتر اجرا می شود یا خیر؟ هر کدام از Select ها که Cost کمتری داشته باشند ، سریعتر اجرا می شود.
ب ) آیا اسکیوال سرور از ایندکس من استفاده می کند یا خیر
ج) آیا نیازی است که من از ایندکس دیگری استفاده کنم.

به طور خلاصه می توان گفت، Execution plan میتواند به شما بگوید که کوئری قراره چطور اجرا بشه یا کوئری چطور اجرا شده. بنابراین کوئری پلن راهکار اولیه DBA ها برای بررسی کوئری  با کارایی پایین هست. به این وسیله می توانید حدس بزنید که چرا کوئری هزاران اسکن انجام داده و I/O رو به میزان زیادی بالا برده است.

انواع Execution Plan

1 – Estimated Plan (نقشه اجرای تخمینی): این نوع نقشه بدون اجرای کوئری ایجاد شده و تخمینی از عملکرد کوئری می باشد. برای بدست آوردن این نوع نقشه : 

الف) کافیست کوئری خود را انتخاب نموده و کلیدهای CTRL+L را با هم بگیریم.
ب) کوئری خود را انتخاب نموده و از منوی نوار ابزار بر روی آیکن Display Estimated Plan کلیک کنیم. دقت کنید که در این روش کوئری را اجرا نمی کنیم.

لازم به ذکر است زمانی که اجرای کوئری باعث کندی سیستم شده و زمان زیادی جهت اجرای آن لازم باشد ، لذا از این روش جهت مشاهده Execution Plan استفاده می کنیم.

2 – Actual Plan (نقشه اجرای واقعی): نقشه اجرایی واقعی ، این نوع نقشه پس از اجرای کوئری ایجاد شده و عملکرد واقعی کوئری را نشان می دهد. برای بدست آوردن این نوع نقشه کافی است به یکی از روش های زیر عمل نماییم.

الف) کافیست کوئری خود را انتخاب نموده و کلیدهای CTRL+M را با هم بگیریم.
ب ) از نوار ابزار بر روی آیکون Display Actual Plan کلیک نموده و سپس کوئری خود را اجرا نماییم.

تذکر مهم : ترتیب اجرای مراحل در Execution Plan ، از راست به چپ و از بالا به پایین می باشد(هنگام خواندن پلن وقتی به دوراهی میرسی برو پایین و برو به انتهای سمت راست).

مقایسه هزینه اجرای دو کوئری به روش Execution Plan : 

کافیست دو کوئری را با هم انتخاب نموده و از نوار ابزار آیکن Include Actual Execution Plan را فعال نموده و سپس بر روی دکمه Execute جهت اجرای دو کوئری کلیک کنید و پس از اتمام اجرای دو کوئری در قسمت Result تب سومی مربوط به Execution Plan ظاهر می شود. بر روی تب سوم یعنی همان تب مربوط به Execution Plan کلیک کنید و سپس عدد مربوط به Cost  و یا به عبارت دقیق تر عدد مربوط به relative to the batch مربوط به دو Plan را با یکدیگر مقایسه کنید. هر کدام که Cost کمتری داشته باشد، کوئری بهینه تری خواهد بود.

تذکر : لازم به ذکر است که منظور از Cost ، برآیند هزینه مربوط به CPU و IO می باشد.

SELECT TOP 100000       ID
FROM    dbo.T1 WITH (INDEX (T1_PRIMARY_KEY))
UNION
SELECT TOP 100000        ID
FROM     dbo.T2 WITH (INDEX (T2_PRIMARY_KEY));
-----------------------------------------------------------------
SELECT TOP 100000       ID
FROM   dbo.T1 WITH (INDEX (T1_PRIMARY_KEY))
UNION ALL
SELECT TOP 100000       ID
FROM   dbo.T2 WITH (INDEX (T2_PRIMARY_KEY));

همانطور که می بینید ، هزینه اجرا یا Cost مربوط به اجرای کوئری اول 63 درصد شده و هزینه اجرای کوئری دوم 37 درصد شده است.

تذکر مهم : هنگامی که می خواهید از یکی از دو دستور Union و Union ALL استفاده نمایید، چنانچه در هر دو جدول رکورد تکراری نداشتید حتما و حتما از دستور Union ALL استفاده نمایید.چون باعث حذف اپراتور Sort خواهید شد.

2 – استفاده از دستور Set Statistics IO ON : 

با فعال کردن آمار IO ، به کمک دستور Set Statistics IO ON ، می توانیم هزینه دو کوئری را با هم مقایسه کنیم. Statistics در لغت به معنای آمار می باشد. کافیست این دستور را قبل از دو کوئری نوشته و کل دستورات را با هم اجرا نماییم. بعد از اتمام اجرای کوئری در پایین صفحه و در تب Messages میتوانیم تعداد IO های مربوط به هر دو کوئری را به تفکیک ببینیم.

تذکر : وقتی در قسمت Messages به طور مثال دیدید Physical Read 5  ، این بدان معناست که پنج تا Page هشت کیلوبایتی اسکیوال سرور خونده و از دیسک به حافظه منتقل کرده است. به عبارتی پنج بار به دیسک مراجعه کرده است، تا جواب Select شما را تهیه و به شما نشان دهد.

تذکر : واحد IO در اسکیوال سرور Page می باشد. ظرفیت هر Page هشت کیلوبایت می باشد.

انواع IO در اسکیوال سرور:

الف) Physical Read : خواندن رکوردها( Page ها ) از روی دیسک و انتقال آنها به حافظه (تعداد دفعات دسترسی به دیسک) را IO فیزیکی می گویند.
ب ) Logical Read : خواندن رکوردها ( Page ها ) از روی حافظه (تعداد دفعات دسترسی به حافظه) را IO منطقی می گویند.
ج ) read-ahead- reads : این نوع خواندن اطلاعات نیز نوعی Physical Read می باشد. زمانی که ما دستوری را اجرا می کنیم اسکیوال سرور ممکن است که IO بیشتری زده و تعداد رکوردهای بیشتری را به حافظه منتقل کند و همچنین این نوع IO بر اثر وجود Fragment نیز ممکن است رخ دهد (تعداد دفعات دسترسی به دیسک).

تذکر : برای برطرف کردن Fragment ایندکس ها می توانیم از دستور Rebuild ایندکس ها استفاده نماییم(به شرطی که عدد Fragment بالاتر از عدد 30 باشد. اگر بین 10 تا 30 باشد از دستور Reorganize جهت برطرف کردن Fragment ایندکس ها باید استفاده نماییم).

د ) LOB Physical Read : خواندن Page ها از روی دیسک که مربوط به Large object ها می باشد.(LOB ها مانند فیلم ، عکس ، تصویر و غیره)
ه ) LOB Logical Read :  خواندن Page ها از روی حافظه که مربوط به Large object ها می باشد.

3 – استفاده از Set Statistics Time ON : 

یکی دیگر از روش های مقایسه سرعت و هزینه اجرای دو کوئری استفاده از آمار Time می باشد. کافیست از دستور Set Statistics Time ON قبل از کوئری خود استفاده نمایید. در این صورت در تب Messages می توانید به ریز زمان مربوط به کامپایل هر کوئری و زمانی که CPU به صورت خالص برای اجرای آن کوئری وقت گذاشته است را ببینید.

4 – استفاده از DMV ، sys.dm_exec_sessions :

در این روش کافیست هر کدام از کوئری ها را در یک Session جداگانه اجرا نموده و سپس در یک Session جدید دیگر از این DMV به صورت زیر استفاده نمایید، تا اطلاعاتی همچون تعداد Read و تعداد Write ها و نام Login Name را به شما نشان خواهد داد. همچنین تعداد IO های منطقی و همچنین زمانی که CPU به صورت خالص وقت گذاشته تا این کوئری را اجرا کند.

     تهیه و تنظیم : استادغلامحسین عبادی