إدخال تقنيات Microsoft SQL Server 2014

Shehap El-Nagar

28 تشرين الأول/أكتوبر

بالمقالة السابقة قمنا بعرض نبذة سريعة و مختصرة عن اهم التقنيات الحديثة التي اضافها محرك قواعد بيانات مايكروسوفت 2014 مثل مشروع ال Hekaton الذي دعم تكنولوجيا الجداول المدعمة بالذاكرة المدمجة  Memory optimized tables و تقنية الفهارس العمودية العنقودية CCI Clustered Columnstore Index  و الأكواد المترجمة ذاتيا  Natively compiled SPs  و اعادة تهيئة الفهارس جزءا يجزءا في الوضع النشط  Single Partition Online index rebuild   و غيرهم اكثر التي حققت تحسينات ملحوظة في معدلات اداء قواعد البيانات تصل إلى 10 اضعاف إلى 30 اضعاف و في بعض الاحيان قد تصل الى 100 ضعف الذي هو حقيقة الهدف الرئيسي لمشروع ال Hekaton لذا ابتداء من هذه المقالة سنبدأ بعرض هذه التقنيات الجديدة من حلال خمس زوايا رئيسية:

1# التعريف

2# التصميم المعماري

3# المميزات و الفوائد

4# التوصيات و المحاذير اللازم مراعتها عن الاستخدام

5# الامثلة و نماذج اختبارات الاجهاد العالي

تكنولوجيا الذاكرة المجمدة:

أولا: التعريف

خلال مؤتمر ال PASS Professional Association for SQL Server الذي عقد في نوفمبر الماضي 2012 فإن مايكروسوفت قد افصحت عن مشروعها الجديد Hekaton الذي اطلقت فيه تكنولوجيا الذاكرة المدمجة In memory Built in لكي تكون جزءا لا يتجزأ من محرك قواعد البيانات نفسه لكي يخدم بشكل اساسي عمليات معالجة البيانات OLTP transaction

ثانيا: التصميم المعماري

بدأت فكرة المشروع بالتفكير جديا في تحديد مناطق الضعف و التأخير في عمليات معالجة البيانات ادخال /تحديث/حذف البيانات و اكتشفوا انه لا علاقة لهذا التأخير بأي نقص في موارد الخادم كموارد وحدة المعالج المركزي CPU الذي قد يؤثر سلبا على توازي تشغيل وحدة المعالج المركزي CPU Parallelism لأن عمليات معالجة البيانات حقيقة لا تحتاج إلى هذا التوازي لكنهم اكتشفوا ان اكثر من 80 % من نسبة التأخير موزعة على محرك تنفيذ العمليات Query Engine و محرك تخزين البيانات على الذاكرة الصلبة Storage Engine لذا تم الاتجاه إلى استحداث طريقة جديدة لتنفيذ عمليات معالجة البيانات بشكل مبسط من خلال الدمج بين التالي:

#1 الجداول المطورة تكنولوجيا بالذاكرة المدمجة Memory optimized tables للتغلب على الجزء الثاني من التأخير المتعلق بمحرك تخزين البيانات .

2# الاكواد المترجمة ذاتيا Natively compiled SPs للتغلب على الجزء الاول من التأخير و هو محرك تنفيذ العمليات حيث انه بدلا من ضياع وقت كبير في ترجمة الاكواد و تحديد خارطة تنفيذها اصبحت الاكواد الان مترجمة ذاتيا في شكل ملفات .dll بلغة البرمجة C التي يمكن بعد ذلك تحويلها باستخدام مترجم لغة ال C/C++ المرئية إلى اكواد لغة الالة التي تتمتع بالطبع بسرعة تنفيذ هائلة. يمكنك الاطلاع على نتائج اختبارات الاداء في الجزء الخاص بالعرض ادناه.

ثالثا: المميزات و الفوائد

هناك العديد من المميزات و الفوائد التي تحقق من تطبيق هذه التكنولوجيا الجديدة كما هو موضح ادناه:

#1قد شكلت نقلة نوعية للأنظمة ذو طبيعة الاجهاد العالي على عمليات معالجة البيانات من غير اي فقدان للبيانات بالرغم من تخزين البيانات اساسا في الذاكرة التخيلية و ليس الذاكرة الصلبة حيث انه من الطبيعي فقدان البيانات بعد

اعادة تشغيل الخادم كما هو الحاصل في محركات قواعد بيانات كثيرة و هذه هي الاسطورة الأولى لمحرك قواعد بيانات مايكروسوفت الجديد 2014 . ..!!

#2 تطور يصل إلى 10 اضعاف إلى 30 ضعف ..!!

#3 لا تعديل جذري في الاكواد على وجه الاطلاق و هذا يشكل الاسطورة الخامسة لمحرك قواعد بيانات مايكروسوفت 2014…!

#4 لا وجود للطرق المسدودة حيث انه لا وجود لأي نوع من حجز الصفحات Page latch خلال تنفيذ العمليات و كذلك لا وجود لطلب حجز على السجلات و الجداول No Lock لاعتماد هذه التكنولوجيا الجدية على خاصية ال

Read Committed Snapshot isolation level using Row versioning التي تتولي اخذ صورة من البيانات إلى قاعدة البيانات الرئيسة Temp DB لكي يتم تنفيذ عمليات المعالجة عليها ثم اعادتها مرة اخرى إلى قاعدة بيانات المستخدم و بهذا لا وجود للطرق المسدودة اطلاقا ..!!

#5حفظ كبير للمساحات التخزينية حيث ان فهارس مثل هذه الجداول لا تستهلك اي مساحة على الذاكرة الصلبة لانها يتم تخزينها على الذاكرة التخيلية Memory و كلما تم اعادة تشغيل الخادم يتم انشائها مرة اخرى و هكذا.

#6تم زيادة طاقة استيعاب السجل الواحد من 8 كيلوبايت إلى 512 جيجا بايت ..!!

#7لا مزيد من القلق حول عمليات حذف البيانات المجمعة Bulk deletion التي تستخدم بكثرة في عمليات الارشفة حيث انه قد صارت تغير طريق تنفيذ عملية الحذف إلى مجرد ترميز السجلات المطلوب حذفها و من ثم تقوم عملية اخرة بحذف البيانات المشار اليها بشكل غير اني Asynchronous بدون اي تأثير يذكر على عمليات المستخدمين الاخرى مهما كان حجم البيانات المطلوب حذفها..!!

رابعا :التوصيات و المحاذير اللازم مراعتها عند الاستخدام

هناك بعض التوصيات و المحاذير اللازم مراعتها عند استخدام اي من الجداول المطورة بتكنولوجيا الذاكرة المدمجة أو الاكواد المترجمة ذاتيا على النحو التالي:

1# هذه الجداول لا تدعم استخدام الحقول المتزايدة تلقائيا سواء القديمة Identity او الجديدة Sequence الخاصة بالاصدارات الاحدث من محرك قواعد البيانات 2012

2# هذه الاكواد المترجمة ذاتيا لا تدعم استخدام الاكواد Merge في عمليات معالجة البيانات حيث انه ليس من المتوقع استخدامها في عمليات معالجة البيانات الاعتيادية الخاصة بالمستخدمين لكن فقط عمليات الارشفة و عمليات تجميع البيانات المركزية و هكذا ..

3# هذه الجداول لا تدعم استخدام استخدام اكواد ال Triggers المستخدمة في مراقبة عمليات معالجة البيانات .

#4هذه الجداول لا تعدم استخدام بعض الحقول مثل ال XML و BLOB كال Image و ال Varbinary (max) و الانواع

المشتقة من CLR مثل ال Geometry و ال Geography و ال hierarchyId و كذلك ايضا لا تدعم Text و ال ntext

و انواع اخرى مثل ال Timestamp و ال Rowguid

#5هذه الجداول لا تدعم تقنيات انكماش البيانات Data Compression

#6هذه الجداول لا تدعم فهرس عنقودي لكنها تدعم نوع آخر جديد من الفهارس الهامشية ( Hash indexes ) التي تتوافق مع تكنولوجيا الذاكرة المدمجة

#7 هذه الجداول لا تدعم الحقول المحتسبة اتوماتيكيا من قيمة حقول اخرى .

#8 هذه الجداول لا تدعم استخدام الاكواد Truncate table من اجل تفريغ الجداول لانها بالفعل لا داعي لاستخدامها مع هذا التطور الهائل لعملية الحذف المجمع باستخدام Delete Commands كما هو مشروح آنفا.

#9هذه الجداول لا تدعم استخدام توجيهات الجداول Table Hints .

#10 هذه الأكواد المترجمة ذاتيا لا تدعم تعديلها لكن اذا دعت الحاجة يمكنك اعادة انشائها مرة اخرى .

ارى انه لا داعي للقلق بخصوص هذه المحاذير لأنها امر طبيعي فيما يخص هذه التكنولوجيا في اي محرك قواعد بيانات آخر مثل محركات قواعد بيانات المختلفة التي من فئة ال NOSQL ( NOT Only SQL )و حتى فأن محرك قواعد بيانات مايكروسوفت 2014 قد وفر العديد من الحلول البديلة لمعظمهم حتى لا تشعر بفقدان هذه الخصائص و بغض النظر عن هذا فأني اري شخصيا حجم تطور معدلات الاداء هائل فيما يخص عمليات معالجة البيانات يكفي لتعويض هذه الخصائص

خامسا: الامثلة و نماذج اختبارات الاجهاد العالي

في البداية كنت افكر مليا في كيفية عمل عرض حي مكون من مجموعة اختبارات حقيقة للإجهاد العالي للأكواد قبل و بعد تطبيق تقنيات محرك قواعد البيانات الجديد 2014 لكي يمكن الاثبات بشكل واضح البيان مدى حجم التطور الهائل في معدلات الاداء لكي تساعدك في اتخاذ قرارا واعي في سرعة تحديث محرك قواعد البيانات إلى SQL Server 2014

أو دمج قواعد بيانات إلى محرك قواعد البيانات الجديد SQL Server 2014 لذا توصلت اخيرا إلى النموذج ادناه:

1# اولا قم بتنزيل نسخة ال SQL Server 2014 CTP2 من خلال الرابط التالي:

https://technet.microsoft.com/en-us/evalcenter/dn205290.aspx

#2قم باسترجاع قاعدة البيانات التي ستستخدم في كل اختبارات الاجهاد العالي القادمة على مدار هذه السلسلة من المقالات ( اضغط هنا للتحميل ) و التي اسميتها مجازا AdventureWorks2014 على الرغم من انه لم يتم اصداراها رسميا حتى الآن ..

#3ثم قم بتنزيل مجلد اختبارات الاجهاد العالي الخاص بتكنولوجيا الذاكرة المجمدة (اضغط هنا للتحميل) لكن ينصح بجمع كل هذه الملفات في مكان مخصص لكل اختبارات هذه السلسة ليمكن الرجوع اليهم لاحقا بسهولة

#4الان قم بفتح مجلد الاختبارات و قم بفتح ملف تجهيزات الاختبارات المسمى ب

”“In memory table and natively complied SPs implementations” ” و قم بتنفيذه على النحو التالي من اجل انشاء مجموعة ملفات جديدة لقاعدة البيانات مطورة بتكنولوجيا الذاكرة المجمدة و من ثم ملفات تحت هذه المجموعة و حينذاك يمكنك انشاء جداول مطورة بتكنولوجيا الذاكرة المجمدة و الأكواد المترجمة ذاتيا للاستعداد لاختبارات الاجهاد العالي لاحقا ، يمكنك الاطلاع على المزيد من المعلومات عن صيغة انشاء كل منهم من خلال الرابط التالي:

https://technet.microsoft.com/en-us/library/dn133079(v=sql.120).aspx

--Create File Group
ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [Memory_Filegroup] CONTAINS MEMORY_OPTIMIZED_DATA 

--Create Files

ALTER DATABASE [AdventureWorks2014] ADD FILE ( NAME = N'MemoryTables_File', FILENAME = N'C:\Shehap\Database\User_DB\MemoryTables_File' ) TO FILEGROUP [Memory_Filegroup]

--Create In memory table
CREATE TABLE [dbo].[SalesOrderDetail_Memory](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int]  NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID1] PRIMARY KEY nonclustered  hash 
(
[SalesOrderID] ,
[SalesOrderDetailID] 
)with (bucket_count =100000) ,index sales_index1 nonclustered hash ([OrderQty],[ProductID]) with(bucket_count =100000)
) with (memory_optimized=on,durability=schema_and_data)

go
--Create natively compiled stored procedure
CREATE PROCEDURE SelectTable_Destination_memorytable
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
--Delete and Insert rows in memory table
delete from [dbo].[SalesOrderDetail_Memory] 
INSERT INTO [dbo].[SalesOrderDetail_Memory] SELECT  [SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[ModifiedDate]
FROM [dbo].[SalesOrderDetail_Memory_source]
END
go

--Create disk-based table
CREATE TABLE [dbo].[SalesOrderDetail_disk-basedtable](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int]  NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL ,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID2] PRIMARY KEY clustered  
(
[SalesOrderID] ,
[SalesOrderDetailID] 
)
) 

go
--Create regular stored procedure on disk-based table
create PROCEDURE SelectTable_Destination_disk-basedtable
as

begin


--Delete and Insert rows in disk-based table
delete from [SalesOrderDetail_disk-basedtable]
INSERT INTO [dbo].[SalesOrderDetail_disk-basedtable] SELECT [SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[ModifiedDate]
FROM [Sales].[SalesOrderDetail]
  
end

5#بعد انشاء الجداول المطورة بتكنولوجيا الذاكرة المجمدة قم بفتح الملف الثاني المسمى ب SalesOrderDetail_Memory_Source”. من اجل تعبئة الجداول ب 2 مليون سجل حتى يتسنى اجراء اختبارات اجهاد حقيقية..

6# بعد ذلك قم فتح آخر ملفين و تشغليهم في وقت واحد مرة باستخدام الجداول المطورة بتكنولوجيا الذاكرة المجمدة و مرة اخرى باستخدام الجداول الاعتيادية على النحو التالي لنرى الفرق بينهم

7# -قم بتنفيذ جملة الحذف التالية :

EXECUTE  dbo.SelectTable_Destination_disk-basedtable

التي ستقوم بدورها بادخال و حذف 2 مليون سجل إلى الجدول الاعتيادي المسمى

[dbo].[SalesOrderDetail_disk-basedtable]

8# في نفس الوقت قم بتنفيذ جملة الاستعلام ادناه بشكل مستمر :

--Heavy locks test for in disk-based table

select top 10000 [SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[ModifiedDate] from [dbo].[SalesOrderDetail_disk-basedtable]

نتائج المرحلة الاولى من الاختبار:

تنفيذ كود الحذف و الادخال اخذ 50 ثانية بينما جملة الاستعلام اخذت 20 ثانية في المرة الاولى و 25 ثانية في المرة الثانية بالرغم من حجم الاستعلام لا يتعدى 10000 سجل فقط..!

يمكنك تكرار هذه المرحلة من الاختبار اكثر من مرة للتأكد اكثر من النتائج

#9الان يمكننا تجاوز هذه المرحلة الاولي لنقوم بتنفيذ المرحلة الثانية من الاختبار بنفس الكيفية لكن على الجدول الجديد المطورة بتكنولوجيا الذاكرة المجمدة كما هو موضح ادناه

#10قم بتنفيذ الكود المبرمج ذاتيا ادناه:

EXECUTE  dbo.SelectTable_Destination_memorytable

و الذي سيقوم بدوره بحذف و ادخال 2 مليون سجل من الجدول المطور بتكنولوجيا الذاكرة المجمدة المسمى “[dbo].[SalesOrderDetail_Memory]“

#11في نفس الوقت قم بتنفيذ جملة الاستعلام و استمر في تنفيذها:

--Heavy locks test for in disk-based table

select top 10000 [SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
,[ModifiedDate] from [dbo].[SalesOrderDetail_disk-basedtable]

نتائج المرحلة الثانية من الاختبار:

تنفيذ كود الحذف و الادخال اخذ 10 ثانية بينما جملة الاستعلام اخذت 1 ثانية في كل مرة ..!!

ملخص نتائج اختبارات المرحلة الأولى و الثانية:

عمليات الحذف و الادخال قد حققت هنا تطور ملحوظا يصل إلى 5 اضعاف باستخدام مزدوج لتكنولوجيا الذاكرة المجمدة و الأكواد المترجمة ذاتيا و هذا يعني حقيقة نقطة تحول غير مسبوقة في معدلات اداء عمليات معالجة البيانات خلال محرك قواعد بيانات مايكروسوفت 2014

اضافة الي هذا التطور المذهل ، هناك تطور آخر قد يدهشك اكثر حيث ان جمل الاستعلام قد وصل معد اداءها إلى 25 ضعف بفضل انعدام الطرق المسدودة التي غالبا ما تشكل تعارض بين جمل الاستعلام و عمليات معالجة البيانات و هذا بحد ذاته بعد نقطة تحول غير مسبوقة في تاريخ محركات قواعد بيانات مايكروسوفت ..!!

حقا لأنه حلم من الاحلام التي تراودنا دائما فيما يخص معدلات الاداء لكن لا عجب هنا لأن محرك قواعد بيانات مايكروسوفت مليئ بالأعاجيب و الاساطير على حد قولهم …!!!

يمكنك متابعتنا و مشاركتنا ارائك عبر:

• YouTube Channel
• Facebook Group
• Network Blogs
• Twitter
• LinkedIn Group
• Facebook Page

| Home | المواد التقنية | مركز مجتمع