Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-17 : 08:51:50
|
| Greg writes "I have a database (SQL 2000 standard)- approx 50 GB in size (growing...) about 50 users work in this database 24/7 - I have 15K RPM HDs RAID 5 - and dual Xeon 2.8 and 4 GB RAM - dual gigabit ethernetIts slow - HDs hit hard (blinking constantly) What am I missing? Would different version of SQL help?" |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-17 : 12:20:36
|
| Well, there are tons of things to look at, but i would recommend we start with the following. Please respond back with the answers and we can continue.Because you are running SQL 2K standard, you will only be able to allocate 2GB of RAM to your SQL Server. This means that you won't be able to throw RAM at this thing to tune it.Chances are you have some queries that are inefficent which are using a large portion of your buffer cache. To find out, we can start with the following:1. Run perfmon and look at the following counters: a. physical disk - current disk queue for all drives b. MSSQL:Buffer Manager - Buffer cache hit ratio c. MSSQL:Memory manager - target and total server memory2. What is your typical CPU utilization? Do you have hyperthreaded CPUs?For a healthy system, disk queuing should not be at any sustained level on any drive. Occasional spikes are expected, but you should not see a sustained level of 10 (for example) in your disk queueing stat for the drive where you have your SQL datafiles.Buffer cache hit ratio should be as close to 100 as possible. With an OLTP type environment you should see values above 98%. For a OLAP or DSS type environment the buffer cache hit ratios will be lower.The Target and Total system memory should be the same or the target value should be lower than the total. If target is higher then SQL is starved for memory.Please respond with the typical values for those counters.Also, are you running anything on this box other than SQL Server? What service pack of SQL are you running? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-17 : 13:46:11
|
| Are the 50 users needing to access all the data, or is (for example) some of it "last years data" which is only needed occasionally?If so federated horizontal partitions might be able to slip your 50GB database dramatically by "outsourcing" all the non-current data to some other server (or possibly even just another database on the same server), leaving the main server/database to chew on a much smaller dataset.Kristen |
|
|
|
|
|
|
slowness issue