Scalability
-
The following is a question I received over the weekend.
“If you had to grow your database capability by a factor of 10, how would you achieve this using the following restrictions?
1.) No new hardware can be added.
2.) DBMS is SQL 2000
3.) Single server with 4 processors and RAID5 “
My initial solution would be to create file groups and place the larger tables on those file groups by I am not sure how that woyld really help.
Thanks for any suggestions
Gary
-
That is a tough one
No hardware additions ! if you are hitting the ceiling with the current HW may be it is recommended to reconsider.
SAN would be a good bet and use RAID 10 (0 +1 ).RAID 5 is bad for intensive write operations.
RAM is a great help and cheap these days.
But without additional HW:
1 - use file groups ... it is a must. it will help SQL to use more threads to access more tables at any instance or simultanuosly, especailly for write intensive operations
2 - If possible, Move the logs into a sparate physical RAID group, also indexes if even more physical groups of drives are a vailable
3 - Tune applications ( easy said ) but that is where most troubles come from. Use stored procedures whenever possible in the applications as it reduces network traffic.
4 - Look after the health of indexes and index statistics. run sp_updatestats frequently. Check and fix any index fragmentation regularly. http://www.sql-server-performance.com offers many useful insights
6 - Use Windows 2003 ... Win2K is not scalable or reliable as W2003.
7 - If the current HW supports 64-bit version then use Win2003 64-bit and SQL 64-bit
-
1. Undo the RAID setting...RAID brings a data storage overhead. I know it's useful, but it does have a capacity cost.
2. Investigate file compression as a possible solution to increasing storage.
3. File groups are only useful if they each can be placed on seperate drives/spindles...otherwise they will contend with other activity going through the same drive.
-
Buy some equipment off of eBay. The constraints don't say anything about adding USED hardware.
heh
Did your question have any context that suggested what type of capability you needed to increase by a factor of 10?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply