بسم الله الرحمن الرحیم

بهینه سازی  SQL

AutoGrowth

سایر مطالب در خصوص بهینه سازی SQL را می توانید از این بخش مطالعه بفرمایید.

طبیعتا همه دوستان با تصویر بالا آشنا هستند. این پنجره وقتی باز می شود که شما می خواهید یک پایگاه داده جدید را طراحی کنید. متاسفانه اکثر افراد تنها در این پنجره نام پایگاه داده خود را مشخص می کنند و سپس کلید Ok را انتخاب می کنند.
یکی از بخش های این پنجره که در تصویر بالا مشخص شده است مربوط به نحوه Growth و یا همان رشد پایگاه داده شما است.
به صورت پیش فرض پایگاه داده شما می تواند 3 MB از فضای درایو مشخص شده را برای فایل MDF خود و همچنین 1 MB را به فایل LDF خود اختصاص دهد. در صورتی که حجم هر یک از فایل های MDF و LDF شما از مقدار مشخص شده بیشتر شود. پایگاه داده شما بر اساس تنظیمات مشخص شده در بخش Autogrowth / Maxsize رشد خواهد کرد.
همانطور که در تصویر مشخص است فایل MDF شما می تواند در صورت بزرگتر شدن از 3 MB به صورت نامحدود در هر مرحله 1 MB رشد کند.
فایل LDF نیز در صورت بزرگتر شدن از 1 مگابابیت 10 درصد رشد خواهد کرد. در صورتی که فایل LDF 100 مگابایت باشد با تنظیم رشد 10 درصدی، فایل مربوطه به 110 مگابایت افزایش خواهد یافت.

بسم الله الرحمن الرحیم

بهینه سازی  SQL

AutoGrowth

سایر مطالب در خصوص بهینه سازی SQL را می توانید از این بخش مطالعه بفرمایید.

طبیعتا همه دوستان با تصویر بالا آشنا هستند. این پنجره وقتی باز می شود که شما می خواهید یک پایگاه داده جدید را طراحی کنید. متاسفانه اکثر افراد تنها در این پنجره نام پایگاه داده خود را مشخص می کنند و سپس کلید Ok را انتخاب می کنند.
یکی از بخش های این پنجره که در تصویر بالا مشخص شده است مربوط به نحوه Growth و یا همان رشد پایگاه داده شما است.
به صورت پیش فرض پایگاه داده شما می تواند 3 MB از فضای درایو مشخص شده را برای فایل MDF خود و همچنین 1 MB را به فایل LDF خود اختصاص دهد. در صورتی که حجم هر یک از فایل های MDF و LDF شما از مقدار مشخص شده بیشتر شود. پایگاه داده شما بر اساس تنظیمات مشخص شده در بخش Autogrowth / Maxsize رشد خواهد کرد.
همانطور که در تصویر مشخص است فایل MDF شما می تواند در صورت بزرگتر شدن از 3 MB به صورت نامحدود در هر مرحله 1 MB رشد کند.
فایل LDF نیز در صورت بزرگتر شدن از 1 مگابابیت 10 درصد رشد خواهد کرد. در صورتی که فایل LDF 100 مگابایت باشد با تنظیم رشد 10 درصدی، فایل مربوطه به 110 مگابایت افزایش خواهد یافت.

بهینه سازی SQL Server  و AutoGrowth

یکی از روش های بهینه سازی یک پایگاه داده تنظیم کردن میزان رشد فایل ها است. قبل از بررسی روش های تعیین میزان رشد فایل ها در SQL Server بهتر است، مواردی را بررسی نماییم.

طبیعتا به ازای هر بار رشد دادن فایل های پایگاه داده Overhead ایی به پایگاه داده تحمیل می شود، پس تعیین این مقدار می تواند به افزایش سرعت پایگاه داده شما کمک کند. بسیاری از دوستان تصور می کنند، در صورتی که در همان بخش تعریف پایگاه داده، اگر فایل ها را بسیار بزرگ در نظر بگیریم، این مشکل را می توانیم به سادگی حل کنیم.
 سوالی که بنده همیشه مطرح می کنم، در اینجا نیز قید می کنم اگر این روش کارامد بود پس چرا چنین امکانی در SQL Server قرار داده شده است؟
پاسخ این جواب مشخص است، همانطور که مشاهده می کنید ما دو نوع فایل به ازای هر پایگاه داده در اختیار داریم، این فایل ها و وظیفه هر یک را قبلا بررسی کرده ایم که می توانید از بخش معرفی فایل های SQL آنها را مطالعه کنید.

اگر فضای قابل دسترسی برای این دو فایل را بسیار بزرگ در نظر بگیریم طبیعتا مدیریت و Handle کردن این فایل ها برای Server و SQL Server بسیار مشکل خواهد شد زیرا بخشی از این اطلاعات باید بر روی RAM قرار گیرد، در نتیجه با بزرگ شدن این فایل ها حافظه RAM بیشتری اشغال خواهد شد، در صورتی که ممکن است هیچگاه به این فضای اشغال شده از سوی پایگاه داده نباشد.

SQL Server دستورات و Procedure های سیستمی بسیاری دارد که اطلاعات و گزارشات کاملی را در اختیار شما قرار می دهد، که با برخی از آنها قبلا در مقالات مختلفی که ارائه دادیم آشنا شده اید.
یکی دیگر از این دستورات، دستوری است که به شما زمان Auto Growth پایگاه داده خاص را به همراه مدت زمان سپری شده برای انجام این کار ارائه می دهد. این گزارش به سادگی می تواند به شما کمک کند تا مدت زمان انجام کار (Overhead) و همچنین دفعات انجام این کار را بدست آورید.

با در اختیار داشتن این اطلاعات بهتر می توانید پایگاه داده خود را مدیریت کنید و همچنین در صورت نیاز می توانید رشد دادن پایگاه داده خود را به صورت Manually با تنظیم یک Schedule در بخش Maintenance این کار را در زمان کاهش تراکنش های SQL Server انجام دهید.

برای مشاهده این گزارش می بایست در ابتدا یک پایگاه داده نمونه را طراحی کنیم و سپس اطلاعاتی را به آن اضافه نماییم.

دستورات زیر را برای طراحی یک پایگاه داده اجرا کنید
 

CREATE DATABASE [ABLY]
GO
ALTER DATABASE [ABLY] SET RECOVERY FULL
GO
BACKUP DATABASE [ABLY] TO DISK = 'NUL'
GO
USE [ABLY]
GO
CREATE TABLE person (id INT ,name CHAR(8000))
GO
SET NOCOUNT ON
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 10000
BEGIN
INSERT INTO person
VALUES (@i,'Esmaeil Sheidaei  ::::   Ably.ir')
SET @i = @i + 1
END

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

در نهایت گزارش خود را برای نمایش مدت زمان سپرسی شده و بازه های زمانی انجام شده برای بسط دادن فایل ها (AutoGrowth) به صورت زیر اجرا می کنیم.

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SET @database_name = 'ABLY'
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT DatabaseName
,Filename
,(Duration / 1000) AS 'TimeTaken(ms)'
,StartTime
,EndTime
,(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
,ApplicationName
,HostName
,LoginName
FROM::fn_trace_gettable(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
AND DatabaseName = @database_name
AND (d.create_date < EndTime)
ORDER BY t.StartTime DESC;

خروجی این دستور به صورت زیر خواهد بود