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 |
|
ColinD
Starting Member
40 Posts |
 Posted - 2007-05-02 : 10:17:19
|
| HiI'm using SQL Server 2000 with Small Business System 2003. I have a smallish database and an Access 2003 Front End, with ODBC links. The system has been running fine for about 2 months with 16 users. This weekend, I will be adding a further 30 users to the system.I've been doing some work on a new copy of the front end over the past day or two, and found that occassionaly, the system runs really slowly, taking a couple of minutes to open my front screen which normally takes a few seconds.The only solution I have found is to stop and start SQL Server Manager on the server, and then everything is fine. However this is clearly not an acceptable solution, because I'm doing it about twice a day.can anybody suggest why this might be, or how I might fix the problem?I have a maintenance plan in place, which runs overnight, Thanks for any help,Colin |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-02 : 10:33:16
|
| What's db size? How about server spec? Got enough memory on it? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-02 : 10:42:12
|
| "I have a maintenance plan in place"Is it doing Index Reorganisation?Do you have a high level of inserts, or deletes, in your application?Kristen |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-05-02 : 11:11:23
|
quote:
Originally posted by Kristen
"I have a maintenance plan in place"Is it doing Index Reorganisation?Do you have a high level of inserts, or deletes, in your application?
Yes I am doing a Index Reorganisation as part of the Maintenance Plan. I don't think I'm doing an excesive number of Inserts or deletes. I only have 16 users at present, and the application isn't that big. However, the app was originally designed to use an Access back end if that gives you any clues as to the problem. I've converted it to SQL Server this year.The spec of the machine might be an issue I guess. We are about to upgrade (i.e. double the memory).ThanksColin |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-05-02 : 19:23:15
|
| Here is what I would start with. Run perfmon and look at the following counters:1. SQLServer:Buffer Manager:Buffer 2. SQLServer:Memory Manager:Target Server Memory (KB)3. SQLServer:Memory Manager:Total Server Memory (KB)4. PhysicalDisk:Current Disk Queue Length (for all instances)5. Processor:% Processor Timemake sure that your buffer cache hit ratio is > 98%. The closer to 100% the better (for the most part). Make sure that Target Server Memory is not greater than Total Server Memory. Make sure that you don't have any prolonged disk queuing occurring on any disks (2 or more IOs queued up for several seconds at a time). Make sure Processor time is not at 80% or more for prolonged periods of time (10 seconds or more). Spikes of activity up to 100% are normal, sustained levels of CPU utilization or disks queuing are not normal and could be a problem.-ec |
|
|
|
ColinD
Starting Member
40 Posts |
Posted - 2007-05-17 : 05:47:40
|
| Hi,I ran perfmon last week and my Target server memory was just marginally above Total server memory. I wasn't too concerned though, because I knew that I had another 2GB of memory arriving this week. So last night, we installed the new memory, and everything seems fine and it recognised it ok, yet now Target server memory is way above the total (72 for target, 16 for total). Any ideas why? The system hasn't gone noticably slower, in fact it seems a little faster.Everything else you suggested seems fine. We haven't experienced the problem since last week, but I suspect that this is because I have stopped a lunch time SQL backup which was probably eating up memory. However, I really need to re-schedule the backup.Thanks Colin |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-17 : 09:43:16
|
| Did you set max memory in sql? Your sql edition can't use more than 2gb memory. And backup shouldn't take much memory, but you like to do it during off hours since it causes heavy disk i/o. |
|
|
|
|
|
|
|
|
Why does my SQL Server go very slow occasionally