February 1, 2008 at 7:19 am
Hi all,
Let's ignore tempdb, logs, and primary filegroup for now, my question just relates to the user Data filegroups:
My SQL Server has 2 dual core Xeons, so I understand the best filegroup set-up is to have 2 drives/arrays for Data, with 1 filegroup on each drive, and 2 datafiles in each filegroup.
Then I have some options as to how to distribute my data across the two filegroups:
1) Data on 1, non-clustered indexes on the other.
or
2) Largest/high-volume table on 1, rest of tables on the other.
or
3) Partition that large/high-volume table across both filegroups, and fill in both filegroups with the rest of the tables.
Question1: Would one of these 3 options provide the most improvement (given my limitation of only 2 drives). Or is there no way to know
without knowing how the DB is being used?
Now to throw a wrinkle into it: My SQL Server has 4 user DB's on it. I now have to decide how to spread 4 user DB's across only 2 drives.
Question2: Should I just optimize each DB as per the answer to my question above, or just do something simple like place 2 DB's on 1 drive, and the other 2 DB's on the second drive?
Thank you for your help,
AlexM
February 1, 2008 at 10:45 am
It really depends.
How big and how often accesses these databases are.
Are they mostly read- or OLTP databases?
Do you have Web or Application server on the same machine?
What are performance requirements vs. data protection requirements?
Do you have performance problems at all or your DB performance is much better than network or web performance?
Are you sure these 2 arrays are separate arrays and not partitions of the same drive?
What are these arrays? RAID 1 or RAID 5 ?
Regards,Yelena Varsha
February 1, 2008 at 11:11 am
Unfortunately you can't ignore the logs unless, that is, you are using simple recovery model (not recommended). Assuming logging is going on, I would start with logs on one array and all data/indexes on the other. Gather performance data with this set up and start building your case for adding more drives, which is what you really need.
February 2, 2008 at 10:07 am
Thanks for the replies, let me try to answer the questions:
> How big and how often accesses these databases are.
- The two 'main' DB's each have a rather large table reaching about 50 mil rows, and then a number of supporting tables never reaching more than a few mil.
These two are accessed the most, and unfortunately it's usually the 50 mil row table.
- The third DB contains extra info about jobs performed on the first two DB's.
- The fourth DB is rather small, and not accessed as much. It's the most read-only DB, but not exclusively.
> Are they mostly read- or OLTP databases?
Unfortunately they're about 50/50.
> Do you have Web or Application server on the same machine?
No, dedicated SQL Server. 2 dual core Xeons, 8 GB ram, 15k SCSI drives.
> What are performance requirements vs. data protection requirements?
I'm looking for performance right now, data can be recovered if lost.
> Do you have performance problems at all or your DB performance is much better than network or web performance?
The SQL Server seems a little sluggish, and my Disk Queue times are too long for sure. Plus our data could eventually grow 10-fold so I want to start thinking ahead.
> Are you sure these 2 arrays are separate arrays and not partitions of the same drive?
They can be whatever I make them, they'll be separate arrays or worst case just separate drives.
> What are these arrays? RAID 1 or RAID 5 ?
I know RAID 5 isn't that great for OLTP, and I probably can't affort RAID 10, so I'm pretty limited to what I can do I think.
And as for the second response post - yes I agree the logs are important, and I am going to do that, but I just wanted to get answers for the data right now, any suggestions?
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply