January 28, 2011 at 11:52 am
Ok, it has been a long week, but I cannot explain this one.
SQL Server 2008 SP1 Enterprise Edition instance. Instant File Initialization enabled (perform volume maint tasks granted to SQL Server service account) as it is on all production instances.
If I create a new database with a 50GB data file and a 1MB log file, it completes in 2 seconds.
If I alter an existing databases (MODIFY FILE) to grow it from 100MB to 5GB, it takes almost 20 seconds. It should be much faster than that. ALTER DATABASE/MODIFY FILE is one scenario under which IFI is utilized.
Thoughts?
What am I missing?
January 28, 2011 at 12:49 pm
When you expand a log file SQL Server has to initialize it internally, and that is what takes more time.
When you expand a database data file, that is not necessary, so that completes much faster.
January 28, 2011 at 1:10 pm
Indeed, like Michael Valentine Jones explained:
Instant file Initialization isn't used for log files.
Log files need to be able to be written to as fast as possible.
The fastest way is of course writing to already initialized pages.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2011 at 1:21 pm
Understood. The file I was expanding was a data file, not a log file.
Sorry if I was not clear on that.
January 28, 2011 at 2:44 pm
did you restart the sql instance after you granted the windows auth for IFI ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply