نحوه ایجاد Column Stored Index در SQL Server 2012

برای ایجاد Column Stored Index ابتدا باید فیلدهای شرکت کننده در ایندکس را تعیین و پس از آن ایندکس را ایجاد نمایید. مثال زیر نحوه انجام اینکار را نمایش می‌دهد.

۱- برای شروع کار یک جدول جدید ایجاد می‌کنیم. این جدول دارای یک Primary Key است که با استفاده از Clustered Index ایجاد شده است. بدلیل اینکه جداول شما از قبل وجود دارند ممکن است این مرحله جزء کار شما نباشد.

USE tempdb

GO

IF OBJECT_ID('Employees', 'U') IS NOT NULL

 DROP TABLE Employees

GO

CREATE TABLE Employees_ColumnBased

(

 Code INT IDENTITY CONSTRAINT PK_Code PRIMARY KEY,

 FirstName NVARCHAR(50),

 LastName NVARCHAR(80),

 HireDate SMALLDATETIME,

 City NVARCHAR(20)

)

GO

۲- در این مرحله تعدادی داده تستی در جدول درج می‌کنیم.

INSERT INTO Employees_ColumnBased (FirstName,LastName,HireDate,City) VALUES

 (N'مسعود',N'طاهری','۲۰۰۰-۰۱-۰۱',N'میانه'),

 (N'فرید',N'طاهری','۲۰۰۳-۰۱-۰۱',N'میانه'),

 (N'احمد',N'غفاری','۲۰۰۳-۰۱-۰۱',N'میانه'),

 (N'خدیجه',N'افروزنیا','۲۰۰۰-۰۱-۰۱',N'تهران'),

 (N'مجید',N'طاهری','۲۰۰۵-۰۱-۰۱',N'تهران')

GO

INSERT INTO Employees_ColumnBased (FirstName,LastName,HireDate,City)

 SELECT FirstName,LastName,HireDate,City FROM Employees_ColumnBased

GO 10

۳- در این مرحله یک Column Stored Index به ازای فیلدهای مورد نیاز ایجاد می‌کنیم. لازم به ذکر است در SQL Server 2012 این نوع ایندکس به شکل Non Clustered Column Stored Index می‌باشد.




CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColumnStore

 ON Employees_ColumnBased(FirstName,LastName,HireDate)

GO

نکته مهمی که باید در ایجاد این نوع از ایندکس‌ها در نظر گرفت این است که به دلیل افزایش Performance معمولاً در بیشتر مواقع این ایندکس‌ها با توجه به کوئری‌های شما ایجاد می‌گردند. بدین صورتکه

۱- لیست تمامی کوئری‌های پر استفاده به ازای جدول مورد نظر را استخراج نمایید.

۲- کلیه فیلدهای مربوط به مرحله اول را استخراج نمایید.

۳- در صورتیکه فیلدهای استخراج شده در مرحله دوم جزء فیلدهای قابل شرکت در Column Stored Index باشند در ایندکس مورد نظر شرکت نمایند.

۴- در صورتیکه بخواهید Column Stored Index را به صورت ویژوالی در Management Studio ایجاد کافی است مراحل زیر را انجام دهید.

Column Stored Index

Column Stored Index یکی از قابلیت‌های جالبی است که در SQL Server 2012 ارائه شده است. بوسیله این قابلیت می‌توان سرعت اجرایی کوئری‌های را تا چندین برابر بهبود بخشید.

مایکروسافت مهمترین هدف خود را از ارائه این قابلیت افزایش سرعت اجرای کوئری‌های بر روی Data Warehouseهای بزرگ ذکر کرده است.

 اما می‌توان از آن در شرایطی خاص بر روی OLTP نیز استفاده نمود.

قبل از اینکه به معرفی این قابلیت جدید SQL Server بپردازیم باید شما با مفاهیم اولیه درباره Performance Tuning آشنا شوید.

۱- آشنایی با مفهوم Data File و Log File : همانطور که می‌دانید یک بانک اطلاعاتی در SQL Server حداقل از دو فایل (Data File و Log File) تشکیل شده است. در Data File کلیه اشیائی (مانند فیلد، جدول، ایندکس و…) که شما ایجاد می‌کنید ذخیره می‌شود و به طور ساده در Log File هر چیزی که منجر به تغییر این داده‌ها شود ذخیره می‌شود. برای مثال چنانچه شما مقدار رکوردی را تغییر دهید Log عملیات مربوط به تغییر در Log File ثبت می‌گردد. این Log می‌تواند شامل تصویر داده قبل و بعد از تغییر داده و همچنین زمان انجام کار و… باشد.

۲- آشنایی با مفهوم Page : همانطور که قبلاً هم اشاره کردیم داده‌هایی که شما ایجاد کرده‌اید در درون Data File ثبت می‌شود. درون Data File بلوک‌های ۸KB قرار دارد که به این بلوک‌ها Page می‌گویند. در SQL Server بیش از ۱۰ نوع Page وجود دارد. که SQL Server بر حسب نیاز از آنها استفاده می‌کند.

۳- آشنایی با مفهوم Index در SQL Server: به طور خیلی ساده، ایندکس لیست مرتب شده‌ای از داده‌ها است که بر اساس منطقی خاص مانند عدد، رشته، تاریخ و … ایجاد شده است. هدف از ایجاد ایندکس افزایش سرعت جستجو برای بازیابی داده‌ها می‌باشد.

۴- آشنایی با انواع ایندکس‌ها:

در SQL Server  پنج نوع ایندکس به شرح زیر وجود دارد

• Clustered Index: این نوع ایندکس‌ها بر روی داده‌های عادی مانند عدد، رشته، تاریخ و… ایجاد می‌شوند. زمانیکه یک Clustered Index بر روی یک جدول ایجاد می‌کنید ترتیب و چینش فیزیکی رکوردها بر اساس کلید ایندکس خواهد بود. بنابراین یک جدول صرفاً می‌تواند یک ایندکس از نوع Clustered  داشته باشد.

 

• NonClustered Index: این نوع ایندکس‌ها مانند Clustered Indexها بر روی داده‌های عادی مانند عدد، رشته، تاریخ و… ایجاد می‌شوند. زمانیکه یک ایندکس از نوع NonClustered بر روی جدول ایجاد می‌کنید کلید ایندکس در فضایی دیگری جدای از فضای جدول مرتب شده و مورد استفاده قرار می‌گیرد. به طور کلی در SQL Server می‌توان تا ۹۹۹ ایندکس از نوع NonClustered را ایجاد کرد.

 

• XML Index : این نوع ایندکس‌ها بر روی داده‌هایی  که در قالب ساختار XML هستند ایجاد می‌شود.

 

• Spatial Index : این نوع ایندکس‌ها بر روی داده‌هایی که از نوع جغرافیایی هستند ایجاد می‌شود.

 

• Column Stored Index : نوع جدیدی از ایندکس‌ها هستند که در SQL Server 2012 معرفی شده‌اند که سرعت دسترسی به داده‌ها در آن بیش از سایر ایندکس‌ها می‌باشد.

۵- آشنایی با مفهوم Execution Plan : Plan اجرایی یا نقشه اجرایی کوئری ترتیب اجرای فیزیکی دستورات را مشخص می‌نماید. زمانیکه شما کوئری را اجرا می‌کنید SQL Server آن را به همان ترتیبی که نوشته شده است اجرا نمی‌کند. بلکه ترتیب دسترسی به داده‌های موجود در جداول با توجه به پارامترهای مختلف مانند وضعیت ایندکس‌ها، وضعیت منابع سرور و…  خواهد بود.

پس از مشخص شدن ترتیب اجرای فیزیکی SQL Server نقشه آن را با عنوان Execution Plan ذخیره می‌کند تا در آینده بتواند از آن استفاده کند. چنانچه بخواهید نقشه اجرایی با Execution Plan یک کوئری را مشاهده نمایید کافی است که کوئری مورد نظر خود را Highlight نموده و کلید Ctrl+L را فشاره دهید تا Estimate Plan یا نقشه اجرایی تخمینی برای شما نمایش داده شود.

در تصویر زیر نمونه‌ای از Execution Plan یک کوئری نمایش داده شده است.

SELECT * FROM Orders O

 INNER JOIN [Order Details] OD ON O.OrderID=OD.OrderID

 INNER JOIN Employees E ON O.EmployeeID=E.EmployeeI

نکته : Execution Plan از سمت راست به چپ و از بالا به پایین خوانده می‌شود.