أهم بعض الأكواد الديناميكيه المستخدمه فى محرك قواعد بيانات مايكروسوفت 2014

  • مقالة

Mostafa El-Masry

أيار/مايو عام 2015

في هذا المقال سوف اقوم بشرح وتحليل ثلاثه اكواد ديناميكيه تعتبر ذو أهميه عاليه لأى مستخدم لمحرك قواعد بيانات مايكروسوفت 2014  , كما نعلم ان محرك قواعد بيانات مايكروسوفت جاء الينا كطفره فى عالم قواعد البيانات وقام بتسجيل آداء ملحوظ فى معدىلات اداء مختلف انواع الاستعلامات  وذلك لأنه جاء بالعديد من المميزات والتطورات الجديده و من أهمها فى محرك قواعد بيانات مايكروسوفت 2014 الجديد جداول الذاكره التخيلية

 (In-memory-table or Memory-Optimized-table)

فهيا جداول لها بعض الخصائص ولها بعض الحدود ولكن جداول متميزه جدا فى سرعه تخزين البيانات والأستعلام عنها لانها يتم أنشائها على نوع جديد من أنواع الفهارس وهو

 (Clustered ColumnStore index) وأيضا تعتمد اعتماد كلى على ذاكره محرك البيانات وبناء على ذلك فهذه النوعيه من الجداول تتطلب ذاكره عاليه السرعه ويجب دعم الخادم او محرك قواعد البيانات 2014 بالذاكره الكافيه لكي تتحمل مثل هذه النوعيه من الجداول لذا فانني بصدد هذا المقال سوف اقوم بشرح وعرض بعض الأكواد الديناميكيه التى تقوم بالأستعلام عن بعض البيانات المهمه لهذا النوع من الجداول والفهارس وهيا كالأتى :

قائمه بكل جداول الذاكره الموجوده على قاعده البيانات List All Memory-Optimized-table in SQL Server 2014.
أيجاد الذاكره المستخدمه لجدول واحد من جداول الذاكره . Find allocated memory for one Memory-Optimized-Table.
ايجاد الذاكره المستخدمه لجميع جداول الذاكره الموجوده على قاعده البيانات. Find allocated memory for All Memory-Optimized-Table.
قائمه بكل الفهارس الجديده التى ظهرت فى محرك قواعد بيانات مايكروسوفت 2014 .HASH Index List on Non-Clustered HASH index in SQL Server 2014.
قائمه بكل الجداول التى يوجد بها فهرس Clustered ColumnStore index List by all tables have Clustered Column Store index in SQL Server 2014

تحضير البيئة الاختبارية  :

  1. فى البدايه سنقوم بأنشاء قاعده بيانات جديده تدعم خاصية جداول الذاكرة  التخيلية:
CREATE DATABASE SQL2014_WorkShop
 
GO
 
ALTER DATABASE SQL2014_WorkShop
ADD FILEGROUP SQL2014_WorkShop_mod CONTAINS MEMORY_OPTIMIZED_DATA
 
ALTER DATABASE SQL2014_WorkShop
ADD FILE( NAME = 'SQL2014_WorkShop_mod' ,
FILENAME = 'c:\SQL2014_WorkShop_mod')
TO FILEGROUP SQL2014_WorkShop_mod
  1. ثانيا نقوم بانشاء جدول جديد من جداول الذاكرة التخيلية:
CREATE TABLE [dbo].[orders_mem](
 [O_OrderKey] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000),
 [O_CustKey] [int] NULL,
 [O_OrderStatus] [Nvarchar](64) NULL,
 [O_TotalPrice] [decimal](13, 2) NULL,
 [O_OrderDate] [datetime] NOt NULL,
 [O_OrderPriority] [Nvarchar](15) NULL,
 [O_Clerk] [Nvarchar](64) NULL,
 [O_ShipPriority] [int] NULL,
 [O_Comment] [Nvarchar](80) NULL,
 [skip] [Nvarchar](64) NULL
 
INDEX IX_OrderDate (O_OrderDate ASC)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
GO
  1. ثالثا نقوم بانشاء جدول جديد مع فهرس من الفهارس الجديده فى محرك قواعد بيانات 2014 Clustered ColumnStore Clustered ColumnStore index
CREATE TABLE SimpleTable(
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON SimpleTable;
GO

ملاحظه هامة : هذه النوعيه من الجداول لاتدعم الأعمده الخاليه (NOT ALLOW NULL)

قائمه بكل جداول الذاكره الموجودة على قاعده البيانات

عن طريق هذا الكود الديناميكى سنكون قادرين على عرض قائمه بأسماء الجداول التى تندرج تحت

(Memory-Optimized-table)

SELECT name,type_desc,durability_desc,Is_memory_Optimized FROM sys.tables
WHERE Is_memory_Optimized = 1

  1. أيجاد الذاكره المستخدمة لجدول واحد من جداول الذاكره.

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

select * from sys.dm_db_xtp_table_memory_stats
where object_id = object_id('dbo.orders_mem')

فمن خلال هذا الكود الديناميكى السابق البسيط جدا يمكن الوصول الى 4 نقاط فى غايه الأهميه :     

  1. نسبه الذاكره المحجوزه لكل جدول يندرج تحت نوعيه جداول الذاكره.
  2. نسبه الذاكره المستخدمه  لكل جدول يندرج تحت نوعيه جداول الذاكره.
  3. نسبه الذاكره المحجوزه لكل فهرس يندرج تحت نوعيه جداول الذاكره.
  4. نسبه الذاكره المستخدمه لكل فهرس يندرج تحت نوعيه جداول الذاكره.
  5. ايجاد الذاكره المستخدمه لجميع جداول الذاكره الموجوده على قاعده البيانات.

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

;SET NOCOUNT ON
;(Declare @TableName Nvarchar(100
 
DECLARE Memory CURSOR
 FOR Select name from Sys.Tables where is_memory_optimized = 1
OPEN Memory
FETCH NEXT FROM Memory into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 
select @TableName As [Memory-Optimized-table],* from sys.dm_db_xtp_table_memory_stats
where object_id = object_id(@TableName)
FETCH NEXT FROM Memory INTO @TableName
END
CLOSE Memory
 DEALLOCATE Memory

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

  • قائمه بكل الفهارس غير النوعيه الجديدة التى ظهرت فى محرك قواعد بيانات مايكروسوفت 2014.

كما نعلم لقد جاء محرك بيانات مايكروسوفت بلعديد من الأمتيازات فى نواحى كثيره منها الفهارس فلقد جاء بنوعين من الفهارس وهما :

NonClustered Hash index

Clustered ColumnStore index

وهذا الكود الديناميكى الأول سيقوم بعرض قائمه الفهارس Hash Index

SELECT
[object_schema_name([object_id]) As [Schema Name
[object_name([object_id]) As [Table Name,
[name] As [Index Name],
[type_desc] As [Index Description],
FROM
sys.hash_indexes

أما هذا الكود الديناميكى يستخدم فى عرض أسماء الفهارس التى من نوع Clustered ColumnStore

Select I.name As [Index Name] , T.name AS [Table Name] from Sys.indexes AS I
				Inner join Sys.tables As T
				ON I.Object_ID = T.Object_ID
				'WHERE   I.Type_desc = 'CLUSTERED COLUMNSTORE

  • قائمه بكل الجداول التى يوجد بها فهرس Clustered ColumnStore index

هذا الكود الديناميكى سيقوم بعرض اسماء جميع الجداول التى تحتوى على هذا الفهرس الجديد Clustered Column Store index و فى المقالات القادمه سأطرح عليكم كيفيه انشاء هذا الفهرس على كل الجداول التى تدعم هذا النوع من الفهارس مع الأخذ فى الأعتبار جميع المراحل التى يجب ان تتبع لتهيئه قواعد البينات قبل عمل الأنشاء واختيار الجداول التى تدعم ذلك الفهرس بناء على المعايير والمحاذير التى اعتمدت من قبل مايكروسوفت.

SELECT  OBJECT_NAME(Object_ID) AS Tablename
                FROM    Sys.indexes
                'WHERE   Type_desc = 'CLUSTERED COLUMNSTOR

المراجع :

http://sqlserver-performance-tuning.net/?p=4485

http://sqlserver-performance-tuning.net/?p=4391

http://sqlserver-performance-tuning.net/?p=4323

http://sqlserver-performance-tuning.net/?p=4152

الى اللقاء في المقالة القادمة مع كل ما هو جديد في ىمحرك قواعد بيانات مايكروسوفت الجديد 2014

لمتابعه الناشر :

View all my tips,LinkedIn,Website,Slideshare,Youtube Channel

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

Facebook Page, LinkedIn Group, Twitter, Networked Blogs, Facebook Group, Youtube Channel