خانه SQL Server پشت صحنه و آنالیز دستور Delete در SQL Server 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 پاک شده. چه رتبه ای می‌دهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش برچسب ها # Database Engine# SQL Server# T-SQL# آموزش SQL Server# افزایش Performance# دستور DELETE# دستور Delete در SQL Server معرفی نویسنده مقالات 6 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده حمید فرد پروفایل نویسنده معرفی محصول مهدی شیشه بری دوره آموزش کوئری نویسی در SQL Server 1.000.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ ha_zarabi_vb6@outlook.com ۰۶ / ۰۳ / ۹۶ - ۰۰:۲۱ با سلام و خسته نباشید خدمت شما از بابت این مقاله بسیار عالی بسیار عالی واقعا ممنونم فقط یک سئوال داشتم و آن این هست که اگر کل رکوردها به صورت فیزیکی اگر حذف بشوند نه یک page چه کوئری را باید اجرا کنیم برای مثال چون در یک page آیتمی وجود دارد به نام m_ghostreccnt که اگر مساوی با ۰ بود یعنی اطلاعات آن page به صورت فیزیکی حذف شده است حالا آیا کوئری وجود دارد که بشود در کل page ها این کار را انجام داد یعنی مثلا بفهمیم که کل اطلاعات به صورت فیزیکی حذف شده است برای کل اطلاعات منظورم هست. با تشکر از شما پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۰۶ / ۰۳ / ۹۶ - ۰۰:۲۱ با سلام و خسته نباشید خدمت شما از بابت این مقاله بسیار عالی بسیار عالی واقعا ممنونم فقط یک سئوال داشتم و آن این هست که اگر کل رکوردها به صورت فیزیکی اگر حذف بشوند نه یک page چه کوئری را باید اجرا کنیم برای مثال چون در یک page آیتمی وجود دارد به نام m_ghostreccnt که اگر مساوی با ۰ بود یعنی اطلاعات آن page به صورت فیزیکی حذف شده است حالا آیا کوئری وجود دارد که بشود در کل page ها این کار را انجام داد یعنی مثلا بفهمیم که کل اطلاعات به صورت فیزیکی حذف شده است برای کل اطلاعات منظورم هست. با تشکر از شما پاسخ به دیدگاه علی اکبر شعبانی ۱۵ / ۰۴ / ۹۵ - ۰۷:۳۵ جالب بود چقدر قشنگ پاسخ به دیدگاه علی اکبر شعبانی ۱۵ / ۰۴ / ۹۵ - ۰۷:۳۵ جالب بود چقدر قشنگ پاسخ به دیدگاه MEHDI ۱۸ / ۰۷ / ۹۴ - ۰۰:۴۵ سلام عالی بودمیتونید برای بقیه عملیات مثه اضافه کردن یا آپذیت هم ی مقاله کامل و مفصل بزارید پاسخ به دیدگاه Hamid J. Fard ۲۴ / ۰۷ / ۹۴ - ۱۱:۲۰ سلام اگر وقتی بود حتما آماده می کنم. با تشکر پاسخ به دیدگاه MEHDI ۱۸ / ۰۷ / ۹۴ - ۰۰:۴۵ سلام عالی بودمیتونید برای بقیه عملیات مثه اضافه کردن یا آپذیت هم ی مقاله کامل و مفصل بزارید پاسخ به دیدگاه Hamid J. Fard ۲۴ / ۰۷ / ۹۴ - ۱۱:۲۰ سلام اگر وقتی بود حتما آماده می کنم. با تشکر پاسخ به دیدگاه تورج عزیزی ۲۲ / ۰۶ / ۹۴ - ۰۲:۳۳ Excellent! دوستت دارم، این کاره! پاسخ به دیدگاه Hamid J. Fard ۲۲ / ۰۶ / ۹۴ - ۰۵:۴۳ تورج عزیز: ممنون. 🙂 پاسخ به دیدگاه تورج عزیزی ۲۲ / ۰۶ / ۹۴ - ۰۲:۳۳ Excellent! دوستت دارم، این کاره! پاسخ به دیدگاه Hamid J. Fard ۲۲ / ۰۶ / ۹۴ - ۰۵:۴۳ تورج عزیز: ممنون. 🙂 پاسخ به دیدگاه Hamid J. Fard ۲۲ / ۰۶ / ۹۴ - ۰۲:۲۲ تورج عزیز: متاسفانه اشتباه فرمودید. Checkpoint و Ghost Cleanup Task دو Thread متفاوت هستند و به صورت Asynchronous اجرا می شوند. Ghost Cleanup Task فقط Slot Offset را از Data Page حذف می کند ولی این عملیات قبل از Checkpoint انجام می شود و فقط در حافظه سیستم است. وقتی LazyWriter اجرا می شود Data Page ها از حافظه به دیسک Flush شده و نوع آنها از کثیف به تمیز تبدیل می شود. در نظر داشته باشید که Checkpoint فقط داده های کثیف را وارد دیسک می کند اما نوع آنها را از کثیف به تمیز تغییر نمی دهد. اینجور مباحث رو نمیشه به صورت نوشتاری توضیح داد. باید حتما رو در رو و با یک تخته وایت برد توضیح بدم. «دوستت دارم!» یادت نره! پاسخ به دیدگاه Hamid J. Fard ۲۲ / ۰۶ / ۹۴ - ۰۲:۲۲ تورج عزیز: متاسفانه اشتباه فرمودید. Checkpoint و Ghost Cleanup Task دو Thread متفاوت هستند و به صورت Asynchronous اجرا می شوند. Ghost Cleanup Task فقط Slot Offset را از Data Page حذف می کند ولی این عملیات قبل از Checkpoint انجام می شود و فقط در حافظه سیستم است. وقتی LazyWriter اجرا می شود Data Page ها از حافظه به دیسک Flush شده و نوع آنها از کثیف به تمیز تبدیل می شود. در نظر داشته باشید که Checkpoint فقط داده های کثیف را وارد دیسک می کند اما نوع آنها را از کثیف به تمیز تغییر نمی دهد. اینجور مباحث رو نمیشه به صورت نوشتاری توضیح داد. باید حتما رو در رو و با یک تخته وایت برد توضیح بدم. «دوستت دارم!» یادت نره! پاسخ به دیدگاه 1 2