تنظیم عملکرد با شاخص ستونی خوشه ای سفارش داده شده

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

در مقابل شاخص ستونی خوشه ای غیر مرتب شده سفارش داده شده

به طور پیش فرض ، برای هر جدول ایجاد شده بدون گزینه شاخص ، یک مؤلفه داخلی (سازنده شاخص) یک شاخص ستونی خوشه ای غیر مرتب شده (CCI) را بر روی آن ایجاد می کند. داده ها در هر ستون به یک بخش جداگانه CCI RowGroup فشرده می شوند. در محدوده مقدار هر بخش ابرداده وجود دارد ، بنابراین بخش هایی که خارج از مرزهای گزاره پرس و جو هستند در هنگام اجرای پرس و جو از دیسک خوانده نمی شوند. CCI بالاترین سطح فشرده سازی داده ها را ارائه می دهد و اندازه بخش ها را برای خواندن کاهش می دهد تا نمایش داده ها بتوانند سریعتر اجرا شوند. با این حال ، از آنجا که سازنده شاخص قبل از فشرده سازی آنها به بخش ها ، داده ها را مرتب نمی کند ، بخش هایی با محدوده با همپوشانی ممکن است رخ دهد ، و باعث می شود نمایش داده ها بخش های بیشتری را از دیسک بخواند و بیشتر به پایان برسد.

هنگام ایجاد یک CCI سفارش داده شده ، موتور اختصاصی SQL استخر داده های موجود را در حافظه توسط کلید (های) سفارش قبل از اینکه سازنده شاخص آنها را در بخش های شاخص فشرده می کند ، مرتب می کند. با داده های مرتب سازی شده ، همپوشانی بخش کاهش می یابد و به نمایش داده ها اجازه می دهد تا از حذف قطعه کارآمدتر و در نتیجه عملکرد سریعتر استفاده کنند زیرا تعداد بخش هایی که از دیسک خوانده می شود کوچکتر است. اگر می توان همه داده ها را به طور همزمان در حافظه مرتب کرد ، می توان از همپوشانی بخش جلوگیری کرد. به دلیل جداول بزرگ در انبارهای داده ، این سناریو اغلب اتفاق نمی افتد.

برای بررسی دامنه بخش برای یک ستون ، دستور زیر را با نام جدول و نام ستون خود اجرا کنید:

در یک جدول CCI سفارش داده شده ، داده های جدید حاصل از همان دسته از DML یا عملیات بارگیری داده ها در آن دسته طبقه بندی می شوند ، هیچ نوع مرتب سازی جهانی در تمام داده های موجود در جدول وجود ندارد. کاربران می توانند CCI سفارش داده شده را برای مرتب کردن تمام داده های موجود در جدول بازسازی کنند. در استخر اختصاصی SQL ، بازسازی شاخص ستونی یک عمل آفلاین است. برای یک جدول تقسیم شده ، بازسازی در یک زمان یک پارتیشن انجام می شود. داده های موجود در پارتیشن که در حال بازسازی است "آفلاین" و در دسترس نیست تا زمانی که بازسازی برای آن پارتیشن کامل نشود.

عملکرد پرس و جو

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

نمایش داده شد با تمام این الگوهای به طور معمول با CCI سفارش داده شده سریعتر اجرا می شود.

1. نمایش داده ها دارای برابری ، نابرابری یا پیش بینی دامنه هستند
2. ستون های محمول و ستون های CCI سفارش داده شده یکسان هستند.

در این مثال ، جدول T1 دارای یک شاخص ستونی خوشه ای است که در دنباله col_c ، col_b و col_a سفارش داده شده است.

عملکرد Query 1 و Query 2 می تواند بیشتر از سایر نمایش داده شدگان از CCI سفارش داده شده بهره مند شود ، زیرا آنها به تمام ستون های CCI سفارش داده شده اشاره می کنند.

عملکرد بارگیری داده ها

عملکرد بارگیری داده ها در یک جدول CCI سفارش داده شده مشابه یک جدول تقسیم شده است. بارگیری داده ها در یک جدول CCI سفارش داده شده می تواند به دلیل عملکرد مرتب سازی داده ها بیشتر از یک جدول CCI غیر مرتب شده باشد ، با این حال نمایش داده شدگان می توانند پس از آن با CCI سفارش داده شده سریعتر اجرا شوند.

در اینجا یک مثال مقایسه عملکرد داده های بارگیری در جداول با طرح های مختلف وجود دارد.

در اینجا یک نمونه مقایسه عملکرد پرس و جو بین CCI و CCI سفارش داده شده است.

همپوشانی بخش را کاهش دهید

تعداد بخش های همپوشانی به اندازه داده ها برای مرتب سازی ، حافظه موجود و حداکثر درجه تنظیم موازی (MAXDOP) در طول ایجاد CCI سفارش داده شده بستگی دارد. در زیر گزینه هایی برای کاهش همپوشانی بخش هنگام ایجاد CCI سفارش داده شده آورده شده است.

از کلاس منابع Xlargerc در DWU بالاتر استفاده کنید تا قبل از اینکه سازنده شاخص داده ها را به بخش ها فشرده کند ، حافظه بیشتری برای مرتب سازی داده ها فراهم شود. هنگامی که در یک بخش شاخص ، مکان فیزیکی داده ها قابل تغییر نیست. هیچ نوع مرتب سازی داده در یک بخش یا بخش ها وجود ندارد.

CCI سفارش داده شده را با گزینه ایجاد کنید (maxdop = 1). هر موضوع مورد استفاده برای ایجاد CCI سفارش داده شده بر روی زیر مجموعه ای از داده ها کار می کند و آن را به صورت محلی مرتب می کند. هیچ مرتب سازی جهانی در داده های مرتب شده توسط موضوعات مختلف وجود ندارد. استفاده از موضوعات موازی می تواند زمان ایجاد CCI سفارش داده شده را کاهش دهد اما بخش های همپوشانی بیشتری نسبت به استفاده از یک نخ واحد ایجاد می کند. با استفاده از یک عمل یک رشته ، بالاترین کیفیت فشرده سازی را ارائه می دهد. مثلا:

در حال حاضر ، در استخرهای اختصاصی SQL در Azure Synapse Analytics ، گزینه MaxDOP فقط در ایجاد یک جدول CCI سفارش داده شده با استفاده از Create Table به عنوان Command پشتیبانی می شود. ایجاد CCI سفارش داده شده از طریق ایجاد فهرست یا ایجاد دستورات جدول ، از گزینه MaxDOP پشتیبانی نمی کند. این محدودیت برای نسخه های SQL Server 2022 و بعدی اعمال نمی شود ، جایی که می توانید MaxDOP را با Create Index یا Create Table مشخص کنید.

• قبل از بارگیری آنها در جداول ، داده ها را با کلید (های) مرتب سازی از قبل مرتب کنید.

در اینجا نمونه ای از توزیع جدول CCI سفارش داده شده است که دارای بخش صفر است که در زیر توصیه های فوق با هم همپوشانی دارد. جدول CCI سفارش داده شده در یک پایگاه داده DWU1000C از طریق CTA از یک جدول 20 گیگابایتی با استفاده از MaxDOP 1 و Xlargerc ایجاد می شود. CCI در یک ستون Bigint بدون نسخه کپی سفارش داده می شود.

CCI سفارش داده شده را در جداول بزرگ ایجاد کنید

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

1. پارتیشن هایی را در جدول بزرگ هدف (به نام Table_A) ایجاد کنید.
2. یک جدول CCI با سفارش خالی (به نام Table_B) با همان جدول و طرح پارتیشن به عنوان Table_A ایجاد کنید.
3. یک پارتیشن را از Table_A به Table_B تغییر دهید.
4. برای بازسازی پارتیشن تغییر یافته در Table_B ، شاخص Alter را در پارتیشن بازسازی کنید.
5. مرحله 3 و 4 را برای هر پارتیشن در Table_A تکرار کنید.
6. هنگامی که تمام پارتیشن ها از Table_A به Table_B تغییر یافته و بازسازی شده اند ، Drop Table_A و Table_B را به Table_A تغییر نام دهید.

برای یک جدول استخر SQL اختصاصی با CCI سفارش داده شده ، بازسازی شاخص Alter داده ها را با استفاده از TEMPDB مجدداً مرتب می کند. نظارت بر TEMPDB در طول عملیات بازسازی. اگر به فضای TempDB بیشتری احتیاج دارید ، استخر را مقیاس کنید. پس از اتمام بازسازی فهرست ، مقیاس را پایین بیاورید.

برای یک جدول استخر SQL اختصاصی با CCI سفارش داده شده ، سازماندهی مجدد شاخص تغییر داده ها را مجدداً مرتب نمی کند. برای متوسل شدن داده ها ، از بازسازی فهرست Alter استفاده کنید.

برای کسب اطلاعات بیشتر در مورد تعمیر و نگهداری CCI سفارش داده شده ، به بهینه سازی شاخص های ستون خوشه ای خوشه ای مراجعه کنید.

تفاوت های ویژگی در قابلیت های SQL Server 2022

SQL Server 2022 (16. X) فهرست های ستونی خوشه ای سفارش داده شده مشابه با ویژگی موجود در استخرهای اختصاصی SQL Azure Synapse را معرفی کرد.

• در حال حاضر ، فقط SQL Server 2022 (16. x) و نسخه های بعدی از ستون های خوشه ای Clustered Pervanced Pervanced Section قابلیت حذف بخش برای انواع داده های رشته ، باینری و GUID و نوع داده DateTimeOffset برای مقیاس بیشتر از دو پشتیبانی می کنند. پیش از این ، این حذف بخش مربوط به انواع داده های عددی ، تاریخ و زمان و نوع داده DateTimeOffset با مقیاس کمتر یا مساوی با دو است.
• در حال حاضر ، فقط SQL Server 2022 (16. x) و نسخه های بعدی از حذف ستون Clustered Columnstore RowGroup برای پیشوند Predicates مانند ، به عنوان مثال ستون مانند "رشته ٪" پشتیبانی می کنند. حذف بخش برای استفاده غیر پیش فرض مانند ستون مانند "٪ رشته" پشتیبانی نمی شود.

مثال ها

الف) برای بررسی ستون های سفارش داده شده و سفارش سفارش:

ب - برای تغییر ترتیب ستون ، ستون ها را از لیست سفارش اضافه یا حذف کنید ، یا از CCI به CCI سفارش داده شده تغییر دهید: < SPAN> در حال حاضر ، فقط SQL Server 2022 (16. x) و نسخه های بعدی از ستون های خوشه ای Clustered Perficed Perfulled Pervance Degment Depalment برای حذف بخش پشتیبانی می کنند. انواع داده های رشته ، باینری و GUID و نوع داده DateTimeOffset برای مقیاس بیشتر از دو. پیش از این ، این حذف بخش مربوط به انواع داده های عددی ، تاریخ و زمان و نوع داده DateTimeOffset با مقیاس کمتر یا مساوی با دو است.