در چه شرایطی 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 مواجه شویم.