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

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

دستور shrink در SQL

همانطور که در تصویر بالا مشاهده می‌کنید طی فرآیند دستور 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 خالی منتقل می‌شود. تصویر زیر این موضوع را به درستی نمایش می‌دهد.

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

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

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

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

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

خوب تا اینجا با مفهوم 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

SP_HELPINDEX Employees2
GO

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

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

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

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

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

SP_SPACEUSED Employees1 GO

_SPACEUSED Employees2 GO

۶- حذف جدول دوم

با توجه به اینکه هدف مان شبیه‌سازی عملیات 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 می‌باشد.

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

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

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

۹- انجام عملیات Shrink

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

DBCC SHRINKDATABASE (Test_Shrink) 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 تاثیر خود را بر روی جداول و ایندکس‌های موجود در بانک اطلاعاتی گذاشته و باعث افزایش آمدن Fragmentation در آنها شده است.

سخن پایانی

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