Filegroups, Parallellism And RAID

Aug 29, 2007

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

View 2 Replies


ADVERTISEMENT

SQL Server Databases On RAID 5 Or RAID 10

Apr 4, 2007

I am configuring a new database server, without SAN access, and want to know what is the best practice for SCSI RAID configuration. Do most folks prefer RAID 5 or RAID 10 configurations where their databases will reside?

View 8 Replies View Related

RAID 1 Or RAID 5 For Mdf Files?

Mar 27, 2008

I've always heard that RAID 5 (or better, RAID 10) is preferred for the actual database (mdf), but RAID 1 for logging.

If I have a dedicated physical volume for each, what's the performance hit for selecting RAID 1 for the MDF files? 3%, 20%, 200%?

Doing so (all RAID1) will allow me to have a separate physical volume for the TEMP database - that is heavily used by my app.

View 1 Replies View Related

RAID 5 Beats RAID 10

May 1, 2006

RAID 5 beats RAID 10Can I get some feedback on these results? We were having some seriousIO issues according to PerfMon so I really pushed for RAID 10. Theresults are not what I expected.I have 2 identical servers.Hardware:PowerEdge 28502 dual core dual core Xeon 2800 MHz4GB RAMController Cards: Perc4/DC (2 arrays), Perc4e/Di (1 array)PowerVault 220SEach Array consisted of 6-300 GB drives.Server 1 = Raid 103, 6-disk arraysServer 2 = Raid 5 (~838 GB each)3, 6-disk arrays (~1360 GB each)TestWinner% FasterSQL Server - UpdateRAID 513Heavy ETLRAID 516SQLIO - Rand WriteRAID 1040SQLIO - Rand ReadRAID 1030SQLIO - Seq WriteRAID 515SQLIO - Seq ReadRAID 5MixedDisktt - Seq WriteRAID 518Disktt - Seq ReadRAID 52000Disktt - Rand ReadRAID 562Pass Mark - mixedRAID 10VariesPass Mark -Simulate SQL ServerRAID 51%I have much more detail than this if anyone is interested.

View 13 Replies View Related

SQL/RAID

May 29, 2001

My SQL 7 is on RAID 5. Sometimes on non-peak hours, on RAID disks first
two lights ( from left ) are constantly on for hours. NT Task manager, nothing
unusual, SQL current activity - no running user processes. Isn't second light
on RAID comes on if any disk activity ( Read/Write ).

Suggestions are appreciated.

Thanks,
Ivan

View 2 Replies View Related

NT RAID /SQL

Sep 28, 1998

I`ve tried implementing NT Software Raid / Stripping with Parity
and am unable to stripe disc that are more than 2g and
use SQL. I have not found any info in technet. Any ideas! Thanks.

View 2 Replies View Related

RAID 5

Dec 20, 1998

Should one install RAID 5 for SQL Server or just use separate hard drives, one for the data and one for the transaction log?

View 4 Replies View Related

RAID

Apr 15, 2008

could any one tell me about the difference between RAID and SHARED DISK ARRAY

View 3 Replies View Related

Which Raid?

Jul 20, 2005

Hi,I was going to buy a server with Raid 1 as I thought that it meant that ifone of the two mirrored drives fail, you simply take it out and put a newone in. At which point presumably the hardware takes over and copies theother drive over to mirror it again.However, my sql server admin book, says raid 1 is bad, as it means you havelots of downtime, when recovering from a broken drive.Can anyone give me some advice on this? What is the best Raid to use whenyou are running SQL server on the server.ThanksJJ

View 1 Replies View Related

Filegroups

May 25, 2000

I was told to move tables to new filegroups by placing the clustered index on the filegroup and the table would
follow. There are times when I see tables listed on the new filegroup but still listed also on Primary. My goal is
to have only system tables on Primary. How can I get a table to totally leave the PRIMARY group?

There seems to be a system index on the table that was not created by me.

View 1 Replies View Related

FileGroups

Jan 23, 2001

I have databse that was created when I got here and the database was created with 3 file groups. The tables in the database are spread out over the 3 file groups.

How can I find out which table belongs to which file group.

Thank You,
John

View 1 Replies View Related

FileGroups

Oct 8, 1999

If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....

On separate disks ?

Thanks in advance..
Any comments welcome ! Peter

View 1 Replies View Related

Filegroups ?

Oct 8, 1999

If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....On separate disks ? Thanks in advance..
Any comments welcome ! Peter

View 1 Replies View Related

Filegroups

Feb 8, 2006

Hi,

I was wondering if there is a downside of placing individual tables into their own filegroup (i.e., 1:1). This would seem to allow me to continue to backup entire databases but give me the flexibility to restore at a table level if necessary. I ask this because the number of filegroups allowed per database has increased from 256 in SS2000 to 32K in SS2005. What kind of headaches could I get into if I follow this type of design ?

Thanks,
Mario

View 4 Replies View Related

Filegroups

Sep 22, 2005

i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??

View 1 Replies View Related

Filegroups

Oct 8, 2007

hi all,

What is the purpose of files?(primary secondary,transaction log ). what is the use of these files?

View 1 Replies View Related

Raid & Transaction Log ?

Sep 13, 2001

Im setting up a hardware raid 5 solution for one of our db servers. The data files will reside on the stripe. We dont realy want to raid more drives for the Transaction log if its not nessesary. If the drive with the log crashes is the data file for the database useless ?

View 1 Replies View Related

What Level Raid Is Best?

Nov 29, 2005

Hi guys,
On a new server with 4 disks, what level of raid is best to apply. In terms of what's important, I'd say speed is at the top of the list.

BJ

View 1 Replies View Related

Which Raid To Configure

Mar 17, 2004

Hello,
I run a small homw office. I am planning to purchase a dell powerdge 1750 server to install SQL server on that.
I am confused here about which RAID should I install on this server RAID 1 or RAID 5.
The dell customer rep could not tell me the advantages of installing only RAID 1 or only RAID 5 or installing both RAID 1 and RAID 5

View 7 Replies View Related

SAN RAID Setup

Jun 11, 2008

HI All,

I'm going to buy 1 IBM DS3400 SAN. I got 2 x SQL Servers
one for our ERP System other for Web

Physical Server are like this ATM

SQL1 (ERP) ( 3 RAID arrays)
-----

OS: 36GB (RAID 1)
DATA: 120GB (RAID 5)
Log : 15GB (RAID 5)
Backup:270GB (RAID 5)

SQL 2 (WEB) ( 2 RAID Arrays)
-----

OS: 120GB (RAID1)

Data/log/bak 569GB (RAID10) disk like this

DATA: 218gb

LOG:15gb

Backup: rest


So how do i raid the SAN and what sort of HDD i should buy?

View 2 Replies View Related

Dell Raid 0+1

Mar 18, 2006

I am recommending that we change our Raid Configuration on some of ourServers from Raid 5 to Raid 0+1; we are experiencing severe IObottlenecks.Our hardware guys are pushing back a bit. They claim that Dell has aweird implementation of 0+1 and told me something about one drivefilling up before it begins to write to the next. They claimed thatthis gets rid of most of the benefits of 0+1.I know that 0+1 is not as good as 10 for availability, fault tolerance,and rebuilding, but shouldn't the write throughput be about the same?Setup:Poweredge 2850Powervault 220SPerc 4/DC Controller 1Perc 4e/DI Controller 0

View 5 Replies View Related

Which Raid For SQL 2005

Jan 5, 2006

I have always used RAID 5 for my database apps.  I am wondering though is this the best solution.  I am purchasing a new server and deciding whether to go with  RAID 1 or RAID 5.  This server will support all our .NET Apps, SQL Server 2005, Reporting Services, and Integration Services.

Any Input?

View 4 Replies View Related

Filegroups In Sql7

Jul 21, 2000

Is there a way to list all the tables in each of the filegroup
in a database. I have a database with 7 filegroups and would like
to have a list of tables in each filegroup
Sp_helpfilegroup gives you list of filegroups and the names of files

View 1 Replies View Related

Filegroups In SQL7.0

Jan 23, 2001

Is there any way in which I can move an objects/ data to another
filegroup using transact sql?
I have created a table such that the data is on a secondary filegroup.
But the table has image and text data and this data is going to
the PRIMARY filegroup.
I tried chnging the filegroup for the text data but this property but does not change(I did it through EM).
As a result, the disk which contains the primary filegroup is getting filled up.
I have configured my database such that the primary file group is on one disk and the secondary on another and the log on the same disk as the primary filegroup.
I have given lesser space to the primary file group and more space to the secondary filegroup.
help on this is greatly appreciated because my system is rapidly growing with respect to data.

thanks and best regards
Sush.

View 1 Replies View Related

Files/Filegroups

Sep 10, 1999

I was under the impression that if you are using multiple files in your filegroup to store your data that the data should be written evenly across the files.

I have 5 files and the data is being written to only one file which is growing rather large, while the other files are still at 5 - 7 MB. They are all marked for Automatic Growth at this point, but only the 1 file is growing.

Is there something else that needs to be done to enable this? BOL left me with the impression that this is automatic.

View 1 Replies View Related

Backup With Filegroups

Sep 14, 2006

We are going to start using fielgroups to move some indexes to a different RAID. Simple ? is, when you do a full database backup, that backup alss contains the new filegroup?

View 2 Replies View Related

Filegroups In Sql Server...

Feb 9, 2006

hi all,
i have a problem with creating filegroups in sql server...i just want to copy the names of the columns from one table into my filegroups...

this is my problem : i m writting the following code in tsql:

SELECT * INTO Sales FROM Sales1 WHERE 0=1 ON [MyFG1]

Can anyone please help meeee???? i'd like it to work this way, to copy only first column names from Sales1 to Sales but in this filegroup.

if this is not the write way...then can anyone please tell me the right syntax and the way to copy just the names into a filegroup?????

thanx in advance.
Regards.

View 3 Replies View Related

Filegroups Question

Jul 18, 2007

Hello everyone -

Currently the setup is a single filegroup sql machine
with each company as a new database in a single system.

Would breaking the companies into thier own filegroups
be benefitial?

or perhaps creating a instance of SQL for each company be a better approach?

thoughts / ideas would be appreciated

thanks
tony

View 2 Replies View Related

Files && FileGroups

Oct 12, 2007

Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.

1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go

2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
.....
TO FILEGROUP filename_table
go

If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?

View 2 Replies View Related

Indexes And Filegroups

May 19, 2004

I'm fishing for some advice here.

I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.

I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)

I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?

I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.

So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?

The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.

many thanks

Steve

View 1 Replies View Related

Question With Filegroups

Jul 23, 2005

I'm trying to tweak our DB as much as I can. We have the full applicationdatabase sitting on a RAID 10. Logs, tempdb, etc.. are on separate arrays.I read some articles about making separate filegroups. Of course, they allsay to move them to different arrays, which because of budget is notpossible right now. I also read articles that say to put large tables in afilegroup then add another data file to the group. This allows SQL Serverto use multiple threads to access the tables. Therefore, to my question.If I split our database up into multipele file groups on the same array,could I see a performance gain. Would the database be able to issuedifferent threads for each group when a query is run and improve overallperformance.Thank you,Adam

View 2 Replies View Related

Queues And Filegroups

Apr 19, 2006

Hi

We can create a queue on a filegroup for performance reasons however what about the sys.transmission_queue which could have messages backing up when the target is down.

What are your thoughts.

Cheers

View 3 Replies View Related

Log Shipping && Filegroups

May 7, 2008

Hi Guys,

I need some help for my Log Shipping database please.

I have a database X on the production server and on the log shipping standby server (Read-only). The logs on the production server is shipped to the standby server. The standby server restores the logs through a schedule job.

Now what happended is couple of days back we created another filegroup (Secondary) on the production server for the database X to keep our history separated from the most current data. When we created the secondary filegroup our logs on the log shipping server cannot restored. We investigate the problem and found that there is no secondary filegroup attached to our database X on the log shipping server, which is obvious.

My question now is there a way we can create a secondary filegroup for the database X on the log shipping server without breaking our existing log shipping? Do we must restore the full backup of the database X on the log shipping server in order to keep the transaction logs applied on the log shipping database X?

Please help.

Thanks.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved