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

انواع Read در SQL Server

توسط admin | گروه SQL Server | 1404/01/15

در 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 را انتخاب کنیم؟

Isolation Level	مزایا	معایب	مناسب برای
Read Uncommitted	سریع، بدون قفل	خواندن داده‌های ناپایدار	گزارش‌گیری سریع با کمترین تأثیر بر سرور
Read Committed	تعادل بین صحت داده و عملکرد	احتمال تغییر داده در حین خواندن	بیشتر سناریوهای عملیاتی
Repeatable Read	جلوگیری از تغییر داده‌های خوانده‌شده	افزایش Lock و کاهش همزمانی	تراکنش‌هایی که نباید داده‌های خوانده‌شده تغییر کنند
Serializable	دقیق‌ترین نتایج	کاهش عملکرد به دلیل Lock	عملیات‌های حساس به داده مثل حسابداری
Snapshot	خواندن بدون قفل، افزایش همزمانی	افزایش مصرف TempDB	سیستم‌های OLTP با خواندن زیاد

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

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

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

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

$Total Reads = Logical Reads + Physical Reads + Read-Ahead Reads$

$Total Reads = Logical Reads + Physical Reads + Read-Ahead Reads$

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.

$Total Reads = 20 + 5 + 10 = 35$

$Total Reads = 20 + 5 + 10 = 35$

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

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

$Disk Read Cost = Number of Pages Read \times Disk Seek Time$

$Disk Read Cost = Number of Pages Read \times Disk Seek Time$

Number of Pages Read: تعداد صفحات خوانده‌شده (در SQL Server هر صفحه ۸KB است).
Disk Seek Time: میانگین زمان دسترسی به دیسک (به میلی‌ثانیه).

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

$Disk Read Cost = 1000 \times 0.004 = 4 ms$

$Disk Read Cost = 1000 \times 0.004 = 4 ms$

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

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

$Total Query Cost = (I/O Cost \times I/O Weight) + (CPU Cost \times CPU Weight)$

$Total Query Cost = (I/O Cost \times I/O Weight) + (CPU Cost \times CPU Weight)$

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

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


SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659;

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

$I/O Cost = 0.015, CPU Cost = 0.005$

$I/O Cost = 0.015, CPU Cost = 0.005$

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

$Total Query Cost = (0.015 \times 0.3) + (0.005 \times 0.7) = 0.0045 + 0.0035 = 0.008$

$Total Query Cost = (0.015 \times 0.3) + (0.005 \times 0.7) = 0.0045 + 0.0035 = 0.008$

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

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

$Query Cost > Cost Threshold for Parallelism$

$Query Cost > Cost Threshold for Parallelism$

اگر هزینه تخمینی 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

برچسبها : ACID Properties Blocking in SQL Server Buffer Pool Concurrency Control Concurrency in Databases Data Access Data Consistency Database Design Database Development Database Reads Database Tuning Deadlock Prevention Dirty Read Disk Access Index Usage Isolation Level Comparison Locking in SQL Logical Read Memory Access MVCC in SQL Server Nonrepeatable Read Phantom Read Physical Read Query Execution Plan Query Optimization Read Committed Read Isolation Levels Read Types in SQL Server Read Uncommitted Repeatable Read Serializable Snapshot Isolation SQL SQL Best Practices SQL Debugging SQL I/O Operations SQL Lock Granularity SQL Memory Management SQL Monitoring Tools SQL Performance SQL Query Performance SQL Server SQL Server Internals SQL Server Locks SQL Server Pages SQL Server Performance SQL Server Snapshot SQL Server Tools SQL Server Training TempDB Usage Transaction Control Transaction Isolation Levels Transactional Systems T-SQL Tips آموزش SQL آموزش SQL Server حل تمرین SQL

مقاله های مرتبط

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر

اطلاعات تماس

آدرس:اصفهان-خیابان ام کلثوم غربی - بعد خیابان تخم چی - بیست متر بعد از پیتزا ننه شب - کوچه تعمیر گاه سمار زغالی - پلاک 354 - درب مشکی - طبقه هفتم
آدرس ایمیل:najafzade@gmail.com
وب سایت:http://www.a00b.com/
تلفن ثابت:(+98)9131253620
تلفن همراه:09131253620

سایر لینکها

آخرین مقاله ها

گروه های خبری