Francisco Tapia
fhtapia at gmail.com
Thu Jun 4 13:37:04 CDT 2009
Some of the things you can do at this time is to check where your biggest bottle necks are with your existing setup.. IF, your biggest problem at this time is I/O, then 1) I would place all transaction logs on a SEPARATE bus and Disk system. This will help when you need to import / update a lot of records (from the sound of your system it seems like a whole lot). 2) I would place your TEMP DB on a separate bus and Disk system as well. Because you create a lot of reports with order by's and group by's... I suggest FAST disks, they don't need to be huge, but should stand to be as big as your biggest possible recordset maybe between 100-500gb by the sound of your system. 3) Raid out your Database. a) place your tables into specific filegroups. Filegroups can help out by separating heavily used tables from tables that do not get used as much. The advantage is that you can have multiple threads running and gain performance by not technically reading from the same file. b) If it's possible you can place filegroups into different raid clusters, this way you can buy superfast drives for the busy tables, and save money by storing less likely used tables into filegroups that are used least often. Lastly, RAM... Really you need a lot to keep your cachehit ratio up, I'd run profiler to see if you are keeping over 90% in cache or if it all is falling out (so you are paging). The only way to use a lot of Ram with sql server 2005 is to go with their enterprise license this allows your sql server to use as much ram as you can afford. CPU... I don't know your system, but you may want to review how pegged your systems get More Cores are useful, but you will also need to adjust the CPU settings so that you allow the OS to keep a CPU to perform the normal tasks such as paging etc. I hope this information helps. -Francisco http://sqlthis.blogspot.com | Tsql and More... On Tue, Jun 2, 2009 at 5:59 PM, jwcolby <jwcolby at colbyconsulting.com> wrote: > I am running a pair of homebuilt servers, Quad core, 8 gigs RAM, Windows > 2003 x64, SQL Server 2005 > x64. I use an Areca hardware raid controller and have an 8 (500 gig) disk > Raid 6 array. I am about > to replace those disks with 5 (1 TB) disks Raid 6. Obviously I will lose a > bit of speed in the > process since I have fewer disks in the array, though I will eventually > fill out the array with 3 > more disks for a total of 8. Maybe. > > My question concerns how to physically implement the database. I know very > little about the details > of file groups, disks for log files and so forth, splitting out indexes > etc. > > The databases are essentially read only under normal use. Because of the > size of the databases, > anywhere from 50 million records and up, as many as 640 fields in one, but > more common 60 to 100 > fields, I tend to set up what I know as "cover indexes". > > I am considering going to a pair of 120 g SSDs to house one specific > database that is kind of the > center of my data universe. In this database, 50 million records, 640 > fields, I will create these > indexes to place the PK and anywhere from a few to a dozen or more fields > into an index. That index > then provides all of the fields needed to supply all of the where clause. > > My question really is, if I am successful in getting the index to supply > all of the data, should the > INDEXES go in the SSD and the main table just sit out on the raid array? > How do I go about > specifying where indexes are physically placed? > > Next question is related, what about the log files? I could put the log > files on a non raid disk > connected directly to the motherboard, or on a pair of disks mirrored on > the Areca Raid controller > (if redundancy is important) or even a pair of disks in Raid 0 if > redundancy is not important but > speed is. > > As I said, I am rebuilding this thing and want to take this opportunity to > think about how to > distribute the parts of the databases, and I know absolutely nothing about > this side of things. > > Any thoughts on this? > > TIA, > > -- > John W. Colby > www.ColbyConsulting.com > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >