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 |
|
timoqvist
Starting Member
8 Posts |
 Posted - 2007-08-29 : 08:56:35
|
| HiBackground: My company is in the process of upgrading our sql 2000 server to 2005 on new server hardware. In this process I'm charged with the actual server and database configuration on the new server so currently I'm investigating how to take advantage of new 2005 features and improve the use of common features on both platforms. We've decided to perform this upgrade in three stages (won't bother you with why) and the first stage is to install the new server with a 2000 installation with a different configuration regarding files/filegroups on the new hardware configuration that has a different raid configuration. The old server configuration was lacking in just about every department so I'm serious about getting it right this time. Its an OLTP system btw.The question(s):The 2780A course book states that sql 2005 can perform the following scans: "Multiple parallell scans for a single table if the filegroup of the table contains multiple files." Does this imply that the files must be on separate disks/raid channels? Ie, if I put all files in the filegroup on the same RAID 10 channel will I gain nothing over having just one file?Can 2000 take advantage somehow of several files in a filegroup to achieve greater parallellism or is there no (end user) performance oriented reason to have more files in a filegroup? (Disregarding any impact of different backup strategies for several files).I'm intending to place all nonclustered indexes in a separate filegroup with all files assigned to a dedicated RAID 0 channel for striping. Due to the lack of fault tolerance here I'm wondering if this is wise. But since nonclustered indexes are basically secondary metadatastructures and doesn't contain any data I'm wondering wether the server can disregard using an index when executing a query if the index resides on disk which have failed and the index is inaccessible. Is there a fallback option available to the server to perform a table scan or use another index instead?Basically the same question can be asked for tempdb, use a RAID 10 with four disks or dare I use a RAID 0 with four disks (since 2005 use tempdb more heavily than 2000).Also considering to put the OS on a dedicated RAID 1 and a dedicated RAID 0 for the pagefile, same concerns apply.I appreciate any input, also examples (or links) or your experiences of good RAIDing practices for OLTP systems. Thanks for your time.PS. Sorry for posting this in both 2000 and 2005 forums but I don't wanna miss out on any good answers. ^^/Timo |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2007-08-29 : 22:56:02
|
| Ok, I'll take a stab at this :) It's been awhile since I posted on SQL Team.Before we get into some complex configs with multiple filegroups, it may make sense to take a step back here and gather some data.1. What is your current disk configuration? (list interface type (SCSI, Fibre Channel etc), number of spindles, RAID config, and what datafiles are on which drive)2. What is your current disk utilization (Logical Disk Read/Write bytee/sec and Disk Read/Writes per sec)?Those two things will help you determine where you are right now with disk perf, and then from there you can take estimated growth and then build a system to handle that. Reply back with those two things and we'll go from there!Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of Microsoft Corp. All information is provided "AS IS" with no warranties and confers no rights. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-30 : 00:48:53
|
| If filegroup have multiple files, os may generates multiple read/write threads to access files even they are on single disk array. That's true for both sql2k and sql2k5. |
|
|
|
|
|
|
|
|
Filegroups, parallellism and RAID