بررسی دستور Shrink در SQL Server

بررسی دستور Shrink در SQL Server

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

مقدمه

Shrink در لغت به معنی جمع شدن و یا چروک شدن می‌باشد. با در نظر گرفتن همین مفهوم می‌توان گفت Shrink کردن فرآیندی است که در آن فضای Data File و Log File جمع و جور می‌شود. در ادامه به توضیح بررسی دستور Shrink در SQL Server می پردازیم.
فضای Data File و Log File
همانطور که در تصویر بالا مشاهده می‌کنید طی فرآیند دستور Shrink در SQL فضای خالی فایل‌های بانک اطلاعاتی تا حد امکان از بین رفته و داده‌ها در یک قسمت جمع می‌گردند.
جهت Shrink کردن بانک اطلاعاتی می‌توان از دستور DBCC ShrinkDatabase استفاده نمود شکل کلی این دستور به صورت زیر می‌باشد.

DBCC ShrinkDatabase
)
database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
(

پارامترهای این دستور به شرح زیر می‌باشد.

١- Database Name

نام بانک اطلاعاتی که قرار است عملیات Shrink بر روی فایل‌های آن اتفاق بیافتد. لازم به ذکر است شما می‌توانید به جای نام بانک اطلاعاتی از ID بانک اطلاعاتی هم به عنوان پارمتر جایگزین استفاده نمایید.

٢- Target Percent

این پارامتر مشخص می‌کند که چند درصد از فضای خالی فایل مورد نظر پس از Shrink در دسترس باشد.

دوره کوئری نویسی نیک آموز

٣- پارامتر سوم شامل دو حالت زیر است.

  • TruncateOnly: در این حالت چنانچه در انتهای فایل مورد نظر فضای خالی وجود داشته باشد این فضای خالی به سیستم عامل بازگشت داده می‌شود. همچنین اگر TruncateOnly با Target Percent تواماً مورد استفاده قرار گیرد در این صورت Target Percent نادیده گرفته می‌شود. نکته مهمی که درباره TruncateOnly وجود دارد این است که اگر این پارامتر با دستور DBCC ShrinkDatabase مورد استفاده قرار گیرد تاثیر آن بر Log File می‌باشد و چنانچه شما خواهان تاثیر عملکرد آن بر روی Data File باشید باید از دستور DBCC ShrinkFile استفاده نمایید.
  •  NoTruncate: عملکرد این حالت صرفاً بر روی Data File بوده و طی آن آخرین فضای پر (Page پر) در Data File به اولین فضای خالی (Page خالی) منتقل می‌شود. طی این حالت Pageهای Data File به بهترین نحو ممکن پر می‌شود. اما این موضوع باعث کاهش Performance بانک اطلاعاتی می‌شود. (دلیل آن در ادامه بررسی خواهد شد.)
    نکته مهمی که درباره NoTruncate وجود دارد این است که تاثیر این پارامتر چه با دستور DBCC ShrinkFile و چه با دستور DBCC ShrinkDatabase صرفاً بر روی Data File می‌باشد. همچنین این در صورت استفاده از این پارامتر هیچ فضای خالی به سیستم عامل بازگشت داده نمی‌شود.
    مثال : دستور زیر را در نظر بگیرید
DBCC ShrinkDatabase(N'MyDB',NOTRUNCATE)

تاثیر اجرای این دستور بر روی Data Fileهای بانک اطلاعاتی بوده و طی آن جابجایی بین Pageهای بانک اطلاعاتی رخ می‌دهد. بدین صورت که آخرین Page پر به اولین Page خالی منتقل می‌شود. تصویر زیر این موضوع را به درستی نمایش می‌دهد.
بررسی دستور Shrink در SQL Serverاما اگر یادتان باشد در ابتدای مقاله اشاره شده که ShrinkDatabase به صورت NoTruncate کارایی بانک اطلاعاتی را پایین می‌آورد. دلیل این موضوع این است که در طی این حالت با توجه به اینکه آخرین Page پُر به اولین Page خالی منتقل می‌شود اندیکس‌ها Fragment می‌شوند.

اگر بخواهیم این موضوع را دقیق‌تر بررسی کنیم باید به تصویر زیر دقت کنید. قبل از انجام عملیات Shrink داده‌های ما (X1 الی X5) به شکلی تقریباً منظم (مطابق آدرس منطقی) کنار هم قرار گرفته‌اند. پس از انجام عملیات Shrink مطابق تعریف ارائه شده برای حلت TruncateOnly آخرین فضای پر به اولین فضای خالی منتقل می‌شود. در طی این حالت چینش داده‌های ما کلاً عوض می‌شود.که این موضوع کارایی بانک اطلاعاتی را پایین می‌آورد.
عملیات Shrinkپس به طور خلاصه باید گفت که Shrink  کردن Data File باعث بوجود آمدن Fragmentation در ایندکس‌ها و جداول می‌شود که طی این حالت آدرس منطقی و فیزیکی Pageها یکسان نخواهد بود و این موضوع باعث می‌شود که کوئری‌های ما IO بیشتری جهت واکشی Data داشته باشند.

چند نکته مهم درباره دستور Shrink در SQL Server

١- دستور DBCC ShrinkFile جهت Shrink کردن یکی از فایل‌های بانک اطلاعاتی مورد استفاده قرار می‌گردد. پارامترهای آن مشابه به دستور DBCC ShrinkDatabase می‌باشد. البته لازم به ذکر است این دستور یک پارامتر اضافی هم دارد. (خارج از موضوع بحث می‌باشد.) جهت کسب اطلاعات بیشتر در مورد این دستور می‌توانید به این لینک مراجعه کنید.
٢- در محیط‌های عملیاتی خصیصه Auto Shrink بانک اطلاعاتی را به هیچ عنوان True نکنید.
Auto Shrink بانک اطلاعاتیخوب تا اینجا با مفهوم Shrink آشنا شدیم در ادامه هدف‌مان این است که وضعیت Fragmentation یک جدول قبل از انجام عملیات Shrink و پس از انجام عملیات Shrink بررسی نماییم.
جهت انجام اینکار مراحل زیر را به ترتیب دنبال نمایید.

١- ایجاد بانک اطلاعاتی تستی 

طی این مرحله وجود بانک اطلاعاتی بررسی شده و در صورتیکه بانک اطلاعاتی وجود داشته باشد حذف و پس از آن پروسه ایجاد بانک اطلاعاتی انجام می‌شود.

USE master
GO
IF DB_ID('Test_Shrink')>0
DROP DATABASE Test_Shrink
GO
CREATE DATABASE Test_Shrink
GO

٢- ایجاد دو جدول تستی 

طی این مرحله وجود جداول بررسی شده و در صورتیکه جداول در بانک اطلاعاتی وجود داشته باشد حذف و پس از آن ایجاد می‌گردند. به ازای  جداول ایجاد شده دو Constraint در نظر گرفته شده است که یکی از آنها به عنوان Primary Key و دیگری به عنوان Unique Key در نظر گرفته شده است.

USE Test_Shrink
GO
IF OBJECT_ID('Employees1')>0
DROP TABLE Employees1
GO
CREATE TABLE Employees1
(
,EmployeeID INT IDENTITY(1,1)
,SSN INT
,FirstName NCHAR(2000)
,LastName NCHAR(2000)
,CONSTRAINT PK_Employees1 PRIMARY KEY (EmployeeID)
CONSTRAINT UK_SSN1 UNIQUE (SSN)
)
GO
IF OBJECT_ID('Employees2')>0
DROP TABLE Employees2
GO
CREATE TABLE Employees2
(
,EmployeeID INT IDENTITY(1,1)
,SSN INT
,FirstName NCHAR(2000)
,LastName NCHAR(2000)
,CONSTRAINT PK_Employees2 PRIMARY KEY (EmployeeID)
CONSTRAINT UK_SSN2 UNIQUE (SSN)
(
GO

نکته : با توجه به اینکه هدف این مثال بوجود آوردن حجم بالا برای جداول Data Typeهای موجود در جداول NChar در نظر گرفته شده است.

٣- بررسی ایندکس‌های موجود در جدول 

با استفاده از Stored Procedure سیستمی sp_HelpIndex می‌توانید ایندکس‌های موجود در جداول را بررسی کنید.

SP_HELPINDEX Employees1
GO

بررسی دستور Shrink در SQL Server

SP_HELPINDEX Employees2
GO

بررسی دستور Shrink در SQL Serverهمانطور که در لیست ایندکس‌ها مشاهده می‌نماید جدول مورد نظر دارای دو ایندکس به شرح زیر می‌باشد.

لیست ایندکس‌ها۴- درج تعداد ۱۰۰۰۰ رکورد تستی در جداول 

توسط Scriptهای زیر می‌توانید با استفاده از یک حلقه While تعدادی رکورد تستی در جداول درج نمایید.
در تصویر زیر نمونه‌ای از رکوردهای درج شده را مشاهده می‌کنید.

بررسی دستور Shrink در SQL Server۵- بررسی تعداد رکوردهای درج شده

با استفاده از Stored Procedure سیستمی sp_SpaceUsed می‌توانید تعداد رکوردهای موجود در جداول را بررسی کنید.

SP_SPACEUSED Employees1 GO

بررسی دستور Shrink در SQL Server

_SPACEUSED Employees2 GO

بررسی دستور Shrink در SQL Server۶- حذف جدول دوم

با توجه به اینکه هدف مان شبیه‌سازی عملیات Shrink است جدول تستی دوم را حذف کنید تا فضای مربوط به آن در Data File بلا استفاده باقی مانده تا عملیات Shrink بتواند طی پروسه Shrink از آن استفاده نماید.

DROP TABLE Employees2 GO

٧- بررسی وضعیت Fragmentation جدول و ایندکس های موجود در آن

با استفاده از DMF (Dynamic Management Function) زیر می‌توانید وضعیت Fragmentation ایندکس‌های موجود در جدول را بررسی کنید.

SELECT index_type_desc,Avg_Fragmentation_In_Percent FROM sys.dm_db_index_physical_stats ( DB_ID ('Test_Shrink'), OBJECT_ID ('Employees1'), NULL, NULL, 'Limited' ) GO

درصدهایی که در جدول زیر مشاهده می‌نمایید قبل از اجرای عملیات Shrink می‌باشد.دستور Shrink در SQL Server

٨- مشاهده تعداد IO جهت واکشی رکوردها

با استفاده از دستور Set Statistics IO… می‌توانید تعداد IO لازم جهت واکشی کلیه رکوردهای جدول را مشاهده نمایید. لازم به ذکر است آمار ارائه شده برای IO قبل انجام عملیات Shrink می‌باشد.

SET STATISTICS IO ON GO SELECT * FROM Employees GO SET STATISTICS IO OFF GO

بررسی دستور Shrink در SQL Server٩- انجام عملیات Shrink

عملیات Shrink بر روی Database انجام می‌شود. نکته مهمی که در این باره وجود دارد این است که اگر عملیات Shrink بر روی تاثیر خود را به Data File به شکل NoTruncate داشته باشد. این موضوع باعث Fragment شدن جداول و ایندکس‌های شما خواهد شد.

DBCC SHRINKDATABASE (Test_Shrink) GO

انجام عملیات Shrinkتوجه داشته باشید که اجرای هر کدام از دستورات زیر به ضرر ایندکس‌ها می‌باشد.

انجام عملیات Shrink١٠- بررسی مجدد وضعیت Fragmentation جدول و ایندکس های موجود در آن 

با استفاده از DMF (Dynamic Management Function) زیر می‌توانید وضعیت Fragmentation ایندکس‌های موجود در جدول را بررسی کنید.

SELECT index_type_desc,Avg_Fragmentation_In_Percent FROM sys.dm_db_index_physical_stats ( DB_ID ('Test_Shrink'), OBJECT_ID ('Employees1'), NULL, NULL, 'Limited' ) GO

درصدهایی که در جدول زیر مشاهده می‌نمایید بعد از اجرای عملیات Shrink می‌باشد.
همانطور که در جدول بالا مشاهده می‌کنید عملیات Shrink تاثیر خود را بر روی جداول و ایندکس‌های موجود در بانک اطلاعاتی گذاشته و باعث افزایش آمدن Fragmentation در آنها شده است.

نکته مهم دستور shrink در sql

در صورتیکه Fragmentation ایندکس‌های شما به هر دلیلی مانند Shrink کردن بانک اطلاعاتی و… رخ دهد بهتر است جهت افزایش کارایی بانک اطلاعاتی ایندکس‌های خود را بسته به شرایط Rebuild و یا Reorganize نمایید.

 

 

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

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

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

اولین نفر باش

title sign
معرفی نویسنده
مسعود طاهری
مقالات
28 مقاله توسط این نویسنده
محصولات
60 دوره توسط این نویسنده
مسعود طاهری

مسعود طاهری مدرس و مشاور ارشد SQL Server & BI ،  مدیر فنی پروژه‌های هوش تجاری (بیمه سامان، اوقاف، جین وست، هلدینگ ماهان و...) ، مدرس دوره‌هــای SQL Server و هوش‌تجاری در شرکت نیک‌آموز و نویسنده کتاب PolyBase در SQL Server

پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • ضمن تشکر از شما یه سوال داشتم چرا باید درمحیط‌های عملیاتی خصیصه Auto Shrink بانک اطلاعاتی را به هیچ عنوان True نکرد حتی اگر schedule کنیم تو زمانهای خاص shrink کنه؟

    • خیلی عالی توضیح دادید سپاس و درود فراوان

    • سلام
      تشکر
      آیا حتماً باید کسی در حال کار با دیتا بیس نباشد ؟ یا مشکلی ندارد در حالت شیرینک با دیتا بیس کار کرد؟

      • درود بر شما
        بهتره کسی درحال کار نباشه دوست عزیز

    • سلام مهندس و وقت شما بخیر و تشکر بابت مطلب مفید و کامل
      مهندس میخوام ببینم چه چیزهایی رو حجم فقط “لاگ” دیتابیس تاثیر داره ؟
      من خیلی وقت پیش ها با یه دیتابیس روبرو شدم که دیگه رشد حجم لاگ فایلش نرمال نبود و خیلی سریع رشد میکرد با اینکه تغییر آنچنانی رو سیستم و حجم کار ایجاد نشده بود.
      پیشاپیش ممنون بابت پاسختون .

      • با سلام
        رشد لاگ فایل به خیلی موارد بستگی دارد، به احتمال خیلی زیاد Recovery Model دیتابیس شما حتما روی حالت Full می‌باشد یکی از علت اصلی رشد لاگ فایل این مورد هست.
        مورد دوم شاید یک Begin Transaction باز گذاشتید مثلا چند هزار رکورد اطلاعات باید تغییر کند و همین موضوع دارد Commit هنوز انجام نگرفته این موضوع می‌تواند باعث رشد لاگ فایل شود.

        مورد سوم شاید Recovery Model بانک شما در حالت Simple قرار دادید اما دستوری که سمت SQL ارسال شده بیشتر از سایز لاگ فایل است در این حالت لاگ فایل مجبور است رشد کند.

        و سایر موارد که باید سناریو را دید تا تشخیص بهتری داد.

        ۱
    • سلام مهندس و وقت شما بخیر و تشکر بابت مطلب مفید و کامل
      مهندس میخوام ببینم چه چیزهایی رو حجم فقط “لاگ” دیتابیس تاثیر داره ؟
      من خیلی وقت پیش ها با یه دیتابیس روبرو شدم که دیگه رشد حجم لاگ فایلش نرمال نبود و خیلی سریع رشد میکرد با اینکه تغییر آنچنانی رو سیستم و حجم کار ایجاد نشده بود.
      پیشاپیش ممنون بابت پاسختون .

      • با سلام
        رشد لاگ فایل به خیلی موارد بستگی دارد، به احتمال خیلی زیاد Recovery Model دیتابیس شما حتما روی حالت Full می‌باشد یکی از علت اصلی رشد لاگ فایل این مورد هست.
        مورد دوم شاید یک Begin Transaction باز گذاشتید مثلا چند هزار رکورد اطلاعات باید تغییر کند و همین موضوع دارد Commit هنوز انجام نگرفته این موضوع می‌تواند باعث رشد لاگ فایل شود.
        مورد سوم شاید Recovery Model بانک شما در حالت Simple قرار دادید اما دستوری که سمت SQL ارسال شده بیشتر از سایز لاگ فایل است در این حالت لاگ فایل مجبور است رشد کند.
        و سایر موارد که باید سناریو را دید تا تشخیص بهتری داد.

    • سلام مهندس طاهری عزیز // یه سوال : ما یه دیتابیس داریم حدود ۸۰۰ گیگ و بدلیل حذف اطلاعات یک جدول برای آزاد سازی فضای مورد نظر نیاز به Shirink داریم// که زمانی که ما خواستیم دیتابیس را شیرینک کنیم حدود ۵۰ ساعت طول کشید آیا راه حلی برای افزایش سرعت شیرینک داریم؟

      • معمولا در زمان Shrink بهتر است Transaction طولانی باز نداشته باشید و کارهای سنگین روی دیتابیس انجام ندهید – اما واقعیت امر این است که این دستور زمان زیادی …
        باید حواستان باشد که Shrink هم باعث Blocking نشود ….
        این پروسه باید در حین اجرا مانیتور شود و مشکلات Blocking و… این رفع و رجوع شود
        در هر حالت در حجم بالا زمان طولانی است

    •     با سلام

      دلیل افزایش حجم logFile تقریبا به اندازه نصف حجم DataFile چه می تواند باشد ؟
    •    با سلام خدمت استاد و تشکر از شما
      موفق و سلامت باشید.

    •    واقعا ممنون

      استفاده کردیم

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