تاثیرات Page split در SQL Server

تاثیرات Page split، این مقاله در مورد هزینه تحمیلی Page split بر Performance کلی دیتابیس است. از آنجایی که SQL Server باید نظم فیزیکی رکورد ها را بر اساس کلید ایندکس حفظ کند در صورتی که در Page مربوطه جایی برای گنجاندن رکورد جدید نباشد پایگاه داده SQL Server باید حدود ۵۰% از رکوردهای درون Page را به Page ای جدید انتقال دهد تا جا برای رکورد جدید باز شود البته برای رکوردهای بزرگ ممکن است با یک Page split مشکل حل نشود و رکوردهای بیشتری نیاز به انتقال داشته باشند.

تاثیرات Page split

البته یافتن فضای خالی در فایل دیتابیس و تخصیص Page هم هزینه دیگری است که Page split به همراه دارد. Page split همیشه عملی گران قیمت محسوب می شده است، اما تا چه حد؟ قصد دارم با استفاده از یک DMV به نام sys.dm_tran_database_transactions به شما نشان دهم وقتی یک page در یک ایندکس می بایست split شود تا چه حد log بیشتری در فایل transaction log تولید می شود. با این اسکریپت یک جدول با ردیف های تقریباً ۱۰۰۰ بایت می سازیم. همجنین شما می‌توانید کوئری نویسی را به صورت گام‌به‌گام از نیک آموز فرا بگیرید. 

CREATE DATABASE PageSplitTest;
GO
USE pagesplittest;
GO
CREATE TABLE BigRows (c1 INT, c2 CHAR (1000));
CREATE CLUSTERED INDEX BigRows_CL ON BigRows (c1);
GO
INSERT INTO BigRows VALUES (1, 'a');
INSERT INTO BigRows VALUES (2, 'a');
INSERT INTO BigRows VALUES (3, 'a');
INSERT INTO BigRows VALUES (4, 'a');
INSERT INTO BigRows VALUES (6, 'a');
INSERT INTO BigRows VALUES (7, 'a');
GO

برای رکورد با مقدار c1 = 5 یک “شکاف” ایجاد کرده ایم. منظور از شکاف این نیست که جایش رزرو شده بلکه صرفاً این  مقدار از نظر منطقی جا انداخته شده است. اجازه دهید با یک تراکنش Explicit یک رکورد دیگر را اضافه کنیم:

BEGIN TRAN
INSERT INTO BigRows VALUES (8, 'a');
GO
SELECT [database_transaction_log_bytes_used] FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID ('PageSplitTest');
GO
database_transaction_log_bytes_used
———————————–
۱۲۲۸

 مقدار لاگ تولید شده منطقی است و آن چیزی است که انتظارش را داشتیم. حالا اگر بخواهم رکورد با مقدار c1=5 را اضافه کنم این رکورد برای حفظ نظم فیزیکی رکوردها (البته از طریق slot array انتهای page) باید به همین Page اضافه شود، در اینجا Page split اتفاق می افتد. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

— commit previous transaction
COMMIT TRAN
GO
BEGIN TRAN
INSERT INTO BigRows VALUES (5, 'a');
GO
SELECT [database_transaction_log_bytes_used] FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID ('PageSplitTest');
GO
database_transaction_log_bytes_used
———————————–
۶۷۲۴

سخن پایانی

برای ردیفی با اندازه ۱۰ بایت من به دو عدد ۲۴۰ و ۱۰۴۳۶ رسیدم ، چون من داده های دارای انحراف در یونیک بودن تولید کردیم:
نوع دیتاتایپ را به CHAR (10) تغییر دادم و سپس مقادیر ۱ ، ۲، ۳ ، ۴، ۶ و ۷ را یکبار و مقدار ۸ را ۲۵۶ بار درج کردم و بعد دو بار مقدار ۵ را درج کردم که باعث روی دادن Page split از نوع non-middle شد (یعنی نصف رکوردهای انتقالی همگی یک مقدار داشتند (۸)). Storage Engine همیشه یک Page split از نوع ۵۰/۵۰ انجام نمی دهد بلکه عمل انتقال رکورد ها را تا آنجایی ادامه می دهد که فضای کافی برای رکورد جدید باز شود (یعنی چندین بار عمل انتقال می تواند صورت بگیرد). در این حالت ۴۳ برابر لاگ بیشتر تولید شد! ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.