It is interesting point that , from my understanding after reading your statement, several files in same filegroup which is in same disk will make better performance because SQL Server give assign threads to each of the files, so one thread per file instead of one thread per disk; is my understanding right?

Kevin4/20/2009 11:16:36 PM

@dnewtontmw: I was actually going to take umbrage at your comment as (in my mind) the advice I gave clearly didn't work off the one-thread-per-CPU mind-set. (And, truth is, I hate that PSS post anyhow, because it does state that multiple files/filegroups CAN/DOES speed up perf, but spends so much time focused on other details that I think that MANY readers focus too much on the words 'Urban Legend' and wronfully assume that asynch IO can't be leveraged to drastically increase throughput.)

At any rate.. after I got off my high-horse, I reread what I had written and realized that I sadly did step in it, as my analogy of using multiple files concludes with the statement "instead of forcing query execution to wait on a single thread to pull data from disk."... which clearly sounds wrong from a technical standpoint. (Still kind of scratching my head about what I meant or was thinking when I wrote that...so feel free to throw crap at me as I deserve it.)

That said, the point is: the use of multiple files can facilitate asynchronicity by increasing available I/O bandwidth (or by helping to keep the I/O path from getting saturated).

Michael4/7/2009 10:02:44 PM

Note however that the "one thread per SQL Server file" is not true, per Microsoft. For details, see:

http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

You could get better performance if you had multple files and those files were all on separate spindles or RAID arrays, but that's just for I/O bandwidth, not multi-threading issues.

Doug4/1/2009 7:27:25 AM

@Marcos, glad you liked the article. And yeah, this was a very basic, or introductory, article. The primary purpose was to introduce basic notions of maximizing performance from a wide variety of angles - and to help DBAs think in terms of maximizing their high-performance disks by offloading as much non-essential work as possible off of them. Covering some of the more advanced (and less covered) features would be a blast too...

Michael3/31/2009 11:40:08 AM

Good article on an already well documented aspect of SQL Server's systems.
Isn't any new feature, or maybe a "not so clear" aspect that deserves attention? I know it is much easier to write about something that has a lot of reference on the web, but I believe that you could explore less documented features, I know it will require more time, but ,maybe, the article will be more useful.

Marcos3/30/2009 7:08:53 AM