03. April 2010 · Comments Off on SQL Server Threads and Multiple Files · Categories: SQL Server · Tags: ,
Here is the latest
From this post:
I have been answering questions on two of the most misunderstood ‘SQL Server Urban Legends’ frequently again so I decided to make a post about them.
·         SQL Server Uses One Thread Per Data File
·         A Disk Queue Length greater than 2 indicates an I/O bottleneck
al123 Multiple Filegroups
Hi experts,
I’m new to SQLServer 2000 and would like some advice on filegroups.
Is there any advantage to seperate the filegroups for different type of data.
For example:
Data_1 for tables
Index_1 for Indexes
Audit_1 for Audit tables
The files for these filegroups would be placed on RAID Disk.
Eric C. (Eric Christensen, SQL Server Storage Engine)
No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.
—————–
If your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let’s say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.
Essentially, the more files that a large table is divided into, the greater the potential performance. Of course there is a point where the additional threads aren’t of much use when you max out the server’s I/O. But up until you do max out the I/O, additional threads (and files) should increase performance. [7.0, 2000] Updated 9-19-2005
—————–
Configuring a server with either 4 or 8 tempdb files may by itself cause performance problems (due to the overhead of SQL having to manage too many files), so the recommendation should be changed to start with just 1, and monitor for tempdb contention. ONLY if you see contention then add more files until the contention is resolved. However it’s worth noting that just adding more files won’t always avoid tempdb performance issues, as it’s entirely possible that the issue is with a slow I/O subsystem. Adding more files ONLY helps resolve contention for the schema lock when creating new objects, which in tempdb can happen at a very high rate.  However not all applications make use of tempdb. It’s possible that specific application may make no use of tempdb. In that case creating multiple tempdb files is a big overhead and a waste of time.
—————-
From: Al – view profile
Date: Wed, Jan 23 2002 5:01 pm
Groups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
BTW, I came across this in SQL 2000 BOL.
—————————————————————————-
Placing Tables on Filegroups
If the computer has multiple processors, Microsoft® SQL Server 2000 can
perform parallel scans of the data. << Multiple parallel scans can be
executed for a single table if the filegroup of the table contains multiple
files.>> Whenever a table is accessed sequentially, a separate thread is
created to read each file in parallel.  << For example, a full scan of a
table created on a filegroup comprising of four files will use four separate
threads to read the data in parallel. Therefore, creating more files per
filegroup can help increase performance because a separate thread is used to
scan each file in parallel. >>
From: Andrew J. Kelly – view profile
Date: Wed, Jan 23 2002 7:37 pm
Groups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
This is an error in BOL2000.  I have already reported it and it is confirmed
that it will state otherwise for the next revision.  It is misleading but I
have confirmed at the source that 2000 does not require multiple files to
spawn multiple threads.
Andrew J. Kelly,  SQL Server MVP
TargitInteractive
Please join me at PASS North America in Denver – the first and only user
conference dedicated to SQL Server.
http://www.sqlpass.org/events/denverjan/index.cfm
“Al” <nos…@nospam.com> wrote in message

Here is the latestFrom this post:http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspxI have been answering questions on two of the most misunderstood ‘SQL Server Urban Legends’ frequently again so I decided to make a post about them.·         SQL Server Uses One Thread Per Data File·         A Disk Queue Length greater than 2 indicates an I/O bottleneck

Here is the older stuffhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=83430&SiteID=1
al123 Multiple Filegroups Hi experts,I’m new to SQLServer 2000 and would like some advice on filegroups.Is there any advantage to seperate the filegroups for different type of data.For example:
Data_1 for tablesIndex_1 for IndexesAudit_1 for Audit tables
The files for these filegroups would be placed on RAID Disk.
Eric C. (Eric Christensen, SQL Server Storage Engine)No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.
—————–http://www.sql-server-performance.com/filegroups.aspIf your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let’s say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.
Essentially, the more files that a large table is divided into, the greater the potential performance. Of course there is a point where the additional threads aren’t of much use when you max out the server’s I/O. But up until you do max out the I/O, additional threads (and files) should increase performance. [7.0, 2000] Updated 9-19-2005
—————–http://sqljunkies.com/WebLog/odds_and_ends/archive/2006/06/21/21985.aspx
Configuring a server with either 4 or 8 tempdb files may by itself cause performance problems (due to the overhead of SQL having to manage too many files), so the recommendation should be changed to start with just 1, and monitor for tempdb contention. ONLY if you see contention then add more files until the contention is resolved. However it’s worth noting that just adding more files won’t always avoid tempdb performance issues, as it’s entirely possible that the issue is with a slow I/O subsystem. Adding more files ONLY helps resolve contention for the schema lock when creating new objects, which in tempdb can happen at a very high rate.  However not all applications make use of tempdb. It’s possible that specific application may make no use of tempdb. In that case creating multiple tempdb files is a big overhead and a waste of time.
—————-From: Al – view profileDate: Wed, Jan 23 2002 5:01 pmGroups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
BTW, I came across this in SQL 2000 BOL.—————————————————————————-Placing Tables on Filegroups
If the computer has multiple processors, Microsoft® SQL Server 2000 canperform parallel scans of the data. << Multiple parallel scans can beexecuted for a single table if the filegroup of the table contains multiplefiles.>> Whenever a table is accessed sequentially, a separate thread iscreated to read each file in parallel.  << For example, a full scan of atable created on a filegroup comprising of four files will use four separatethreads to read the data in parallel. Therefore, creating more files perfilegroup can help increase performance because a separate thread is used toscan each file in parallel. >>
From: Andrew J. Kelly – view profileDate: Wed, Jan 23 2002 7:37 pmGroups: microsoft.public.sqlserver.server, microsoft.public.sqlserver.setup
This is an error in BOL2000.  I have already reported it and it is confirmedthat it will state otherwise for the next revision.  It is misleading but Ihave confirmed at the source that 2000 does not require multiple files tospawn multiple threads.
— Andrew J. Kelly,  SQL Server MVPTargitInteractive
Please join me at PASS North America in Denver – the first and only userconference dedicated to SQL Server.http://www.sqlpass.org/events/denverjan/index.cfm
“Al” <nos…@nospam.com> wrote in message