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 |
|
pphillips001
Starting Member
23 Posts |
 Posted - 2010-03-15 : 07:00:48
|
| Hi,I've found myself a newly appointed DBA of a 300Gb database running windows based client software with around 400 users. SQL 2005 on SQL Server 2003 (running 16 processors) Raid 5 8Gb RAM 1Tb storage, it is mirrored and has a separate replicated reporting server .It was set up way before I joined and I gather was just dumped into place. It is OLTP environment and is getting slower.The question is - will adding more ram noticeably speed things up? Or should I roll my sleeves up and go through all of the indexing with a fine toothed comb?Any help would be appreciated.Regards,Paul===========================There is no reality - only perspective. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-15 : 07:12:49
|
| With a 300GB database and only 8GB RAM my default answer would be "yes". You can put up a Profiler trace and look for "Buffer Cache Hit Ratio"...it should be pretty close to 100 ( > 97) at least trough a 24-hour period. You can look at this article, pretty old but most of the principles are still valid: http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspxThe thing that has given me the greatest performance increase though is to move "old" data, either by using partitioning or just to split transaction tables into a "current-table" and an "historic-table". At least the OLTP systems I have worked on has a bunch of old data in the transaction tables just sitting there for reporting purposes and because "disk is cheap". Cleaning up every once in a while has a tremendous impact...- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
pphillips001
Starting Member
23 Posts |
Posted - 2010-03-15 : 07:19:50
|
| Thanks for the speedy response!That article is great. The DB does have an audit table (making up 65% of the entire db!) of which I am in the process of slicing into chunks to store elsewhere. Hearing you've had success with a similar process is certainly good to hear.The Buffer Cache Hit Ratio has indeed been maxing out at 100% every time I've looked at it.Cheers!===========================There is no reality - only perspective. |
|
|
|
DaleTurley
Yak Posting Veteran
76 Posts |
Posted - 2010-03-15 : 09:52:43
|
| Raid 5 for database files probably isn't ideal either...Try this tool and see what read and write speeds you get from that disk[url]http://www.iometer.org/[/url] |
|
|
|
|
|
|
A very newbie question