Re: SQL Server 2005 and file systems, any recommendations

Erland Sommarskog wrote:
I get your point(s) :-) So FAT32 is out, not a big surprise.

Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.

So even if the file system isn't the performance bottleneck right now,
I'd prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn't
find anything about tuning the journal. I'll have to reread it, I
guess...


There is the option of raw partition. A fellow MVP told me that he
made some tests some years back (so it was not on SQL 2005), and got
a 20% improvement with raw partition. But raw partitions are difficult
to manage, and I guess that you would still have to use NTFS for things
like database snapshots. Personally, I would never consider using raw partition for a production system.

I thought that support for raw partitions was dropped? Even if it still exists, I won't use it for data, maybe for a transaction log safely placed on a tiny slice of a 15K RAID-1 disk. I hope I'll find the time to test and measure the performance for such a setup. Nice just to know, even if it can be a pita to find the proper statement mix to test with.

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 ;-)




20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation,

What about proper sorting of our æøå's and other funny european characters? Won't work, will it?

or using an SQL
collation for varchar data. In fact, for a query like.

SELECT ... FROM tbl WHER col LIKE '%Whatever%'

the difference between a CI/CS collation and a binary collation can be
factor seven.

You also lose 90% in performance by writing a bad query.

That I've noticed. Lots of 'silly' gotchas to remember and beware of.
BTW, I did som more profiling today on one of the production servers, expected everything to be quiet as it is sunday. Quite a surprise to find that it was performing 662 calls to sp_reset_connection per second over a period of 45 minutes. As you may have guessed, the system has 'issues'...


You may be interested in this article:
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx.


Thanks, I'll read it asap.

Another thing: Have you got any links to a site describing proper use of SARGs and operators and the query optimizer? I threw away my copy of SQL Server Unleashed (never do that) and have forgotten all about it. The Unleashed for 2005 won't we available until Nov. 27th...

Bjørn
.

Relevant Pages

  • 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: SQL Server 2005 and file systems, any recommendations
    ... NTFS with the journal on a separate disk. ... journaling applies only to file allocation, ... My interpretation, hopefully wrong, is that NTFS journal entries will be written to disk whenever a file is modified. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: FAT32 or NTSF
    ... There are certainly good reasons for using NTFS over FAT-32. ... there is no journaling on ... >changes can be committed to disk. ...
    (microsoft.public.windowsxp.general)
  • Re: Journaling - small remaining confusion about one detail
    ... even I realize that journaling will not protect user data that is ... being written to disk at the time of any power interruption. ... I was concerned that some updates to the file system would ... If that was the only reason for using journaling, ...
    (comp.sys.mac.system)
  • Re: Desktop Filesystem Benchmarks in 2.6.3
    ... o I see you are using and IDE disk with a large write cache. ... Has anyone done any benchmarks concerning write cache and journaling? ...
    (Linux-Kernel)
Loading