nikamooz logo
nikamooz logo
0
سبد خرید
پشت صحنه و آنالیز دستور Delete در 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 ، به مقاله زیر مراجعه کنید.
 
دستورهای SQL Server

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

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

اولین نفر باش

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

دوره آموزشی کوئری نویسی در SQL Server
2.190.000 تومان
title sign
مقالات مرتبط
ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده
۰۲ آبان
SQL Server

ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده
تیم فنی نیک آموز
معرفی Performance Monitor ابزار مانیتورینگ SQL Server
۱۵ مهر
SQL Server

معرفی Performance Monitor ابزار مانیتورینگ SQL Server
تیم فنی نیک آموز
راهنمای جامع مانیتورینگ بکاپ ها در SQL Server
۱۱ مهر
SQL Server

راهنمای جامع مانیتورینگ بکاپ ها در SQL Server
تیم فنی نیک آموز
Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن
۰۸ مهر
SQL Server

Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن
تیم فنی نیک آموز
title sign
دیدگاه کاربران

    • با سلام و خسته نباشید خدمت شما
      از بابت این مقاله بسیار عالی بسیار عالی واقعا ممنونم فقط یک سئوال داشتم و آن این هست که اگر کل رکوردها به صورت فیزیکی اگر حذف بشوند نه یک page چه کوئری را باید اجرا کنیم
      برای مثال چون در یک page آیتمی وجود دارد به نام m_ghostreccnt که اگر مساوی با ۰ بود یعنی اطلاعات آن page به صورت فیزیکی حذف شده است حالا آیا کوئری وجود دارد که بشود در کل page ها این کار را انجام داد یعنی مثلا بفهمیم که کل اطلاعات به صورت فیزیکی حذف شده است برای کل اطلاعات منظورم هست.

      با تشکر از شما

    • با سلام و خسته نباشید خدمت شما
      از بابت این مقاله بسیار عالی بسیار عالی واقعا ممنونم فقط یک سئوال داشتم و آن این هست که اگر کل رکوردها به صورت فیزیکی اگر حذف بشوند نه یک page چه کوئری را باید اجرا کنیم
      برای مثال چون در یک page آیتمی وجود دارد به نام m_ghostreccnt که اگر مساوی با ۰ بود یعنی اطلاعات آن page به صورت فیزیکی حذف شده است حالا آیا کوئری وجود دارد که بشود در کل page ها این کار را انجام داد یعنی مثلا بفهمیم که کل اطلاعات به صورت فیزیکی حذف شده است برای کل اطلاعات منظورم هست.
      با تشکر از شما

    •    جالب بود چقدر قشنگ

    • سلام عالی بود
      میتونید برای بقیه عملیات مثه اضافه کردن یا آپذیت هم ی مقاله کامل و مفصل بزارید

      • سلام

        اگر وقتی بود حتما آماده می کنم.
        با تشکر

    • Excellent!
         دوستت دارم، این کاره!

    •     تورج عزیز: متاسفانه اشتباه فرمودید. Checkpoint و Ghost Cleanup Task دو Thread متفاوت هستند و به صورت Asynchronous اجرا می شوند. Ghost Cleanup Task فقط Slot Offset را از Data Page حذف می کند ولی این عملیات قبل از Checkpoint انجام می شود و فقط در حافظه سیستم است. وقتی LazyWriter اجرا می شود Data Page ها از حافظه به دیسک Flush شده و نوع آنها از کثیف به تمیز تبدیل می شود.

      در نظر داشته باشید که Checkpoint فقط داده های کثیف را وارد دیسک می کند اما نوع آنها را از کثیف به تمیز تغییر نمی دهد.
      اینجور مباحث رو نمیشه به صورت نوشتاری توضیح داد. باید حتما رو در رو و با یک تخته وایت برد توضیح بدم. 
      «دوستت دارم!» یادت نره!

    •    پس می توان گفت که Ghost Cleanup Task فقط رکوردهایی را حذف می کند که بعد از ثبت لاگ آنها حتماً CheckPoint اتفاق افتاده، اگه اینطوره بگو که بگم دوستت دارم!

    •     فرض کنید تراکنش commit شد رکورد به طور فیزیکی حذف شد و قبل از Checkpoint ،

      در SQL Server حالت crash اتفاق افتاد، پس از  Start دوباره SQL Server، در فاز Recovery باید رکورد های لاگ پس از آخرین Checkpoint دوباره اجرا شوند. در اینجا رکورد به صورت فیزیکی پاک شده چطور SQL Server می تونه اون رکورد رو به شکل حذف شده در Page مربوطه نشون بده؟
      امیدوارم سوالم رو خوب رسونده باشم.

      •     البته این رو هم در نظر داشته باشید که تا موقعی که Ghost Cleanup Task اجرا نشه رکورد به صورت فیزیکی هنوز حذف نشده.

      • اگر قبل از Checkpoint کرش کنه. یعنی Page مورد نظر روی دیسک Flush نشده. پس بعد از شروع دوباره SQL Server در قسمت REDO تراکنشهای بعد از اخرین CHeckpoint دوباره اجرا می شه. در Error Log شما پیغامهایی با مضنون Transaction Forwarded مواجه می شود. حالا در قسمت Redo در حقیقت SQL Server آن Page مورد نظر را در حافظه بارگذاری کرده و تغییرات را انجام می دهد و در آخر یک عملیات Checkpoint انجام می شود.

           

    •     سلام

      ممنون بابت مقاله،
      اگر یک تراکنش DELETE به شکل EXPLICIT اجرا شود و و Commit اجرا نشود، آیا این رکورد باز هم به شکل فیزیکی حذف می شود؟

    •     فکر کنم مقاله به صورت کامل انتشار نیافته. لطفا یک چکی بکنید.

      •    اصلاح گردید. با تشکر

        •     ممنون. این مشکل ذخیره سازی مقالات رو هم اگر لطف کنید درستش کنید ممنون می شم.

    • حمید عزیز خیلی عالی بود

      تورج عزیز یک نگاهی به لینک های زیر بیاندازید. مثال اون رو یه کوچولو تغییر بدهید متوجه پاسخ سوالتون می شوید. (Transaction را Commit نکنید و محتوای لاگ را بررسی کنید)
      در تکمیل مطلب (حالت هایی که Ghost Recordها پاک می شوند)
      در تکمیل مقاله حمید عزیز حتما به مطلب زیر هم نگاه کنید

      مقاله زیر یک Trace Flag را معرفی کرده که در عملکرد پروسه Ghost Record تاثیر می گذارد البته نباید اون را همینطوری روی سرور فعال کرد.