Reply to Re: SQL Server 2005 and file systems, any recommendations

Your name:

Reply:


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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация