آشنایی با مکانیسم Truncate Table

آشنایی با مکانیسم Truncate Table

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

مقدمه

Truncate Tableجهت پاک نمودن داده‌­های یک Table یا Partition­های یک Partitioned Table مورد استفاده قرار می‌­گیرد. عمل Truncate بسیار سریع انجام می­‌شود. در این مقاله قصد داریم توضیح دهیم که SQL Server چگونه این کار را انجام می­‌دهد.
ممکن است تصور شود که عمل Truncate Table در واقع Log نمی­‌شود و این عمل لاگ رکوردی را در Log File نمی­‌نویسد، این تصور از آنجا ناشی می‌گردد که SQL Server می­‌تواند عملیات Truncate یک جدول بزرگ را با سرعت بسیار بالایی انجام دهد.

دوره آموزشی SQL Server ویژه برنامه‌نویسان
مثال زیر عدم صحت تصور فوق را نشان می‌­دهد. کد زیر یک Database و جدول ایجاد می­نماید و نشان می‌­دهد که ۱۰۰۰۰ رکورد درون جدول ذخیره گردیده است.

Use master
GO
Create Database MyDatabase
Alter Database MyDatabase Set Recovery Simple;
GO
Use MyDatabase
GO
Create Table MyTable
(ID Int Identity Not Null, MyColumn Char(8000) Default 'Truncate Table')
Set Nocount On
GO
Insert Into MyTable Default Values
GO 10000
Select Count(*) As N'RowCount' From MyTable

قطعه کد زیر جدول ایجاد شده در مرحله قبل را درون یک Transaction در واقع Truncate می­‌نماید و تعداد رکوردهای جدول را نمایش می‌دهد:

Begin Transaction
 Truncate Table MyTable
 Select Count(*) As N'RowCount' From MyTable

همان­طور که مشاهده می­‌نمایید جدول خالی می­‌باشد و تعداد رکوردها برابر با صفر است. اما می­‌توانیم Transaction را Rollback نماییم و همه رکوردهای حذف شده به جدول باز خواهند گشت.

Rollback Transaction
Select Count(*) As N'RowCount' From MyTable
Row Count:  10000

به وضوح مشخص است که عمل Truncate Table لاگ شده است در غیر این صورت عملیات Rollback انجام نمی­‌شد.
در ادامه دستور Truncate Table را مجدد اجرا می­‌نماییم و این بار تعداد Log Records را نیز بررسی می‌­کنیم ( دستور Truncate و Select مطابق تصویر زیر به صورت هم­زمان اجرا گردد):

CheckPoint
 GO 5
Truncate Table MyTable
 Select Count(*) As RowLogCount From fn_dblog (Null, Null)

همان­طور که در تصویر مشاهده می‌­گردد تعداد Log Records برابر با ۲۳ می‌­باشد.
در مثال فوق هر رکورد جدول درون یک Data File Page قرار دارد زیرا هر ردیف شامل دو Field است، یکی از نوع Int و دیگری از نوع Char(8000) می­‌باشد و هر Data File Page ظرفیتی برابر با ۸ کیلوبایت دارد.
به هر هشت Data File Page مجاور یک Extent گفته می­‌شود و به جدول MyTable قبل از اینکه Truncate شود ۱۲۵۰ تا Extent تعلق داشت.
اما عدد ۲۳ (تعداد لاگ رکوردها) با هیچ کدام از اعداد ۱۰۰۰۰ (تعداد Pageها) و ۱۲۵۰ (تعداد Extentها) متناسب نیست! در ادامه با مکانیسم Deffered-Drop آشنا خواهیم شد.
مکانیسم Deffered-Drop کامل شدن عملیات­‌های Truncate Table و Drop Table را سریعا شبیه سازی می­‌کند، اما فضاهایی که به جدول تعلق دارد و شامل Pageها و Extentها است باید در واقع Deallocate شوند. بنابراین اطلاعات مربوطه را درون Deffered-Drop Queue قرار می­‌دهد، این اطلاعات بعدا توسط Background Task پردازش می‌­شوند. تا این جا کل این عملیات تعداد کمی Log Records ایجاد می‌­نماید.
Background Task هر چند ثانیه یک بار اجرا می­شود و همه Pageها و Extentهایی که اطلاعات آن‌­ها درون Deffered-Drop Queue قرار گرفته است را Deallocate می‌­نماید.
اگر بعد از چند ثانیه مجددا دستور زیر را اجرا نماییم تعداد Log Records برابر با ۳۷۹۷ خواهد بود.

Select Count(*) As LogRecCount From fn_dblog (Null, Null)

ممکن است تعجب نمایید که چرا حداقل ۱۰۰۰۰ لاگ رکورد ایجاد نشد. (به ازای هر Page که Deallocate می­‌شود). این موضوع به این خاطر است که به ازای هر ۸ تا Data File Page متوالی که Deallocate شدن آن­‌ها در PFS Page تاثیر می­‌گذارد یک لاگ رکورد نوشته می­‌شود. (البته لاگ رکوردهای سیستمی هم نوشته می­‌شوند.)

نتیجه گیری

عمل Truncate Table به این خاطر سریع است که اولا از مکانیسم Deffered-Drop استفاده می­‌گردد، دوما به ازای حذف هر رکورد یک لاگ رکورد نوشته نمی­‌شود.

 

 

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

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

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

اولین نفر باش

title sign
دانلود مقاله
آشنایی با مکانیسم Truncate Table
فرمت PDF
3 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
مهدی قپانوری
مقالات
15 مقاله توسط این نویسنده
محصولات
1 دوره توسط این نویسنده
مهدی قپانوری

مهدی قپانوری بیش از 6 سال است که در زمینه‌های نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزه‌هایPerformance Tuning، Database Administration، Database Development و طراحی سیستم‌های OLTP می‌باشد. مهدی علاقه‌مند به R&D در حوزه‌های نوین SQL Server است.

title sign
دیدگاه کاربران

    • Nonclustered Index ها

    • با سلام،
      یک سوال داشتم،
      آیا Truncate کردن یک یا چند پارتیشن باعث Fragment شدن ایندکس های جدول می شود؟
      ممنون میشم راهنمایی کنید.

      • درود بر شما
        بستگی به این دارد که پارتیشن فانکشن روی چه ستونی تعریف شده باشد. ایندکسی که روی آن ستون است خیلی fragment نمیشود اما ایندکس های دیگر بسیار fragment شده و نیاز به rebuild دارند.
        من روی دیتا ۶۰ میلیون (تستی) انجام دادم.
        بر روی ایندکس های که پارتیشن روی آنها انجام شد و همان فایل Fragment ایجاد میشد.
        مابقی ایندکس و فایل گروپ Fragment ندارند.

        • سلام،
          بستگی به Align بودن یا نبودن Nonclusterd Index ها دارد، می توانیم Nonclusterd Index ها را نیز پارتیشن نمائیم اما باید به تبعات این کار توجه داشته باشیم که تاثیر منفی بر روی Performance نداشته باشد.
          موفق باشید.

    • با سلام،
      یک سوال داشتم،
      آیا Truncate کردن یک یا چند پارتیشن باعث Fragment شدن ایندکس های جدول می شود؟
      ممنون میشم راهنمایی کنید.

      • درود بر شما
        بستگی به این دارد که پارتیشن فانکشن روی چه ستونی تعریف شده باشد. ایندکسی که روی آن ستون است خیلی fragment نمیشود اما ایندکس های دیگر بسیار fragment شده و نیاز به rebuild دارند.

        من روی دیتا ۶۰ میلیون (تستی) انجام دادم.
        بر روی ایندکس های که پارتیشن روی آنها انجام شد و همان فایل Fragment ایجاد میشد.
        مابقی ایندکس و فایل گروپ Fragment ندارند.

        • سلام،
          بستگی به Align بودن یا نبودن Nonclusterd Index ها دارد، می توانیم Nonclusterd Index ها را نیز پارتیشن نمائیم اما باید به تبعات این کار توجه داشته باشیم که تاثیر منفی بر روی Performance نداشته باشد.
          موفق باشید.

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