nikamooz logo
nikamooz logo
0
سبد خرید
مفهوم Instant File Initialization در SQL Server

مفهوم Instant File Initialization در SQL Server

SQL Server افزایش سرعت SQL Server
نوشته شده توسط: تورج عزیزی
تاریخ انتشار: ۲۷ مهر ۱۳۹۴
آخرین بروزرسانی: 28 بهمن 1403
زمان مطالعه: 5 دقیقه
۳
(۴)

مفهوم Instant File Initialization در SQL Server، در این مقاله قصد داریم شما رو با مفهوم Instant Initialization که مبحث مهمی در Performance پایگاه داده SQL Server است، آشنا کنیم. فایل های دیتا و لاگ هنگام انجام هر یک از اعمال زیر آماده سازی اولیه (File Initialization) می شوند. تا داده هایی که از فایل های پاک شده روی دیسک باقی مانده با مقدار صفر رونویسی شوند.

  • ایجاد دیتابیس
  • اضافه کردن فایل داده یا لاگ به دیتابیس
  • افزایش اندازه یک فایل موجود (شامل عملیات auto growth هم می شود)
  • restore یک دیتابیس  یا Filegroup

مفهوم Instant File Initialization در SQL Server

File Initialization باعث می شود تا انجام این عملیات طولانی تر شود. SQL Server می تواند از این فرآیند صرفنظر کند و بدون رونویسی فضای تخصیص داده شده با صفر، فوراً آن را تخصیص دهد. کلمه Instant به معنی “فوری” است.  Instant Initialization قابلیتی است که در SQL Server 2005 ارائه شد.  Instant Initialization بر اساس قابلیتی است که به NTFS در ویندوز XP اضافه شد و به تبع آن در Windows Server 2003 هم قابل استفاده است. Instant Initialization به ما اجازه میدهد تا SQL Server درخواست تخصیص فضای ذخیره سازی از فرآیند پر کردن فضای تخصیص داده شده با صفر صرف نظر کند.  به این فرآیند Zero Initialization گفته می شود. بنابراین فارغ از اندازه فضای تخصیص داده شده در فایل داده ای فرآیند تخصیص می تواند به سرعت و فوری انجام شود.
SQL Server فرآیند نادیده گرفتن Zero Initialization را تنها می تواند در مورد DATA File ها اعمال کند. Zero Initialization در مورد transaction log اجباری است چون طبیعت این فایل چرخشی است یعنی پس از رسیدن به انتهای فایل SQL Server دوباره از ابتدای فایل شروع به رونویسی روی لاگ های قدیمی می کند (به این عمل Warp-around هم گفته می شود) و علت اجباری بودن هم مربوط به روند اجرای recovery است که فایل لاگ را می خواند و با رسیدن به مقدار صفر به این فرآیند خاتمه می دهد (این موضوع را در یک مقاله دیگر پوشش می دهم). برای فعال کردن قابلیت Instant Initialization باید به اکانت سرویس SQL Server مجوز “Perform Volume Maintenance Tasks” داده شود. شما می‌توانید کوئری نویسی را به صورت گام‌به‌گام از نیک آموز فرا بگیرید. 

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز

فعال کردن قابلیت Instant Initialization در SQL Server

در این بخش می خواهیم نحوه فعال کردن این قابلیت در SQL Server را برای شما علاقه مندان به پایگاه داده توضیح دهیم:

۱- در Command prompt دستور secpol.msc را وارد کنید تا این پنجره ظاهر شود و سپس مسیر  Local Policies > User Rights Assignment را دنبال کنید:
۲- روی گزینه Perform Volume Maintenance Tasks راست کلیک کنید و Properties را انتخاب کنید تا این کادر ظاهر شود:
۳- روی Add User Group کلیک کنید و سپس در این کادر سرویسی که SQL Server با آن در حال اجراست را وارد کنید:
۴- سرویس SQL Server را Restart کنید.
پیدا کردن اکانت سرویس SQL Server می توانید از اسکریپت زیر استفاده کنید:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<span style="color: #000000;">DECLARE @DBEngineLogin VARCHAR(100)
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEMCurrentControlSetServicesMSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT
SELECT [DBEngineLogin] = @DBEngineLogin
</span>
<span style="color: #000000;">DECLARE @DBEngineLogin VARCHAR(100) EXECUTE master.dbo.xp_instance_regread @rootkey = N'HKEY_LOCAL_MACHINE', @key = N'SYSTEMCurrentControlSetServicesMSSQLServer', @value_name = N'ObjectName', @value = @DBEngineLogin OUTPUT SELECT [DBEngineLogin] = @DBEngineLogin </span>
DECLARE @DBEngineLogin VARCHAR(100)
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEMCurrentControlSetServicesMSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT
SELECT [DBEngineLogin] = @DBEngineLogin
برای تست تاثیر فعال کردن می توانید این اسکریپت را قبل و بعد از فعال کردن اجرا کنید:
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<span style="color: #000000;">CREATE DATABASE TestDatabase ON PRIMARY
(
NAME = N'TestDatabase',
FILENAME = N'G:SQLDATATestDatabase.mdf' ,
SIZE = 51200000KB ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'TestDatabase_log',
FILENAME = N'G:SQLLogTestDatabase_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO</span>
<span style="color: #000000;">CREATE DATABASE TestDatabase ON PRIMARY ( NAME = N'TestDatabase', FILENAME = N'G:SQLDATATestDatabase.mdf' , SIZE = 51200000KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDatabase_log', FILENAME = N'G:SQLLogTestDatabase_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10% ) GO</span>
CREATE DATABASE TestDatabase ON PRIMARY
(
NAME = N'TestDatabase',
FILENAME = N'G:SQLDATATestDatabase.mdf' ,
SIZE = 51200000KB ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'TestDatabase_log',
FILENAME = N'G:SQLLogTestDatabase_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO

برای ساخت دیتابیس بالا SQL Server باید ۵۰ GB مقدار ۰ را روی دیسک بنویسد! و البته این کار با فراخوانی یک تابع API ویندوز به نام SetFileValidData انجام می شود. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.
در تصویر زیر که لاگ SQL است میتوانید از یک نمونه فرآیند ساخت دیتابیس تا قبل از دادن مجوز Perform Volume Maintenance Tasks به اکانت SQL Server را مشاهده می کنید و همانطور که می بینید پر کردن با صفر هم برای فایل داده ای و هم لاگ انجام شده است.

سخن پایانی

مفهوم Instant File Initialization در SQL Server، پس از دادن مجوز Perform Volume Maintenance Tasks و فعال کردن دو trace flag با شماره های ۳۰۰۴ و ۳۶۰۵ (با دستور DBCC TRACEON (۳۶۰۵)) و اجرای مجدد اسکریپت ساخت دیتابیس می توانید در لاگ SQL Server در عکس زیر ببینید که فرآیند Zeroing فقط برای لاگ دیتابیس انجام شده است. همچنین تاثیر حذف این مجوز پس از reboot اعمال می شود. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

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

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

اولین نفر باش

جشواره عیدانه نیک آموز
title sign
معرفی نویسنده
تورج عزیزی
مقالات
18 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تورج عزیزی
title sign
معرفی محصول
مسعود طاهری

دوره ۳ در ۱ آموزش performance tuning در SQL Server
6.700.000 تومان
title sign
مقالات مرتبط
title sign
دیدگاه کاربران

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

    • فعال شدن با Restart سرویس SQL است و غیر فعال شدن نیاز به reboot ویندوز دارد.

    •   سلام

      تشکر از مقاله خوبی که در مورد Performance به اشتراک گذاشتید.
      میشه بگید منظورتون از ((نکته آخر اینکه تاثیر حذف این مجوز پس از reboot اعمال می شود)) دقیقاً چی هستش؟

      • یعنی SQL Server فقط بعد از Restart شدن می تواند از این قابلیت استفاده کند.

    •     مهندس منظور من این بود که با دو trace flag می تونید در لاگ SQL فرآیند Zeroing رو ببینید.

    •     تریس فلگ ۳۶۰۵ و ۳۰۰۴ فقط برای نشان دادن پیغام در فایل لاگ است نه اینکه این قابلیت رو فعال سازی کند.

      نکته دیگر اینکه شما اگر این قابلیت رو فعال سازی کنید می توانید داده های یک پایگاه داده حذف شده از دیسک را بخوانید به این صورت که 
      ۱- پایگاه داده ساخته و از داده انبوه کنید.
      ۲- پایگاه داده را حذف کرده.
      ۳- یک پایگاه داده جدید با همان نام – آدرس فیزیکی و حجم بسازید
      ۴- با دستورات DBCC PAGE می توانید داده های قبلی را بازیابی کنید.
      ۵- خیلی حرفه ای تر – شما می توانید Data Page را به IAM نسبت داده و داده را با دستور Select بازیابی کنید.
      مقاله خوبی بود.

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

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

    • سلام

      یکی از کارهای جالب استفاده از دستور DBCC WritePage است. این دستور جزء دستورات Undocumented می باشد.

      مثال های خوبی به ازای این دستور در لینک زیر وجود دارد.
      ضمنا لینک های زیر کمک مفیدی به شما می تواند بکند.

دانلود رایگان: آموزش SQL Server

هر روز یک ویدئو آموزشی رایگان برای شما ایمیل خواهد شد!

پاپ آپ | SQL Server

  • این قسمت برای اهداف اعتبارسنجی است و باید بدون تغییر باقی بماند.