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

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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tlyczko - 25 Sep 2006 20:13 GMT

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

Reply to this Message

Hugo Kornelis - 26 Sep 2006 00:04 GMT

>I am new to SS2005, and I've just started working on a small test/dev
>database.
[quoted text clipped - 14 lines]
>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.

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message

tlyczko - 26 Sep 2006 13:14 GMT

> 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

Reply to this Message

Hugo Kornelis - 26 Sep 2006 20:52 GMT

>> 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
[quoted text clipped - 7 lines]
>
>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.

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message

tlyczko - 27 Sep 2006 14:09 GMT

> Seperate locations for data and log files are always good.
> The data file requires lots of random access, i.e. lots of head
[quoted text clipped - 8 lines]
> 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

Reply to this Message

Hugo Kornelis - 28 Sep 2006 23:57 GMT

>> Seperate locations for data and log files are always good.
>> The data file requires lots of random access, i.e. lots of head
[quoted text clipped - 19 lines]
>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.

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message