خانه SQL Server بررسی دستور Shrink در SQL Server SQL Server افزایش سرعت SQL Server نوشته شده توسط: مسعود طاهری تاریخ انتشار: ۱۹ آبان ۱۳۹۳ آخرین بروزرسانی: ۲۳ آبان ۱۴۰۲ زمان مطالعه: 20 دقیقه ۴ (۶) مقدمه Shrink در لغت به معنی جمع شدن و یا چروک شدن میباشد. با در نظر گرفتن همین مفهوم میتوان گفت Shrink کردن فرآیندی است که در آن فضای Data File و Log File جمع و جور میشود. در ادامه به توضیح بررسی دستور Shrink در SQL Server می پردازیم. همانطور که در تصویر بالا مشاهده میکنید طی فرآیند دستور 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 نمایید. برای بدست آوردن اطلاعات بیشتر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید. چه رتبه ای میدهید؟ میانگین ۴ / ۵. از مجموع ۶ اولین نفر باش معرفی نویسنده مقالات 20 مقاله توسط این نویسنده محصولات 65 دوره توسط این نویسنده مسعود طاهری مسعود طاهری مدرس و مشاور ارشد SQL Server & BI ، مدیر فنی پروژههای هوش تجاری (بیمه سامان، اوقاف، جین وست، هلدینگ ماهان و...) ، مدرس دورههــای SQL Server و هوشتجاری در شرکت نیکآموز و نویسنده کتاب PolyBase در SQL Server معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان مقالات مرتبط ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز ۰۳ مهر SQL Server روش استفاده از Policy-Based Management در امنیت SQL server تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ مصطفی نظام ۲۳ / ۰۴ / ۰۳ - ۰۵:۳۲ سلام و خدا قوت یه سوال داشتم از استاد طاهری اینکه ما چطور می تونیم لاگ فایل دیتابیسی که HA داره رو شرینک کنیم؟ مدام خطا میده؟ متشکرم پاسخ به دیدگاه احمدی ۲۴ / ۰۹ / ۰۱ - ۰۲:۵۱ ضمن تشکر از شما یه سوال داشتم چرا باید درمحیطهای عملیاتی خصیصه 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