September 24, 2009 at 8:33 am
The DB size is 500GB with single log file and data file; There is a table with 20 million rows which will be concurrently inserted and updated, selected by 800 users;
Pls give Hardware configuration tips/ SQL tunning tips..
Thanks for your help
September 24, 2009 at 9:04 am
It is a good question.:-)
Without seeing the database and a table it is difficult to be very specific, but the general guidelines is here.
1. Use table partitioning (vertical or horizontal) and localize the part of your table that rare used and could be considered as archived part of your table.
2. Place non-archived part of your table into the own Filegroup, and place it filegroup onto RAID 10 disk drive set.
3. Review the user queries and the execution plans to come up with the right indexes on the table.
September 24, 2009 at 9:10 am
have a look at MS best pratice for Storage I/O for some tips on tuning your hardware..
http://technet.microsoft.com/en-gb/library/cc966534.aspx"> http://technet.microsoft.com/en-gb/library/cc966534.aspx
September 24, 2009 at 12:19 pm
Get lots of memory, and fast I/O. Beyond that, you need to examine code carefully and test. I'm not sure we can give you general tips without more details, or without a direction for issues you are having.
September 24, 2009 at 8:37 pm
Thanks for your replies; But the replies does not help me much; Assume that the enviroment is going to be set up new; and there should not be any problems once implemented; in that situation how SQL Server should be set-up.
-- DB size will be >500GB
-- 5 Tables -- each with >20 million rows.
-- 800 users at peak, could INSERT, UPDATE, SELECT
-- 70% Write, 30% Read operations
I looking for answers like:
What RAM size?
How many Phy. CPU?
Processer speed? Any Speical Known Server like DELL ...or HP??...
Database File Groups?
One data file or many data file?
One Log file or many log file?
How many Volumes Drives?
Any special SP_Configure needed?
Page file size?
---Assume Recovery model is SIMPLE.
---Assueme DB has 90% of required indexes.
---Assume SQL Stements 90% tunned good.
---Assume No other log ship..replication..etc..
In what circumstance, the users will not complain that the server is SLOW; the response time is slow..when they run reports, updates..etc.
Pls help;
September 25, 2009 at 2:44 am
As far as machine specs I would say go for as large as your budget will allow.
And are you sure you want to be runnning your 500gb database with 800 users writing data in Simple mode?
September 25, 2009 at 6:42 am
yes it will be in simple mode;
September 25, 2009 at 8:38 am
I'm curious why SIMPLE mode. It's usually a bad idea.
As far as specs, it's hard to know unless you know what SQL is being run. One person on the box, running a large query, could grind it down.
What specs do you have now? What hardware is the application using. If there isn't one, then the SQL isn't tuned. And you'd have to have more of an idea on what the queries might do.
We could easily say by a quad core box, 8GB of RAM, etc., but it won't guarantee anything, and that's what we want you to know. If you experienced, you'll know this. If you are not, you will keep arguing, thinking there's some magical answer. There isn't. You don't spec on the number of users. You spec on the way the application works and the load.
The general advice:
- get as much RAM as you can afford.
- spread out across many disks
- separate backups from data on disks
- separate log files from data files
- separate out temp db if you can.
September 25, 2009 at 8:49 am
Joe-420121 (9/25/2009)
yes it will be in simple mode;
What is the reason for running in Simple mode, I would have thought that a database of this size and activity would require point in time restores.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply