SQL Server Performance Forum – Threads Archive
hardware for large database
Hello, I have a large production database that will have roughly 3.5 million records in the carton table, and probably 12 million in another related table. I need to get answers from the carton table extremely fast. Preferrable under 0.2 seconds. There will be multiple transactions hitting this database every second. This db will be isolated on a server, but i’m not sure what type of hardware requirements would be optimal. We usually use dell products running windows server 2003. Could someone help me out with a desired configuration. Thanks in advance, BenYou haven’t given us enough information. Those tables might not be that big, depending on how wide they are. If they are properly indexed, you should be able to get responses within the desired time. Where you are going to run into trouble is if you’re updating or deleting data you are trying to read from. You can then get into all kinds of concurrency issues. How big will the overall database be in size?
How many transactions are you looking at per second?
How many inserts/updates/deletes/selects roughly in percents? Is this going to be on a SAN, external array, etc?
What OS and version of SQL Server are you going to have available? The two questions above can alter the final spec fairly significantly.
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thank you. I will do my best to clarify. The database is currently 8.5 GB. Our customer plans on growing from 40 stores to 70 stores within the next couple years, so volume on the db might almost double. The table that i need a speedy response from has roughly 2million records and should grow to about 3.5 million. This is by far the widest table in the db at about 20 columns wide. I would say about 10 to 15 transactions/sec peak. Inserts will come in bursts every 2 minutes there will be a file (200 to 1000 records) to process. 80% inserts 20% updates.
Updates will be less frequent, maybe one a second.
Deletes usually only happen on down time (right around midnight), and then it will be a mass deletion of all the cartons not needed in the DB any longer. Most of the 10 to 15 transactions/sec will be reads, so as far as percents, I would guess: 80% reads
8% inserts
11% updates
1% deletes I’m not real familiar with SAN’s. Currently we have a Dell PowerEdge Server with RAID 5, but we are open to suggestions of something faster. I know that RAID 5 isn’t usually the best for this type of stuff. We’ll be using Windows Server 2003 along with MS SQL Server 2000. Thank you very much, Ben
At present on which DELL PowerEdge model your database server is running ..? From DELL PowerMatch sizing tools are available to assist you with managing and sizing PowerEdge and PowerVault product solutions for your application environment. The sizing information and algorithms have been developed using testing and performance data on PowerEdge and PowerVault products. http://www1.us.dell.com/content/topics/global.aspx/alliances/en/sizing?c=us&cs=555&l=en&s=biz you can download installer and find out which DELL model will suit your future requirments. Another, try to replace RAID 5 with RAID 10 with 15K disk, this will surely help. Deepak Kumar –An eye for an eye and everyone shall be blind
the bigger question is: will this server be clustered?
while it is quite possible to cluster servers on SCSI disks,
most people i have come across prefer the SAN for clustering,
otherwise, there is not a lot performance difference between SCSI and SAN, unless the test data size sits inside the SAN cache but not the SCSI/RAID controller cache.
]]>