|
|
Posted by Bjψrn Augestad on 08/06/06 15:38
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
Navigation:
[Reply to this message]
|