بهینه سازی Query های پیچیده SQL Server در پروژه‌های با حجم تراکنش بالا

مرداد ۲۹, ۱۴۰۵ - By TNVN

در پروژه‌های پرتراکنش، کندی SQL Server معمولاً فقط به پیچیده بودن Query مربوط نیست. ترکیبی از ایندکس نامناسب، قفل شدن رکوردها، اجرای مکرر Query، برآورد اشتباه تعداد رکوردها و طراحی نادرست تراکنش می‌تواند زمان پاسخ را از چند میلی ثانیه به چند ثانیه افزایش دهد.

در این آموزش، به جای توصیه‌های کلی، یک مسیر عملی برای پیدا کردن و اصلاح Queryهای کند در SQL Server ارائه می‌شود.

قبل از تغییر Query، مشکل را اندازه گیری کنید

اولین اشتباه در بهینه سازی SQL Server این است که بدون مشاهده Execution Plan یا آمار اجرای Query، شروع به تغییر کد کنیم.

دو دستور زیر را فعال کنید:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Query مورد نظر
SELECT *
FROM Sales.Orders
WHERE CustomerId = 1250;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

خروجی این دستورات اطلاعات مهمی در اختیار شما قرار می‌دهد:

تعداد Logical Read
تعداد Physical Read
زمان CPU
زمان سپری شده
تعداد Scan و Seek
مقدار خواندن از حافظه یا دیسک

تفاوت Logical Read و Physical Read

Logical Read تعداد صفحات ۸ کیلوبایتی خوانده شده از Buffer Pool است. مقدار زیاد آن معمولاً نشانه اسکن گسترده یا ایندکس نامناسب است.

Physical Read نشان می‌دهد SQL Server مجبور شده داده را از دیسک بخواند. در سیستم‌های پرتراکنش، Physical Read بالا می‌تواند باعث رقابت شدید روی دیسک شود.

در SSMS، برای مشاهده Execution Plan واقعی از کلیدهای زیر استفاده کنید:

Ctrl + M

سپس Query را اجرا کنید. در Plan به این موارد توجه داشته باشید:

Table Scan
Index Scan
Key Lookup
Sort پرهزینه
Hash Match
هشدارهای مربوط به Implicit Conversion
اختلاف بین Estimated Rows و Actual Rows

بر اساس مستندات رسمی مایکروسافت، پایش مداوم Queryها، Execution Planها و Runtime Statistics پایه تصمیم گیری صحیح برای بهینه سازی است. راهنمای مانیتورینگ و بهینه سازی SQL Server در Microsoft Learn

۱. Query را با فیلتر قابل استفاده برای ایندکس بنویسید

SQL Server زمانی می‌تواند از ایندکس به شکل مؤثر استفاده کند که شرط WHERE روی خود ستون قابل جستجو باشد.

Query نامناسب

SELECT OrderId, TotalAmount
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2026;

در این حالت، تابع YEAR روی ستون اجرا شده و SQL Server ممکن است مجبور شود تعداد زیادی از رکوردها را بررسی کند.

Query اصلاح شده

SELECT OrderId, TotalAmount
FROM Sales.Orders
WHERE OrderDate >= '20260101'
  AND OrderDate <  '20270101';

این روش به SQL Server اجازه می‌دهد محدوده ایندکس را مستقیماً جستجو کند.

چند نمونه رایج دیگر

نامناسب

WHERE CONVERT(date, CreatedAt) = '2026-08-20'

مناسب

WHERE CreatedAt >= '20260820'
  AND CreatedAt <  '20260821'

نامناسب

WHERE ISNULL(Status, 0) = 1

مناسب

WHERE Status = 1

در صورت نیاز، مقدار NULL را جداگانه مدیریت کنید:

WHERE Status = 1
   OR Status IS NULL;

۲. برای Queryهای پرتکرار ایندکس ترکیبی بسازید

فرض کنید Query اصلی سیستم سفارش‌ها به شکل زیر است:

SELECT OrderId, CustomerId, TotalAmount, CreatedAt
FROM Sales.Orders
WHERE CustomerId = @CustomerId
  AND Status = @Status
  AND CreatedAt >= @FromDate
  AND CreatedAt < @ToDate
ORDER BY CreatedAt DESC;

یک ایندکس مناسب می‌تواند این باشد:

CREATE INDEX IX_Orders_Customer_Status_CreatedAt
ON Sales.Orders
(
    CustomerId,
    Status,
    CreatedAt DESC
)
INCLUDE
(
    OrderId,
    TotalAmount
);

چرا ترتیب ستون‌ها مهم است؟

در این مثال:

CustomerId و Status برای فیلتر برابری استفاده می‌شوند.
CreatedAt برای فیلتر بازه‌ای و مرتب سازی استفاده می‌شود.
ستون‌های OrderId و TotalAmount در بخش INCLUDE قرار گرفته‌اند تا SQL Server برای تکمیل نتیجه به جدول اصلی مراجعه نکند.

ستون‌هایی که در WHERE و JOIN استفاده می‌شوند، معمولاً کاندیدهای اصلی بخش کلیدی ایندکس هستند. ستون‌هایی که فقط در SELECT قرار دارند، اغلب برای INCLUDE مناسب‌ترند.

۳. مشکل Key Lookup را برطرف کنید

فرض کنید این ایندکس وجود دارد:

CREATE INDEX IX_Orders_CustomerId
ON Sales.Orders(CustomerId);

و Query زیر اجرا می‌شود:

SELECT OrderId, CustomerId, TotalAmount, CreatedAt, Status
FROM Sales.Orders
WHERE CustomerId = @CustomerId;

SQL Server ابتدا رکوردها را از ایندکس پیدا می‌کند، اما برای خواندن سایر ستون‌ها به جدول اصلی برمی گردد. این عملیات در Execution Plan با عنوان Key Lookup نمایش داده می‌شود.

اگر Query پرتکرار است، ایندکس را پوششی کنید:

CREATE INDEX IX_Orders_CustomerId_Covering
ON Sales.Orders(CustomerId)
INCLUDE
(
    OrderId,
    TotalAmount,
    CreatedAt,
    Status
);

چه زمانی Covering Index نسازیم؟

Covering Index همیشه بهترین راه حل نیست. ایجاد ایندکس بزرگ باعث افزایش هزینه این عملیات می‌شود:

INSERT
UPDATE
DELETE
عملیات نگهداری ایندکس
مصرف فضای دیسک
مصرف حافظه

فقط برای Queryهای پرتکرار و حساس به زمان پاسخ، از این روش استفاده کنید.

۴. از SELECT * در مسیرهای پرترافیک استفاده نکنید

Query نامناسب

SELECT *
FROM Sales.Orders
WHERE CustomerId = @CustomerId;

این Query به تمام ستون‌ها وابسته است. در نتیجه:

حجم داده بیشتری از دیسک خوانده می‌شود.
امکان استفاده از Covering Index کاهش می‌یابد.
انتقال داده بین SQL Server و برنامه افزایش پیدا می‌کند.
تغییر ساختار جدول می‌تواند رفتار Query را تغییر دهد.

Query بهتر

SELECT
    OrderId,
    CustomerId,
    Status,
    TotalAmount,
    CreatedAt
FROM Sales.Orders
WHERE CustomerId = @CustomerId;

در APIها و صفحات لیست، فقط ستون‌هایی را دریافت کنید که واقعاً نمایش داده می‌شوند.

۵. Pagination را با Keyset انجام دهید

در جدول‌های بزرگ، استفاده از OFFSET برای صفحات انتهایی بسیار پرهزینه است.

روش پرهزینه

SELECT
    OrderId,
    CreatedAt,
    TotalAmount
FROM Sales.Orders
ORDER BY CreatedAt DESC, OrderId DESC
OFFSET 500000 ROWS
FETCH NEXT 50 ROWS ONLY;

SQL Server برای رسیدن به صفحه مورد نظر باید تعداد زیادی رکورد را مرتب و رد کند.

روش Keyset Pagination

در این روش، آخرین رکورد صفحه قبلی را به Query بعدی ارسال می‌کنیم:

DECLARE @LastCreatedAt datetime2 = '2026-08-15 12:30:00';
DECLARE @LastOrderId bigint = 987654;

SELECT TOP (50)
    OrderId,
    CreatedAt,
    TotalAmount
FROM Sales.Orders
WHERE
    CreatedAt < @LastCreatedAt
    OR
    (
        CreatedAt = @LastCreatedAt
        AND OrderId < @LastOrderId
    )
ORDER BY CreatedAt DESC, OrderId DESC;

برای این Query ایندکس زیر مناسب است:

CREATE INDEX IX_Orders_CreatedAt_OrderId
ON Sales.Orders(CreatedAt DESC, OrderId DESC)
INCLUDE
(
    TotalAmount
);

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

۶. مراقب Parameter Sniffing باشید

SQL Server معمولاً هنگام اولین اجرای Stored Procedure، مقدار پارامترها را بررسی کرده و بر اساس آن Execution Plan می‌سازد.

اگر توزیع داده یکنواخت نباشد، یک Plan ممکن است برای یک مقدار مناسب و برای مقدار دیگر بسیار ضعیف باشد.

فرض کنید یک مشتری فقط ۱۰ سفارش دارد، اما مشتری دیگری چند میلیون سفارش ثبت کرده است:

CREATE OR ALTER PROCEDURE Sales.GetCustomerOrders
    @CustomerId bigint
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CreatedAt,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerId = @CustomerId;
END;

ممکن است Plan ساخته شده برای مشتری کم تراکنش، هنگام اجرای Query برای مشتری بزرگ مناسب نباشد.

راه حل اول: بازکامپایل در همان اجرا

SELECT
    OrderId,
    CreatedAt,
    TotalAmount,
    Status
FROM Sales.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

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

تعداد اجرای آنها بسیار زیاد نیست.
اختلاف حجم داده بین پارامترها زیاد است.
زمان ساخت Plan نسبت به هزینه اجرای اشتباه کمتر است.

راه حل دوم: استفاده از OPTIMIZE FOR UNKNOWN

SELECT
    OrderId,
    CreatedAt,
    TotalAmount,
    Status
FROM Sales.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);

در این روش SQL Server از مقدار واقعی پارامتر برای ساخت Plan استفاده نمی‌کند و برآورد عمومی‌تری انجام می‌دهد.

راه حل سوم: متغیر محلی

DECLARE @LocalCustomerId bigint = @CustomerId;

SELECT
    OrderId,
    CreatedAt,
    TotalAmount,
    Status
FROM Sales.Orders
WHERE CustomerId = @LocalCustomerId;

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

۷. Implicit Conversion را حذف کنید

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

فرض کنید نوع ستون CustomerId از نوع bigint است:

CREATE TABLE Sales.Orders
(
    OrderId bigint NOT NULL,
    CustomerId bigint NOT NULL
);

اما برنامه مقدار را به صورت nvarchar ارسال می‌کند:

WHERE CustomerId = N'1250'

ممکن است SQL Server مجبور به تبدیل نوع داده شود. در Execution Plan، هشدار CONVERT_IMPLICIT را بررسی کنید.

روش صحیح

نوع پارامتر در برنامه و Stored Procedure باید با نوع ستون یکسان باشد:

CREATE OR ALTER PROCEDURE Sales.GetOrders
    @CustomerId bigint
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CreatedAt, TotalAmount
    FROM Sales.Orders
    WHERE CustomerId = @CustomerId;
END;

در پروژه‌های .NET نیز نوع پارامتر را صحیح مشخص کنید:

command.Parameters.Add("@CustomerId", SqlDbType.BigInt).Value = customerId;

۸. JOIN را با ستون‌های درست و ایندکس مناسب اجرا کنید

فرض کنید Query زیر برای نمایش سفارش‌های مشتری اجرا می‌شود:

SELECT
    o.OrderId,
    o.CreatedAt,
    c.CustomerName,
    o.TotalAmount
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE o.Status = 1
  AND o.CreatedAt >= @FromDate;

ایندکس‌های پیشنهادی:

CREATE INDEX IX_Orders_Status_CreatedAt_CustomerId
ON Sales.Orders(Status, CreatedAt, CustomerId)
INCLUDE
(
    OrderId,
    TotalAmount
);

و در جدول مشتری:

CREATE UNIQUE INDEX UX_Customers_CustomerId
ON Sales.Customers(CustomerId)
INCLUDE
(
    CustomerName
);

از JOIN غیرضروری جلوگیری کنید

اگر هیچ ستونی از جدول مشتری استفاده نمی‌شود، این JOIN را حذف کنید:

SELECT o.OrderId, o.TotalAmount
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE o.Status = 1;

نسخه ساده‌تر:

SELECT OrderId, TotalAmount
FROM Sales.Orders
WHERE Status = 1;

هر JOIN اضافی می‌تواند هزینه CPU، حافظه و I/O را افزایش دهد.

۹. به جای IN بزرگ از روش مناسب استفاده کنید

روش مشکل ساز

SELECT OrderId, TotalAmount
FROM Sales.Orders
WHERE OrderId IN
(
    100001, 100002, 100003, 100004
);

برای تعداد کم شناسه مشکلی ایجاد نمی‌کند، اما در برنامه‌های پرترافیک و فهرست‌های بزرگ، بهتر است از Table-Valued Parameter استفاده شود.

تعریف نوع جدولی

CREATE TYPE Sales.OrderIdList AS TABLE
(
    OrderId bigint NOT NULL PRIMARY KEY
);

Stored Procedure

CREATE OR ALTER PROCEDURE Sales.GetOrdersByIds
    @OrderIds Sales.OrderIdList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.CustomerId,
        o.TotalAmount,
        o.Status
    FROM Sales.Orders AS o
    INNER JOIN @OrderIds AS ids
        ON ids.OrderId = o.OrderId;
END;

این روش نسبت به ساختن رشته‌های طولانی و Dynamic SQL قابل کنترل‌تر و امن‌تر است.

۱۰. LIKE را برای جستجوی متنی درست انتخاب کنید

Query قابل استفاده از ایندکس

WHERE CustomerName LIKE @Search + N'%'

Query پرهزینه

WHERE CustomerName LIKE N'%' + @Search + N'%'

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

برای جستجوی واقعی در متن‌های بزرگ، Full-Text Search گزینه مناسب‌تری است:

CREATE FULLTEXT CATALOG CustomerCatalog AS DEFAULT;

سپس روی ستون مورد نظر Full-Text Index ایجاد کنید و Query را با CONTAINS یا FREETEXT اجرا کنید:

SELECT CustomerId, CustomerName
FROM Sales.Customers
WHERE CONTAINS(CustomerName, @SearchTerm);

۱۱. تراکنش‌ها را کوتاه نگه دارید

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

الگوی نامناسب

BEGIN TRANSACTION;

SELECT *
FROM Sales.Orders
WHERE OrderId = @OrderId;

-- پردازش طولانی در برنامه
-- ارسال درخواست به سرویس خارجی
-- محاسبه سنگین

UPDATE Sales.Orders
SET Status = 2
WHERE OrderId = @OrderId;

COMMIT TRANSACTION;

در این فاصله، قفل‌ها ممکن است بیشتر از زمان لازم حفظ شوند.

الگوی بهتر

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

SELECT
    OrderId,
    Status,
    TotalAmount
FROM Sales.Orders
WHERE OrderId = @OrderId;

سپس:

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Sales.Orders
    SET
        Status = 2,
        UpdatedAt = SYSUTCDATETIME()
    WHERE OrderId = @OrderId
      AND Status = 1;

    IF @@ROWCOUNT = 0
        THROW 50001, 'Order is not available for update.', 1;

    INSERT INTO Sales.OrderStatusHistory
    (
        OrderId,
        OldStatus,
        NewStatus,
        CreatedAt
    )
    VALUES
    (
        @OrderId,
        1,
        2,
        SYSUTCDATETIME()
    );

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

نکته مهم

شرط وضعیت در دستور UPDATE نقش کنترل همزمانی دارد:

WHERE OrderId = @OrderId
  AND Status = 1

به این ترتیب، اگر درخواست دیگری قبلاً وضعیت سفارش را تغییر داده باشد، عملیات فعلی بدون بررسی اضافی شکست می‌خورد.

۱۲. Blocking و Deadlock را بررسی کنید

برای مشاهده درخواست‌های در حال انتظار:

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS SqlText
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.wait_time DESC;

برای پیدا کردن Sessionهای مسدود شده:

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

علت‌های رایج Blocking

تراکنش‌های طولانی
نبود ایندکس روی شرط UPDATE یا DELETE
اسکن گسترده جدول
اجرای گزارش سنگین روی جداول عملیاتی
ترتیب متفاوت دسترسی به جداول در تراکنش‌های مختلف
به روز رسانی دسته‌ای در ساعات شلوغ

الگوی کاهش Deadlock

در تمام بخش‌های برنامه، ترتیب دسترسی به جداول را ثابت کنید. برای مثال، اگر یک تراکنش ابتدا Customers و سپس Orders را تغییر می‌دهد، تراکنش دیگر نباید ابتدا Orders و بعد Customers را تغییر دهد.

همچنین عملیات بزرگ را به بخش‌های کوچک‌تر تقسیم کنید:

WHILE 1 = 1
BEGIN
    DELETE TOP (1000)
    FROM Audit.Logs
    WHERE CreatedAt < @BeforeDate;

    IF @@ROWCOUNT = 0
        BREAK;
END;

۱۳. سطح Isolation را آگاهانه انتخاب کنید

در بسیاری از سیستم‌های OLTP، خواندن‌های طولانی با نوشتن‌ها تداخل ایجاد می‌کنند. یکی از راهکارهای مهم، فعال کردن READ_COMMITTED_SNAPSHOT است:

ALTER DATABASE [SalesDb]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

در این حالت، خواندن‌های معمولی از Version Store در tempdb استفاده می‌کنند و معمولاً کمتر منتظر قفل‌های نوشتاری می‌مانند.

اما پیش از فعال سازی باید موارد زیر بررسی شود:

فضای کافی برای tempdb
حجم Update و Delete
مدت اجرای تراکنش‌ها
مصرف Version Store
سازگاری برنامه با رفتار جدید خواندن

برای تراکنش‌هایی که نیاز به ثبات کامل داده دارند، سطح Isolation را بدون بررسی تغییر ندهید.

۱۴. Statistics را به روز نگه دارید

اگر Statistics قدیمی باشد، SQL Server ممکن است تعداد رکوردهای خروجی را اشتباه تخمین بزند و Join یا ایندکس نامناسب انتخاب کند.

به روز رسانی دستی:

UPDATE STATISTICS Sales.Orders
WITH FULLSCAN;

برای جدول‌های بزرگ، FULLSCAN ممکن است پرهزینه باشد. بنابراین آن را در ساعات مناسب و با بررسی زمان اجرا انجام دهید.

مشاهده زمان آخرین به روز رسانی Statistics:

SELECT
    OBJECT_SCHEMA_NAME(sp.object_id) AS SchemaName,
    OBJECT_NAME(sp.object_id) AS TableName,
    sp.stats_id,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.dm_db_stats_properties
(
    OBJECT_ID(N'Sales.Orders'),
    1
) AS sp;

در پروژه‌هایی که داده به سرعت تغییر می‌کند، فقط فعال بودن Auto Update Statistics کافی نیست. آمار Query Store، زمان اجرای Query و تغییرات داده را نیز بررسی کنید.

۱۵. Query Store را فعال و بررسی کنید

Query Store برای پیدا کردن Queryهایی که در طول زمان کند شده‌اند بسیار کاربردی است.

فعال سازی:

ALTER DATABASE [SalesDb]
SET QUERY_STORE = ON;

برای پیدا کردن Queryهای پرهزینه:

SELECT TOP (20)
    qt.query_sql_text,
    rs.execution_count,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
ORDER BY rs.avg_duration DESC;

برای پیدا کردن Queryهایی که بیشترین مجموع زمان را مصرف کرده‌اند:

SELECT TOP (20)
    qt.query_sql_text,
    SUM(rs.count_executions) AS TotalExecutions,
    SUM(rs.avg_duration * rs.count_executions) AS TotalDuration
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY qt.query_sql_text
ORDER BY TotalDuration DESC;

گاهی Queryای که میانگین زمان کمی دارد، به دلیل تعداد اجرای بسیار زیاد، بیشترین فشار را به SQL Server وارد می‌کند.

۱۶. برای اصلاح Query از این ترتیب استفاده کنید

برای هر Query پیچیده، این مراحل را به صورت مشخص انجام دهید:

مرحله اول: اجرای فعلی را ثبت کنید

زمان متوسط
زمان صدک ۹۵ و ۹۹
CPU
Logical Read
تعداد اجرا
تعداد Timeout
Wait Type

مرحله دوم: Execution Plan را بررسی کنید

به دنبال این موارد باشید:

Scan روی جدول بزرگ
اختلاف Estimated Rows و Actual Rows
Key Lookup تکرارشونده
Sort بدون ایندکس مناسب
Hash Join ناشی از کمبود ایندکس یا برآورد اشتباه
Implicit Conversion
Spill به tempdb

مرحله سوم: Query را اصلاح کنید

حذف SELECT *
حذف تابع از روی ستون فیلتر
اصلاح Pagination
حذف JOIN غیرضروری
اصلاح نوع پارامترها
کاهش تعداد ستون‌های خروجی

مرحله چهارم: ایندکس را بررسی کنید

آیا ایندکس با الگوی واقعی Query هماهنگ است؟
آیا ترتیب ستون‌ها صحیح است؟
آیا Query نیاز به INCLUDE دارد؟
آیا ایندکس مشابه از قبل وجود دارد؟
هزینه این ایندکس برای عملیات نوشتن چقدر است؟

مرحله پنجم: زیر بار واقعی آزمایش کنید

Query فقط در محیط توسعه آزمایش نشود. حجم داده، همزمانی کاربران، پارامترهای مختلف و زمان‌های پرترافیک را شبیه سازی کنید.

نمونه کامل بررسی یک Query

Query اولیه

SELECT *
FROM Sales.Orders
WHERE CONVERT(date, CreatedAt) = @OrderDate
  AND CustomerId IN
  (
      SELECT CustomerId
      FROM Sales.Customers
      WHERE IsActive = 1
  )
ORDER BY CreatedAt DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

مشکل‌های این Query:

استفاده از تابع روی CreatedAt
استفاده از SELECT *
Pagination با OFFSET در صفحات بزرگ
احتمال اسکن جدول Customers
نامشخص بودن ایندکس‌های مورد نیاز

نسخه اصلاح شده

SELECT TOP (@PageSize)
    o.OrderId,
    o.CustomerId,
    o.CreatedAt,
    o.TotalAmount,
    o.Status
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE c.IsActive = 1
  AND
  (
      o.CreatedAt < @LastCreatedAt
      OR
      (
          o.CreatedAt = @LastCreatedAt
          AND o.OrderId < @LastOrderId
      )
  )
ORDER BY o.CreatedAt DESC, o.OrderId DESC;

ایندکس‌های پیشنهادی:

CREATE INDEX IX_Customers_IsActive_CustomerId
ON Sales.Customers(IsActive, CustomerId);

CREATE INDEX IX_Orders_CreatedAt_OrderId_CustomerId
ON Sales.Orders(CreatedAt DESC, OrderId DESC, CustomerId)
INCLUDE
(
    TotalAmount,
    Status
);

این نسخه برای صفحه‌های انتهایی، خواندن ستون‌های محدود و اجرای پرتکرار، قابلیت مقیاس پذیری بیشتری دارد.

چه کارهایی معمولاً نتیجه معکوس دارند؟

ایجاد ایندکس برای هر ستون

تعداد زیاد ایندکس، عملیات نوشتن را کند می‌کند و فضای زیادی مصرف می‌کند.

استفاده دائمی از `NOLOCK`

SELECT *
FROM Sales.Orders WITH (NOLOCK);

NOLOCK ممکن است Dirty Read، رکوردهای تکراری یا داده‌های ناپایدار برگرداند. این دستور راه حل عمومی برای مشکل Blocking نیست.

استفاده بی دلیل از `OPTION (RECOMPILE)`

برای Queryهای پرتکرار، بازسازی Plan در هر اجرا می‌تواند CPU را افزایش دهد.

انتقال همه Queryها به Stored Procedure

Stored Procedure مفید است، اما اگر منطق Query، ایندکس یا مدل داده نادرست باشد، صرفاً قرار دادن کد در Stored Procedure مشکل را حل نمی‌کند.

افزایش سخت افزار بدون اصلاح Query

افزایش RAM یا CPU گاهی کمک می‌کند، اما Query دارای اسکن، Sort یا Blocking همچنان در حجم بالاتر مشکل ایجاد خواهد کرد.

چک لیست نهایی بهینه سازی Query در SQL Server

پیش از انتشار تغییرات، این موارد را بررسی کنید:

Execution Plan واقعی مشاهده شده است.
Logical Read قبل و بعد مقایسه شده است.
زمان CPU و زمان سپری شده ثبت شده است.
Query از SELECT * استفاده نمی‌کند.
روی ستون‌های فیلتر تابع اجرا نمی‌شود.
نوع پارامتر با نوع ستون یکسان است.
ایندکس ترکیبی بر اساس الگوی واقعی Query طراحی شده است.
Key Lookup غیرضروری حذف شده است.
Pagination برای صفحات بزرگ با Keyset انجام می‌شود.
تراکنش‌ها کوتاه هستند.
Blocking و Deadlock بررسی شده‌اند.
Statistics و Query Store در نظر گرفته شده‌اند.
Query با پارامترهای کم حجم و پرحجم آزمایش شده است.
تأثیر ایندکس جدید بر INSERT و UPDATE بررسی شده است.

جمع بندی

بهینه سازی Queryهای پیچیده SQL Server در پروژه‌های پرتراکنش، با یک تغییر ساده یا افزودن ایندکس تصادفی انجام نمی‌شود. باید ابتدا هزینه واقعی Query را اندازه گیری کرد، سپس Execution Plan، ایندکس، نوع پارامتر، مدت تراکنش و رفتار همزمانی را بررسی کرد.

در عمل، بیشترین نتیجه معمولاً از این اقدامات به دست می‌آید:

حذف Scanهای غیرضروری
ایجاد ایندکس ترکیبی متناسب با Query
حذف Key Lookupهای پرتکرار
استفاده از Keyset Pagination
جلوگیری از Parameter Sniffing در Queryهای حساس
کوتاه کردن تراکنش‌ها
بررسی Blocking و Deadlock
استفاده مستمر از Query Store و آمار واقعی اجرا

برای مطالعه نکات تکمیلی درباره طراحی ایندکس، مدیریت تراکنش‌ها، tempdb و بهینه سازی دیتابیس، مقاله بهینه سازی دیتابیس؛ جایی که ۸۰٪ سرعت سایت شما در آن نهفته است را نیز بخوانید.

اگر در پروژه خود با Queryهای کند، قفل شدن جداول یا افت عملکرد SQL Server روبه رو هستید، برای مشاوره و بررسی فنی پروژه با طراحان نوین تماس بگیرید.

قبل از تغییر Query، مشکل را اندازه گیری کنید

تفاوت Logical Read و Physical Read

۱. Query را با فیلتر قابل استفاده برای ایندکس بنویسید

Query نامناسب

Query اصلاح شده

چند نمونه رایج دیگر

نامناسب

مناسب

نامناسب

مناسب

۲. برای Queryهای پرتکرار ایندکس ترکیبی بسازید

چرا ترتیب ستون‌ها مهم است؟

۳. مشکل Key Lookup را برطرف کنید

چه زمانی Covering Index نسازیم؟

۴. از SELECT * در مسیرهای پرترافیک استفاده نکنید

Query نامناسب

Query بهتر

۵. Pagination را با Keyset انجام دهید

روش پرهزینه

روش Keyset Pagination

۶. مراقب Parameter Sniffing باشید

راه حل اول: بازکامپایل در همان اجرا

راه حل دوم: استفاده از OPTIMIZE FOR UNKNOWN

راه حل سوم: متغیر محلی

۷. Implicit Conversion را حذف کنید

روش صحیح

۸. JOIN را با ستون‌های درست و ایندکس مناسب اجرا کنید

از JOIN غیرضروری جلوگیری کنید

۹. به جای IN بزرگ از روش مناسب استفاده کنید

روش مشکل ساز

تعریف نوع جدولی

Stored Procedure

۱۰. LIKE را برای جستجوی متنی درست انتخاب کنید

Query قابل استفاده از ایندکس

Query پرهزینه

۱۱. تراکنش‌ها را کوتاه نگه دارید

الگوی نامناسب

الگوی بهتر

نکته مهم

۱۲. Blocking و Deadlock را بررسی کنید

علت‌های رایج Blocking

الگوی کاهش Deadlock

۱۳. سطح Isolation را آگاهانه انتخاب کنید

۱۴. Statistics را به روز نگه دارید

۱۵. Query Store را فعال و بررسی کنید

۱۶. برای اصلاح Query از این ترتیب استفاده کنید

مرحله اول: اجرای فعلی را ثبت کنید

مرحله دوم: Execution Plan را بررسی کنید

مرحله سوم: Query را اصلاح کنید

مرحله چهارم: ایندکس را بررسی کنید

مرحله پنجم: زیر بار واقعی آزمایش کنید

نمونه کامل بررسی یک Query

Query اولیه

نسخه اصلاح شده

چه کارهایی معمولاً نتیجه معکوس دارند؟

ایجاد ایندکس برای هر ستون

استفاده دائمی از NOLOCK

استفاده بی دلیل از OPTION (RECOMPILE)

انتقال همه Queryها به Stored Procedure

افزایش سخت افزار بدون اصلاح Query

چک لیست نهایی بهینه سازی Query در SQL Server

جمع بندی

Related Posts

تکنیک های فشرده سازی و بهینه سازی ذخیره سازی تصاویر در پروژه های وب

ابزارهای اتوماسیون و workflow؛ راهنمای انتخاب و اجرای فرایندهای خودکار

بهینه سازی دیتابیس؛ جایی که ۸۰٪ سرعت سایت شما در آن نهفته است

دیدگاهتان را بنویسید لغو پاسخ

استفاده دائمی از `NOLOCK`

استفاده بی دلیل از `OPTION (RECOMPILE)`