Column Store Index یکی از ویژگیهای جدید SQL Server 2012 می باشد، که کارایی Query های قایل اجرا روی دیتابیس‌های با حجم داده ای بسیار بالا را (که اصطلاحا به آنها Data Warehouse یا انبار داده گویند)، چندین برابر بهبود بخشیده است. 

 قبل از توضیح در مورد Column Store مختصری در مورد نحوه ذخیره سازی داده‌ها در SQL Server می پردازیم. می‌توان گفت در SQL Server دو روش ذخیره سازی وجود دارد،یکی بصورت ردیفی که اصطلاحا به آن  Row Storeیا  Row-Wise گویند، و دیگری بصورت ستونی که اصطلاحا به آن Column Store گویند. 

در روش ذخیره سازی Row Store، مقادیر ستونها در یک سطر بصورت متوالی ذخیره می‌شوند، در این روش ذخیره سازی از ساختار B-Tree یا Heap استفاده می‌شود.

یادآوری: در ساختار B-Tree، یک گره Root وجود دارد، و گره بعد از Root گره ای است که آدرس گره راست بعدی و آدرس گره چپ بعدی را در خود نگه می‌دارد.

شکل زیر نمای یک درخت B-Tree می‌باشد:

جهت کسب اطلاعات بیشتر درمورد ساختار B-Tree 

یادآوری: وقتی در یک جدول، ایندکسی از نوع Clustered ایجاد نماییم، SQL Server، در ابتدا یک کپی از جدول ایجاد و داده‌های جدول را از نو مرتب می‌نماید، و ساختار صفحه ریشه و دیگر صفحات را ایجاد می‌کند و سپس جدول اصلی را حذف می‌نماید. به جدولی که Clustered Index ندارد، اصطلاحا Heap گویند.

  برخلاف ذخیره سازی Row Store، در ذخیره سازی Column Store، داده‌ها بصورت ستونی ذخیره می‌شوند،در این روش داده ها، فشرده سازی می‌شوند و اینکار باعث می‌شود،در زمان درخواست یک Query، نیاز به Disk I/o به حداقل برسد، در نتیجه، زمان و سرعت پاسخگویی به پرس و جو‌ها بسیار افزایش می‌یابد.

شکل زیر نحوه ذخیره سازی داده ها،بصورت Row Store را نمایش می‌دهد:

  شکل بالا ذخیره سازی داده ها، در ساختار B-Tree یا Heap را نمایش می‌دهد، در شکل فوق یک جدول چهار ستونی با N سطر (Row) در نظر گرفته شده است.بطوریکه ستونهای هر Row بطور متوالی در یک صفحه (Page) یکسان ذخیره می‌شوند.

 شکل زیر نحوه ذخیره سازی داده ها،بصورت Column Store را نمایش می‌دهد:

  مطابق شکل،ستونهای مربوط به هر Row،همگی در یک صفحه (Page) یکسان ذخیره شده اند. به عنوان مثال ستون C1 که مربوط به سطر اول (Row1) می‌باشد، با ستون C1 که مربوط به سطر دوم (Row2) می‌باشد، در یک ستون و در یک صفحه (Page1) ذخیره شده اند، و الی آخر ...

   سئوال: یکبار دیگر به هردو شکل با دقت نگاهی بیاندازید، عمده تفاوت آنها در چیست؟

   جواب: درست حدس زدید، تفاوت بارز بین دو روش Column Store و Row Store در نحوه ذخیره سازی داده‌ها می‌باشد. بطور مثال، فرض کنید،در روش ذخیره سازی Row Store، به دنبال مقادیری از ستون C2 می‌باشید، SQL Server می‌بایست کل رکورد‌های جدول (منظور همه Row‌ها در همه Page ها)را Scan نماید، تا مقادیر مربوط به ستون C2 را بدست آورد.درحالیکه در روش ذخیره سازی Column Store، جهت یافتن مقادیر ستون C2، نیازی به Scan نمودن کل جدول نیست،بلکه SQL Server فقط به Scan نمودن ستون دوم (C2) یا Page2 بسنده می‌نماید.همین امر باعث افزایش چندین برابری، زمان پاسخگویی به هر Query می‌شود.

  سئوال: در روش ذخیره سازی Column Store، چگونه مصرف حافظه بهینه می‌شود؟

  جواب: واضح است،  که در روش SQL Server، Row Store مجبور است، برای بدست آوردن داده‌های مورد نظرتان،کل اطلاعات جدول را وارد حافظه نماید(اطلاعات اضافه ای که به هیچ وجه بدرد، نتیجه پرس و جوی شما نمی‌خورد)، و شروع به Scan داده‌های مد نظر شما می‌نماید.بطوریکه در روش SQL Server، Column Store، فقط ستون داده‌های مورد پرس و جو را در حافظه قرار می‌دهد.(در واقع فقط داده هایی را در حافظه قرار می‌دهد، که شما به آن نیاز دارید)،بنابراین،طبیعی است که در روش Column Store مقدار حافظه کمتری نسبت به روش Row Store در هنگام اجرای Query استفاده می‌شود. به عبارت دیگر می‌توان گفت که در روش Column Store به دلیل، به حداقل رساندن استفاده از Disk I/o سرعت و زمان پاسخگویی به پرس و جو‌ها چندین برابر می‌شود.

  برای درک بیشتر Row Store و Column Store مثالی می‌زنیم:

   فرض کنید،قصد بدست آوردن ستونهای C1 و C2 از جدول A را داریم، بنابراین خواهیم داشت:

Select C1, C2 from A

روش Row Store:

    در این روش همه صفحات دیسک (مربوط به جدول A) درون حافظه قرار داده می‌شود، یعنی علاوه بر ستونهای C1 و C2، اطلاعات مربوط به ستونهای C3 و C4 نیز درون حافظه قرار می‌گیرد،بطوریکه مقادیر ستونهای C3 و C4 به هیچ وجه مورد قبول ما نیست، و در خروجی پرس و جوی ما تاثیری ندارد، و فقط بی جهت حافظه اشغال می‌نماید.

روش Column Store:

  در این روش فقط صفحات مروبط به ستون C1 و C2 در حافظه قرار می‌گیرد.(منظور Page1 و Page2 می‌باشد) بنابراین فقط اطلاعات مورد نیاز در خروجی، در حافظه قرار می‌گیرد.

•  از دیگر مزایای استفاده از روش Column Store، فشرده سازی داده می‌باشد،برای درک بیشتر توضیح می‌دهم:

چه موقع می‌توانیم از Column Store استفاده نماییم:

   در تعریف Column Store گفته بودم، روش فوق، جهت بهبود بخشیدن به زمان و سرعت پاسخگویی به Query‌های اجرا شده روی دیتابیس‌های با حجم داده ای بسیار بالا(Data Warehouse ) می‌باشد، به بیان ساده‌تر Column Store را روی دیتابیس‌های offline یا دیتابیسهایی که صرفا جهت گزارش گیری مورد استفاده قرار می‌گیرند، تنظیم می‌نمایند.در واقع با تنظیم Column Store Index روی Database‌های بزرگ مانند Database‌های بانک‌ها که حجم داده ای میلیونی در جداول آنها وجود دارد، سرعت پاسخگویی Query ها، چندین برابر افزایش می‌یابد.

•      در یک جدول می‌توانید، هم Column Store Index داشته باشید و هم یک Row Store Index (منظور یک  Clustered Index می باشد)
  
• Syntax برای ایجاد  Column Store Index به شرح ذیل می‌باشد:

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
 }

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MyFirstName_ColumnStore]
ON [Test]
(Firstname)

ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] DISABLE

ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] Rebuild

1. پروسس‌هایی که در relational engine رخ می‌دهند
2. پروسس‌هایی که در storage engine رخ می‌دهند

در relational engine، هر کوئری pars شده و سپس بوسیله query optimizer پردازش و پلن اجرایی (execution plan) آن که بفرمت باینری است، ایجاد می‌شود و به storage engine ارسال می‌گردد. در storage engine پروسس‌هایی مانند قفل گذاری، نگهداری ایندکس‌ها و تراکنش‌ها رخ می‌دهد. هنگامیکه اس کیو ال سرور کوئری را دریافت می‌نمایند، آن را بلافاصله به relational engine ارسال می‌کند. سپس نحو (syntax) آن بررسی می‌شود؛ این عمل  query parsing نامیده می‌شود. خروجی عملیات پارسر، یک ساختار درختی (query tree) است. این ساختار درختی مشخص کننده مراحل لازم جهت اجرای کوئری ارائه شده می‌باشد.
اگر یک کوئری شامل DML نباشد (مانند ساخت جدول)، علمیات بهبود برروی آن صورت نخواهد گرفت. ولی در صورتیکه کوئری ارسالی، DML باشد، درخت اشاره شده در بالا به algebrizer فرستاده می‌شود که وظیفه آن تفسیر و بررسی کلیه نام اشیاء، جداول و ستون‌های اشاره شده در متن کوئری است. فرآیند algebrizer بسیار مهم و حیاتی است؛ بدلیل اینکه در کوئری ممکن است اشاره کننده‌هایی به اشیایی باشند که در بانک اطلاعاتی موجود نیست. خروجی algebrizer یک query processor tree باینری است که به بهبود دهنده کوئری ارسال می‌گردد. 

معرفی Query Optimizer (بهبود دهنده پرس و جو)

بهبود دهنده، بهترین مسیر اجرای کوئری را مشخص می‌کند. این بهبود دهنده است که مشخص می‌کند که اطلاعات بوسیله ایندکس دریافت شوند، یا اینکه از چه اتصالی استفاده شود و الی آخر. این تصمیمات براساس محاسبات هزینه‌های (میزان پردازش لازم cpu و I/O) پلن اجرایی صورت خواهد پذیرفت. بهمین دلیل به پلن cost-based نیز شناخته می‌شود.
هنگامیکه کوئری ساده‌ای مانند دریافت اطلاعات از یک جدول، که بر روی آن ایندکس گذاری انجام نشده‌است، ارسال شود، بهبود دهنده بجای مشخص نمودن یک پلن مناسب بهینه، از یک پلن ساده (trivial) استفاده می‌کند. ولی برعکس در صورتیکه کوئری trivial نباشد (یعنی مثلا کوئری به گونه‌ای باشد که از ایندکس‌ها به شکل صحیحی استفاده شده باشند)، بهبود دهنده یک پلن مناسب را براساس اطلاعات آماری مهیا شده در اس کیو ال سرور، تولید و انتخاب می‌نماید.

اطلاعات آماری از ستون‌ها و ایندکس‌ها جمع آوری می‌شود. این اطلاعات شامل نحوه توزیع داده، یکتایی و انتخاب شوندگی است. این اطلاعات توسط یک histogram ارائه می‌شود. اگر اطلاعات آماری برای یک ستون و یا ایندکس وجود داشته باشد، بهبود دهنده از آن‌ها برای محاسبات خود استفاده خواهد کرد. اطلاعات آماری بصورت خودکار برای تمام ایندکس‌ها و یا هر ستونی که بشود بر روی آن‌ها where یا join نوشت، فراهم خواهد شد.
بهبود دهنده با مقایسه پلن‌ها براساس بررسی تفاوت‌های انواع joinها، چیدمان مجدد ترتیب join و بررسی ایندکس‌های مختلف و سایر فعالیت‌های دیگر، پلن مناسب را انتخاب و از آن استفاده می‌کند. در طی هر کدام از فعالیت‌های اشاره شده، زمان اجرای آن‌ها نیز تخمین زده (estimated cost) خواهد شد و در پایان، زمان کل تخمینی بدست خواهد آمد و بهبود دهنده از این زمان برای انتخاب پلن مناسب بهره خواهد برد. باید توجه داشت که این زمان تقریبی است. زمانیکه بهبود دهنده پلن اجرایی انتخاب می‌کند، یک actual plan را ایجاد و در حافظه ذخیره می‌شود؛ بنام plan cache. البته درصورتیکه پلن مشابه و بهینه‌تری وجود نداشته باشد. 

استفاده مجدد از پلن ها

تولید پلن هزینه بر است. به‌همین دلیل اس کیوال سرور اقدام به ذخیره سازی و نگهداری آن‌ها می‌کند تا بتواند از آن‌ها مجددا استفاده نماید؛ البته تا جایی که مقدور باشد. هنگامیکه آن‌ها تولید می‌شوند، در قسمتی از حافظه بنام plan cache ذخیره می‌شوند. به این عمل procedure cache نیز گفته می‌شود.

هنگامیکه کوئری به سرور ارسال می‌شود، بوسیله بهبود دهنده، یک estimated plan ایجاد خواهد شد و قبل از اینکه به storage engine ارسال شود، بهبود دهنده estimated plan را با actual execution planهای موجود در plan cache مقایسه می‌کند. در صورتیکه یک actual plan را مطابق با estimated plan پیدا نماید، از آن مجدد استفاده خواهد کرد. این استفاده مجدد به عدم تحمیل سربار اضافه‌ای به سرور جهت کوئری‌های بزرگ و پیچیده که در زمان واحد، هزاران بار اجرا خواهند شد، منجر می‌شود.
هر پلن فقط یکبار در حافظه ذخیره خواهد شد. ولی در مواقعی با تشخیص بهبود دهنده و هزینه پلن، یک کوئری می‌تواند پلن دیگری نیز داشته باشد. بنابراین پلن دوم نیز با مجموعه عملیاتی متفاوت، جهت اجرای موازی (parallel execution) برای یک کوئری ایجاد و در حافظه ذخیره می‌شود.
پلن‌های اجرایی برای همیشه در حافظه باقی نخواهند ماند. پلن‌های اجرایی دارای طول عمری طبق فرمول حاصل ضرب هزینه، در تعداد دفعات می‌باشند. مثلاً پلنی با هزینه 10 و تعداد دفعات اجرای 5، طول عمر 50 را خواهد داشت. پروسس lazywriter که یک پروسس داخلی است وظیفه آزاد سازی تمام انواع کش‌ها، از جمله پلن کش را دارد. این پروسس در بازه‌های مشخص، تمام اشیاء درون حافظه را بررسی کرده و یک واحد از طول عمر آن‌ها می‌کاهد.
در موارد زیر، یک پلن از حافظه پاک خواهد شد:
1. به حافظه بیشتری نیاز باشد
2. طول عمر پلن صفر شده باشد