پشت صحنه و آنالیز دستور Delete در SQL Server

پشت صحنه و آنالیز دستور Delete در SQL Server

نوشته شده توسط: حمید فرد
تاریخ انتشار: ۲۱ شهریور ۱۳۹۴
آخرین بروزرسانی: ۱۹ مهر ۱۴۰۲
زمان مطالعه: 24 دقیقه
۰
(۰)

مقدمه

در این مقاله می خوام عملیات دستور Delete در SQL Server را توضیح بدم. بیشتر جنبه اکادمیک داره و حداقل شما خواننده عزیز از روند کاری این دستور مطلع می شوید.

وقتی شما دستور Delete را اجرا می کنید رکوردهای مورد نظر در همان لحظه به صورت فیزیکی از جداول حذف نمی شوند بلکه یک سری عملیاتی در پشت صحنه انجام می شود.
در هنگام اجرا دستور Delete یکی از مشخصه های رکورد به نام Record Type از مقدار Primary_Record به Ghost_Data_Record تبدیل می شود. این تغییر مقدار باعث می شود که SQL Server آن رکورد را دیگر نخواند ولی در نظر داشته باشید که Ghost Record تا زمانی که تراکنش Commit نشده باشد به صورت فیزیکی از جدول حذف نمی شود.
در مرحله دوم یک نخ به نام Ghost CleanUp Task در هر ۵ ثانیه شروع به خواندن تمامی PFS  های هر پایگاه داده می کند که بتواند رکورد های Ghost را شناسایی و حذف کند. حذف رکورد ها به صورت فیزیکی به این صورت است که Ghost Cleanup Task شماره ردیف رکورد را حذف می کند تا از Deallocate شدن Data Page جلوگیری کند.
به یاد داشته باشید که Ghost Record را نمی توان حتی با NOLOCK یا Read Uncommitted Isolation Level خواند و بازیابی کرد.
Ghost Cleanup Task همانطور که گفته شد هر ۵ ثانیه اجرا شده و حداکثر تا ۱۰ Data Page را شناسایی میکند این محدودیت باعث بالا رفتن بازدهی سیستم در زمان حذف رکوردها می باشد. در انتهای شناسایی رکوردهای حذف شده اگر دیگر رکورد حذف شده ای در پایگاه داده وجود نداشت Ghost Cleanup Task مقدار m_GhostRecCnt را به مقدار ۰ تغییر می دهد. توجه داشته باشید که هر Data Page این مشخصه را دارا می باشد.
وقت مثال رسید!!! ووووو!!!
توجه: بسیاری از این کدها از مایکروسافت معرفی نشده اند و شما نیز نباید در سیستم اصلی آنها را اجرا کنید. تمامی عواقب اجرا این کدها در سیستم اصلی به عهده شخص شما می باشد.
کد زیر زمان اجرا Ghost Cleanup را بازیابی می کند.
 Use Master;
go
Drop Table Requests;
go
While (Object_ID('Requests') Is NULL)
Begin
Select * Into Requests From sys.dm_exec_requests Where Command like '%ghost%';
If(@@Rowcount < 1)
Drop Table Requests;
End
go
select * from Requests;
حال به چه صورت می توانیم Ghost Record ها را بازیابی کنیم. کد زیر یک جدول ساخته و یک سری رکورد در آن درج می کند و در انتها با دستور DBCC IND اطلاعات IAM و ROOT و LEAF را برای جدول مشخص شده بازیابی می کند.
Create Table Temp (ID Int Identity(1,1), Name varchar(30), Designation varchar(50));
Create Clustered Index CIX on Temp(ID);
go
Insert Into Temp (Name,Designation)
    Values    ('Hamid J. Fard','Data Platform Expert'),
            ('Melissa Lenjap', 'Managing Director'),
            ('Samantha David','Marketing Manager'),
            ('John Smith','Executive');
go
Begin Transaction
Delete From Temp Where Name = 'John Smith';
go
DBCC IND ('tempdb','Temp',1);
go
DBCC TRACEON(3604);
go
--Place the PageID at the 3rd Parameter.
DBCC PAGE ('Tempdb',1,317,3);
go
حال به ساختار Data Page زیر یک نگاهی بی اندازید… الان متوجه شدید که چه تغییراتی در قسمت PAGE HEADER انجام شده.
و در ادامه ساختار در قسمت ALLOCATION STATUS مشاهده می کنید که Record_Type تغییر کرده است.
حالا چطور می تونیم بفهمیم که در Transaction Log توسط Ghost Cleanup چه چیزی ثبت شده؟؟
برای یافتن این حقیقت باید دست به دامن فانکشنی بشیم به نام fn_dblog.
در عکس زیر دو تراکنش آخر نشان دهنده این است که رکورد مورد نظر به صورت Ghost Record در آمده است.
به دلیل اینکه تعداد رکورها زیاد نبوده و تعداد Data Pageها بیش از ۸ عدد نبوده SQL Server جدول را در Mix Extent اختصاص داده که وقتی ما رکورد را حذف می کنیم نوع عملیات LOP_SET_BITS در GAM  و SGAM اتفاق بیفتد. به عکس زیر نگاهی کنید. توجه کنید که شماره تراکنش عملیات LOP_SET_BITS و LOP_DELETE_ROWS متفاوت است یعنی SQL Server این دو تراکنش را جدا از هم اجرا می کند.
یک سوال آیا تغییر مقدار m_GhostRecCnt در تراکنش جای گرفته؟ البته این را هم باید از فایل تراکنش بازیابی کنیم. کد زیر این کار را انجام می دهد.
select * from fn_dblog(null,null) where AllocUnitName Like '%temp%' or Operation like '%set%'

به دلیل اینکه تعداد رکورها زیاد نبوده و تعداد Data Pageها بیش از ۸ عدد نبوده SQL Server جدول را در Mix Extent اختصاص داده که وقتی ما رکورد را حذف می کنیم نوع عملیات LOP_SET_BITS در GAM  و SGAM اتفاق بیفتد. به عکس زیر نگاهی کنید. توجه کنید که شماره تراکنش عملیات LOP_SET_BITS و LOP_DELETE_ROWS متفاوت است یعنی SQL Server این دو تراکنش را جدا از هم اجرا می کند.
تراکنش را Commit می کنیم. حالا زمان آن رسیده که یک بار دیگر ساختار Data Page را مشاهده کنیم.
 DBCC PAGE ('Tempdb',1,317,2);

اووووپپسس !!!! همانطور که می بینید رکورد سر جاش هست ولی Slot Offset از انتهای Data Page پاک شده.

 

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.
 
دستورهای SQL Server

چه رتبه ای می‌دهید؟

میانگین ۰ / ۵. از مجموع ۰

اولین نفر باش

title sign
معرفی نویسنده
حمید فرد
مقالات
6 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
حمید فرد
title sign
دیدگاه کاربران

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link