خانه SQL Server پشت صحنه و آنالیز دستور Delete در SQL Server SQL Server دستورات SQL نوشته شده توسط: حمید فرد تاریخ انتشار: ۲۱ شهریور ۱۳۹۴ آخرین بروزرسانی: ۱۹ مهر ۱۴۰۲ زمان مطالعه: 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 ، به مقاله زیر مراجعه کنید. چه رتبه ای می‌دهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش معرفی نویسنده مقالات 6 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده حمید فرد معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 2.190.000 تومان مقالات مرتبط ۲۶ شهریور SQL Server سرویس SQL Server Browser چیست؟ آشنایی با نحوه راه اندازی و کاربردها تیم فنی نیک آموز ۱۹ شهریور SQL Server علت Attach نشدن دیتابیس در SQL Server و راه حل آن تیم فنی نیک آموز ۱۱ شهریور SQL Server پروتکل های SSL و TLS چه تفاوت هایی دارند؟ تیم فنی نیک آموز ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مسعود طاهری ۲۱ / ۰۶ / ۹۴ - ۰۲:۴۸ حمید عزیز خیلی عالی بود تورج عزیز یک نگاهی به لینک های زیر بیاندازید. مثال اون رو یه کوچولو تغییر بدهید متوجه پاسخ سوالتون می شوید. (Transaction را Commit نکنید و محتوای لاگ را بررسی کنید) http://www.sqlskills.com/blogs/paul/ghost-cleanup-redux/ در تکمیل مطلب (حالت هایی که Ghost Recordها پاک می شوند) http://www.agile-code.com/blog/sql-server-ghost-records-in-a-nutshell/ در تکمیل مقاله حمید عزیز حتما به مطلب زیر هم نگاه کنید http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/ مقاله زیر یک Trace Flag را معرفی کرده که در عملکرد پروسه Ghost Record تاثیر می گذارد البته نباید اون را همینطوری روی سرور فعال کرد. http://www.sqlskills.com/blogs/paul/turning-off-the-ghost-cleanup-task-for-a-performance-gain/ پاسخ به دیدگاه
