March 22, 2011 at 12:38 am
Hi,
Can anyone please let me know is there is any effect if we alter database size during production hours.
Regards
Minto
March 22, 2011 at 1:43 am
if your database have enough free space then it will not affect performance.
But if your database almost full and then you increasing the space then you will see more IO and performance hit as well
----------
Ashish
March 22, 2011 at 11:04 am
crazy4sql (3/22/2011)
if your database have enough free space then it will not affect performance.But if your database almost full and then you increasing the space then you will see more IO and performance hit as well
I don't think your statement is correct. Growing a database absolutely CAN affect performance which is why you'll want to plan your grow operations. Leaving your database on autogrow is a good thing, but you should be monitoring how full your database files are and growing them manually during maintenance windows to make sure there is a lower impact on your system.
Are you planning to grow a data file or a log file?
When growing a data file, if you have Instant File Initialization configured, the data file will be grown almost instantly so you have a much better chance of avoiding performance issues. Unfortunately Log files cannot be instantly initialized, i.e. all bytes must be initialized to 0 which uses I/O and causes in-process transactions to wait for the grow operation to complete so it absolutely can affect performance, and in a very bad way. If you do not have the option to setup Instant File Initialization then your data files will suffer from the same issue as your log files, i.e. all new bytes will need to be zeroed out before the new space in the file can be used.
Have a look at this article: http://msdn.microsoft.com/en-us/library/ms175935.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply