|
|
| larkspur 2004-03-25, 11:07 am |
| Everyday at 4 pm the SQL process on my server just grapss a butt load of resources and just brings the applications to a crawl.
A little tech info:
We do a dump every hour.
We have 30 pc's hitting this DB.
Any ideas? Not sure if more info is needed, let me knonw?
TIA | |
| necrophantasm 2004-03-29, 7:20 am |
| How much physical memory do you have in the machine? Is the disk subsystem a RAID array? Is it a multiprocessor system or a cluster environment?
SQL Server is a huge resource pig, and honestly I wouldn't run a SQL server that has 30 concurrent connections with any less than 2gb of physical memory (I've had to dump 3gb with 30 before). Do you have database maintenance plans set up? If so, are you using the full recovery mode with transaction log backups and consistency checks nightly? With that many users (and probably data in and out like crazy) the transaction log may grow well over a gigabyte. When it gets up to 10 or 20gb users can actually get locked out due to lack of resources and your application log may actually log "out of memory" errors. The only MS product I have worked with that is worse is Exchange 2000 when it comes to that stuff. SQL at least can be controlled. If you are unsure what kind of load the machine is under, I'd highly recommend using the SQL profiler utility to run a trace log on available physical memory, disk queue length, pages per second, and network utilitization per second. Once you have run this you may have a better idea of what the issue is. If you can give me some details to the system I can give you a better recommendation, but that should get you started. | |
| larkspur 2004-03-31, 10:59 am |
| Server specs:
dual 800 intel processors
1.5 gig of ram
raid 5
i will run the profiler today. | |
| necrophantasm 2004-03-31, 5:59 pm |
| I'd add more memory if possible.. if it is dumping, what are the stack dump messages displayed? | |
| ClintonN 2004-04-08, 6:36 pm |
| I'm no DBA by no means but you can allocate how much memory SQL takes up by changing the dynamically configure SQL memory to a fixed amount. I used to run into similar problems with our ERP system which runs with a SQL backend until I fixed the memory to 700mb on a dual p3 1.2gig 1 gig of memory on our server. But this was just a member server that was just hosting applications tied to our ERP. HTH | |
| larkspur 2004-04-11, 2:03 pm |
| What tool did you use to determine the problem? | |
|
|
| larkspur 2004-04-19, 11:07 pm |
| Sorry it took so long but things were running well for awhile. Anyway found the problem. A DBA is pushing a replication at the same time. When the there is a large data consolidation SQL graps a butt load of resources(no surprise there) and brings it down. Thanks for your input. I used perf mon to vaildate the answer. |
|
|
|