Arthur Fuller
fuller.artful at gmail.com
Wed Jun 3 08:09:00 CDT 2009
JC, I forwarded your post to my friend Dejan. Here is his reply. A. ---------- Forwarded message ---------- From: Dejan Sunderic <dejans at hotmail.com> Date: 2009/6/3 Subject: RE: [dba-SQLServer] Optimizing my SQL Server for speed To: Arthur Fuller <fuller.artful at gmail.com>, jwcolby at colbyconsulting.com > 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? That would iprove their performance. > How do I go about specifying where indexes are physically placed? Create Index statement contains a segment for specifying the file group. > 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. Redandancy is typically critical. But if loading performance is not an issue for teh system, you can even leave it on RAID6. I do not see scenario for logs on RAID0. Dejan Šunderić Database Architect www.trigonblue.com www.tsql.ca ------------------------------ Date: Wed, 3 Jun 2009 08:30:55 -0400 Subject: Fwd: [dba-SQLServer] Optimizing my SQL Server for speed From: fuller.artful at gmail.com To: dejans at hotmail.com I'm forwarding this from my friend John Colby. Do you have any suggestions for him? Thanks, Arthur ---------- Forwarded message ---------- From: *jwcolby* <jwcolby at colbyconsulting.com> Date: Tue, Jun 2, 2009 at 8:59 PM Subject: [dba-SQLServer] Optimizing my SQL Server for speed To: Dba-Sqlserver <dba-sqlserver at databaseadvisors.com> 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 ------------------------------ We are your photos. Share us now with Windows Live Photos.<http://go.microsoft.com/?linkid=9666045>