store db objects in *.ndf vs. *.mdf??

[tlyczko]

I am new to SS2005, and I've just started working on a small test/dev
database.

I recently read that one should store things like tables, views,
constraints, etc. in the *.ndf file rather than in the *.mdf file.

Does this make it any easier to transfer/copy files or databases or
other items from test/dev to production??

If I have a database already with items in the *.mdf file, how do I
transfer things like tables, constraints, views, etc. to an *.ndf file
in another database??

I also read that one can make it the default in SS2005 to store things
in the *.ndf file, how is this done??

Thank you,
Tom

[Hugo Kornelis]

On 25 Sep 2006 12:13:23 -0700, tlyczko wrote:

>I am new to SS2005, and I've just started working on a small test/dev
database.

I recently read that one should store things like tables, views,
constraints, etc. in the *.ndf file rather than in the *.mdf file.

Does this make it any easier to transfer/copy files or databases or
other items from test/dev to production??

If I have a database already with items in the *.mdf file, how do I
transfer things like tables, constraints, views, etc. to an *.ndf file
in another database??

I also read that one can make it the default in SS2005 to store things
in the *.ndf file, how is this done??

Thank you,
Tom

Hi Tom,

I'd like to know where you've read this nonsense. For starters, views
and constraints are only stored as metadata. And even for tables, this
is an incorrect general rule.

Very experienced DBAs will sometimes use different filegroups, placed on
seperate spindles, either to tweak performance or to facilitate advanced
backup and recovery schemes for large DBs. This kind of tweaking is not
of the "all tables go to the *.ndf file (the second filegroup)" kind -
indeed, it involves carefully planning the location of each individual
object, based on typical usage patterns.

Beginning DBAs shouldn't worry about this. For now, making sure to place
your datafile (.mdf) on one spindle and the log file (.ldf) on another
spindle is a good start. Worry about diifferent filegroups when you have
plenty of experience and read and experimented enough to know what the
consequences of using different filegroups are.

--
Hugo Kornelis, SQL Server MVP

[tlyczko]

Hugo Kornelis wrote:

I'd like to know where you've read this nonsense. For starters, views
and constraints are only stored as metadata. And even for tables, this
is an incorrect general rule.

Thanks a lot for responding. :)

I'll try to find the reference, it was in a book about SS2005. The DB
isn't going to be large enough for a long long time to merit separate
locations for the data and log files, though.

Thank you, Tom

[Hugo Kornelis]

On 26 Sep 2006 05:14:45 -0700, tlyczko wrote:

>
Hugo Kornelis wrote:

>I'd like to know where you've read this nonsense. For starters, views
and constraints are only stored as metadata. And even for tables, this
is an incorrect general rule.

Thanks a lot for responding. :)

I'll try to find the reference, it was in a book about SS2005. The DB
isn't going to be large enough for a long long time to merit separate
locations for the data and log files, though.

Thank you, Tom

Hi Tom,

Seperate locations for data and log files are always good.

The data file requires lots of random access, i.e. lots of head
movement. Luckily, SQL Server buffers data read from the data file, so
recently used data doesn't have to be read from disk again. Writes are
buffered as well.

The log file is mainly written to. These writes have to be completed
before SQL Server will signal the client that an operation is finished.
So write performance on your log file is critical to performance. Since
all log writes are sequential, you can gain a lot of performance if you
have the log file on a dedicated disk - the heads hardly have to move
and you write performance is it the highest possible rate - increasing
the time to completion for yur transactions.

--
Hugo Kornelis, SQL Server MVP

[tlyczko]

Hugo Kornelis wrote:

Seperate locations for data and log files are always good.
The data file requires lots of random access, i.e. lots of head
movement. Luckily, SQL Server buffers data read from the data file, so
recently used data doesn't have to be read from disk again. Writes are
buffered as well.
The log file is mainly written to. These writes have to be completed
before SQL Server will signal the client that an operation is finished.
So write performance on your log file is critical to performance. Since
all log writes are sequential, you can gain a lot of performance if you
have the log file on a dedicated disk - the heads hardly have to move
and you write performance is it the highest possible rate - increasing
the time to completion for yur transactions.

Thank you for explaining...particularly to people like me new to this
software.
However, this test DB is only a few meg for now...it'won't grow very
fast.
We do however have a production SQL server though with Great Plains and
perhaps later another SQL-based app.
Is there any sort of general guideline as to what database size(s) that
this sort of thing becomes particularly important?? Such as DBs over 4
GB or over 6 GB for example??
Thank you, Tom

[Hugo Kornelis]

On 27 Sep 2006 06:09:06 -0700, tlyczko wrote:

>
Hugo Kornelis wrote:

>Seperate locations for data and log files are always good.
The data file requires lots of random access, i.e. lots of head
movement. Luckily, SQL Server buffers data read from the data file, so
recently used data doesn't have to be read from disk again. Writes are
buffered as well.
The log file is mainly written to. These writes have to be completed
before SQL Server will signal the client that an operation is finished.
So write performance on your log file is critical to performance. Since
all log writes are sequential, you can gain a lot of performance if you
have the log file on a dedicated disk - the heads hardly have to move
and you write performance is it the highest possible rate - increasing
the time to completion for yur transactions.

Thank you for explaining...particularly to people like me new to this
software.
However, this test DB is only a few meg for now...it'won't grow very
fast.
We do however have a production SQL server though with Great Plains and
perhaps later another SQL-based app.
Is there any sort of general guideline as to what database size(s) that
this sort of thing becomes particularly important?? Such as DBs over 4
GB or over 6 GB for example??
Thank you, Tom

Hi Tom,

Putting log on a seperate volume increases performance for databases
wiith high data modification rates. This is regardless of size. You can
have databases several TB in size with minimal update activity, or
databases of just a few MB with very high activity - the latter will
proit much more from data-log seperation than the former.

--
Hugo Kornelis, SQL Server MVP