Columnstore چیست و چگونه کار می‌کند؟

در ساختار Rowstore، داده‌های یک ردیف کنار هم ذخیره می‌شوند؛ اما در Columnstore مقادیر هر ستون در Segmentهای جداگانه قرار می‌گیرند. این چیدمان برای Queryهایی که تعداد محدودی ستون را از میلیون‌ها ردیف می‌خوانند بسیار مناسب است، زیرا موتور می‌تواند ستون‌های غیرضروری را نخواند و داده‌های مشابه را با نسبت بهتری فشرده کند.

واحد منطقی مهم در این معماری Rowgroup است. هر Rowgroup شامل مجموعه‌ای از ردیف‌ها است که برای هر ستون به Segment تبدیل می‌شوند. ردیف‌های کوچک و تازه‌وارد ابتدا ممکن است در Delta Store قرار گیرند و سپس Tuple Mover آن‌ها را به Rowgroup فشرده منتقل کند. حذف ردیف‌های فشرده نیز معمولاً ابتدا در Deleted Bitmap ثبت می‌شود.

در سطح اجرای Query، Batch Mode می‌تواند تعداد زیادی ردیف را در هر چرخه پردازش کند. در سطح خواندن داده، Segment Elimination با بررسی Min/Max Segmentها بخش‌های نامرتبط را کنار می‌گذارد. در نتیجه بهترین کارایی زمانی به‌دست می‌آید که کیفیت Rowgroup، ترتیب داده و شرط Query با یکدیگر هماهنگ باشند.

اجزای اصلی معماری و ارتباط آن‌ها

دو مدل اصلی ایندکس Columnstore

Clustered Columnstore Index

CCI ساختار اصلی جدول را ستونی می‌کند و برای Fact Tableهای بزرگ، بارگذاری دسته‌ای و Queryهای اسکن‌محور مناسب است. این مدل بیشترین مزیت فشرده‌سازی را دارد، اما طراحی کلیدهای دسترسی نقطه‌ای، نگه‌داری و عملیات DML باید جداگانه دیده شود.

آموزش کامل Clustered Columnstore Index با مثال‌های عملی

Nonclustered Columnstore Index

NCCI نسخه ستونی ستون‌های منتخب را کنار ساختار Rowstore نگه می‌دارد. این گزینه برای Operational Analytics مفید است، اما هزینه اضافی INSERT، UPDATE و DELETE دارد و باید ستون‌ها و فیلتر آن با دقت انتخاب شوند.

آموزش کامل Nonclustered Columnstore Index و تحلیل بلادرنگ

اجرای Query: Batch Mode و Segment Elimination

Batch Mode و Segment Elimination دو مفهوم متفاوت اما مکمل هستند. Batch Mode نحوه پردازش ردیف‌ها در اپراتورهای Plan را تغییر می‌دهد، درحالی‌که Segment Elimination مقدار داده خوانده‌شده را کاهش می‌دهد. ممکن است یک Query از Batch Mode استفاده کند اما Segmentهای زیادی را بخواند، یا برعکس.

راهنمای اجرای Batch Mode در SQL Server

راهنمای Segment Elimination و شرط‌های SARGable

آموزش Ordered Columnstore Index و Data Skipping

Delta Store، Tuple Mover و کیفیت Rowgroup

بارگذاری‌های کوچک معمولاً مستقیماً Rowgroup فشرده کامل تولید نمی‌کنند. داده ابتدا وارد Delta Store می‌شود، Rowgroup از OPEN به CLOSED می‌رود و سپس Tuple Mover یا عملیات نگه‌داری آن را فشرده می‌کند. اگر ETL به تعداد زیادی Batch کوچک تقسیم شود، Rowgroupهای کم‌حجم یا Delta Rowgroupهای متعدد ایجاد می‌شوند.

کیفیت Rowgroup فقط تعداد ردیف نیست. Trim Reason، اندازه Dictionary، فشار حافظه، هم‌زمانی و توزیع داده نیز اثر دارند. برای همین باید از DMV فیزیکی Rowgroup استفاده و روند چند Snapshot را مقایسه کرد.

آموزش Tuple Mover و Background Merge

آموزش Delta Store و وضعیت OPEN/CLOSED

راهنمای فشرده‌سازی Rowgroup و Trim Reason

آموزش Deleted Bitmap و حذف منطقی در Columnstore

DMVهای ضروری برای پایش Columnstore

نام رسمی DMV سوم sys.dm_column_store_object_pool است. عنوان خام مجموعه دارای dm_db بود، اما در مقاله تخصصی Syntax قابل اجرا و نام صحیح موتور استفاده شده است.

۸ مثال کاربردی ترکیبی

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    SELECT COUNT_BIG(*) AS row_count FROM dbo.FactSalesDemo;

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    SELECT YEAR(OrderDate) AS sales_year,SUM(Amount) AS total_amount FROM dbo.FactSalesDemo GROUP BY YEAR(OrderDate);

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    SET STATISTICS IO ON;
    SELECT SUM(Amount) FROM dbo.FactSalesDemo WHERE OrderDate>='2026-01-01' AND OrderDate<'2026-02-01';
    SET STATISTICS IO OFF;

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    SELECT state_desc,COUNT(*) AS groups,SUM(total_rows) AS rows_count,SUM(deleted_rows) AS deleted_rows FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id=OBJECT_ID(N'dbo.FactSalesDemo') GROUP BY state_desc;

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    DELETE TOP(2500) FROM dbo.FactSalesDemo;
    SELECT SUM(total_rows) AS total_rows,SUM(deleted_rows) AS deleted_rows FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id=OBJECT_ID(N'dbo.FactSalesDemo');

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    ALTER INDEX CCI_FactSalesDemo ON dbo.FactSalesDemo REORGANIZE;
    SELECT state_desc,COUNT(*) FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id=OBJECT_ID(N'dbo.FactSalesDemo') GROUP BY state_desc;

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    ALTER INDEX CCI_FactSalesDemo ON dbo.FactSalesDemo REORGANIZE WITH(COMPRESS_ALL_ROW_GROUPS=ON);

USE tempdb;
    DROP TABLE IF EXISTS dbo.FactSalesDemo;
    CREATE TABLE dbo.FactSalesDemo
    (
        SaleID bigint NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        ProductID int NOT NULL,
        Quantity smallint NULL,
        Amount decimal(18,2) NULL
    );

    INSERT INTO dbo.FactSalesDemo
    (
        SaleID, OrderDate, CustomerID, ProductID, Quantity, Amount
    )
    SELECT TOP (25000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, CONVERT(date, '20260727')),
        1 + ABS(CHECKSUM(NEWID())) % 5000,
        1 + ABS(CHECKSUM(NEWID())) % 800,
        1 + ABS(CHECKSUM(NEWID())) % 12,
        CONVERT(decimal(18,2), 10 + ABS(CHECKSUM(NEWID())) % 50000 / 10.0)
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b;

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSalesDemo
    ON dbo.FactSalesDemo;

    SET STATISTICS IO ON;
    SET STATISTICS TIME ON;
    SELECT CustomerID,COUNT_BIG(*) AS orders,SUM(Amount) AS revenue FROM dbo.FactSalesDemo GROUP BY CustomerID;
    SET STATISTICS TIME OFF;
    SET STATISTICS IO OFF;

نگه‌داری: REORGANIZE یا REBUILD؟

REORGANIZE برای Columnstore فقط معادل Defrag سبک B-tree نیست؛ Rowgroupهای بسته Delta را فشرده می‌کند و می‌تواند برخی Rowgroupهای کوچک یا دارای حذف زیاد را ادغام کند. گزینه COMPRESS_ALL_ROW_GROUPS برای بستن Rowgroupهای باز نیز قابل استفاده است، اما اجرای بی‌برنامه آن می‌تواند Rowgroup کوچک بسازد.

REBUILD ساختار را دوباره می‌سازد و برای حذف زیاد، بازیابی کیفیت شدیداً افت‌کرده یا بازمرتب‌سازی Ordered Columnstore مناسب است. هزینه Log، CPU، حافظه و زمان آن بیشتر است؛ بنابراین نباید فقط با یک درصد ثابت برنامه‌ریزی شود.

IDENT_CURRENT و IDENT_SEED؛ موضوع تکمیلی مجموعه

آخرین موضوع ورودی به معماری Columnstore مربوط نیست، اما به‌عنوان مقاله مستقل در همین بسته نگه داشته شده است. IDENT_SEED مقدار اولیه Identity را گزارش می‌کند و IDENT_CURRENT آخرین مقدار تولیدشده برای جدول را در همه Sessionها و Scopeها می‌بیند؛ بنابراین برای دریافت شناسه همان INSERT در محیط هم‌زمان انتخاب مناسبی نیست.

آموزش کامل تفاوت IDENT_CURRENT و IDENT_SEED در SQL Server

اشتباهات رایج

• ساخت CCI یا NCCI فقط براساس اندازه جدول و بدون تحلیل Queryها.
• استفاده از تابع روی ستون تاریخ و ازبین‌بردن SARGability.
• اجرای REBUILD دوره‌ای روی همه پارتیشن‌ها بدون سنجش حذف و Rowgroup.
• تفسیر یک Snapshot DMV به‌عنوان روند دائمی.
• نادیده گرفتن هزینه DML در NCCI و تحلیل بلادرنگ.
• توقع مرتب‌بودن خروجی از Ordered Columnstore بدون ORDER BY.
• استفاده از IDENT_CURRENT برای دریافت شناسه درج‌شده در Session جاری.

بهترین روش‌ها و نقشه راه اجرا

1. Queryهای مهم را از Query Store یا ابزار مانیتورینگ استخراج کنید.
2. خط پایه CPU، IO، Duration، Plan، Rowgroup و حذف منطقی را ثبت کنید.
3. نوع CCI یا NCCI را براساس نسبت تحلیل به تراکنش انتخاب کنید.
4. الگوی بارگذاری را به Batchهای مناسب تبدیل و Delta Store را پایش کنید.
5. Predicateهای پرتکرار را SARGable بنویسید و Segment Elimination را تأیید کنید.
6. نگه‌داری را شرطی، پارتیشن‌محور و قابل توقف طراحی کنید.
7. پس از هر تغییر، Snapshot و گزارش قبل/بعد تولید کنید.
8. نسخه SQL Server، Edition و Compatibility Level را در مستندات ثبت کنید.

پرسش‌های متداول

کارایی Columnstore دقیقاً چه مشکلی را حل می‌کند؟

کارایی Columnstore حاصل تعامل معماری ستونی، Rowgroup، فشرده‌سازی، Batch Mode، Segment Elimination، الگوی بارگذاری و نگه‌داری هدفمند است. این مقاله مادر نقشه راهی برای تحلیل و بهینه‌سازی این اجزا ارائه می‌کند. انتخاب آن باید براساس نوع بارکاری و معیارهای قابل اندازه‌گیری انجام شود.

برای شروع یادگیری Columnstore Performance چه پیش‌نیازی لازم است؟

آشنایی با Execution Plan، ایندکس‌ها و دستورات پایه T-SQL کافی است. سپس باید مفاهیم Rowgroup و Batch Mode را روی یک پایگاه داده آزمایشی مشاهده کنید.

آیا کارایی Columnstore برای همه پروژه‌های تجاری مناسب است؟

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

هزینه پیاده‌سازی کارایی Columnstore چگونه برآورد می‌شود؟

برآورد باید شامل تحلیل Workload، طراحی آزمایش، زمان مهاجرت، پایش پس از اجرا و آموزش تیم باشد. اندازه جدول و حساسیت توقف سرویس نیز روی زمان انجام پروژه اثر مستقیم دارد.

تفاوت کارایی Columnstore با یک ایندکس یا روش عمومی چیست؟

روش عمومی معمولاً فقط ساختار یا دستور را می‌بیند، اما این موضوع روی Columnstore Index، Segment Elimination و رفتار واقعی موتور تمرکز دارد. مقایسه باید با Plan، IO، CPU و مدت اجرا انجام شود.

چه زمانی برای اجرای پروژه یا دریافت خدمات مرتبط با Columnstore Performance مناسب است؟

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

رایج‌ترین خطا در استفاده از کارایی Columnstore چیست؟

یکی از خطاهای پرتکرار «ساخت ایندکس بدون تحلیل Workload» است. خطای دیگر تصمیم‌گیری بدون مقایسه قبل و بعد و بدون توجه به نسخه SQL Server است.

کارایی Columnstore چه اثری بر Performance دارد؟

اثر اصلی از مسیر IO، CPU و Rowgroup Quality دیده می‌شود. نتیجه می‌تواند بسیار مثبت یا در Workload نامناسب منفی باشد، بنابراین تست کنترل‌شده ضروری است.

بهترین روش عملی برای کارایی Columnstore چیست؟

از یک محیط آزمایشی مشابه Production شروع کنید، خط پایه بسازید و Plan واقعی را بررسی کنید را اجرا کنید و معیارها را در Query Store یا سامانه پایش ثبت نمایید.

سازگاری Columnstore Performance با نسخه‌های SQL Server چگونه است؟

مقاله با درنظرگرفتن قابلیت‌های SQL Server 2016 تا SQL Server 2025 نوشته شده است؛ برخی گزینه‌ها مانند Ordered Columnstore به نسخه‌های جدیدتر وابسته‌اند. پیش از انتشار در Production، Syntax و گزینه‌های قابل پشتیبانی را روی همان Edition، Version و Compatibility Level بررسی کنید.

سؤالات مصاحبه و ارزیابی فنی

1. چرا Rowgroup کوچک می‌تواند نسبت فشرده‌سازی و Batch Mode را تحت‌تأثیر قرار دهد؟
2. تفاوت Segment Elimination و Partition Elimination چیست؟
3. چه زمانی NCCI برای Operational Analytics مناسب‌تر از CCI است؟
4. چگونه deleted_rows را به تصمیم REORGANIZE یا REBUILD تبدیل می‌کنید؟
5. Ordered Columnstore چه مشکلی را حل می‌کند و چرا جای ORDER BY را نمی‌گیرد؟
6. سه DMV اصلی Columnstore چه اطلاعات متفاوتی ارائه می‌کنند؟
7. چرا IDENT_CURRENT در محیط هم‌زمان برای دریافت شناسه INSERT ناامن است؟