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
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
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
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
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
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
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
Apr 15, 2008
could any one tell me about the difference between RAID and SHARED DISK ARRAY
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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