Striping and splitting

SQL Server 2005 SP2

I read the excellent advice
"Optimize tempdb in SQL Server by striping and splitting to multiple
files" at
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci 1276989,00.html

My question: All of my database files, primary and log, are on a SAN
that is using RAID 10 -- so these files are striped and mirrored across
multiple spindles anyway.

Will I get any advantage from creating multiple files per processor in
my filegroups, as the article suggests?

Will SQL Server be smart enough to use multiple workers (threads?) with
a single file, or do I have to signal that it should do that by creating
multiple files in my filegroups?

TIA

aj

Re: Striping and splitting

>Will I get any advantage from creating multiple files per processor in
>my filegroups, as the article suggests?

There are performance advantages to creating multiple files for
tempdb, one per processor. Multiple files per processor are not
suggested by the article. Note that you can find this advice in Books
On Line, on the page titled Optimizing tempdb Performance:

"As a general guideline, create one data file for each CPU on the
server (accounting for any affinity mask settings) and then adjust the
number of files up or down as necessary. Note that a dual-core CPU is
considered to be two CPUs."

There are other good points there too about file sizes and autogrow.

Roy Harvey
Beacon Falls, CT

On Wed, 07 Nov 2007 09:37:11 -0500, aj wrote:

>SQL Server 2005 SP2
>
>I read the excellent advice
>"Optimize tempdb in SQL Server by striping and splitting to multiple
>files" at
>http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gc i1276989,00.html
>
>My question: All of my database files, primary and log, are on a SAN
>that is using RAID 10 -- so these files are striped and mirrored across
>multiple spindles anyway.
>
>Will I get any advantage from creating multiple files per processor in
>my filegroups, as the article suggests?
>
>Will SQL Server be smart enough to use multiple workers (threads?) with
>a single file, or do I have to signal that it should do that by creating
>multiple files in my filegroups?
>
>TIA
>
>aj