در چه شرایطی  Auto Growth  می تواند بر Performance تاثیر منفی بگذارد

در چه شرایطی Auto Growth می تواند بر Performance تاثیر منفی بگذارد

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

مقدمه

در این مقاله قصد دارم در مورد تاثیر تنظیم نادرست مقدار Auto growth بر Performance صحبت کنم. اغلب افراد تنها به این دلیل که Auto Growth *کار می کند* به آن اعتماد می کنند. Auto Growth البته که کار می کند اما شما هزینه گزافی بابت آن می پردازید.

 پیامدهای تنظیم نادرست Auto growth

اول اینکه باعث ایجاد مشکلی به نام Log Fragmentation می شود که تاثیری منفی بر زمان اجرای Crash Recovery که SQL Server در Startup خواهد داشت میگذارد. و علاوه بر این تراکنش های write (تراکنش هایی که دیتا را تغییر می دهند) شما وقتی Transaction Log یک Auto Growth اجرا می کند باید منتظر بمانند.

وقتی مکانیزم Auto Growth در Transaction Log اجرا می شود، SQL Server باید تکه جدید که به انتهای فایل Transaction Log اضافه می شود را *همیشه* با صفر مقدار دهی اولیه کند (Zero initialization ). اهمیتی ندارد که SQL Server با مجوز Instant File Initialization اجرا می شود یا خیر – Transaction Log *همیشه* با صفر مقدار دهی می شود.
علت کاملاً واضح است: اگر SQL Server یک wrap-around (یعنی به انتهای فایل Transaction Log رسیده باشد و دوباره به ابتدای فایل چرخش کرده باشد) در گذشته انجام داده باشد، فرآیند Crash recovery باید با روشی نقطه توقف را شناسایی کند. و نقطه توقف هم جایی است که Crash recovery به جای رکوردهای لاگ Transaction معتبر با مقدار صفر مواجه می شود.
مشکل Zero initialization این است که می تواند زمان بر باشد (بسته به نرخ Auto Growth و سرعت رسانه ذخیره سازی).و در طول این مدت زمان هیچ تراکنش دیگری نخواهد توانست رکوردهای Transaction Log را در Transaction Log بنویسد. این تراکنش ها توسط یک Latch که توسط Transaction Log Manager گرفته می شود بلاک می شوند. بنابراین تراکنش های write شما در وضعیت معلق قرار میگیرند (تا زمانی که latch مورد نیاز را بدست بیاورند) همینطور در حال انتظار باقی می مانند تا Auto Growth فایل Transaction Log کامل شود. اجازه دهید این رفتار را با یک مثال ساده نشان دهم.
 
در مرحله اول قصد دارم یک دیتابیس جدید ایجاد کنم. اما قصد ندارم از تنظیمات پیش فرض دیتابیس استفاده کنم، چون قصد دارم نرخ Auto Growth را ۱۰ GB برای Transaction Log انتخاب کنم. این نرخ واقعاً یک نرخ بد است اما من فقط قصد دارم به شما عوارض جانبی این تنظیمات را نشان دهم. لطفاً هرگز از این تنظیمات در محیط های تولید استفاده نکنید.
 -- Create a new database with 10 GB Auto Growth for the Transaction Log
CREATEDATABASE AutoGrowthTransactionLog ONPRIMARY
(
NAME =N'AutoGrowthTransactionLog',
FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog.mdf',
SIZE = 5120KB,
FILEGROWTH = 1024KB
)
LOGON
(
NAME =N'AutoGrowthTransactionLog_log',
FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AutoGrowthTransactionLog_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10240000KB -- 10 GB Auto Growth!
)
GO
در قدم بعدی من ۲ جدول در دیتابیس ایجاد می کنم. در جدول اول با درج برخی رکوردها خیلی سریع Transaction Log را پر می کنم. و در طول زمان فاز Auto Growth فایل Transaction Log سعی می کنیم در جدول دوم رکوردهای جدید درج کنیم تا ثابت شود این تراکنش ها توسط مکانیزم Auto Growth بلاک می شوند.
 -- Create a new table, every records needs a page of 8kb
CREATETABLE Chunk
(
Col1 INTIDENTITYPRIMARYKEY,
Col2 CHAR(8000)
)
GO
-- Another simple table
CREATETABLE Foo
(
Bar INTNOTNULL
)
GO

 تا الان تمام اشیای لازم در دیتابیس را ساختیم، بنابراین با شروع یک تراکنش جدید و کامیت نکردن فوری آن Transaction Log را پر می کنم:

 -- Begin a new transaction, that blocks the 1st VLF in the Transaction Log
BEGINTRANSACTION
INSERTINTO Chunk VALUES (REPLICATE('x', 8000))
GO
به دلیل اینکه ما حالا یک تراکنش uncommitted داریم، SQL Server نخواهد توانست آن بخش از Transaction Log که رکوردهای لاگ تراکنش باز اخیر در آن ذخیره شده را reuse کند. این رکوردهای لاگ برای انجام یک rollback احتمالی مورد نیاز خواهند بود. بنابراین Transaction Log را با درج ۶۶ رکورد از طریق یک session دیگر پر می کنم.
INSERTINTO AutoGrowthTransactionLog.dbo.Chunk VALUES (REPLICATE('x', 8000))
GO 66
-- Commit the ongoing transaction from the different session
-- Execute this code in the 1st session
COMMIT
و در آخر تراکنش اول را commit می کنیم. به این معنی که ما یک Transaction Log در مقابل داریم که تقریباً پر است. ما می توانیم با استفاده از DBCC LOGINFO به این موضوع پی ببریم:
حالا وقتی می خواهیم یک رکورد جدید درج کنیم، Transaction Log دیگر فضای در دسترسی نخواهد داشت و SQL Server فرآیند Auto Growth را شروع می کند.
 -- This statement will trigger the Auto Growth mechanism!
INSERTINTO Chunk VALUES (REPLICATE('x', 8000))
GO
برای مانیتور کردن اتفاقاتی که در طول زمان Auto Growth رخ می دهد، می توانید یک session متفاوت در SSMS باز کرده و سعی کنید در جدول دوم رکورد دیگری درج کنید:
-- This statement is now blocked by the Auto Growth mechanism.
INSERTINTO Foo VALUES (1)
GO
دستور SQL بالا بلاک می شود، چون تراکنش باید رکوردهای Log را در Transaction Log بنویسد که در حال حاضر در دسترس نیست. برای تحلیل بیشتر این حالت از blocking می توانید یک session سومی باز کرده و دستورات SQL زیر را اجرا کنید:
 -- Analyze the blocking situation
SELECT wait_type,*FROMsys.dm_exec_requests
WHERE session_id > 50
SELECT wait_type,*FROMsys.dm_os_waiting_tasks
WHERE session_id > 50
GO
همانطوری که در کدهای بالا می بینید، من به DMV های  sys.dm_exec_requests و sys.dm_os_waiting_tasks دو session نگاه می کنم–session ای که باعث تحریک Auto Growth شده و session ای که در حال حاضر توسط مکانیزم Auto Growth بلاک شده. session ای که باعث تحریک Auto Growth شده یک به اصطلاح Preemptive Wait Type گزارش می دهد و  در مورد کوئری ما این نوع wait عبارت است از PREEMPTIVE_OS_WRITEFILEGATHER.
یک wait type انحصاری نوعی از wait است که توسط SQL Server برگشت داده می شود و آن وقتی است که SQL Server یک تابع API اجرا می کند که خارج از مکانیزم زمان بندی اش است. و در مورد کوئری ما Auto Growth از طریق یک تابع Win32 API به نام WriteFileGather انجام می شود.
و دستور INSERT ای که سعی می کند در جدول FOO رکوردی جدید درج کند wait type ای از نوع LATCH_EX گزارش می دهد. همانطور که در ستون resource_description از dmv sys.dm_os_waiting_tasks می توانید ببینید یک latch توسط Log Manager در SQL Serverباید بدست بیاید. این Latch توسط تراکنشی که Auto Growth را تحریک کرده گرفته می شود و هر تراکنش دیگری که قصد نوشتن در Transaction Log دارد تا زمانی که این Latch آزاد نشده در حالت بلاک باقی می ماند. بنابراین اگر زمان های wait بالایی را در سیستم تان تجربه می کنید می تواند علامتی باشد برای اینکه شما در Auto Growth فایل Transaction Log مشکل دارید.
 
امیدوارم با این مقاله توانسته باشم شما را متقاعد کنم که تنها اتکا بر مکانیزم Auto Growth نمی تواند بهترین راه حل باشد. و همانطوری که با این مثال بسیار ساده دیدید، هر تراکنشی که عمل write انجام می دهد توسط عمل Auto Growth در دیتابیس بلاک می شود و این امر قطعاً به خروجی و مقیاس پذیری دیتابیس شما صدمه می زند.
عدد ثابتی برای تنظیم Auto Growth وجود ندارد و مقدار آن بسته به بار کاری سیستم متغیر است، و باید طوری انتخاب شود که Auto Growth دائماً اتفاق نیفتد و از طرفی زمان اجرایش آنقدر طولانی نباشد که باعث بلاک سایر تراکنش ها بشود، بهتر است Auto Growth به شکل درصدی انتخاب نشود چون سربار محاسباتی دارد و عدد ثابت آنقدر کوچک نباشد که با مشکل Log fragmentation مواجه شویم.

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

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

اولین نفر باش

title sign
معرفی نویسنده
تورج عزیزی
مقالات
17 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تورج عزیزی
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    •    در تکمیل فرمایش استاد  طاهری، Instant File Initialization  روی فایل لاگ  اثری ندارد.
      •   البته تاثیر دارد ولی رابطه خیلی شدیدی با سایز لاگ فایل دارد. اصولا لاگ فایل هم همانطور که گفتم باید به صورت دستی حجم داده شود و حجم آن باید بسته به تعداد VLFهایی که ساخته می شود هماهنگ باشد تا از  Log Fragmentation جلو گیری کند. البته اگر از Auto Growth در لاگ فایل با مقدار کم استفاده شود باعث به وجود آمدن Log Fragmentation می شود و در نتیجه سرعت تراکنشها کاهش میابد 

        • حمید از بابت ارائه نظرات سازنده + مفید متشکرم

        •   البته تاثیر دارد 

          حمید جان اگر منظورتون استفاده مکانیزم Instant File Initialization یا همون Zeroing روی لاگ فایل است
          باید اشاره کنم که تا جایی که من اطلاع دارم و مستندات رو خوندم و بررسی کردم Instant File Initialization تاثیری روی لاگ فایل ندارد و تاثیر مثبت روی دیتا فایل + پروسه Restore + Backup دارد

          مباحث دیگر هم در خصوص لاگ فایل صحیح است. معمولا اندازه را Fix می دهیم + لاگ بکاپ و اینکه اگر نیاز به رشد داشته باشیم بهتر است مقدار درصدی نباشد و عدد Fix باشد .

          تورج عزیز در این خصوص برای دوستان نیک آموز گذاشته است

          البته فک کنم الگوریتم تخصیص VLF و… اگر فراموش نکرده باشم در ۲۰۱۲ کمی هم تغییر پیدا کرده است.

    •  سلام استاد

      استاد طاهری شما مطلبی را که در خصوص  رشد (Auto Growth) حجم فایل mdf  ،  جناب آقای حمید راد فرمودند را ، تایید میفرمایید .
      •     بلی کاملا درست است

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

      لطفا منظورتون از ایراد امنیتی را بیان نمایید . با تشکر . بلاخره روی سرور اصلی این کار را انجام دهیم یا خیر 
    •    در تکمیل فرمایش استاد طاهری، Instant File Initialization  روی لاگ فایل اعمال نیست.

    •  سلام مهندس

      سوال دوم اینکه منظور شما از مقدار عدد مربوط به Auto Growth بسته به بار کاری سیستم متغیر است ، چه عواملی می باشد .
      •     جناب عبادی عزیز این موضوع در کلاس بررسی شده و در فیلم های ضبط شده ای که خدمتتون ارائه دادیم هست.

        خواندن محتوای دیتابیسی که پاک شده با ایجاد یک بانک اطلاعاتی جدید و…
        حمید درست میگه به ندرت این موضوع پیش می آید و خیلی هم پیچیدگی داره در برخی از مستندات صرفا به شکل عنوانی از این موضوع به عنوان ایراد امنیتی ذکر می شود
      • در ضمن توجه داشته باشید که برای اینکه درگیر طولانی شدن زمان رشد دیتا فایل نشوید می توانید از Instant File Initialization استفاده کنید که در مقاله به آن اشاره شده است.

        البته اینکار یک ایراد امنیتی خاص برای خودش دارد که همه جا صدق نمی کند و خیلی نمی توان به آن ایراد امنیتی گفت. 
        • البته اون موضوع خیلی کم پیش میاد به این دلیل که پایگاه داده جدید باید در همان محل قبلی و به همان اندازه حجم داشته باشد تا بتوان داده های قبلی را خواند البته فایل سیستم پاک شده را هم می توان خواند و بازیابی کرد.

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

        ۱- استفاده از Extended Event
        ۲- استفاده از Server Side Trace
        ۳- استفاده از  Data Collection
        ۴- استفاده از Alert 
        ۵- و…
        البته هر کدام از آنها معایب و مزایای خاص خودش را به همراه دارد
        موفق باشید
        •    به شخصه من از Extended Events و یک مکانیزم برای جمع آوری حجم داده ها به صورت روزانه و هفتگی استفاده می کنم البته اگر شما یک مزرعه از SQL Server داشته باشید Management Data Warehouse بهترین گزینه است.

      •    اصولا شما نباید به Auto Growth تکیه کنید. شما باید حجم داده هایی که ممکن از در یک سال یا شش ماه آینده خواهید داشت را محاسبه کرده و به صورت دستی به حجم پایگاه داده اضافه کنید و مقدار Auto growth را به اندازه یک چهارم حجم محاسبه شده در نظر بگیرید در مواقع ضروری که ممکن است داده ها حجم بیشتر از محاسبات شما داشته باشند.

    •     با سلام و عرض ادب

      ممنون بابت مقاله خوبتون ، خسته نباشید
      لطفا بفرمایید به نظر شما برای یک بانک در حدود ۱TB  میزان رشد فایل (AutoGrowth) حدودا چه اندازه باشد 
    •     خواهش می کنم

      بیشتر مقالات من از SQLPassion.at است…
    •     با سلام

      تورج جان مقاله خوبی بود. لطفا اگر می شود منبع انگلیسی این مقاله را برای مطالعه بیشتر ارایه بدهید.
      ممنون
    •     با سلام. ممنون مهندس بسیار عالی بود.ممنون از مقاله های خوبی که قرار میدی.

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