|
|
Posted by Erland Sommarskog on 08/05/06 22:35
>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.
20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation, 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.
You may be interested in this article:
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx.
--
Erland Sommarskog, Stockholm, esquel@sommarskog.se
Navigation:
[Reply to this message]
|