SQL Server Performance

Filegroups, parallellism and RAID

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by timo4711, Aug 29, 2007.

  1. timo4711 New Member

    Hi
    Background:
    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
  2. satya Moderator

    Golden rule is: No amount of SQL tweaking or system tuning can optimize the performance of queries run against a poorly designed or disorganized database.
    If parallelism is being used you will see the parallelism operator against the index but it does not break it down to the level your looking for (is it doing parallel scans). In this case you can take help of DMVs within SQL 2005. Also limiting factor will be CPU, not disk IO. Creating the correct indexes on tables in the database is perhaps the single greatest performance tuning technique that a DBA can perform. Indexes are used to enhance performance. See this Whitepaper that explains indexes and filegroups.
    For more information continue disucssion in this thread, by referring to SQL 2000 in thsi case. As it will be hard to followup in 2threads.

Share This Page