تكنولوجيا الجداول الدائمه والغير دائمه Microsoft SQL Server 2014

Mostafa El-Masry

نيسان/أبريل عام 2015

في هذا المقال ساقوم بشرح وتحليل ميزه وخاصيه جديده ( In-Memory OLTP ) و التي تعد من أهم تطورات محرك قواعد بيانات مايكروسوفت 2014 في كيفيه تخزين و معالجة البيانات بشكل أسرع مما كانت عليها سابقا في شكلها الجديد (Memory-Optimized-Table)و للتوضيح اكثر فان جداول قواعد البيانات مقسمة بشلك عام الى نوعين:

1. الجداول الدائمه (Durable Tables)
2. الجداول الغير دائمه (Non-Durable tables)

النوع الاول  الجداول الدائمه (Durable Tables) وهو النوع المعروف من بدايه قواعد البيانات حتى الأن اما النوع الثانى فهو الجداول الغير الدائمه (Non-Durable Tables)  وهى جداول يتم مسحها أليا من قبل محرك قواعد البيانات ... نعم أنها جداول ليس لديها خاصيه الحفاظ على المعلومات او البيانات  لفترات طويله من الزمان ولكن طبيعيه هذه الجداول تقوم أليا بفقدان كل البيانات المخرزنه بها بعد عمليه اعاده التشغيل لمحرك قواعد البيانات (SQL Server Instance).

بناء على ذلك يمكن استخدام هذا النوع من الجداول فى خوادم التطوير او الخوادم التحضيريه (Development or staging Servers) لانه يمكن الأستغناء عن البيانات الموجوده فى هذه النوعيه من الخوادم لعدم اهميتها وذلك يرجع ايضا لطبيعه العمل . ففى النهايه اذا كان لدينا معلومات او بيانات ليست مهمه بالدرجه لدينا وفقدانها لا يسبب مشكله لدينا فى هذه الحاله فاننا نتحدث عن (Non-durable tables).

ويمكن التفرقه بين هذين النوعين من خلال طريقه بناء الجدول :

النوع الأول : الجداول الدائمه DURABILITY = SCHEMA_AND_DATA

النوع الثانى : الجدال الغير دائمه DURABILITY = SCHEMA_ONLY

اولا : ماهى الممزات التى يمكن ان استفيد منها عند استخدم الجداول الغير دائمه ؟

أن هذا النوع من الجداول (Non-Durable Tables) اسرع بكثير فى عمليه تعبئه البيانات من نوع الجداول الأساسى (Durable Tables) ومن افضل البيئات التى يمكن ان نستخدم فيها هذا النوع من الجداول الغير دائمه هو بيئه التحضير او التطوير (Staging , Development Server) وبيئات مستودع البيانات (Data Warehouse) , ولكن انه من الخطر جدا ان نستخدم هذا النوع من الجدوال على بيئه العمل الحيه (Production Server) .

ثانيا :الجزء العملي ( Demo ):

لتوضيح ذلك يمكنا متابعة الجزء العملى ادناه لنرى كيف هذا النوع الجداول الغير دائمه اسرع من الجداول الدائمه فى عمليه حفظ البيانات. وكيفيه فقدان المعلومات بعد أعاده تشغيل محرك قواعد البيانات.

1. يجب ان تقوم اولا بأنشاء قاعده بيانات جديده لتكون مثل ورشه عمل لهذا الجزء العملى (ًWorkshop)

CREATE DATABASE [SQL2014_WorkShop]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'SQL2014_WorkShop', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\SQL2014_WorkShop.mdf'
, SIZE = 4288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [SQL2014_WorkShop_mod] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'SQL2014_WorkShop_mod', FILENAME = N'c:\SQL2014_WorkShop_mod' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'SQL2014_WorkShop_log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\SQL2014_WorkShop_log.ldf'
, SIZE = 1072KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

حينذاك قم بأنشاء نوعين مختلفين من الجداول الدائمه والغير دائمه (Durable and Non-Durable tables)

تنبيه : هذه النوعيه من الجداول لايمكن انشاؤها على قواعد البيانات الرئيسية (Master,MSDB.TEMPDB.MODEL)

الجداول الدائمه (Durable tables)

Use SQL2014_WorkShop
Go
Create Table Customer
(
Customer_ID INT NOT NULL Primary key nonclustered Hash WITH (bucket_Count = 1000000),
FirstName Nvarchar(200) NOT NULL,
lastname Nvarchar(200)NOT NULL
)
With(Memory_optimized=on,Durability=SCHEMA_And_DATA)

الجداول الغير دائمه (Non-Durable tables)

Use SQL2014_WorkShop
Go
Create Table Non_Durable_Customer
(
Customer_ID INT NOT NULL Primary key nonclustered Hash WITH (bucket_Count = 1000000),
FirstName Nvarchar(200) NOT NULL,
lastname Nvarchar(200)NOT NULL
)
With(Memory_optimized=on,Durability=SCHEMA_ONLY)

3. قم بادخال عدد كبير من البيانات يصل الى 100,000 فى الجداول الدائمه وهذه العمليه ستسغرق من 11 ثانيه الى 15 ثانيه .

Use SQL2014_WorkShop
Go
set nocount on
go
DECLARE @counter INT
SET @counter = 1

WHILE @counter <= 100000
BEGIN
INSERT INTO dbo.Customer
VALUES (@counter, 'mustafa','Elmasry'),
(@counter+1, 'Amro','Silem'),
(@counter+2, 'Shehab','Elnagar')

SET @counter = @counter + 3
END

4. حينذاك كرر نفس الخطوة السابقة لكن على الجدول الغير دائم (Non-Durable tables) سوف تجد ان هذه العملية لا تستغرق سوى صفر ثانيه.

Use SQL2014_WorkShop
Go
set nocount on
go
DECLARE @counter INT
SET @counter = 1

WHILE @counter <= 100000
BEGIN
INSERT INTO dbo.Non_Durable_Customer
VALUES (@counter, 'mustafa','Elmasry'),
(@counter+1, 'Amro','Silem'),
(@counter+2, 'Shehab','Elnagar')

SET @counter = @counter + 3
END

5. و لتاكيد دقة عملية الاختبار قم بالأستعلام عن البيانات فى الجداول التى تم أنشائها سنجد ان حجم البيانات متساوى

Use SQL2014_WorkShop
Go
Select count (1) from [dbo].[Customer] with(nolock)
GO
Select count (1) from [dbo].[Non_Durable_Customer] with(nolock)
GO

وبذلك نجد ان الجداول الغير دائمه (Non-Durable tables) هيا الأسرع فى عمليات أدخال البيانات (OLTP Transactions) من الجداول الدائمه (Durable tables) فى محرك قواعد البيانات (SQL Server 2014)

كما وضحنا من قبل انه لايمكن استخدام هذه النوعيه من الجداول الا لحالات معينه كخوادم التطوير وذلك لان البيانات لايمكن الحفاظ عليها لانه ستزول بعد أول عمليه أعاده تشغيل لمحرك قواعد البيانات كما هو موضح أدناه :

6. سنقوم الأن بعمل أعاده تشغيل لمحرك قواعد البيانات او عن طريق تغيير حاله قاعده البيانات من (Online)  الى حاله (Offline)  ثم نقوم بارجاعها مره أخرى الى حاله الــ (online)

Use Master
GO
ALTER DATABASE SQL2014_WorkShop SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SQL2014_WorkShop SET ONLINE
GO

7. كرر نفس الخطوة الخامسة من اجل الاستعلام مرة اخرىعن كم البيانات المسجله فى كل جدول :

Use SQL2014_WorkShop
Go
Select count (1) from [dbo].[Customer] with(nolock)
GO
Select count (1) from [dbo].[Non_Durable_Customer] with(nolock)
GO

سنجد ان جدول (Customer) الذى تم انشاؤه من نوع الجداول الدائمه مازال يحتفظ على 100,000 سطر أما الجدول (Non_Durable_Customer) الذى تم أنشاؤه من نوعيه الجداول الغير دائمه فلقد فقد تمام جميع البيانات التى تم تسجيلها فيه من قبل و1لك بعد عمليه اعاده تشغيل محرك البيانات او تغيير حاليه قاعده البيانات

وفى النهايه أحب ان اقوم بالتنبيه على انه يجب جيدا معرفه متى يمكن استخدام الجداول الغير الدائمه فى بيئه العمل فى حاله اذا كانت البيانات ليست لها اى اهيمه فأن هذا النوع من الجداول هو الحل الأمثل لهذه البيئه.