Re: Another Filegroup question
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/25/05
- Next message: Andrew J. Kelly: "Re: index fragmentation"
- Previous message: Mardy: "Re: Another Filegroup question"
- In reply to: Mardy: "Re: Another Filegroup question"
- Next in thread: Sarav: "Re: Another Filegroup question"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 24 Jan 2005 21:57:18 -0500
When it comes to db performance size doesn't matter as much as the number of
heads<g>. I would suggest multiple filegroups only so it would be easier to
split them off onto multiple drive arrays in the future. There is no
performance benefit in this case. The same goes for multiple files as well.
SQL Server 2000 can read a single file with multiple threads anyway. With a
small array as this I don't think multiple files will buy you anything. I
don't think you have too much of a choice if you only have 4 drives that are
each 146GB and you have a 130GB db that will grow. Unless you do some
extensive testing you don't know how well splitting the indexes and data
will do. It is safer to make a single Raid 1+0 and put it all on the one
array.
-- Andrew J. Kelly SQL MVP "Mardy" <Mardy@discussions.microsoft.com> wrote in message news:ACD2CA69-F9CA-4A1C-8250-F1E6C39073D1@microsoft.com... > Andrew > > Sorry for the confusion. Yes 2 raid 1 arrays, 4 - 146 gb drives. I know > it's > less than ideal but it's what I have to work with and I need to make the > most > of it until I get the funding to upgrade. So you suggest 1 array, 1 file > group. Correct? What about using multiple files... any benefit? > > Now I do have a couple of other options. I can break the mirror. After all > it is a reporting server.. important but not mission critical. Also, one > other option. Due to an expense classification/policy oddity, I could > secure > 4 - 300 gb drives. My concern, however, is that the seek time on these > drives > would be painful and the manufacturer does not recommend them for database > use. I believe that the rpms are a little lower than the 146gb drives as > well. > > Thanks again MG > > "Andrew J. Kelly" wrote: > >> Mardy, >> >> I am confused now as to what you actually have. You originally stated: >> >> >> I have a server with 2 processors, 2 drive >> >> arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) >> >> A Raid 1+0 needs a minimum of 4 disks so I assumed you had two 4 disk >> Raid >> 1+0's. If you only have 4 disks you can not have two separate Raid 1+0 >> drive arrays. You must have two Raid 1 drive arrays. That is a big >> difference. If this is indeed a reporting server that usually means you >> will >> have lots of table or index scans and will most likely have a lot of disk >> access. This configuration will probably not meet your expectations due >> to >> the drive configurations. If you are going to need more disk space than >> 146GB you might be better off creating ONE Raid 1+0 (not a Raid 1) with >> all >> 4 disks. That will give you twice the disk space but you will have to >> place >> everything on one array. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Mardy" <Mardy@discussions.microsoft.com> wrote in message >> news:519A8841-46F6-42A2-86AD-9BD0BD74ED07@microsoft.com... >> > Thanks for the suggestions. I should have mentioned that the size of >> > the >> > db >> > will grow quickly to accomodate maintaining more history on the report >> > server. The size of the data will surpass the 146 gb of the one array >> > very >> > quickly so I can't put the t-log on a separate array. I only have >> > space >> > for >> > 4 physical drives on this box and I'll have to live with it for about 9 >> > months. Consequently I see my options as 1. creating one filegroup >> > across >> > two >> > arrays and possibly creating multiple files to improve threading and >> > performance or 2. create two filegroups and look at distributing the >> > load >> > somehow. >> > >> > Now, considering that this is a reporting server I'm toying with the >> > idea >> > of >> > breaking the mirrors to free up 2 drives ????? >> > >> > MG >> > >> > "Andrew J. Kelly" wrote: >> > >> >> I agree with Michael. I would use 2 disks in a RAID 1 for the log >> >> files >> >> and >> >> add those other two disks to the other Raid 1+0 and place all the >> >> files >> >> there. Yes you can get increased performance by placing data and >> >> indexes >> >> on >> >> separate arrays but probably not as much overall gain from adding more >> >> disks >> >> to the Raid 1+0 and separating the logs. >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Mardy" <Mardy@discussions.microsoft.com> wrote in message >> >> news:2B260F96-324A-40A2-9EFE-88B66E85C349@microsoft.com... >> >> > I'm building a reporting server. I have a server with 2 processors, >> >> > 2 >> >> > drive >> >> > arrays (2 raid 1+0 arrays - 4 disks total of 146 gb each) server >> >> > with 2 >> >> > gb >> >> > of >> >> > memory (negotiating for more). I have about 130 gb of data and a >> >> > little >> >> > more >> >> > than half is indexes. One table makes up 50% of the data and >> >> > indexes. >> >> > Also, I >> >> > plan to use transactional replication. >> >> > >> >> > I'm considering creating 2 file groups, one on each drive/array and >> >> > separating the indexes from the data. I'm assuming this will yield a >> >> > performance benefit and I'm wondering if I should also create 2 >> >> > files >> >> > in >> >> > each >> >> > filegroup to futher enhance performance but am not sure if this will >> >> > result >> >> > in further gains. >> >> > >> >> > Thanks >> >> > >> >> > MG >> >> >> >> >> >> >> >> >>
- Next message: Andrew J. Kelly: "Re: index fragmentation"
- Previous message: Mardy: "Re: Another Filegroup question"
- In reply to: Mardy: "Re: Another Filegroup question"
- Next in thread: Sarav: "Re: Another Filegroup question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
