Re: SQL Server 2005 and file systems, any recommendations

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/WindowsServer/en/library/59a9462a-cbdd-45e7-828b-12c6cd9ae4781033.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/ms130214(en-US,SQL.90).aspx
--

.

Relevant Pages

  • Re: SQL Server 2005 and file systems, any recommendations
    ... systems *with* the journal on the same disk as the db data ... I read the NTFS spec and browsed through all the options, ... There is the option of raw partition. ... The best solution, IMHO, would be to have NTFS without journaling or NTFS with the journal on a separate disk. ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Server 2005 and file systems, any recommendations
    ... RAID levels and more dedicated disk usage(e.g. ... The db server runs on Win2003. ... I'd prefer to have no journaling and sacrifice ... Are there any commonly recommended tweaks to NTFS when the server and ...
    (comp.databases.ms-sqlserver)
  • Re: wifi usb adaptor
    ... make) of keyboard so it might be a case of your favourite keyboard ... Yes, I've considered that, but the only USB2 PCI cards I have in stock, ... NTFS in the event of FS 'damage'. ... journaling) is way better than the superblock-duplication of e2fs. ...
    (uk.comp.homebuilt)
  • Re: SQL Server 2005 and file systems, any recommendations
    ... NTFS with the journal on a separate disk. ... journaling applies only to file allocation, ... performance constraint at runtime. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: "Resistenz" bei Hard Reset
    ... Wer WinXP mit NTFS ... die besseren Karten (Journaling). ... | NTFS is a journaling file system that uses database-like logging ... | quickly rolls back the uncommitted transactions and so is quickly ...
    (microsoft.public.de.german.windowsxp.sonstiges)
Loading