SQL Server 2005 and file systems, any recommendations

[bjorn.augestad]

We're planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.

Right now we're thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))

>From what I can tell, NTFS is a journaling file system, at least it

does some journaling. I'd prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?

Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLast AccessUpdate.

How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?

TIA for all comments.

Bjørn Augestad

[Bjørn Augestad]

David Portas wrote:

Bjørn Augestad wrote:

>>The best solution, IMHO, would be to have NTFS without journaling or
NTFS with the journal on a separate disk. If that's not possible, we'll
probably just settle for regular NTFS partitions, or maybe throw in a
couple of RamSan-400 boxes ;-)

I'm by no means an expert on NTFS but as far as I can determine
journaling applies only to file allocation, not to data. In a
well-managed environment SQL Server's storage should generally be
pre-allocated so I don't see why journaling should need to be a
performance constraint at runtime.

I'm no NTFS expert either, so I read the tech spec for NTFS at
http://technet2.microsoft.com/Window....mspx?mfr=true

Here's a quote:
NTFS Change Journal

As files, folders, and other NTFS objects are added, deleted, and
modified, NTFS enters change journal records in streams, one for each
volume on the computer.

My interpretation, hopefully wrong, is that NTFS journal entries will be
written to disk whenever a file is modified. This will lead to disk head
movement and decreased performance on a busy disk, won't it?

>
Maybe I've missed something but I have actually never heard anyone
raise a concern about this before and I've found virtually no relevant
information on it. That suggests to me that it's not an issue I need to
be concerned about. My impression is the same as Erland's: other
factors must rate as far more important determinants of DBMS
performance.

I mostly agree, but it's nice to get the disk configuration correct from
day one as it is very hard to change it later. I didn't know if it is an
issue or not, that's why I wanted to check this.

Bjørn

>
--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[DA Morgan]

Erland Sommarskog wrote:

DA Morgan (da******@psoug .org) writes:

>Erland Sommarskog wrote:

>>Correct. A binary collation comes with a price. So does raw partitions.

Would you please explain what you intend with this comment about block
devices. With any database, other apparently than Microsoft's, there is
an advantage that accrues to those who forgo file systems. Why is SQL
Server different?

I referred previously in the thread to a fellow MVP who had found a 20%
improvement with raw devices over the file system.

The price I'm talking about is the more complex administration that raw
devices buy you.

As for why they are complex... Many years ago when I worked with Sybase,
I had to travel to customer to perform a triple upgrade: upgrade HP-UX
from 8.0 to 9.0, Sybase from 4.2 to 4.9 and our own application. The
customer had adequate staff to run the OS upgrade themselves, but still
they wanted us to it. The reason: in this OS upgrade HP-UX introduced
logical volumes, having had only fixed partitions before, and the customer
did not know how to handle Sybase, where all data was on raw partitions.
Had the databases been on plain files, the OS upgrade would have been
trivial for them. But in those days, Sybase strongly discouraged you from
running production databases on file-system devices, and with a good reason,
since buffering in the file system could cause problems.

Thanks. The "price" is well understood and there are a lot of good
solutions for simplifying block device administration: Just not with
SQL Server (for example AIX makes it very easy). Thanks.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[DA Morgan]

Erland Sommarskog wrote:

DA Morgan (da******@psoug .org) writes:

>Erland Sommarskog wrote:

>>Correct. A binary collation comes with a price. So does raw partitions.

Would you please explain what you intend with this comment about block
devices. With any database, other apparently than Microsoft's, there is
an advantage that accrues to those who forgo file systems. Why is SQL
Server different?

It occurred to me that is another reason why SQL Server is different, which
is inherent in its architecture. Other engines have to support multiple
platforms and file systems, so they need to implement some abstract
interface to handle the file system. Using a raw device means that that
layer they can cirumventet.

Since SQL Server supports only Windows, it can be spared that layer of
abstraction, and thus the gain with raw devices is not equally compelling.

I like to stress that all this is pure speculation on my part.

Thanks.

I've seen this in other aspects as well. Being limited to a single
operating system, and one you control, moves many things from the
database to the operating system.

Of course if Vista shows up as a fattened pig ... that may not be such
a good thing. ;-) Again Thanks.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[David Portas]

Bjørn Augestad wrote:

>

I'm no NTFS expert either, so I read the tech spec for NTFS at
http://technet2.microsoft.com/Window....mspx?mfr=true

Here's a quote:
NTFS Change Journal

As files, folders, and other NTFS objects are added, deleted, and
modified, NTFS enters change journal records in streams, one for each
volume on the computer.

My interpretation, hopefully wrong, is that NTFS journal entries will be
written to disk whenever a file is modified. This will lead to disk head
movement and decreased performance on a busy disk, won't it?

The point of NTFS journaling is to protect the integrity of file system
META-DATA - avoiding the integrity issues inherent with FAT. NTFS
doesn't guarantee data consistency. For that SQL Server relies on the
transaction log. Your transaction logs can and should be placed on
separate drives to avoid disk contention.

"The Windows NT Filesystem (NTFS) is a journaling, or transactional
file system. This means that any I/O that alters the file system data
or meta-data (directory structure, etc.) is completed atomically so
that either all of the changes are completed"
http://www.microsoft.com/technet/arc....mspx?mfr=true

"NTFS does not do data journaling"
http://www.cs.wisc.edu/adsl/Publicat...a-usenix05.pdf

"An important feature of NTFS is journaling. Before a change is made to
the metadata of a file, a transaction is logged in the $LOGFILE."
http://www.mcmillan.cx/ntfs.html

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[boa]

* David Portas wrote, On 07.08.2006 20:33:

Bjørn Augestad wrote:

>>
I'm no NTFS expert either, so I read the tech spec for NTFS at
http://technet2.microsoft.com/Window....mspx?mfr=true

Here's a quote:
NTFS Change Journal

As files, folders, and other NTFS objects are added, deleted, and
modified, NTFS enters change journal records in streams, one for each
volume on the computer.

My interpretation, hopefully wrong, is that NTFS journal entries will be
written to disk whenever a file is modified. This will lead to disk head
movement and decreased performance on a busy disk, won't it?

[snip]

>
"NTFS does not do data journaling"
http://www.cs.wisc.edu/adsl/Publicat...a-usenix05.pdf

Excellent, that's exactly what I needed to know. Thanks. So standard
NTFS is what I'll use.

While we're at it, any thoughts on the registry setting
NTFSDisableLast AccessUpdate?
Bjørn

[snip]

[Greg D. Moore \(Strider\)]

"boa" <bo*****@gmail. comwrote in message
news:mq******** ************@te lenor.com...

>
While we're at it, any thoughts on the registry setting
NTFSDisableLast AccessUpdate?

None other than you're probably over thinking the problem.

Seriously, any performance gains, etc. you're almost certainly better off
looknig elsewhere.

>
Bjørn

[snip]

[Bjørn Augestad]

Greg D. Moore (Strider) wrote:

"boa" <bo*****@gmail. comwrote in message
news:mq******** ************@te lenor.com...

>>While we're at it, any thoughts on the registry setting
NTFSDisableLa stAccessUpdate?

None other than you're probably over thinking the problem.

Seriously, any performance gains, etc. you're almost certainly better off
looknig elsewhere.

Sorry if I've given you the impression that we're not looking elsewhere.
Right now we are 5 guys rewriting queries and client applications, as
well as adding memory, servers, more spindles and everything else that
may improve performance, and the profiler and dta runs 24/7(Almost...).

Maybe it's just me, but I enjoy knowing these things, and if MSFT added
NTFSDisableLast AccessUpdate(ak a unix noatime) I presume they did it for
a reason. It would be nice to know that reason ;-)

Bjørn

>

>>Bjørn

[snip]

[Greg D. Moore \(Strider\)]

"Bjørn Augestad" <bj************ @gmail.comwrote in message
news:eb******** **@bat-news01.banetele .basefarm.net.. .

Greg D. Moore (Strider) wrote:

"boa" <bo*****@gmail. comwrote in message
news:mq******** ************@te lenor.com...

>While we're at it, any thoughts on the registry setting
NTFSDisableLas tAccessUpdate?

None other than you're probably over thinking the problem.

Seriously, any performance gains, etc. you're almost certainly better

off

looknig elsewhere.

Sorry if I've given you the impression that we're not looking elsewhere.
Right now we are 5 guys rewriting queries and client applications, as
well as adding memory, servers, more spindles and everything else that
may improve performance, and the profiler and dta runs 24/7(Almost...).

Maybe it's just me, but I enjoy knowing these things, and if MSFT added
NTFSDisableLast AccessUpdate(ak a unix noatime) I presume they did it for
a reason. It would be nice to know that reason ;-)

I can definitely understand curiousity. :-)
My point is simply that any performance gains here are probably miniscule
compared to what you can make elsewhere.

>
Bjørn

>Bjørn

[snip]

[Roy Harvey]

On Wed, 09 Aug 2006 02:18:46 GMT, "Greg D. Moore \(Strider\)"
<mo************ ****@greenms.co mwrote:

>My point is simply that any performance gains here are probably miniscule
compared to what you can make elsewhere.

I would add that exploring settings that are not commonly used is an
opportunity to discover problems that have not yet been found.

There are no magic bullets.

Roy Harvey
Beacon Falls, CT