۱۴۰۴/۰۷/۱۳ Nebular

بهینه‌سازی یک Stored Procedure

تحلیل عملکرد فعلی (Execution Plan & Statistics)

اول از همه باید بدونم دقیقاً مشکل از کجاست.
برای این کار:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

و بعد:

EXEC MyProcedure;
  • با این کار می‌فهمم هر query چقدر I/O، زمان CPU و Logical Read داره.
  • بعد با اجرای Execution Plan (Ctrl+M در SSMS) بررسی می‌کنم:
    • کدوم بخش بیشترین Cost داره (مثلاً Table Scan یا Nested Loop).
    • آیا از Index Seek استفاده میشه یا Table Scan (نشونه‌ی کمبود ایندکس).

۱. Execution Plan — «نقشه‌ی اجرای Query»

🔹 SQL Server وقتی یه query می‌نویسی، خودش تصمیم می‌گیره چطور اون query رو اجرا کنه:
از کدوم جدول شروع کنه، از چه نوع Join استفاده کنه، کدوم ایندکس‌ها رو بخونه، و غیره.
این تصمیمات در قالب یک Execution Plan (طرح اجرا) نمایش داده می‌شن.

📊 دو نوع Execution Plan داریم:

  1. Estimated Execution Plan
    فقط نشون میده SQL Server فکر می‌کنه چطور query رو اجرا کنه، بدون اجرای واقعی.
    • در SSMS: Ctrl + L یا: SET SHOWPLAN_ALL ON;
  2. Actual Execution Plan
    وقتی query واقعاً اجرا میشه، SQL Server اطلاعات واقعی مثل تعداد رکوردهای خوانده‌شده، زمان، I/O و … رو نشون میده.
    • در SSMS: Ctrl + M بعد query رو اجرا کن.

🧠 با Execution Plan چه چیزهایی می‌فهمیم؟

  • SQL Server از Index استفاده کرده یا نه؟
    اگر “Index Scan” یا “Table Scan” دیدی یعنی احتمالاً جای بهینه‌سازی داری.
  • آیا Join درست انجام شده؟ (Nested Loop, Merge Join, Hash Join)
  • آیا بخشی از query بیشترین هزینه (Cost) رو داره؟
  • آیا Sort یا Key Lookup غیرضروری وجود داره؟

📍 یعنی Execution Plan بهت نشون میده کجای query سنگینه.

⚙️ ۲. Statistics — «آمار و تخمین داده‌ها»

Statistics اطلاعات آماری درباره‌ی توزیع داده‌ها در ستون‌ها هستن.
SQL Server ازش برای تصمیم‌گیری در Execution Plan استفاده می‌کنه.

مثلاً فرض کن یه جدول داری با ۱ میلیون ردیف، ولی در ستون City فقط ۵ تا مقدار مختلف وجود داره.
Statistics به SQL Server کمک می‌کنه بفهمه اگه بنویسی:

WHERE City = 'Paris'

چند تا ردیف تقریباً برمی‌گرده.
بر اساس همین تخمین تصمیم می‌گیره از Index Seek استفاده کنه یا Table Scan.

📈 چطور Statistics رو بررسی کنیم:

DBCC SHOW_STATISTICS ('TableName', 'IndexName');

این دستور بهت نشون میده:

  • آخرین باری که statistics به‌روزرسانی شده.
  • توزیع داده‌ها (Histogram).
  • تخمین ردیف‌ها.

⚠️ اگر Statistics قدیمی بشن:

SQL Server تخمین اشتباه می‌زنه → Execution Plan اشتباه می‌سازه → Query کند میشه.
برای همین باید هر از گاهی به‌روزشون کنیم:

UPDATE STATISTICS TableName;

یا سراسری:

EXEC sp_updatestats;

📌 جمع‌بندی کوتاه برای مصاحبه:

Execution Plan نقشه‌ی نحوه‌ی اجرای Query است،
و Statistics آمار توزیع داده‌هاست که SQL Server ازش برای ساختن اون نقشه استفاده می‌کنه.
اگر statistics دقیق نباشه، execution plan بهینه نیست و query کند اجرا میشه.

Table Scan — «خواندن کل جدول»

  • وقتی SQL Server کل جدول رو می‌خونه تا ردیف‌های موردنظر پیدا کنه، بهش میگن Table Scan.
  • این اتفاق معمولاً وقتی رخ میده که:
    1. ستون مورد جستجو ایندکس ندارد
    2. یا query خیلی پیچیده است و optimizer تصمیم می‌گیرد کل جدول را بخواند

مثال ساده:

SELECT * FROM Customers WHERE City = 'Paris';
  • اگر ستون City ایندکس نداشته باشه، SQL Server باید تمام ردیف‌های جدول Customers رو بخونه تا Paris ها رو پیدا کنه → Table Scan

مشکل Table Scan:

  • با بزرگ شدن جدول، بسیار کند میشه
  • CPU و I/O زیادی مصرف می‌کنه

🧩 Index Seek — «جستجوی هوشمند با ایندکس»

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

مثال:
فرض کن روی ستون City ایندکس داری:

CREATE INDEX IX_Customers_City ON Customers(City);

حالا همین query:

SELECT * FROM Customers WHERE City = 'Paris';
  • SQL Server فقط قسمتی از جدول که City=’Paris’ هست رو می‌خونه → Index Seek
  • CPU و I/O بسیار کمتر مصرف میشه

⚖️ تفاوت کلیدی:

ویژگیTable ScanIndex Seek
چه چیزی می‌خونهکل جدولفقط ردیف‌های موردنیاز با کمک ایندکس
سرعتکند (خصوصاً جدول بزرگ)سریع
استفاده CPU / I/Oزیادکم
چه زمانی رخ میدهبدون ایندکس، یا query پیچیدهوقتی ایندکس روی ستون فیلتر/Join وجود داره

💡 نکته مصاحبه‌ای

اگر سوال پرسیدن: “فرق Table Scan و Index Seek چیه؟”
می‌تونی کوتاه بگی:

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

2️⃣ بررسی Query‌ها و Join‌ها

داخل Stored Procedure می‌رم و:

  • مطمئن می‌شم که فیلترها روی ستون‌های ایندکس‌دار هستن.
  • از SELECT * استفاده نشده (فقط ستون‌های لازم).
  • در Joinها، نوع Join مناسب انتخاب شده (INNER, LEFT, CROSS).
  • در صورت امکان از CTE یا Temp Table موقت برای شکستن queryهای پیچیده استفاده می‌کنم تا Optimizer راحت‌تر کار کنه.

🧩 مفهوم Temp Table

Temp Table یک جدول موقت است که فقط برای session فعلی وجود دارد و بعد از پایان session یا پایان Stored Procedure حذف می‌شود.

  • نام آن با # شروع می‌شود: #TempCustomers
  • SQL Server می‌تواند روی آن ایندکس، constraint و statistics بسازد.
  • برای ذخیره نتایج میانی queryهای پیچیده و جلوگیری از محاسبات تکراری کاربرد دارد.

⚡ چرا استفاده از Temp Table باعث بهبود Performance می‌شود

  1. شکستن query پیچیده
    • اگر query شامل چند Join و Aggregate است، SQL Server گاهی Execution Plan بهینه نمی‌سازد.
    • با ذخیره نتایج میانی در Temp Table، می‌توان query را به چند مرحله ساده تقسیم کرد.
  2. کاهش محاسبات تکراری
    • وقتی یک Subquery یا CTE چند بار استفاده می‌شود، Temp Table یک بار محاسبه می‌شود و بار بعدی فقط از آن خوانده می‌شود.
  3. ایجاد Index روی داده میانی
    • می‌توان روی Temp Table ایندکس ساخت و Joinها و Filterهای بعدی سریع‌تر اجرا شوند.
  4. کم کردن Lock و Blocking
    • وقتی داده‌های میانی را در Temp Table ذخیره می‌کنیم، دسترسی به جدول اصلی کمتر می‌شود → کمتر lock می‌خورد.

🛠 مثال عملی

فرض کن یک query پیچیده داریم:

SELECT c.CustomerID, c.Name, SUM(o.Amount) as Total
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate > '2025-01-01'
GROUP BY c.CustomerID, c.Name
HAVING SUM(o.Amount) > 1000
ORDER BY Total DESC;

💡 استفاده از Temp Table

-- 1. ایجاد Temp Table برای ذخیره نتایج میانی
SELECT c.CustomerID, c.Name, o.Amount
INTO #TempOrders
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate > '2025-01-01';

-- 2. ایجاد Index روی Temp Table
CREATE INDEX IX_TempOrders_CustomerID ON #TempOrders(CustomerID);

-- 3. انجام Aggregate روی Temp Table
SELECT CustomerID, Name, SUM(Amount) as Total
FROM #TempOrders
GROUP BY CustomerID, Name
HAVING SUM(Amount) > 1000
ORDER BY Total DESC;

-- 4. Temp Table به صورت خودکار بعد از پایان session حذف می‌شود

🔹 نکات مهم:

  • Query بزرگ → چند مرحله: ابتدا داده میانی را فیلتر کن، سپس Aggregate و Joinهای بعدی را انجام بده.
  • Index روی Temp Table: اگر قرار است join یا filter روی آن انجام شود، حتماً ایندکس بساز.
  • استفاده منطقی: Temp Table روی داده‌های خیلی کوچک ممکن است overhead اضافه کند، فقط برای داده‌های بزرگ کاربردی است.

✅ جمع‌بندی

  • Temp Table = حافظه موقت + قابلیت Index + تفکیک مراحل Query
  • بهترین کاربرد:
    • Joinهای سنگین
    • Aggregate روی داده بزرگ
    • چند بار استفاده از نتایج میانی
  • تاثیر: کاهش I/O، CPU، Lock و سرعت اجرای Stored Procedure افزایش می‌یابد.

🧩 مفهوم CTE

CTE یک query موقت و نام‌دار است که می‌توان آن را فقط در همان query بعد از تعریفش استفاده کرد.

مثال ساده:

WITH RecentOrders AS (
    SELECT CustomerID, Amount
    FROM Orders
    WHERE OrderDate > '2025-01-01'
)
SELECT CustomerID, SUM(Amount) AS Total
FROM RecentOrders
GROUP BY CustomerID;
  • RecentOrders یک view موقت است که فقط برای همین query وجود دارد.
  • بعد از اجرای query، به صورت خودکار حذف می‌شود.

⚡ کاربرد CTE در بهبود Performance

  1. خوانایی و نگهداری کد
    • CTE باعث می‌شود queryهای پیچیده بخش‌بندی شوند و بهتر دیده شوند.
    • به optimizer کمک می‌کند Execution Plan را بهتر بسازد، مخصوصاً وقتی query طولانی است.
  2. جایگزین Subqueryهای تکراری
    • به جای نوشتن Subquery چندبار، می‌توان یکبار با CTE تعریف کرد و در query اصلی استفاده کرد.
  3. استفاده در Recursive Query
    • اگر بخواهی داده‌های سلسله‌مراتبی (مثل سازمان، دسته‌بندی‌ها) را بخوانی، CTE بسیار بهینه و ساده است.

⚠️ نکته مهم Performance

  • CTE خود به خود Index ندارد
    • برخلاف Temp Table، نمی‌توان روی آن ایندکس ساخت.
    • بنابراین اگر داده بزرگ باشد و نیاز به Join یا Aggregate سنگین داشته باشی، Temp Table سریع‌تر است.
  • Execution Plan CTE
    • SQL Server CTE را به عنوان inline view در Execution Plan می‌بیند.
    • گاهی اگر query پیچیده باشد، ممکن است باعث Nested Loop یا Table Scan شود.

🛠 مثال عملی

فرض کن query پیچیده داریم:

SELECT c.CustomerID, c.Name, SUM(o.Amount) AS Total
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate > '2025-01-01'
GROUP BY c.CustomerID, c.Name
HAVING SUM(o.Amount) > 1000;

💡 استفاده از CTE

WITH FilteredOrders AS (
    SELECT CustomerID, Amount
    FROM Orders
    WHERE OrderDate > '2025-01-01'
)
SELECT c.CustomerID, c.Name, SUM(f.Amount) AS Total
FROM Customers c
JOIN FilteredOrders f ON c.CustomerID = f.CustomerID
GROUP BY c.CustomerID, c.Name
HAVING SUM(f.Amount) > 1000;

✅ نتیجه:

  • query خواناتر شد
  • اگر query خیلی پیچیده باشد، optimizer راحت‌تر Plan می‌سازد
  • اگر داده‌ها خیلی زیاد باشند و چندین Join/aggregate داشته باشیم، می‌توان CTE را با Temp Table ترکیب کرد:
    1. ابتدا CTE برای خوانایی و فیلتر
    2. بعد ذخیره نتایج در Temp Table برای ایجاد Index و کاهش I/O

🔹 جمع‌بندی:

ویژگیCTETemp Table
مدت زمان وجودفقط همان queryتا پایان session / SP
قابلیت Indexندارددارد
مناسبخوانایی، recursive، کاهش Subqueryداده بزرگ، join/aggregate سنگین، استفاده چندباره
تاثیری بر Performanceبهتر می‌کنه readability و Planمی‌تونه سرعت واقعی را بالا ببرد

3️⃣ بررسی Indexها

  • برای ستون‌هایی که در WHERE, JOIN, ORDER BY زیاد استفاده شدن، Index می‌سازم.
  • ولی دقت می‌کنم Index زیاد باعث کندی در INSERT/UPDATE/DELETE نشه.
  • اگر ایندکس ساخته شده ولی کار نمی‌کنه، با:
DBCC SHOW_STATISTICS ('TableName', 'IndexName');

بررسی می‌کنم که statistics به‌روز هست یا نه.
در صورت نیاز:

UPDATE STATISTICS TableName;

4️⃣ بررسی Parameter Sniffing

گاهی Stored Procedure برای اولین پارامتر اجرا، Execution Plan می‌سازه و همونو برای بقیه نگه می‌داره که ممکنه باعث افت عملکرد بشه.
در این صورت:

  • از OPTION (RECOMPILE) یا
  • متغیرهای محلی داخل procedure استفاده می‌کنم تا plan مخصوص هر بار ساخته بشه.

🧩 ۱. مفهوم Parameter Sniffing

  • وقتی یک Stored Procedure با پارامتر فراخوانی می‌شود، SQL Server در اولین اجرا، Execution Plan می‌سازد.
  • برای این Plan، SQL Server مقدار واقعی پارامتر را “Sniff” می‌کند و بر اساس آن تصمیم می‌گیرد که از کدام ایندکس، Join type، Scan یا Seek استفاده کند.

🔹 مثال:
فرض کن یک Stored Procedure داریم:

CREATE PROCEDURE GetOrders
    @CustomerID INT
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

وقتی بار اول این SP با @CustomerID = 1 اجرا شود، SQL Server بر اساس چند ردیف که CustomerID = 1 دارند، Execution Plan می‌سازد.

🧩 ۲. مشکل Parameter Sniffing

  • اگر بار دوم SP با @CustomerID = 500000 اجرا شود (مثلاً داده‌های زیادی وجود دارد)،
    همان Execution Plan قبلی دوباره استفاده می‌شود.

🔴 نتیجه:

  • Execution Plan مناسب CustomerID=1 نیست → query بسیار کند می‌شود.
  • این مشکل را Parameter Sniffing می‌نامند.

به زبان ساده: SQL Server “اولین مقدار پارامتر” را sniff می‌کند و Plan خودش را بر اساس آن می‌سازد.

🛠 ۳. چگونه تشخیص داده می‌شود؟

  1. SP کند اجرا می‌شود اما داده‌ها درست هستند
  2. مشاهده Execution Plan نشان می‌دهد:
    • Table Scan یا Nested Loop غیر بهینه
    • تعداد ردیف‌ها بسیار بیشتر از تخمین (Estimated Number of Rows) است

🧩 ۴. روش‌های رفع مشکل Parameter Sniffing

روش ۱: OPTION (RECOMPILE)

  • SQL Server هر بار که SP اجرا می‌شود، یک Plan جدید می‌سازد بر اساس مقدار پارامتر فعلی.
CREATE PROCEDURE GetOrders
    @CustomerID INT
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);
END;

✅ مزایا: همیشه Plan مناسب اجرا می‌شود
⚠️ معایب: هزینه CPU کمی بالاتر است، چون هر بار Plan ساخته می‌شود

روش ۲: متغیر محلی داخل SP

  • مقدار پارامتر را داخل یک متغیر محلی بریز و از آن استفاده کن تا SQL Server Stats پارامتر اصلی را نبیند و یک Plan کلی بسازد:
CREATE PROCEDURE GetOrders
    @CustomerID INT
AS
BEGIN
    DECLARE @LocalCustomerID INT = @CustomerID;

    SELECT *
    FROM Orders
    WHERE CustomerID = @LocalCustomerID;
END;

✅ مزایا: Plan به صورت “generic” ساخته می‌شود
⚠️ معایب: برای برخی مقادیر خاص بهینه نیست

روش ۳: OPTIMIZE FOR

  • به SQL Server بگو برای چه مقدار پارامتری Plan بسازد:
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 1));
  • یا:
OPTION (OPTIMIZE FOR UNKNOWN); -- برای Plan عمومی

✅ مزایا: کنترل روی Plan و بهینه‌سازی
⚠️ معایب: اگر مقدار واقعی خیلی متفاوت باشد، باز هم ممکن است Plan مناسب نباشد

🔹 نکات مهم برای مصاحبه

  1. تعریف کوتاه:

Parameter Sniffing زمانی رخ می‌دهد که SQL Server برای ساخت Execution Plan، اولین مقدار پارامتر را بررسی (sniff) می‌کند و همان Plan را برای مقادیر بعدی استفاده می‌کند.

  1. چرا مشکل‌ساز است:

اگر مقدارهای بعدی خیلی متفاوت باشند، Execution Plan غیر بهینه می‌شود → Query کند اجرا می‌شود.

  1. راه حل‌ها:
  • OPTION (RECOMPILE)
  • متغیر محلی داخل SP
  • OPTIMIZE FOR / OPTIMIZE FOR UNKNOWN
  1. بهترین پاسخ مصاحبه‌ای:

“اول Execution Plan را بررسی می‌کنم و اگر مشکل Parameter Sniffing وجود داشت، با روش‌های Recompile یا متغیر محلی یا OPTIMIZE FOR Plan مناسب برای پارامترهای مختلف می‌سازم تا Performance بالا برود.”

5️⃣ استفاده از Temp Table در مواقع لازم

اگر query خیلی پیچیده باشه یا join روی چند جدول سنگین داریم، گاهی ذخیره موقت نتایج در #TempTable کمک می‌کنه SQL Engine بهتر plan بسازه.

6️⃣ کاهش قفل‌ها (Locking)

اگر procedure زیاد اجرا میشه (مثلاً در گزارش‌ها)، از حالت فقط خواندنی استفاده می‌کنم:

WITH (NOLOCK)

یا isolation level مناسب:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

(البته فقط وقتی داده‌ها دقیقاً consistent بودنش حیاتی نیست.)

🧩 ۱. مفهوم Lock در SQL Server

  • وقتی SQL Server یک query اجرا می‌کند، برای جلوگیری از تداخل بین چند کاربر یا transaction، روی داده‌ها Lock می‌گذارد.
  • انواع Lock ها:
    • Shared Lock (S): وقتی فقط می‌خوای دیتا رو بخونی.
    • Exclusive Lock (X): وقتی می‌خوای دیتا رو تغییر بدی (Insert, Update, Delete).
    • Update Lock (U): ترکیبی، برای جلوگیری از deadlock در زمان update.
  • Lock ها روی صفحه (Page)، سطر (Row) یا جدول (Table) اعمال می‌شوند.

⚠️ مشکل Locking و Blocking

فرض کن ۲ کاربر همزمان از جدول Orders استفاده می‌کنند:

  1. کاربر A در حال Update روی رکورد 100 است → Exclusive Lock روی آن رکورد
  2. کاربر B می‌خواهد همان رکورد را بخواند → منتظر می‌ماند تا Lock آزاد شود

🔴 نتیجه:

  • Blocking: کاربر دوم باید صبر کند
  • Deadlock: اگر دو کاربر منتظر Locks همدیگر باشند، خطا می‌دهد
  • Performance کاهش پیدا می‌کند

⚡ ۲. روش‌های کاهش Lock و افزایش Performance

روش ۱: Read Uncommitted / NOLOCK

  • وقتی داده‌ها را فقط برای خواندن می‌خوای، می‌توانی بگی:
SELECT * FROM Orders WITH (NOLOCK)
WHERE OrderDate > '2025-01-01';
  • SQL Server دیگر Shared Lock نمی‌گذارد و دیگر کاربران بلاک نمی‌شوند
  • این تکنیک Dirty Read دارد (ممکن است داده‌ای که هنوز commit نشده را بخوانی)
  • مناسب گزارش‌ها و queryهای خواندنی است.

روش 2: SET TRANSACTION ISOLATION LEVEL

  • با تغییر Isolation Level می‌توان رفتار Locks را کنترل کرد:
Isolation Levelتوضیح
READ UNCOMMITTEDهیچ Lock خواندن نمی‌گیرد (مثل NOLOCK)
READ COMMITTED (پیش‌فرض)فقط Shared Lock روی خواندن اعمال می‌شود
REPEATABLE READShared Lock تا پایان تراکنش نگه داشته می‌شود
SERIALIZABLEسختگیرانه‌ترین، همه رکوردها و بازه‌ها Lock می‌شوند

مثال برای Read Uncommitted:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Orders;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- بازگشت به حالت پیش‌فرض

روش ۳: شکستن Transaction های طولانی

  • هر چه تراکنش طولانی‌تر باشد، Lockها مدت بیشتری نگه داشته می‌شوند → Blocking بیشتر
  • بنابراین:
    • Queryهای بزرگ را به چند query کوچک تقسیم کن
    • تغییرات غیر ضروری در یک تراکنش را خارج کن

روش ۴: استفاده از Snapshot Isolation

  • SQL Server نسخه‌های جدید امکان Snapshot Isolation دارند:
ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;
  • کاربر می‌تواند بدون انتظار برای Locks، داده‌های consistent (نسخه‌ای از داده) را بخواند
  • مزیت: Blocking کاهش می‌یابد، خطای Dirty Read ندارد

🔹 نکات مهم برای Performance و مصاحبه

  1. Lock ها برای جلوگیری از تداخل داده ضروری هستند، اما باید کنترل شوند.
  2. برای گزارش‌ها و queryهای خواندنی سنگین:
    • NOLOCK یا Read Uncommitted می‌تواند مفید باشد
    • Snapshot Isolation هم گزینه امن‌تری است
  3. تراکنش طولانی → Lock طولانی → کاهش Performance
  4. همیشه بررسی کن کدام جدول یا query بیشترین Blocking را ایجاد می‌کند:
-- نمایش session هایی که در حال انتظار Lock هستند
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

💡 جمع‌بندی مصاحبه‌ای کوتاه:

برای کاهش Lock و بهبود Performance، از NOLOCK/Read Uncommitted برای خواندن داده‌های گزارش‌محور، تراکنش‌های کوتاه، و در صورت نیاز Snapshot Isolation استفاده می‌کنم تا Blocking و Deadlock کاهش یابد.

وقتی یک کاربر داره رکوردی رو Update می‌کنه، کاربر دوم چطور ممکنه منتظر بمونه (Block بشه)
و چطور می‌تونی با NOLOCK یا Snapshot جلویش رو بگیری.

🧩 مرحله ۱ — کاربر اول (Session 1)

BEGIN TRANSACTION;

UPDATE Orders
SET OrderStatus = 'Processed'
WHERE OrderID = 1001;

-- تراکنش رو عمداً باز نگه می‌داریم
-- هنوز COMMIT نکردیم، پس رکورد قفل شده (Exclusive Lock)

✅ الان رکورد OrderID = 1001 قفل شده (Lock X)
تا وقتی COMMIT یا ROLLBACK نکردی، هر کاربر دیگه‌ای که بخواد اون رکورد رو بخونه یا تغییر بده باید منتظر بمونه.

🧩 مرحله ۲ — کاربر دوم (Session 2)

SELECT * FROM Orders WHERE OrderID = 1001;

🔴 نتیجه:
این query منتظر می‌مونه (block میشه) چون رکورد قفل شده.
SQL Server نمی‌تونه داده‌ی در حال تغییر رو بخونه تا وقتی تراکنش اول تموم بشه.

⚡ حالا کاهش Lock — راه‌حل ۱: استفاده از NOLOCK

SELECT * FROM Orders WITH (NOLOCK)
WHERE OrderID = 1001;

✅ نتیجه:

  • Query فوراً اجرا میشه
  • رکورد رو می‌خونه حتی اگه تراکنش هنوز Commit نشده باشه
    ⚠️ اما ممکنه داده‌ای رو بخونه که بعداً Rollback بشه (بهش می‌گن Dirty Read)

⚡ راه‌حل ۲: استفاده از Snapshot Isolation (روش امن‌تر)

اول باید فعالش کنی (فقط یک بار):

ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;

بعد در Session 2 می‌گی:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;
SELECT * FROM Orders WHERE OrderID = 1001;
COMMIT;

✅ نتیجه:

  • SQL Server نسخه‌ی قدیمی و معتبر (Versioned Data) رکورد رو از tempdb می‌خونه
  • نه بلاک میشه
  • نه Dirty Read داره
  • داده‌ای که کاربر اول هنوز commit نکرده، دیده نمی‌شه

💡 جمع‌بندی برای پاسخ مصاحبه:

در SQL Server وقتی تراکنش‌ها روی رکوردها Lock می‌گیرند، queryهای دیگر ممکن است block شوند.
برای کاهش Lock و افزایش Performance:

  • از WITH (NOLOCK) یا Isolation Level READ UNCOMMITTED برای گزارش‌ها استفاده می‌کنم.
  • در سناریوهای حساس، از SNAPSHOT ISOLATION برای خواندن داده بدون بلاک و بدون Dirty Read استفاده می‌کنم.
  • سعی می‌کنم تراکنش‌ها کوتاه باشند تا Lock زیاد نگه نداشته شوند.

7️⃣ نگهداری منظم

در نهایت:

  • مطمئن می‌شم statistics و indexها به‌روز هستن.
  • از jobهایی مثل:
EXEC sp_updatestats;
ALTER INDEX ALL ON TableName REBUILD;

استفاده می‌کنم تا optimizer همیشه بهترین تصمیم رو بگیره.

✅ جمع‌بندی پاسخ مصاحبه‌ای (مختصر ولی فنی):

برای بهینه‌سازی Stored Procedure اول Execution Plan و Statistics رو بررسی می‌کنم تا نقاط کندی مشخص بشن، بعد query‌ها رو بازنویسی می‌کنم (حذف SELECT *, اضافه‌کردن ایندکس مناسب، شکستن queryهای پیچیده، و رفع parameter sniffing). در نهایت با تنظیم و به‌روز نگه‌داشتن index و statistics مطمئن می‌شم performance در سطح بالا باقی بمونه.

Accept Cookies
Accept Cookies
[your-shortcode]