خانه SQL Server در چه شرایطی Auto Growth می تواند بر Performance تاثیر منفی بگذارد SQL Server افزایش سرعت SQL Server نوشته شده توسط: تورج عزیزی تاریخ انتشار: ۲۹ دی ۱۳۹۴ آخرین بروزرسانی: ۱۰ اردیبهشت ۱۴۰۱ زمان مطالعه: 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 مواجه شویم. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ تورج عزیزی ۰۵ / ۱۱ / ۹۴ - ۰۲:۵۶ در تکمیل فرمایش استاد طاهری، Instant File Initialization روی فایل لاگ اثری ندارد. پاسخ به دیدگاه Hamid J. Fard ۰۵ / ۱۱ / ۹۴ - ۰۲:۴۵ البته تاثیر دارد ولی رابطه خیلی شدیدی با سایز لاگ فایل دارد. اصولا لاگ فایل هم همانطور که گفتم باید به صورت دستی حجم داده شود و حجم آن باید بسته به تعداد 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 استفاده کنید که در مقاله به آن اشاره شده است. البته اینکار یک ایراد امنیتی خاص برای خودش دارد که همه جا صدق نمی کند و خیلی نمی توان به آن ایراد امنیتی گفت. پاسخ به دیدگاه Hamid J. Fard ۰۵ / ۱۱ / ۹۴ - ۰۱:۳۵ البته اون موضوع خیلی کم پیش میاد به این دلیل که پایگاه داده جدید باید در همان محل قبلی و به همان اندازه حجم داشته باشد تا بتوان داده های قبلی را خواند البته فایل سیستم پاک شده را هم می توان خواند و بازیابی کرد. پاسخ به دیدگاه مسعود طاهری ۰۵ / ۱۱ / ۹۴ - ۰۱:۲۰ برای لاگ گیری از نحوه رشد دیتا فایل و… می توانید از روش های زیر استفاده کنید ۱- استفاده از Extended Event ۲- استفاده از Server Side Trace ۳- استفاده از Data Collection ۴- استفاده از Alert ۵- و… البته هر کدام از آنها معایب و مزایای خاص خودش را به همراه دارد موفق باشید پاسخ به دیدگاه Hamid J. Fard ۰۵ / ۱۱ / ۹۴ - ۰۱:۴۹ به شخصه من از Extended Events و یک مکانیزم برای جمع آوری حجم داده ها به صورت روزانه و هفتگی استفاده می کنم البته اگر شما یک مزرعه از SQL Server داشته باشید Management Data Warehouse بهترین گزینه است. پاسخ به دیدگاه Hamid J. Fard ۰۵ / ۱۱ / ۹۴ - ۱۲:۴۰ اصولا شما نباید به Auto Growth تکیه کنید. شما باید حجم داده هایی که ممکن از در یک سال یا شش ماه آینده خواهید داشت را محاسبه کرده و به صورت دستی به حجم پایگاه داده اضافه کنید و مقدار Auto growth را به اندازه یک چهارم حجم محاسبه شده در نظر بگیرید در مواقع ضروری که ممکن است داده ها حجم بیشتر از محاسبات شما داشته باشند. پاسخ به دیدگاه غلامحسین عبادی ۰۵ / ۱۱ / ۹۴ - ۱۱:۴۷ با سلام و عرض ادب ممنون بابت مقاله خوبتون ، خسته نباشید لطفا بفرمایید به نظر شما برای یک بانک در حدود ۱TB میزان رشد فایل (AutoGrowth) حدودا چه اندازه باشد پاسخ به دیدگاه تورج عزیزی ۰۳ / ۱۱ / ۹۴ - ۱۰:۰۸ خواهش می کنم بیشتر مقالات من از SQLPassion.at است… پاسخ به دیدگاه Hamid J. Fard ۰۳ / ۱۱ / ۹۴ - ۰۶:۰۰ با سلام تورج جان مقاله خوبی بود. لطفا اگر می شود منبع انگلیسی این مقاله را برای مطالعه بیشتر ارایه بدهید. ممنون پاسخ به دیدگاه مهدی ربانی ذبیحی ۰۱ / ۱۱ / ۹۴ - ۰۶:۰۵ با سلام. ممنون مهندس بسیار عالی بود.ممنون از مقاله های خوبی که قرار میدی. پاسخ به دیدگاه