Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2004-11-10 : 04:47:45
|
| I read once that if there are several tables which are used in a join, then it is best to place the tables on different disk drives so that there will be simultanous access and therefore better performance. If this is the case then how is it possible to place tables on different drives? Thanks |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-11-10 : 05:25:48
|
| Look in BOL for files & filegroupsAndy |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-10 : 05:34:45
|
| I already have and not yet found information about pointing the tables to be placed on different disk drives.Thanks |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-11-10 : 05:46:36
|
| BOLUsing Files and FilegroupsFilegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, Microsoft® SQL Server™ 2000 writes an amount proportional to the free space in the file to each file within the filegroup, rather than writing all the data to the first file until full, and then writing to the next file. For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. This way both files become full at about the same time, and simple striping is achieved.As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all set to automatically grow. When space in all files in the filegroup is exhausted, only the first file is expanded. When the first file is full, and no more data can be written to the filegroup, the second file is expanded. When the second file is full, and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full, and no more data can be written to the filegroup, the first file is expanded again, and so on.Using files and filegroups improves database performance by allowing a database to be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that comprises three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can simultaneously access the data in parallel, which speeds up database operations.Additionally, files and filegroups allow data placement because a table can be created in a specific filegroup. This improves performance because all I/O for a specific table can be directed at a specific disk. For example, a heavily used table can be placed on one file in one filegroup, located on one disk, and the other, less heavily accessed tables in the database can be placed on the other files in another filegroup, located on a second disk.RecommendationsThese are some general recommendations for files and filegroups: Most databases will work well with a single data file and a single transaction log file.If you use multiple files, create a second filegroup for the additional file and make that filegroup the default filegroup. This way, the primary file will contain only system tables and objects.To maximize performance, create files or filegroups on as many different available local physical disks as possible, and place objects that compete heavily for space in different filegroups.Use filegroups to allow placement of objects on specific physical disks.Place different tables used in the same join queries in different filegroups. This will improve performance, due to parallel disk I/O searching for joined data.Place heavily accessed tables and the nonclustered indexes belonging to those tables on different filegroups. This will improve performance, due to parallel I/O if the files are located on different physical disks.Do not place the transaction log file or files on the same physical disk with the other files and filegroups. See AlsoCREATE DATABASEPhysical Database Files and FilegroupsPlacing Tables on FilegroupsTransaction LogsAndy |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-11-10 : 05:55:24
|
| I can not find what I am after in BOL as you suggested. But using your tip on looking for Placing Tables on Filegroups on the net gave me this link.Thankshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_tuning_6dkp.asp |
|
|
|
|
|
|
|
|
performance