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 |
|
palloquin
Starting Member
4 Posts |
 Posted - 2004-09-22 : 16:44:12
|
Hi,I'm quite new here, so if I ask questions that have been asked a million times, please point me in the right direction. Or if I seem to be totaly green, I'm sorry, I am!In the project I'm currently working on, alas, all DBA work landes on my desk.I'm a developer in nature, not a dba. I know my way around SQL server, and the OS, but never realy got down to learning the finer points of DBA work.Lately our server has been getting slower by day. I have no clue where to start to solve this.We're running on a dual xeon box, with 1.5GB RAM.Our main database has been growing quite rapidly and at the moment the datafiles are about 2.2GB in size.When keeping an eye on the performance monitor, the CPU does not seem to be the problem. Where rarely peak over 70% usage.Memory on the other hand is becoming a bigger issue. The SQL Server process uses all memory we allocate for it (at the moment 1.2GB).- What is the bottle neck here. Do I need to stuff our server with more memory than the size of the datafiles?!
- Could I configure SQL so that it does things *smarter* so that I might not need so much memory.
- Is 1.5GB RAM just not enough these days?
Where do I start, what do I need to read..?Thank you for all or any advise. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 16:56:15
|
| SQL Server is designed to be a memory hog, so the fact that SQL Server is using all of the memory doesn't mean that there is a bottleneck. You need to look at other PerfMon counters to determine if you have a memory bottleneck:http://www.sql-server-performance.com/performance_monitor_counters_memory.asp1.5GB of RAM does seem rather small, but it just depends on your needs.Tara |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-23 : 04:40:23
|
| Have you got a DBA there who does peer-review of SQL code the developers write?If not the likihood is that your SQL queries are not optimised to be as fast as possible.Are you using dynaic SQL or Stored Procedures? Again, if you are not using stored procedures your queries will be slower.However, unless some frequently referenced table is growing at an alarming rate I am not sure that any of this would point to things slowing down overall day-by-day (I would just expect certain things to have got slower over time)Do you have any .LDF files that are bigger than, say, 50MB - or worse, bigger than their corresponding .MDF files? (they are probably in MSSQL's DATA folder)Kristen |
|
|
|
palloquin
Starting Member
4 Posts |
Posted - 2004-09-23 : 05:59:39
|
| Hi Kristen, Hi Tara,Thank you for your responses.Logfiles are small. Our webserver talks to the DB directly, most data retreival is done directly, most inserts and updates are done via SP's.Actually things have been getting slower for quite some time. We are just now beginning to feel the results... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-23 : 10:35:00
|
| OK, that's helpful. Unlikely to be any of the things I was thinking of them.I'll scratch the grey matter a little harder ...Have a go with the performance counters that Tara linked to, and let us know which figures you get.Maybe something is querrying the DB frequently, and locking people out for a while - not long enough to generate error, but long enough to slow people down.Kristen |
|
|
|
palloquin
Starting Member
4 Posts |
Posted - 2004-09-23 : 10:46:06
|
| Hi again.I'll collect some of the prefMon data. somebody deceded to restart the SQL process this morning. Memory usage droped like a brick, and is slowly on the rise again. We are up to 900MB of memory usage after 7 hours or so. I'll keep an eye on the performance over the weekend and try to draw some conclusions after that. I'll post my findings!Thanks so far! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-23 : 11:19:04
|
| SQL will grab all available memory (but only as it finds it needs it), so don't worry about that figure.Unless you are running other things on that server ... are you?Kristen |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-09-23 : 14:07:08
|
| Use SQL Profiler, there is a wizaard in there (or a template or something) for long-running queries. This will highlight the queries which are causing problems.I would suspect indexes or poorly designed queries from the web application that worked fine initially when data volume was low, but get exponentially worse as data volumes increase. |
|
|
|
|
|
|
|
|
Performance, Memory, Where to start...