Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scalability Expand / Collapse
Author
Message
Posted Monday, October 16, 2006 11:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 21, 2014 1:34 PM
Points: 428, Visits: 692

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

 




Post #315681
Posted Monday, October 16, 2006 1:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 4:46 AM
Points: 409, Visits: 17

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. 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

 




Post #315701
Posted Tuesday, October 17, 2006 2:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 2:15 AM
Points: 278, Visits: 303

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.




Post #315802
Posted Tuesday, October 17, 2006 11:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:20 AM
Points: 966, Visits: 380

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?

Post #316007
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse