Database Size

If I'm creating a db from an inport, and I know it will be large (400 gig),
is it faster to create the DB that size, or create a 1 gig DB and let it
auto grow by 1 gig at a time when it needs it during the import?
Or would it take the same amount of time?Steve wrote:
> If I'm creating a db from an inport, and I know it will be large (400 gig),
> is it faster to create the DB that size, or create a 1 gig DB and let it
> auto grow by 1 gig at a time when it needs it during the import?
> Or would it take the same amount of time?
>
>
If you plan to keep this database for a while, I would create it all at
one time. Your import will run faster, and, assuming you can create the
initial file as one contiguous file, you won't have disk fragmentation
to worry about later.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know the import would run faster, as it wouldn't have to pause to claim
more disk space. But would I be saving time over all or would it take the
same amount of time?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
>> If I'm creating a db from an inport, and I know it will be large (400
>> gig), is it faster to create the DB that size, or create a 1 gig DB and
>> let it auto grow by 1 gig at a time when it needs it during the import?
>> Or would it take the same amount of time?
>>
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
creating a single 400 gig mdf file?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e8zyPUopGHA.4196@.TK2MSFTNGP04.phx.gbl...
> Steve wrote:
>> If I'm creating a db from an inport, and I know it will be large (400
>> gig), is it faster to create the DB that size, or create a 1 gig DB and
>> let it auto grow by 1 gig at a time when it needs it during the import?
>> Or would it take the same amount of time?
>>
> If you plan to keep this database for a while, I would create it all at
> one time. Your import will run faster, and, assuming you can create the
> initial file as one contiguous file, you won't have disk fragmentation to
> worry about later.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Steve wrote:
> I know the import would run faster, as it wouldn't have to pause to claim
> more disk space. But would I be saving time over all or would it take the
> same amount of time?
>
Overall, the entire growth + import process will probably take as long
as just initially creating the database that size. Fragmentation
resulting from the constant addition of 1GB chunks would be your concern
if you let it auto-grow. You'll end up with chunks of the database
scattered all over the disk, causing additional disk overhead when
retrieving data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Steve wrote:
> would creating the db with a 200 gig mdf and 200 gif ndf file be faster than
> creating a single 400 gig mdf file?
>
Probably not, because you're still initializing the same amount of
space. If you could create them simultaneously, on seperate I/O
channels, then yes.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your help.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Steve wrote:
>> would creating the db with a 200 gig mdf and 200 gif ndf file be faster
>> than creating a single 400 gig mdf file?
> Probably not, because you're still initializing the same amount of space.
> If you could create them simultaneously, on seperate I/O channels, then
> yes.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||One more thing to consider about autogrow: you have no control over when the
growth happens - so a random insert/update could cause growth and IO delays
at a busy period for your server. Far better for you to manage the growth of
the server yourself.
On SQL 2005, you can setup the system such that file growth is instantaneous
(i.e. the file is not zeroed when its created or grown)
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:ee26VkopGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Thanks for your help.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OD$9QhopGHA.4424@.TK2MSFTNGP05.phx.gbl...
>> Steve wrote:
>> would creating the db with a 200 gig mdf and 200 gif ndf file be faster
>> than creating a single 400 gig mdf file?
>>
>> Probably not, because you're still initializing the same amount of space.
>> If you could create them simultaneously, on seperate I/O channels, then
>> yes.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>