بررسی Threshold در SQL Server

مقاله کامل در مورد Threshold for Parallelism در SQL Server

مقدمه

در SQL Server، برای بهبود عملکرد پردازش کوئری‌ها و کاهش زمان پاسخ‌دهی، تکنیک‌های مختلفی به کار گرفته می‌شود که یکی از آن‌ها استفاده از Parallelism است. SQL Server تصمیم می‌گیرد که آیا یک کوئری باید به صورت موازی پردازش شود یا خیر، با توجه به چندین پارامتر مختلف، یکی از این پارامترها Threshold for Parallelism (آستانه هزینه برای موازی‌سازی) است. این پارامتر نقش حیاتی در عملکرد سیستم دارد زیرا تعیین می‌کند که کدام کوئری‌ها باید از موازی‌سازی استفاده کنند و کدام کوئری‌ها باید به صورت خطی اجرا شوند.

در این مقاله، به طور مفصل به Threshold for Parallelism، نحوه تنظیم آن، اثرات آن و تأثیر عدد واردشده خواهیم پرداخت.

1. Threshold for Parallelism چیست؟

Threshold for Parallelism در SQL Server، به حداقل هزینه‌ای اطلاق می‌شود که برای یک کوئری تعیین می‌شود تا سیستم تصمیم بگیرد از Parallelism استفاده کند یا نه. این هزینه به عنوان Cost Threshold for Parallelism شناخته می‌شود و به SQL Server می‌گوید که اگر هزینه اجرای یک کوئری بیشتر از این مقدار باشد، از موازی‌سازی استفاده کند. در غیر این صورت، کوئری به صورت خطی (Single-threaded) اجرا خواهد شد.

هزینه کوئری (Query Cost):

هزینه کوئری به میزان پردازشی که یک کوئری برای اجرا نیاز دارد اطلاق می‌شود. این هزینه می‌تواند شامل مواردی مانند تعداد ردیف‌ها، حجم داده‌ها، پیچیدگی کوئری و تعداد عملیات‌ها باشد. SQL Server به طور خودکار این هزینه را محاسبه کرده و اگر این هزینه از حد Threshold بالاتر باشد، از Parallelism استفاده خواهد کرد.

چرا استفاده از Threshold برای Parallelism مهم است؟

استفاده صحیح از Cost Threshold for Parallelism به مدیران پایگاه داده کمک می‌کند تا از موازی‌سازی به صورت بهینه استفاده کنند و از منابع سیستم بهره‌برداری بهتری داشته باشند. اگر این مقدار خیلی پایین باشد، SQL Server ممکن است برای کوئری‌های ساده نیز از موازی‌سازی استفاده کند که موجب مصرف اضافی منابع می‌شود. از طرف دیگر، اگر این مقدار خیلی بالا باشد، ممکن است SQL Server از موازی‌سازی برای کوئری‌های پیچیده و سنگین استفاده نکند و در نتیجه زمان پاسخ‌دهی کوئری‌ها افزایش یابد.

2. نحوه تنظیم Threshold for Parallelism

برای تنظیم Cost Threshold for Parallelism در SQL Server، از دستور sp_configure استفاده می‌شود. با این دستور می‌توان مقدار این آستانه را تغییر داد.

دستور تنظیم Threshold for Parallelism

-- مشاهده تنظیمات فعلی 
EXEC sp_configure 'cost threshold for parallelism';
-- تنظیم مقدار جدید برای 
Threshold EXEC sp_configure 'cost threshold for parallelism', 50; 
RECONFIGURE; 

در اینجا، دستور sp_configure برای مشاهده یا تغییر مقدار Threshold for Parallelism استفاده می‌شود. به طور پیش‌فرض، این مقدار در SQL Server برابر با 5 است. با استفاده از دستور RECONFIGURE تغییرات اعمال می‌شود.

چگونه عدد Threshold را تنظیم کنیم؟

• مقدار پایین‌تر (مثلاً 5) به SQL Server می‌گوید که موازی‌سازی را برای کوئری‌های با هزینه پایین‌تر فعال کند.

• مقدار بالاتر (مثلاً 50 یا 100) باعث می‌شود که SQL Server تنها برای کوئری‌های با هزینه بالا از موازی‌سازی استفاده کند.

3. تأثیر عدد Threshold for Parallelism

تنظیم مقدار Threshold for Parallelism تأثیر زیادی بر عملکرد سیستم و نحوه استفاده از منابع سیستم خواهد داشت. این تنظیم می‌تواند به بهینه‌سازی زمان پاسخ‌دهی کوئری‌ها کمک کند یا منجر به مصرف منابع غیرضروری شود. در ادامه، به بررسی تأثیرات مختلف تغییر مقدار Threshold خواهیم پرداخت.

تأثیر مقدار پایین Threshold

اگر مقدار Threshold for Parallelism را خیلی پایین تنظیم کنید، SQL Server به سرعت به دنبال موازی‌سازی برای کوئری‌ها می‌گردد. این می‌تواند در برخی شرایط مفید باشد، به ویژه زمانی که سیستم با بار پردازشی کم یا پردازش کوئری‌های ساده مواجه است.

مزایای تنظیم مقدار پایین:

• بهبود سرعت اجرای کوئری‌ها: کوئری‌های کوچک و ساده ممکن است بهبود چشمگیری در سرعت اجرا تجربه کنند.

• استفاده از منابع پردازشی بیشتر: در صورت وجود پردازنده‌های چند هسته‌ای، استفاده از موازی‌سازی برای کوئری‌های کوچک ممکن است موجب بهره‌برداری بیشتر از منابع پردازشی شود.

معایب تنظیم مقدار پایین:

• افزایش مصرف منابع: برای کوئری‌های ساده و کم‌هزینه، استفاده از موازی‌سازی ممکن است منابع اضافی (پردازنده، حافظه) را مصرف کند.

• کاهش کارایی: برخی از کوئری‌ها که بهتر است به صورت خطی اجرا شوند، ممکن است زمان بیشتری بگیرند اگر موازی‌سازی برای آن‌ها فعال شود.

• افزایش پیچیدگی سیستم: با استفاده بیش از حد از موازی‌سازی برای کوئری‌های ساده، ممکن است پیچیدگی‌هایی در مدیریت سیستم به وجود آید.

تأثیر مقدار بالا Threshold

اگر مقدار Threshold for Parallelism را بالاتر تنظیم کنید (مثلاً 50 یا 100)، SQL Server تنها برای کوئری‌هایی با هزینه بالا از موازی‌سازی استفاده خواهد کرد.

مزایای تنظیم مقدار بالا:

• صرفه‌جویی در منابع: تنها کوئری‌های پیچیده و سنگین از موازی‌سازی استفاده می‌کنند که به مصرف منابع بهینه‌تر منجر می‌شود.

• کاهش پیچیدگی سیستم: استفاده از موازی‌سازی فقط برای کوئری‌های سنگین باعث ساده‌تر شدن مدیریت منابع سیستم و اجرای بهینه‌تر خواهد شد.

• کاهش هزینه پردازشی: کوئری‌های ساده به صورت خطی اجرا می‌شوند و از منابع پردازشی اضافی استفاده نمی‌شود.

معایب تنظیم مقدار بالا:

• کاهش سرعت برای کوئری‌های پیچیده: برای برخی از کوئری‌های پیچیده که به طور طبیعی باید موازی اجرا شوند، محدود کردن موازی‌سازی ممکن است باعث افزایش زمان اجرا شود.

• عدم استفاده بهینه از هسته‌های پردازشی: اگر سیستم شما پردازنده‌های چند هسته‌ای دارد، استفاده نکردن از موازی‌سازی برای کوئری‌های سنگین می‌تواند به معنای استفاده ناکارآمد از منابع پردازشی باشد.

4. نحوه بررسی تأثیر Threshold for Parallelism

برای بررسی تأثیر مقدار Threshold for Parallelism، می‌توانید از Execution Plan استفاده کنید. در Execution Plan، اگر SQL Server از Parallelism برای اجرای یک کوئری استفاده کرده باشد، مراحل موازی با علامت‌های خاصی (مانند Parallelism (Gather Streams)) نمایش داده می‌شود.

بررسی Execution Plan

 
-- فعال‌سازی نمایش 
Execution Plan SET STATISTICS IO ON;
SET STATISTICS TIME ON; 
GO
-- اجرای کوئری 
SELECT * FROM Orders JOIN Customers 
ON Orders.CustomerID = Customers.CustomerID 
WHERE Orders.OrderDate BETWEEN '2022-01-01' 
AND '2022-12-31';  
GO 
SET STATISTICS IO OFF; 
SET STATISTICS TIME OFF; 

در Execution Plan، اگر موازی‌سازی فعال باشد، می‌توانید مراحل موازی را مشاهده کنید. همچنین، از Dynamic Management Views (DMVs) می‌توانید اطلاعاتی در مورد استفاده از موازی‌سازی و وضعیت اجرای کوئری‌ها به دست آورید.

 
-- بررسی وضعیت استفاده از 
Parallelism SELECT * FROM 
sys.dm_exec_requests 
WHERE command = 'SELECT'; 

توضیحات تکمیلی

Threshold for Parallelism یکی از پارامترهای کلیدی در تنظیم SQL Server برای مدیریت عملکرد و موازی‌سازی کوئری‌ها است. این تنظیم تعیین می‌کند که SQL Server چه زمانی باید از موازی‌سازی استفاده کند و این می‌تواند تأثیر زیادی بر زمان اجرای کوئری‌ها و بهره‌برداری از منابع سیستم داشته باشد. با تنظیم صحیح این مقدار، می‌توانید از موازی‌سازی به صورت بهینه استفاده کرده و کارایی سیستم خود را بهبود بخشید.

!نکته

ورود 'cost threshold for parallelism' در SQL Server الزامی نیست، زیرا این پارامتر یک تنظیم پیش‌فرض است که SQL Server به طور خودکار برای کوئری‌ها استفاده می‌کند. با این حال، شما می‌توانید مقدار آن را تغییر دهید تا تأثیر بیشتری در انتخاب زمان استفاده از موازی‌سازی داشته باشید.

دلایل اینکه چرا الزامی نیست:

1. تنظیم پیش‌فرض موجود است:

   • به طور پیش‌فرض، SQL Server یک مقدار از پیش تنظیم‌شده برای cost threshold for parallelism دارد که معمولاً ۵ است. این بدان معناست که اگر هزینه کوئری (که SQL Server به طور خودکار محاسبه می‌کند) بیشتر از ۵ باشد، SQL Server از موازی‌سازی استفاده خواهد کرد.

2. خودکار بودن فرآیند موازی‌سازی:

   • SQL Server به صورت خودکار تصمیم می‌گیرد که آیا یک کوئری باید به صورت موازی اجرا شود یا خیر، بر اساس هزینه محاسباتی آن کوئری و تنظیمات موجود مانند cost threshold for parallelism.

   • برای کوئری‌هایی با هزینه بالا، SQL Server به صورت خودکار موازی‌سازی را فعال می‌کند و نیازی به تغییر دستی این مقدار نیست مگر اینکه بخواهید آن را بر اساس نیازهای خاص سیستم یا کوئری‌های خاص تنظیم کنید.

چرا باید مقدار 'cost threshold for parallelism' را تغییر داد؟

اگر بخواهید عملکرد سیستم خود را بهینه کنید یا مدیریت دقیقی بر روی استفاده از منابع سیستم داشته باشید، تغییر مقدار این پارامتر می‌تواند مفید باشد. به طور مثال:

• تنظیم مقدار پایین‌تر می‌تواند باعث شود SQL Server برای کوئری‌های با هزینه پایین‌تر نیز موازی‌سازی را فعال کند که ممکن است منابع اضافی را مصرف کند.

• تنظیم مقدار بالاتر باعث می‌شود که فقط برای کوئری‌های پیچیده‌تر و سنگین‌تر از موازی‌سازی استفاده شود که می‌تواند موجب بهینه‌تر شدن استفاده از منابع سیستم و کاهش پیچیدگی اجرایی شود. 

ورود 'cost threshold for parallelism' الزامی نیست، اما تنظیم آن بسته به نیازهای سیستم و کوئری‌ها می‌تواند تأثیر زیادی در عملکرد SQL Server داشته باشد. تغییر این مقدار به شما این امکان را می‌دهد که از موازی‌سازی بهینه‌تر استفاده کنید و از منابع سیستم به بهترین شکل بهره‌برداری کنید.

برای اینکه بهتر مفهوم Threshold for Parallelism و Cost در SQL Server را متوجه شوید، ابتدا باید این مفاهیم را با چند مثال و توضیحات دقیق‌تری بررسی کنیم.

1. مفاهیم پایه: Cost و Threshold for Parallelism

• Cost در SQL Server، به میزان پیچیدگی و منابع مصرفی برای اجرای یک کوئری گفته می‌شود. SQL Server این هزینه را به‌طور خودکار محاسبه می‌کند و بر اساس آن تصمیم می‌گیرد که آیا کوئری باید به صورت موازی اجرا شود یا نه.

• Threshold for Parallelism یا Cost Threshold for Parallelism به حداقل هزینه‌ای گفته می‌شود که برای SQL Server تعیین می‌کند تا برای کوئری، موازی‌سازی (یعنی استفاده از چند هسته پردازشی به طور هم‌زمان) فعال شود.

2. تنظیمات پیش‌فرض

به طور پیش‌فرض، مقدار Threshold for Parallelism در SQL Server برابر با 5 است. این به این معناست که اگر هزینه اجرای یک کوئری از مقدار ۵ بیشتر باشد، SQL Server تصمیم می‌گیرد از موازی‌سازی استفاده کند.

3. چگونه SQL Server هزینه کوئری را محاسبه می‌کند؟

SQL Server به‌طور خودکار هزینه یک کوئری را محاسبه می‌کند و این هزینه به‌طور عمومی به عنوان هزینه اجرایی کوئری شناخته می‌شود. این هزینه معمولاً به تعداد عملیات‌های مورد نیاز برای اجرا و منابع مصرفی کوئری بستگی دارد.

4. مثال‌ها

4.1. تنظیمات پیش‌فرض (مقدار Threshold = 5)

فرض کنید که مقدار Threshold for Parallelism برابر با ۵ است و ما یک کوئری ساده داریم که به دیتابیس دسترسی می‌یابد.

کوئری مثال:

 
SELECT * FROM Employees; 

این کوئری ساده‌ای است که فرض می‌کنیم برای اجرای آن هزینه محاسباتی به اندازه ۴ باشد (کمتر از ۵). در این صورت، چون هزینه کوئری کمتر از آستانه مشخص‌شده است، SQL Server تصمیم می‌گیرد از موازی‌سازی استفاده نکند.

4.2. تغییر Threshold for Parallelism

حال فرض کنید شما مقدار Threshold for Parallelism را به ۲۰ تغییر می‌دهید.

دستور تنظیم Threshold:

 
EXEC sp_configure 'cost threshold for parallelism', 20; 
RECONFIGURE; 

با این تنظیم جدید، برای استفاده از موازی‌سازی، هزینه کوئری باید بیشتر از ۲۰ باشد.

کوئری پیچیده‌تر با هزینه بالا:

 
SELECT COUNT(*)  FROM Orders  
WHERE OrderDate 
BETWEEN '2020-01-01' AND '2020-12-31'; 

در این کوئری، SQL Server محاسبه می‌کند که هزینه آن بیشتر از ۲۰ است (مثلاً ۲۵). چون این مقدار از آستانه تنظیم‌شده بیشتر است، SQL Server تصمیم می‌گیرد که از موازی‌سازی استفاده کند و کوئری را با چندین هسته پردازشی اجرا کند.

4.3. نحوه بررسی اینکه موازی‌سازی فعال شده است

برای بررسی اینکه SQL Server از موازی‌سازی استفاده کرده یا خیر، می‌توانید از Execution Plan استفاده کنید. در Execution Plan، اگر موازی‌سازی فعال باشد، به وضوح نشان داده می‌شود.

کوئری برای مشاهده Execution Plan:

 
SET STATISTICS IO ON; 
SET STATISTICS TIME ON;  
-- اجرای کوئری 
SELECT COUNT(*)  FROM Orders  
WHERE OrderDate BETWEEN '2020-01-01' AND '2020-12-31';  
GO 
SET STATISTICS IO OFF; 
SET STATISTICS TIME OFF; 

خروجی Execution Plan چیزی مشابه این خواهد بود:

 
|--Parallelism (Gather Streams)    |    
    |--Sort       |       
         |--Table Scan 

در اینجا، نشان داده شده که SQL Server برای این کوئری از موازی‌سازی استفاده کرده است و به وضوح Parallelism (Gather Streams) را در Execution Plan می‌بینید.

5. مثال‌های عملی از تغییر Threshold

5.1. Threshold پایین‌تر از ۵

اگر مقدار Threshold for Parallelism را به عددی پایین‌تر از ۵ تغییر دهید (مثلاً ۲)، SQL Server ممکن است از موازی‌سازی برای بسیاری از کوئری‌های ساده نیز استفاده کند.

کوئری ساده:

 
SELECT * FROM Products; 

این کوئری ممکن است هزینه‌ای برابر با ۳ داشته باشد. چون این مقدار بیشتر از ۲ است، SQL Server از موازی‌سازی استفاده خواهد کرد، حتی برای کوئری‌های ساده‌ای که هزینه کمی دارند.

5.2. Threshold بالاتر از ۲۰

اگر مقدار Threshold for Parallelism را به عددی بالاتر از ۲۰ تغییر دهید (مثلاً ۵۰)، SQL Server تنها برای کوئری‌هایی با هزینه بالای ۵۰ از موازی‌سازی استفاده خواهد کرد.

کوئری پیچیده‌تر:

 
SELECT AVG(Price)  FROM Products  
WHERE Category = 'Electronics'; 

اگر هزینه این کوئری ۴۰ باشد (کمتر از ۵۰)، SQL Server از موازی‌سازی استفاده نخواهد کرد. در عوض، کوئری به صورت خطی اجرا خواهد شد.

6. نحوه بررسی استفاده از Parallelism

برای بررسی استفاده از Parallelism، از Dynamic Management Views (DMV) استفاده کنید. یکی از DMVs که می‌تواند اطلاعات مفیدی در این زمینه ارائه دهد، sys.dm_exec_requests است.

دستور برای مشاهده استفاده از Parallelism:

 
SELECT      session_id,     command,     
cpu_time,     total_elapsed_time,     
is_parallel FROM      
sys.dm_exec_requests WHERE      
command = 'SELECT'; 

این دستور به شما نشان می‌دهد که آیا SQL Server از Parallelism برای اجرای کوئری‌ها استفاده کرده است یا خیر.

7. نتیجه‌گیری

• Threshold for Parallelism به SQL Server می‌گوید که وقتی هزینه کوئری بیشتر از مقدار تعیین‌شده باشد، از موازی‌سازی استفاده کند.

• تغییر این مقدار می‌تواند تأثیر زیادی در عملکرد سیستم داشته باشد و استفاده از موازی‌سازی را بهینه کند.

• اگر مقدار Threshold پایین باشد، SQL Server برای کوئری‌های ساده نیز ممکن است از موازی‌سازی استفاده کند که ممکن است باعث مصرف اضافی منابع شود.

• اگر مقدار Threshold بالا باشد، SQL Server تنها برای کوئری‌های پیچیده از موازی‌سازی استفاده خواهد کرد که می‌تواند باعث استفاده بهینه از منابع شود. 

با تنظیم مناسب Threshold for Parallelism می‌توانید عملکرد سیستم را بهینه کرده و از منابع به‌طور بهتری استفاده کنید. در SQL Server، هزینه کوئری (Query Cost) به‌طور خودکار توسط SQL Server محاسبه می‌شود و این هزینه نشان‌دهنده پیچیدگی اجرای کوئری و منابع مورد نیاز برای اجرای آن است. این هزینه معمولاً به‌صورت نسبی و بر اساس موارد مختلف محاسبه می‌شود که می‌توانند شامل CPU، I/O، و حافظه مصرفی برای اجرای کوئری باشند.

چگونه هزینه کوئری محاسبه می‌شود؟

1. محاسبه هزینه توسط Optimizer

SQL Server از یک Query Optimizer استفاده می‌کند که مسئول انتخاب بهترین طرح اجرایی (Execution Plan) برای یک کوئری است. در طول این فرایند، SQL Server هزینه‌های مختلف را برای چندین طرح اجرایی مختلف محاسبه می‌کند و طرحی را که کمترین هزینه را داشته باشد انتخاب می‌کند.

2. عوامل مؤثر در محاسبه هزینه کوئری

هزینه یک کوئری معمولاً به مجموعه‌ای از عوامل بستگی دارد:

• CPU (Central Processing Unit): زمان پردازشی که برای اجرای دستورات کوئری نیاز است.

• I/O (Input/Output): تعداد دسترسی‌های دیسک یا ورودی/خروجی‌هایی که برای خواندن و نوشتن داده‌ها از منابع دیسک انجام می‌شود.

• Memory (حافظه): حافظه مورد نیاز برای اجرای عملیات‌های مختلف کوئری.

3. روش‌های اجرای مختلف (Execution Methods)

SQL Server با توجه به تعداد و نوع عملیات‌هایی که برای اجرای کوئری باید انجام دهد، چندین روش مختلف برای اجرای کوئری در نظر می‌گیرد. برای مثال، برای کوئری‌هایی که از جداول بزرگ استفاده می‌کنند، ممکن است از Table Scan یا Index Scan استفاده شود. این انتخاب‌ها هزینه متفاوتی دارند.

• Table Scan: برای جستجو در کل جدول استفاده می‌شود و معمولاً هزینه بالاتری دارد زیرا تمام داده‌های جدول باید خوانده شوند.

• Index Scan: اگر یک ایندکس وجود داشته باشد، می‌تواند سریع‌تر از Table Scan باشد زیرا فقط بخش‌های مورد نیاز جدول خوانده می‌شود.

• Index Seek: این روش معمولاً سریع‌ترین روش است زیرا فقط داده‌های خاصی از ایندکس خوانده می‌شود و بر اساس کلیدهای ایندکس جستجو انجام می‌شود.

4. استفاده از Cardinality و Statistics

• Cardinality: تعداد ردیف‌هایی که باید پردازش شوند. برای مثال، اگر یک کوئری برای یافتن تمامی کارکنان یک شرکت اجرا می‌شود، باید تعداد کارکنان در آن جدول (Cardinality) را در نظر بگیرد.

• Statistics: SQL Server از آمارهای مربوط به جداول و ایندکس‌ها برای تخمین تعداد ردیف‌ها و توزیع داده‌ها استفاده می‌کند تا تصمیم‌گیری بهتری برای انتخاب طرح اجرایی بهینه داشته باشد.

5. محاسبه هزینه در Execution Plan

در Execution Plan هر گام (Operator) دارای یک هزینه نسبی است که به‌صورت درصدی از کل هزینه کوئری نشان داده می‌شود. این هزینه‌ها معمولاً به‌صورت درصد از کل منابع مصرفی برای اجرای کوئری در نظر گرفته می‌شوند.

مثال محاسبه هزینه کوئری

فرض کنید یک کوئری مانند زیر داریم:

 
SELECT *  FROM Orders  
WHERE CustomerID = 'ALFKI'; 

SQL Server به صورت خودکار هزینه کوئری را به‌طور تقریبی محاسبه می‌کند. این محاسبه به‌طور عمده بر اساس موارد زیر است:

• آیا ایندکسی بر روی ستون CustomerID وجود دارد؟

• تعداد ردیف‌هایی که با CustomerID = 'ALFKI' تطابق دارند چقدر است؟

• آیا SQL Server نیاز به اسکن کامل جدول (Table Scan) دارد یا می‌تواند از ایندکس برای جستجو استفاده کند؟

فرضیات:

• اگر ایندکسی بر روی CustomerID وجود داشته باشد، SQL Server می‌تواند از Index Seek استفاده کند.

• اگر تعداد تطابق‌ها کم باشد (مثلاً فقط یک ردیف برای CustomerID = 'ALFKI' وجود داشته باشد)، هزینه I/O کم خواهد بود.

• اگر ایندکسی وجود نداشته باشد، SQL Server مجبور به استفاده از Table Scan خواهد بود که هزینه بالاتری دارد.

چگونه می‌توان هزینه کوئری را مشاهده کرد؟

برای مشاهده هزینه محاسبه‌شده SQL Server برای یک کوئری، می‌توان از Execution Plan استفاده کرد.

1. مشاهده Execution Plan:

 
SET SHOWPLAN_ALL ON; 
GO  
SELECT *  FROM Orders  
WHERE CustomerID = 'ALFKI';  
GO 
SET SHOWPLAN_ALL OFF; 

این دستور باعث می‌شود که SQL Server Execution Plan را قبل از اجرای کوئری نمایش دهد و در آن هزینه هر گام به‌طور دقیق مشخص شده است.

2. مشاهده Execution Plan گرافیکی:

در SQL Server Management Studio (SSMS)، می‌توانید به راحتی با فشردن Ctrl + M قبل از اجرای کوئری، Execution Plan گرافیکی را مشاهده کنید. این پلان شامل جزئیات مختلفی مانند Index Seek، Table Scan، Join و هزینه نسبی هر کدام از این مراحل است.

3. محاسبه هزینه و بررسی آن در Execution Plan

برای مثال، خروجی یک Execution Plan می‌تواند به‌صورت زیر باشد:

 
|--Table Scan(Estimated Cost = 0.015)    |    
       |--Index Seek(Estimated Cost = 0.005) 

در اینجا، هزینه اجرای Table Scan و Index Seek به‌صورت تقریبی محاسبه شده است و نشان‌دهنده میزان منابع مصرفی هر گام است. SQL Server از این اطلاعات برای تصمیم‌گیری در مورد انتخاب بهترین روش اجرایی استفاده می‌کند. 

هزینه یک کوئری در SQL Server به‌طور خودکار توسط Query Optimizer محاسبه می‌شود و به‌عنوان یک معیار برای انتخاب بهترین طرح اجرایی استفاده می‌شود. این هزینه شامل عواملی مانند زمان پردازش (CPU)، I/O، و حافظه است و به SQL Server کمک می‌کند تا از منابع به‌طور بهینه استفاده کند. شما می‌توانید با مشاهده Execution Plan هزینه‌های دقیق و جزئیات هر گام را مشاهده کنید و در صورت نیاز، با استفاده از ایندکس‌ها و بهینه‌سازی کوئری‌ها، این هزینه‌ها را کاهش دهید.

در SQL Server، وقتی که به هزینه‌ها (Estimated Cost) در Execution Plan نگاه می‌کنیم، باید توجه داشته باشیم که این اعداد به‌طور مستقیم قابل جمع کردن نیستند. در واقع، هزینه‌ها نمایانگر درصد نسبی استفاده از منابع سیستم برای هر عملیات هستند، اما به‌صورت مستقیم جمع نمی‌شوند تا هزینه نهایی یک کوئری را به ما بدهند. این هزینه‌ها بیشتر برای مقایسه بین گزینه‌های مختلف (مانند استفاده از ایندکس یا اسکن کامل جدول) و بهینه‌سازی کوئری به کار می‌روند.

درک هزینه‌ها در Execution Plan:

1. هزینه‌های نسبی: عددی که در کنار هر عملیات در Execution Plan مشاهده می‌کنید، نشان‌دهنده "هزینه نسبی" آن عملیات در مقایسه با کل هزینه کوئری است. این عدد معمولاً به‌صورت نسبی و تقریبی است و میزان استفاده از منابع (مثل CPU، I/O و حافظه) را در همان مرحله از اجرا نشان می‌دهد.

2. مجموع هزینه‌ها: به این نکته توجه داشته باشید که این هزینه‌ها (مثل 0.015 و 0.005 که برای Table Scan و Index Seek آمده است) به‌طور مستقیم جمع نمی‌شوند. به عبارت دیگر، مجموع هزینه‌های عملیات مختلف، به معنی هزینه نهایی کل کوئری نیست.

چرا هزینه‌ها جمع نمی‌شوند؟

در SQL Server، هزینه‌ها نشان‌دهنده درصد تخمینی از کل هزینه اجرا هستند که مربوط به هر عملیات می‌شود. این عدد در حقیقت هزینه نسبی است که به‌صورت تخمینی بیان می‌شود تا بتوان بین چندین روش مختلف انتخابی تصمیم گرفت. این هزینه‌ها نشان‌دهنده "منابع مورد استفاده در عملیات" هستند، نه اینکه "چه مقدار پول یا زمان در عمل صرف خواهد شد".

مثال با توجه به داده‌های شما:

در خروجی که ذکر کرده‌اید، ما دو عملیات داریم:

1. Table Scan با هزینه 0.015

2. Index Seek با هزینه 0.005

این اعداد باید به‌عنوان هزینه نسبی برای هر عملیات در نظر گرفته شوند. این هزینه‌ها به این معنا نیست که برای اجرای کوئری باید هزینه کل 0.015 + 0.005 = 0.02 را بپردازید. بلکه این اعداد نشان‌دهنده این است که:

• عملیات Table Scan در حدود 1.5 درصد از کل هزینه کوئری را به خود اختصاص داده است.

• عملیات Index Seek در حدود 0.5 درصد از کل هزینه کوئری را به خود اختصاص داده است.

هزینه نهایی چطور محاسبه می‌شود؟

در حقیقت، هزینه نهایی کوئری می‌تواند توسط SQL Server در پس‌زمینه محاسبه شود، و آن‌چه در Execution Plan نشان داده می‌شود، تنها تخمین‌های نسبی برای هر عملیات است. SQL Server پس از محاسبه هزینه برای همه عملیات‌های موجود در Execution Plan، تصمیم می‌گیرد که کدام طرح اجرایی را انتخاب کند. هزینه کل برای اجرای کوئری ترکیبی از هزینه‌های نسبی برای همه مراحل است.

نحوه بررسی بیشتر:

برای درک بهتر هزینه‌های اجرایی، می‌توانید از ویژگی‌های مختلفی در SQL Server مانند Actual Execution Plan (که پس از اجرای کوئری به‌دست می‌آید) استفاده کنید. این به شما کمک می‌کند که هزینه‌ها را برای اجرای واقعی و نه فقط تخمین‌ها مشاهده کنید.

نکته مهم:

• هزینه‌ها همیشه به صورت نسبی نمایش داده می‌شوند و در مجموع به‌طور مستقیم جمع نمی‌شوند. هدف این است که به شما کمک کنند تا از منابع بهینه استفاده کنید و تصمیمات بهتری بگیرید.

• هزینه نهایی کوئری زمانی به‌دست می‌آید که SQL Server تصمیم بگیرد که چه طرحی برای اجرای کوئری انتخاب شود، که این تصمیم بر اساس مقایسه هزینه‌ها و منابع مصرفی هر گام از اجرای کوئری است. 

بنابراین، هزینه‌های 0.015 و 0.005 که به‌طور جداگانه برای Table Scan و Index Seek داده شده‌اند، به این معنا نیست که مجموع این‌ها هزینه نهایی کوئری باشد. این اعداد هزینه نسبی آن عملیات‌ها را نشان می‌دهند و باید در زمینه کلی هزینه کل کوئری و منابع مورد استفاده در نظر گرفته شوند.

 
SET STATISTICS IO ON; 
GO  
SELECT * FROM Orders 
WHERE CustomerID = 'ALFKI';  
SET STATISTICS IO OFF; 
GO 

 
SELECT *  FROM Orders 
WHERE CustomerID = 'ALFKI'; 

 
|--Index Seek(Actual Time: 50ms, Logical Reads: 1000, Physical Reads: 50)    |    
     |--Filter(Actual Time: 25ms, Logical Reads: 50, Physical Reads: 10)