فصل ۳

کارایی و مقیاس‌پذیری

Microsoft SQL Server 2012 نوع جدیدی از ایندکس را با نام Columnstore معرفی می‌کند. در مراحل توسعهٔ SQL Server 2012 و هنگام انتشار نسخه‌های Community Technology Preview یا CTP، این ویژگی با نام پروژهٔ Apollo شناخته می‌شد. ترکیب این ایندکس جدید با بهبودهای پیشرفتهٔ پردازش پرس‌وجو، بهینه‌سازی کارایی بسیار سریعی برای بارهای کاری انبار داده و پرس‌وجوهای مشابه ارائه می‌کند. در بسیاری از موارد، کارایی پرس‌وجوی انبار داده ده‌ها تا صدها برابر بهتر شده است.

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

• ایندکس Columnstore چیست؟
• چگونه سرعت پرس‌وجوهای انبار داده را به‌طور چشمگیری افزایش می‌دهد؟
• مدیر پایگاه‌داده چه زمانی باید آن را بسازد؟
• آیا بهترین‌روش‌های تثبیت‌شده‌ای برای استقرار آن وجود دارد؟

اکنون سازوکار داخلی را بررسی می‌کنیم تا ببینیم سازمان‌ها چگونه از افزایش قابل‌توجه کارایی انبار داده با فناوری جدید و درون‌حافظه‌ای Columnstore بهره می‌برند؛ فناوری‌ای که به مدیریت حجم رو‌به‌رشد داده نیز کمک می‌کند.

مروری بر ایندکس Columnstore

با افزایش داده‌ای که از دستگاه‌ها، برنامه‌ها و خدمات گوناگون ثبت می‌شود، سازمان‌های امروزی برای ادارهٔ موفق کسب‌وکار خود باید حجم بسیار بزرگی از داده را ذخیره کنند. با ادامهٔ رشد داده‌های موردنظر کاربران، استفاده از ابزارهای سنتی برای ثبت، مدیریت و پردازش داده در زمان قابل‌قبول دشوارتر می‌شود. برای مثال، حجم داده توان انبارهای داده برای اجرای به‌موقع پرس‌وجوها را تحت فشار قرار می‌دهد و زمان زیادی صرف تنظیم پرس‌وجو، طراحی و نگه‌داری ایندکس‌ها برای رسیدن به کارایی قابل‌قبول می‌شود. گاهی فاصلهٔ اجرای پرس‌وجو تا بازگشت نتیجه آن‌قدر طولانی است که سازمان موضوع درخواست اصلی را به‌سختی به یاد می‌آورد. تأخیری که باعث ازدست‌رفتن فرصت کسب‌وکار می‌شود نیز به همان اندازه زیان‌بار است.

تیم‌های Query Processing و Storage در گروه محصول SQL Server برای حل این مشکلات، روی فناوری‌هایی کار کردند که مجموعه‌داده‌های بسیار بزرگ را سریع و دقیق بخوانند و داده را در زمان مناسب به اطلاعات و دانش مفید تبدیل کنند. تیم Query Processing پژوهش‌های دانشگاهی دربارهٔ نمایش ستونی داده را بررسی و قابلیت‌های بهتر اجرای پرس‌وجو برای انبار داده را تحلیل کرد. این تیم با گروه Analysis Services نیز همکاری کرد تا پیاده‌سازی ستونی PowerPivot در SQL Server 2008 R2 را بهتر بشناسد. نتیجهٔ پژوهش‌ها، ایندکس Columnstore جدید و بهینه‌سازی پرس‌وجو بر پایهٔ اجرای برداری بود که کارایی پرس‌وجوی انبار داده را به‌طور چشمگیری افزایش می‌دهد.

در توسعهٔ ایندکس جدید، تیم اهدافی داشت: کاربر نهایی باید با مجموعه‌داده‌های کوچک و بزرگ تجربه‌ای تعاملی و مثبت داشته باشد و زمان پاسخ داده سریع باشد. این هدف برای پرس‌وجوهای موردی و گزارش‌گیری نیز صدق می‌کند. مدیران شاید بتوانند نیاز به تنظیم دستی پرس‌وجو، جدول‌های خلاصه، نمای ایندکس‌شده و در برخی موارد مکعب‌های OLAP را کاهش دهند. همهٔ این اهداف هزینهٔ کل مالکیت (Total Cost of Ownership یا TCO) را کاهش می‌دهند، زیرا هزینهٔ سخت‌افزار پایین می‌آید و افراد کمتری برای انجام کار لازم‌اند.

مبانی و معماری Columnstore

پیش از طراحی، پیاده‌سازی یا مدیریت ایندکس Columnstore بهتر است شیوهٔ کار، نحوهٔ ذخیرهٔ داده و نوع پرس‌وجوهای بهره‌مند از آن را بشناسید.

داده در Columnstore چگونه ذخیره می‌شود؟

در جدول‌ها و ایندکس‌های سنتی، یعنی Heapها و B-treeها، SQL Server داده را به‌شکل ردیفی در صفحه‌ها ذخیره می‌کند؛ این مدل Row Store نام دارد. Column Store مانند چرخاندن مدل سنتی به اندازهٔ ۹۰ درجه است: همهٔ مقادیر یک ستون به‌صورت پیوسته و فشرده ذخیره می‌شوند. ایندکس Columnstore به‌جای ذخیرهٔ چند ردیف در هر صفحه، هر ستون را در مجموعه‌ای جداگانه از صفحه‌های دیسک ذخیره می‌کند.

برای مقایسه، جدول ۳-۱ شامل شناسه، نام، شهر و ایالت کارکنان است.

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

تفاوت اصلی آن است که Columnstore دادهٔ هر ستون را گروه‌بندی و ذخیره می‌کند و سپس ستون‌ها ایندکس کامل را می‌سازند؛ ایندکس سنتی دادهٔ هر ردیف را گروه‌بندی و ذخیره می‌کند و سپس ردیف‌ها ایندکس را تشکیل می‌دهند. اکنون اثر این مدل ذخیره‌سازی و بهینه‌سازی‌های پیشرفتهٔ پرس‌وجو را بر سرعت بازیابی بررسی می‌کنیم.

Columnstore چگونه سرعت پرس‌وجو را افزایش می‌دهد؟

مدل ستونی به چند دلیل سرعت پرس‌وجوی انبار داده را افزایش می‌دهد. نخست، داده‌های یک ستون شباهت بیشتری به یکدیگر دارند و در نتیجه نسبت به داده‌های ردیفی بسیار بهتر فشرده می‌شوند. ایندکس Columnstore از الگوریتم VertiPaq استفاده می‌کند که در SQL Server 2008 R2 فقط در Analysis Services برای PowerPivot موجود بود. فشرده‌سازی VertiPaq از فشرده‌سازی سنتی ردیف و صفحه در Database Engine بهتر است و نسبت‌هایی تا ۱۵ به ۱ به دست آمده است. دادهٔ فشرده I/O کمتری می‌خواهد، زیرا حجم انتقال از دیسک به حافظه کاهش می‌یابد. کاهش I/O به پاسخ سریع‌تر منجر می‌شود و فضای حافظهٔ لازم برای Working Set پرس‌وجو را نیز کم می‌کند.

دوم، SQL Server هنگام اجرای پرس‌وجو فقط ستون‌های موردنیاز را واکشی می‌کند. در شکل ۳-۱ جدول ۱۵ ستون دارد، اما چون پرس‌وجو فقط به ستون‌های ۷، ۸ و ۹ نیاز دارد، تنها همین سه ستون بازیابی می‌شوند.

پرس‌وجوهای انبار داده معمولاً فقط ۱۰ تا ۱۵ درصد ستون‌های جدول‌های Fact بزرگ را لمس می‌کنند؛ بنابراین واکشی ستون‌های انتخابی حدود ۸۵ تا ۹۰ درصد I/O را کاهش می‌دهد و کارایی را افزایش می‌دهد.

پردازش Batch Mode

فناوری پیشرفتهٔ دیگری برای پردازش پرس‌وجوهای Columnstore نیز سرعت را افزایش می‌دهد. دادهٔ ستون‌ها با فناوری برداری بسیار کارآمد در Batchها پردازش می‌شود. در Plan اجرای پرس‌وجو، گروه‌هایی از عملگرها در Batch Mode اجرا می‌شوند. همهٔ عملگرها Batch Mode نیستند، اما مهم‌ترین عملگرهای انبار داده مانند Hash Join و Hash Aggregation این‌گونه‌اند. الگوریتم‌ها برای معماری سخت‌افزار جدید، هسته‌های بیشتر و RAM افزوده بهینه شده‌اند و موازی‌سازی را بهتر می‌کنند. در نتیجه، Batch Mode از Row Mode سنتی بهتر است.

سازمان‌دهی فضای ذخیره‌سازی Columnstore

دادهٔ ایندکس به Segmentها تقسیم می‌شود. هر Segment دادهٔ یک ستون را برای مجموعه‌ای تا حدود یک میلیون ردیف در بر می‌گیرد. Segmentهای مربوط به یک مجموعه ردیف، یک Row Group را تشکیل می‌دهند. SQL Server به‌جای ذخیرهٔ صفحه‌به‌صفحه، Row Group را به‌عنوان یک واحد ذخیره می‌کند. هر Segment در یک Large Object یا LOB جدا ذخیره می‌شود؛ بنابراین واحد خواندن از دیسک و انتقال میان دیسک و حافظه یک Segment است.

پشتیبانی Columnstore در SQL Server 2012

ایندکس‌های Columnstore و حالت اجرای Batch Query عمیقاً در SQL Server 2012 یکپارچه‌اند و با بسیاری از ویژگی‌های Database Engine کار می‌کنند. برای مثال، پس از ایجاد Columnstore همچنان می‌توان از AlwaysOn Availability Groups، AlwaysOn FCI، Database Mirroring، Log Shipping و ابزارهای مدیریتی SQL Server Management Studio استفاده کرد.

انواع دادهٔ رایج پشتیبانی‌شده عبارت‌اند از:

• char و varchar.
• همهٔ انواع عدد صحیح: int، bigint، smallint و tinyint.
• real و float.
• رشته.
• money و smallmoney.
• همهٔ انواع تاریخ و زمان به‌جز datetimeoffset با دقت بیشتر از ۲.
• decimal و numeric با دقت حداکثر ۱۸ رقم.

برای هر جدول فقط یک ایندکس Columnstore می‌توان ساخت.

محدودیت‌ها

• فشرده‌سازی PAGE یا ROW را می‌توان روی جدول پایه فعال کرد، اما روی خود Columnstore نه.
• جدول و ستون نمی‌توانند در توپولوژی Replication مشارکت کنند.
• جدول‌ها و ستون‌های دارای Change Data Capture نمی‌توانند عضو Columnstore باشند.
• ایندکس را نمی‌توان روی decimal بیشتر از ۱۸ رقم، binary، varbinary، BLOB، CLR و (n)varchar(max) ایجاد کرد.

• uniqueidentifier و datetimeoffset با دقت بیشتر از ۲ نیز پشتیبانی نمی‌شوند.
• نگه‌داری جدول: جدول دارای Columnstore خواندنی است اما مستقیماً به‌روزرسانی نمی‌شود؛ زیرا این ایندکس برای بارهای کاری عمدتاً خواندنی انبار داده طراحی شده است.
• پردازش پرس‌وجو: همهٔ پرس‌وجوهای فقط‌خواندنی T-SQL قابل اجرا هستند، اما چون Batch Mode فقط با برخی عملگرها کار می‌کند، افزایش سرعت پرس‌وجوها متفاوت است.
• ستون دارای دادهٔ FILESTREAM نمی‌تواند عضو باشد.
• دستورهای INSERT، UPDATE، DELETE و MERGE روی جدول دارای Columnstore مجاز نیستند.
• بیش از ۱۰۲۴ ستون پشتیبانی نمی‌شود.
• فقط Columnstore غیرخوشه‌ای مجاز است و نوع Filtered پشتیبانی نمی‌شود.
• ستون‌های محاسباتی و Sparse نمی‌توانند عضو باشند.
• Columnstore روی Indexed View ساخته نمی‌شود.

ملاحظات طراحی و بارگذاری داده

برخی پرس‌وجوها بسیار بیشتر از دیگران شتاب می‌گیرند؛ بنابراین باید بدانید چه زمانی Columnstore بسازید و چه زمانی نسازید.

چه زمانی Columnstore بسازیم؟

• هنگامی که بار کاری عمدتاً خواندنی، به‌ویژه انبار داده، است.
• هنگامی که گردش کار اجازه می‌دهد برای دادهٔ جدید از پارتیشن‌بندی یا راهبرد حذف و بازسازی ایندکس استفاده شود؛ معمولاً در پنجرهٔ نگه‌داری دوره‌ای یا با انتقال جدول Staging به پارتیشن خالی.
• هنگامی که بیشتر پرس‌وجوها الگوی Star Join دارند یا حجم بزرگی از داده را اسکن و تجمیع می‌کنند.

• هنگامی که به‌روزرسانی‌ها عمدتاً دادهٔ جدید را Append می‌کنند و می‌توان آن‌ها را با جدول Staging و Partition Switching بارگذاری کرد.

Columnstore برای جدول‌های Fact بزرگ و جدول‌های Dimension بزرگ با میلیون‌ها ردیف مناسب است.

چه زمانی Columnstore نسازیم؟

• دادهٔ جدول دائماً نیاز به به‌روزرسانی دارد.
• Partition Switching یا بازسازی ایندکس با گردش کار کسب‌وکار سازگار نیست.
• پرس‌وجوهای کوچک Lookup بسیار پرتکرارند. با این حال ممکن است Columnstore همچنان مفید باشد، زیرا Query Optimizer با Statistics به‌روز می‌تواند B-tree سنتی را انتخاب کند.
• آزمایش روی بار کاری هیچ سودی نشان نمی‌دهد.

بارگذاری دادهٔ جدید

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

• غیرفعال‌کردن ایندکس: ابتدا Columnstore را Disable کنید، داده را به‌روزرسانی کنید و سپس ایندکس را Rebuild کنید. برای این روش پنجرهٔ نگه‌داری لازم است و زمان موردنیاز باید در محیط نمونه آزمایش شود.
• پارتیشن‌بندی و Partition Switching: این روش زیرمجموعه‌های داده را سریع و کارآمد مدیریت و منتقل می‌کند و با Columnstore کاملاً پشتیبانی می‌شود.

برای بارگذاری داده با پارتیشن‌بندی:

1. یک پارتیشن خالی برای دادهٔ جدید داشته باشید.
2. داده را در جدول Staging خالی بارگذاری کنید.
3. جدول Staging را به پارتیشن خالی Switch کنید.

برای به‌روزرسانی دادهٔ موجود:

1. پارتیشن حاوی داده را مشخص کنید.
2. آن را به جدول Staging خالی Switch کنید.
3. Columnstore را روی جدول Staging غیرفعال کنید.
4. داده را به‌روزرسانی کنید.
5. ایندکس را روی جدول Staging بازسازی کنید.
6. جدول Staging را به پارتیشن اصلی که خالی مانده بود برگردانید.

• UNION ALL: دادهٔ اصلی را در جدول Fact دارای Columnstore نگه دارید، جدول ثانویه‌ای برای افزودن یا ویرایش بسازید و با UNION ALL همهٔ داده را برگردانید. دادهٔ جدول ثانویه را دوره‌ای با Partition Switching یا Disable/Rebuild به جدول اصلی منتقل کنید. برخی پرس‌وجوها در این روش ممکن است از حالت یک‌جدولی کندتر باشند.

ایجاد ایندکس Columnstore

ایجاد آن شبیه دیگر ایندکس‌های SQL Server است و با رابط SSMS یا Transact-SQL انجام می‌شود. بسیاری رابط گرافیکی را ترجیح می‌دهند تا نام همهٔ ستون‌ها را تایپ نکنند. معمولاً باید همهٔ ستون‌های پشتیبانی‌شدهٔ جدول را افزود، هرچند الزامی نیست. Columnstore خوشه‌ای در این نسخه مجاز نیست و همهٔ این ایندکس‌ها Nonclustered هستند.

ایجاد با SQL Server Management Studio

1. در SSMS با Object Explorer به Database Engine متصل شوید.
2. نمونه، Databases، پایگاه‌داده و جدول موردنظر را باز کنید.
3. روی پوشهٔ Index راست‌کلیک و New Index سپس Non-Clustered Columnstore Index را انتخاب کنید.
4. در زبانهٔ General نام ایندکس را وارد و Add را انتخاب کنید.
5. در Select Columns ستون‌ها را انتخاب و OK کنید.
6. در صورت نیاز Options، Storage و Extended Properties را تنظیم کنید؛ در غیر این صورت برای ایجاد ایندکس OK را بزنید.

ایجاد با Transact-SQL

نحو ایجاد ایندکس به‌شکل زیر است:

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name
        ON <object> ( column [ ,...n ] )
        [ WITH ( <column_index_option> [ ,...n ] ) ]
        [ ON {
                { partition_scheme_name ( column_name ) }
                | filegroup_name
                | "default"
              }
        ]
    [ ; ]
    <object> ::=
    {
        [database_name. [schema_name ] . | schema_name . ]
          table_name
    {

    <column_index_option> ::=
    {
          DROP_EXISTING = { ON | OFF }
        | MAXDOP = max_degree_of_parallelism
    }

• NONCLUSTERED نشان می‌دهد ایندکس نمایش ثانویه‌ای از داده است.
• COLUMNSTORE نوع ایندکس را مشخص می‌کند.
• index_name نام یکتای ایندکس در جدول یا View است.
• column ستون‌های عضو را مشخص می‌کند؛ سقف ۱۰۲۴ ستون است.
• ON partition_scheme_name(column_name) طرح پارتیشن و ستون پارتیشن‌بندی را تعیین می‌کند. نوع، طول و دقت ستون باید با تابع پارتیشن تطابق داشته باشد. اگر ذکر نشود و جدول پارتیشن‌بندی شده باشد، ایندکس از همان طرح و ستون جدول پایه استفاده می‌کند.

• ON filegroup_name فایل‌گروه مقصد ایندکس را مشخص می‌کند.
• ON "default" ایندکس را روی فایل‌گروه پیش‌فرض می‌سازد.
• DROP_EXISTING=ON ایندکس موجود را حذف می‌کند؛ در حالت OFF وجود ایندکس خطا می‌دهد.
• MAXDOP تعداد پردازنده‌های اجرای موازی را در طول عملیات ایندکس محدود می‌کند. مقدار ۱ اجرای موازی را متوقف، مقدار بزرگ‌تر از ۱ سقف پردازنده و مقدار ۰ تعداد واقعی پردازنده‌ها را نشان می‌دهد.

استفاده از Columnstore

برای بررسی اینکه ایندکس واقعاً پرس‌وجو را شتاب داده است، Plan اجرا را مشاهده کنید. در نمایش گرافیکی Plan، نماد جدید Columnstore Index Scan Operator نشان می‌دهد برای Scan از Columnstore استفاده شده است.

با انتخاب نماد، شاخص‌ها و هزینه‌های بیشتری دیده می‌شود. در شکل ۳-۵، Physical Operation برابر Columnstore Index Scan و Storage برابر Columnstore است.

استفاده از Hint

اگر باور دارید پرس‌وجو از Columnstore سود می‌برد ولی Plan از آن استفاده نمی‌کند، با Hint به‌شکل WITH (INDEX(<indexname>)) استفاده از ایندکس را اجبار کنید.

SELECT DISTINCT (SalesTerritoryKey)
    FROM dbo.FactResellerSales WITH (INDEX (Non-ClusteredColumnStoreIndexSalesTerritory)
    GO

نمونهٔ بعد استفاده از یک ایندکس متفاوت، مثلاً B-tree خوشه‌ای، را به‌جای Columnstore اجبار می‌کند. فرض کنید جدول دو ایندکس ClusteredIndexSalesTerritory و Non-ClusteredColumnStoreIndexSalesTerritory دارد.

SELECT DISTINCT (SalesTerritoryKey)
    FROM dbo.FactResellerSales with (index (ClusteredIndexSalesTerritory)
    GO

نمونهٔ نهایی، نادیده‌گرفتن Columnstore را اجبار می‌کند:

SELECT DISTINCT (SalesTerritoryKey)
    FROM dbo.FactResellerSales
    Option (ignore_nonclustered_columnstore_index)
    GO

مشاهدات و بهترین‌روش‌ها

گروه محصول SQL Server برای کاهش زمان پردازش پرس‌وجوهای انبار داده سرمایه‌گذاری عمده‌ای انجام داده است. تیم‌های Query Optimization، Query Execution و Storage Engine با SQL Server Performance Team، SQLCAT و Microsoft Technology Centers، این فناوری را با مشتریان متعدد آزمایش کرده‌اند. مشتریان نتیجه را «به‌طرز مضحکی سریع» و «شگفت‌آور» توصیف کرده‌اند.

• پرس‌وجوها را تا حد امکان با نقطهٔ بهینهٔ Columnstore، به‌ویژه Star Join، هماهنگ کنید.
• در صورت امکان از سازه‌هایی مانند Outer Join، Union و Union All که سود را کاهش می‌دهند دوری کنید.
• تا حد امکان همهٔ ستون‌ها را در ایندکس قرار دهید.
• در صورت امکان دقت decimal/numeric را به ۱۸ یا کمتر تبدیل کنید.
• ساخت ایندکس حافظهٔ زیادی می‌خواهد؛ حافظهٔ سامانه را متناسب انتخاب کنید. تخمین درخواست حافظه:

• تا حد امکان مطمئن شوید پرس‌وجو از Batch Mode استفاده می‌کند؛ این عامل بسیار مهم و سودمند است.
• در صورت امکان از انواع عدد صحیح استفاده کنید، زیرا نمایش فشرده‌تر و فرصت بیشتری برای فیلتر زودهنگام دارند.
• برای آسان‌شدن به‌روزرسانی‌ها، پارتیشن‌بندی جدول را در نظر بگیرید.
• حتی اگر پرس‌وجو نتواند Batch Mode استفاده کند، کاهش I/O با Columnstore همچنان مزیت کارایی دارد.

این صفحه در نسخهٔ اصلی فاقد محتوای متنی است.