خانه SQL Server Page split تا چه حد می تواند گران تمام شود SQL Server افزایش سرعت SQL Server نوشته شده توسط: تورج عزیزی تاریخ انتشار: ۱۴ مهر ۱۳۹۴ آخرین بروزرسانی: ۲۹ بهمن ۱۴۰۰ زمان مطالعه: 10 دقیقه ۵ (۱) مقدمه این مقاله در مورد هزینه تحمیلی Page split بر Performance کلی دیتابیس است. از آنجایی که SQL Server باید نظم فیزیکی رکورد ها را بر اساس کلید ایندکس حفظ کند در صورتی که در Page مربوطه جایی برای گنجاندن رکورد جدید نباشد SQL Server باید حدود ۵۰% از رکوردهای درون Page را به Page ای جدید انتقال دهد تا جا برای رکورد جدید باز شود البته برای رکوردهای بزرگ ممکن است با یک 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 اتفاق می افتد: — 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 ———————————– ۶۷۲۴ Wow! ۵.۵ برابر لاگ بیشتر که ناشی از لاگ تراکنش سیستمی است که عمل Page split را انجام می دهد! این نرخ اگر اندازه ردیف کوچکتر شود می تواند بدتر هم بشود. برای ردیفی با اندازه ۱۰۰ بایت (از همان کد بالا استفاده کنید فقط به CHAR(100) تغییر دهید) ۶۷ ردیف را درج کنید و البته یک شکاف هم ایجاد کنید (یعنی یک کلید را جا بیندازید مثل c1=5 در بالا) با درج رکورد ۶۸ ام Page split روی میدهد و دو عدد برابر ۳۲۸ و ۵۹۲۴ خواهند بود (یعنی اندازه لاگ تولید شده قبل و بعد از Page split و البته در تست شما این مقادیر می تواند متفاوت باشد). Page split روی داده در این حالت ۱۸ برابر لاگ بیشتر تولید کرده! برای ردیفی با اندازه ۱۰ بایت من به دو عدد ۲۴۰ و ۱۰۴۳۶ رسیدم ، چون من داده های دارای انحراف در یونیک بودن تولید کردم: نوع دیتاتایپ را به CHAR(10) تغییر دادم و سپس مقادیر ۱ ، ۲، ۳ ، ۴، ۶ و ۷ را یکبار و مقدار ۸ را ۲۵۶ بار درج کردم و بعد دو بار مقدار ۵ را درج کردم که باعث روی دادن Page split از نوع non-middle شد (یعنی نصف رکوردهای انتقالی همگی یک مقدار داشتند (۸)). Storage Engine همیشه یک Page split از نوع ۵۰/۵۰ انجام نمی دهد بلکه عمل انتقال رکورد ها را تا آنجایی ادامه می دهد که فضای کافی برای رکورد جدید باز شود (یعنی چندین بار عمل انتقال می تواند صورت بگیرد). و در این حالت ۴۳ برابر لاگ بیشتر تولید شد! چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ محسن بندامیر ۰۸ / ۰۸ / ۹۴ - ۱۰:۰۹ مقاله جالبی بود.اما در حالت Char(100 فکر کنم منظورتون ۷۷ و ۷۸ بوده به جای اعداد ۶۷و۶۸ چون هر صفحه ۸۰۶۰ Byte فضا داره و هر رکوردمون شده اینجا ۱۰۴ Bye در نتیجه: 8060/104=77.5 پاسخ به دیدگاه en.bakhtiari@yahoo.com ۰۶ / ۰۸ / ۹۴ - ۰۸:۳۷ راه حل این مشکل هم اگر بیان می شد خوب بود پاسخ به دیدگاه مسعود طاهری ۰۶ / ۰۸ / ۹۴ - ۰۸:۵۶ ۱- Rebuild یا Reorganize کردن ایندکس ها (با توجه به شرایط) ۲- تنظیم درست Fillfactor ۳- پرهیز از ایجاد ایندکس های اشتباه ۴- و… این ها مواردی است که باعث جلوگیری از این اتفاقات می شود. پاسخ به دیدگاه تورج عزیزی ۱۹ / ۰۷ / ۹۴ - ۰۹:۰۴ و البته یک نکته بسیار مهم دیگر هم اینکه در حین انجام عمل Page Split ، پیج حاوی رکورد ها با استفاده از Latch قفل شده و تراکنش هایی که درخواست انجام خواندن یا نوشتن روی این Page دارند Block می شوند. پاسخ به دیدگاه حمید ج. فرد ۱۸ / ۰۷ / ۹۴ - ۰۸:۲۵ البته این موضوع رابطه خیلی شدید و نزدیکی با fill factor دارد پاسخ به دیدگاه تورج عزیزی ۱۸ / ۰۷ / ۹۴ - ۰۱:۳۴ http://www.sqlballs.com/2012/08/how-to-find-bad-page-splits.html پاسخ به دیدگاه تورج عزیزی ۱۸ / ۰۷ / ۹۴ - ۰۱:۳۴ http://www.sqlballs.com/2012/08/how-to-find-bad-page-splits.html پاسخ به دیدگاه تورج عزیزی ۱۸ / ۰۷ / ۹۴ - ۰۱:۲۱ یک مقاله فوق العاده برای مطالعه بیشتر: پاسخ به دیدگاه Hamid J. Fard ۱۸ / ۰۷ / ۹۴ - ۰۱:۰۲ « Storage Engine همیشه یک Page split از نوع ۵۰/۵۰ انجام نمی دهد بلکه عمل انتقال رکورد ها را تا آنجایی ادامه می دهد که فضای کافی برای رکورد جدید باز شود» در رابطه با جمله بالا. نکته اینجاست که عمل ۵۰/۵۰ یک بار اجرا شده و بعد فضای کافی برای رکورد جدید چک می شود اگر رکورد جدید در datapage دوم جا نشود یک data page جدید ساخته شده و رکورد در آن جای می گیرد. مقاله خوبی بود. موفق باشید. پاسخ به دیدگاه مسعود طاهری ۱۸ / ۰۷ / ۹۴ - ۰۹:۵۱ عالی بود تورج جان. در ادامه این مبحث اگر وقت کردید DMV زیر را هم برای مانیتور کردن وضعیت Fragmentation ایندکس ها معرفی کنید https://msdn.microsoft.com/en-us/library/ms188917.aspx متشکرم پاسخ به دیدگاه مسعود طاهری ۱۸ / ۰۷ / ۹۴ - ۰۹:۵۱ عالی بود تورج جان. در ادامه این مبحث اگر وقت کردید DMV زیر را هم برای مانیتور کردن وضعیت Fragmentation ایندکس ها معرفی کنید https://msdn.microsoft.com/en-us/library/ms188917.aspx متشکرم پاسخ به دیدگاه