انواع read در دسترسی به داده در sql server

 در SQL Server، انواع Read به شیوه‌های مختلفی انجام می‌شود که هرکدام تأثیر متفاوتی بر عملکرد سیستم، میزان قفل‌ها (Locks) و رفتار Concurrency Control دارند. در این مقاله، انواع Read را بررسی کرده و تفاوت‌های آن‌ها را همراه با مثال توضیح می‌دهیم.

1. Logical Read vs. Physical Read

Logical Read و Physical Read به نحوه دسترسی SQL Server به داده‌ها از حافظه و دیسک مربوط می‌شوند:

Logical Read (خواندن منطقی)

• وقتی SQL Server داده‌ها را از Buffer Cache می‌خواند، یک Logical Read اتفاق می‌افتد.

• این خواندن سریع‌تر از Physical Read است، زیرا نیازی به دسترسی به دیسک ندارد.

Physical Read (خواندن فیزیکی)

• زمانی که داده موردنیاز در حافظه (Buffer Pool) موجود نباشد، SQL Server باید آن را از دیسک بخواند.

• این فرآیند کندتر از Logical Read است.

مثال برای بررسی تعداد Logical و Physical Read

SET STATISTICS IO ON; 
SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659; 
SET STATISTICS IO OFF; 

خروجی نمونه:

Table 'SalesOrderDetail'. 
Scan count 1, 
logical reads 5, 
physical reads 1, 
read-ahead reads 0. 

• Logical Reads: 5 صفحه از حافظه خوانده شده است.

• Physical Reads: 1 صفحه از دیسک خوانده شده است.

2. Read Committed vs. Read Uncommitted vs. Repeatable Read vs. Serializable vs. Snapshot

در SQL Server، سطح Isolation Level تعیین می‌کند که چگونه داده‌ها هنگام خواندن از جداول مدیریت می‌شوند.

(1) Read Uncommitted (خواندن بدون تعهد)

• به Dirty Read نیز معروف است.

• داده‌هایی که هنوز Commit نشده‌اند را نیز نمایش می‌دهد.

• کمترین سطح Isolation است و بالاترین سطح Concurrency را ارائه می‌دهد.

مثال:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT * FROM Orders; 

این کوئری ممکن است داده‌های تأییدنشده از یک تراکنش دیگر را بخواند.

مشکل: اگر تراکنش دیگری Rollback شود، داده‌های خوانده‌شده ممکن است نادرست باشند.

(2) Read Committed (خواندن تأییدشده) (پیش‌فرض SQL Server)

• فقط داده‌هایی را می‌خواند که Commit شده‌اند.

• از Dirty Read جلوگیری می‌کند اما Phantom Read ممکن است رخ دهد.

• Lock‌های اشتراکی (Shared Locks) را نگه می‌دارد تا از خواندن داده‌های تأییدنشده جلوگیری کند.

مثال:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 
SELECT * FROM Orders; 

SQL Server فقط داده‌های Commit شده را نشان می‌دهد.

مشکل: تراکنش‌های دیگر ممکن است داده‌ها را تغییر دهند و در اجرای مجدد کوئری، نتایج متفاوتی دریافت شود.

(3) Repeatable Read (خواندن تکرارپذیر)

• از Dirty Read و Non-Repeatable Read جلوگیری می‌کند.

• اگر یک تراکنش داده‌ای را خوانده باشد، دیگر تراکنش‌ها اجازه تغییر آن داده را نخواهند داشت.

• اما همچنان امکان Phantom Read وجود دارد.

مثال:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
BEGIN TRANSACTION; 
SELECT * FROM Orders WHERE CustomerID = 10; 
-- در این مرحله، تراکنش‌های دیگر نمی‌توانند این داده را تغییر دهند. 
COMMIT; 

مشکل: اگر داده جدیدی اضافه شود، تراکنش ممکن است نتواند آن را ببیند (Phantom Read).

(4) Serializable (سخت‌ترین سطح ایزوله‌سازی)

• از Dirty Read، Non-Repeatable Read و Phantom Read جلوگیری می‌کند.

• Lock روی کل جدول اعمال می‌شود.

• بالاترین سطح Consistency اما کمترین Concurrency را دارد.

مثال:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
BEGIN TRANSACTION; 
SELECT * FROM Orders WHERE CustomerID = 10; 
-- هیچ تراکنش دیگری اجازه درج، حذف یا تغییر این داده‌ها را ندارد. 
COMMIT; 

مشکل: به دلیل Lock شدن کل جدول، عملکرد کند می‌شود.

(5) Snapshot (ایزوله‌سازی با استفاده از نسخه‌های قبلی داده)

• داده‌ها را بر اساس یک نسخه پایدار (Snapshot) در زمان اجرای کوئری ارائه می‌دهد.

• از Row Versioning استفاده می‌کند و نیازی به Lock ندارد.

• امکان Dirty Read، Non-Repeatable Read و Phantom Read وجود ندارد.

مثال:

ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON; 
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; 
BEGIN TRANSACTION; 
SELECT * FROM Orders WHERE CustomerID = 10; 
COMMIT; 

در این حالت، داده‌ها از نسخه ذخیره‌شده خوانده می‌شوند و Locking نداریم.

مزایا:

• افزایش Concurrency بدون Lock شدن داده‌ها.

• افزایش کارایی برای سیستم‌هایی با حجم بالای خواندن داده.

معایب:

• مصرف فضای TempDB برای ذخیره نسخه‌های قبلی داده.

3. Read Committed Snapshot (RCSI)

• نسخه‌ای از Read Committed است که از Row Versioning به‌جای Locks استفاده می‌کند.

• باید روی دیتابیس فعال شود:

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON; 

این ویژگی باعث می‌شود که عملیات خواندن بدون قفل انجام شود.

• Logical و Physical Read نشان‌دهنده نحوه دسترسی SQL Server به داده‌ها از حافظه و دیسک هستند.

• Read Uncommitted سریع است اما داده‌های ناپایدار را نشان می‌دهد.

• Read Committed تعادل بین عملکرد و صحت داده را فراهم می‌کند.

• Repeatable Read از تغییر داده‌های خوانده‌شده جلوگیری می‌کند.

• Serializable سخت‌ترین سطح ایزوله‌سازی است اما عملکرد را کند می‌کند.

• Snapshot Isolation بدون Lock کار می‌کند اما فضای TempDB را مصرف می‌کند.

کدام Isolation Level را انتخاب کنیم؟

فرمول‌های مرتبط با خواندن داده‌ها در SQL Server

برای بررسی عملکرد خواندن داده‌ها در SQL Server، می‌توان از برخی فرمول‌های عمومی که SQL Server در Query Optimization استفاده می‌کند بهره برد. این فرمول‌ها به SQL Server کمک می‌کنند تا هزینه تخمینی یک Query را محاسبه کند.

1. محاسبه هزینه I/O Reads

وقتی یک Query اجرا می‌شود، SQL Server برای خواندن داده‌ها از دیسک یا حافظه، میزان I/O مصرفی را محاسبه می‌کند.

$$\text{<span style="line-height: 210\%;font-size: x-large;">Total Reads</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\text{<span style="line-height: 210\%;font-size: x-large;">Logical Reads</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span>\text{<span style="line-height: 210\%;font-size: x-large;">Physical Reads</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span>\text{<span style="line-height: 210\%;font-size: x-large;">Read-Ahead Reads</span>}$$

• Logical Reads: تعداد صفحات خوانده‌شده از Buffer Pool (حافظه).

• Physical Reads: تعداد صفحات خوانده‌شده از دیسک چون در Buffer Pool موجود نبودند.

• Read-Ahead Reads: صفحات خوانده‌شده از دیسک که به احتمال زیاد در آینده موردنیاز خواهند بود.

🔹 مثال محاسبه I/O Reads در SQL Server

SET STATISTICS IO ON; 
SELECT * FROM Orders WHERE CustomerID = 5; 
SET STATISTICS IO OFF; 

خروجی:

Table 'Orders'. 
Scan count 1, 
logical reads 20, 
physical reads 5, 
read-ahead reads 10. 

$$\text{<span style="line-height: 210\%;font-size: x-large;">Total Reads</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">20</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">5</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">10</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">35</span>}$$

2. هزینه خواندن یک صفحه از دیسک (Disk Read Cost)

SQL Server برای محاسبه هزینه خواندن یک صفحه از دیسک از مدل هزینه‌ای زیر استفاده می‌کند:

$$\text{<span style="line-height: 210\%;font-size: x-large;">Disk Read Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\text{<span style="line-height: 210\%;font-size: x-large;">Number of Pages Read</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">\times </span>\text{<span style="line-height: 210\%;font-size: x-large;">Disk Seek Time</span>}$$

• Number of Pages Read: تعداد صفحات خوانده‌شده (در SQL Server هر صفحه ۸KB است).

• Disk Seek Time: میانگین زمان دسترسی به دیسک (به میلی‌ثانیه).

💡 اگر فرض کنیم که 1000 صفحه از دیسک خوانده شود و میانگین زمان دسترسی دیسک 4 میلی‌ثانیه باشد:

$$\text{<span style="line-height: 210\%;font-size: x-large;">Disk Read Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">1000</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">\times </span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.004</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">4</span>}\text{<span style="line-height: 210\%;font-size: x-large;"> ms</span>}$$

3. محاسبه Cost Estimate (هزینه تخمینی یک Query)

SQL Server از یک مدل هزینه‌ای برای تخمین Execution Plan استفاده می‌کند که شامل هزینه‌های CPU و I/O است:

$$\text{<span style="line-height: 210\%;font-size: x-large;">Total Query Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">(</span>\text{<span style="line-height: 210\%;font-size: x-large;">I/O Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">\times </span>\text{<span style="line-height: 210\%;font-size: x-large;">I/O Weight</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">)</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">(</span>\text{<span style="line-height: 210\%;font-size: x-large;">CPU Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">\times </span>\text{<span style="line-height: 210\%;font-size: x-large;">CPU Weight</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">)</span>$$

• I/O Cost: هزینه خواندن از دیسک.

• CPU Cost: هزینه پردازش داده در CPU.

• I/O Weight & CPU Weight: ضرایب وزن‌دهی که توسط SQL Server تنظیم می‌شوند.

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

SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659; 

اگر SQL Server هزینه‌ها را این‌گونه محاسبه کند:

$$\text{<span style="line-height: 210\%;font-size: x-large;">I/O Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.015</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">,</span>\text{<span style="line-height: 210\%;font-size: x-large;">CPU Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.005</span>}$$

و فرض کنیم وزن CPU و I/O به ترتیب 0.7 و 0.3 باشند:

$$\text{<span style="line-height: 210\%;font-size: x-large;">Total Query Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">(</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.015</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">\times </span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.3</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">)</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">(</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.005</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">\times </span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.7</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">)</span><span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.0045</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">+</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.0035</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">=</span>\mathrm{<span\; style="line-height:\; 210\%;font-size:\; x-large;">0.008</span>}$$

4. محاسبه Threshold برای Parallelism

SQL Server برای تصمیم‌گیری در مورد Parallel Execution از مقدار Cost Threshold for Parallelism استفاده می‌کند:

$$\text{<span style="line-height: 210\%;font-size: x-large;">Query Cost</span>}<span\; style="line-height:\; 210\%;font-size:\; x-large;">></span>\text{<span style="line-height: 210\%;font-size: x-large;">Cost Threshold for Parallelism</span>}$$

• اگر هزینه تخمینی Query از مقدار Threshold بیشتر باشد، SQL Server آن را به‌صورت Parallel اجرا می‌کند.

• مقدار پیش‌فرض Threshold در SQL Server 5 است اما می‌توان آن را تغییر داد:

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

🔹 مثال:
اگر هزینه تخمینی کوئری 30 باشد و Threshold = 50 باشد، کوئری Parallel اجرا نمی‌شود.
اما اگر مقدار Threshold = 25 تنظیم شود، کوئری به‌صورت Parallel اجرا می‌شود.

• SQL Server برای خواندن داده‌ها از Buffer Cache (Logical Read) و دیسک (Physical Read) استفاده می‌کند.

• هزینه Query Cost بر اساس مدل ترکیبی از I/O و CPU محاسبه می‌شود.

• مقدار Cost Threshold for Parallelism تعیین می‌کند که آیا SQL Server یک Query را به‌صورت Parallel Execution اجرا کند یا نه.

• با بررسی Execution Plan و Statistics IO می‌توان هزینه واقعی خواندن داده‌ها را به دست آورد.

5. منابع معتبر (فقط کتاب‌ها)

📚 1. "SQL Server Query Performance Tuning" - Grant Fritchey
📚 2. "Inside Microsoft SQL Server" - Kalen Delaney
📚 3. "SQL Server 2019 Administration Inside Out" - Randolph, Blinch, Massengill
📚 4. "High-Performance SQL Server" - Benjamin Nevarez