| Author |
Topic |
|
dre3
Starting Member
14 Posts |
 Posted - 2008-06-26 : 17:30:00
|
| I have inherited a server that is used exclusively for runnng SQL Server jobs, and the machine bogs down over a time period ranging from a few days to a few hours. There is heavy traffic against the server from applications and ad hoc queries. The machine will stop responding and then the only solution is to reboot which clears the buffers and the machine will begin to run normally again.The server is running Microsoft Server 2003, has all the service packs for both the OS and SQL Server 2005, all the virus software is up to date, and the firmware was just updated with no apparent increase in performance. There is only one common theme I have been able to discern. When the machine begins to bog down, the performance counter shows that the disk queue length maxes out and remains there while there is no page activity at all, and the processor is basically idling at about 2% to 5%. This server has 4 dual core processors running at 3.2 Ghz, 8 GB of memory, ample free disk space, and a ganged NIC configuration that yields 1GB I/O.Does anyone have any ideas? Even our onsite Microsoft guy hides from me. Maybe someone has experienced a similar problem. I would appreciate any suggestions at this point. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 18:27:31
|
| Is this 64-bit SQL server Standard Edition? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-26 : 19:04:37
|
| We had this issue with Reporting Server and after we applied patches the issue was solved for paging out memory? |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-27 : 15:08:07
|
| Check the MAX Memory settings in SQL server (sp_configure) or SSMS.Maninder |
|
|
|
dre3
Starting Member
14 Posts |
Posted - 2008-06-27 : 15:26:28
|
| Thanks for the input. The version is Enterprise 32 bit, and the memory settings appear to be normal. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-28 : 00:16:32
|
| Then how often do you rebuild index/update statistics? |
|
|
|
dre3
Starting Member
14 Posts |
Posted - 2008-06-30 : 09:06:54
|
| For the heavily used databases, monthly. There are about 300 databases on this server, but 10 of them account for 99% of the server traffic. Tuning and index rebuilding are accomplished on an ad hoc basis for the other databases. A complicating factor is that this server also heavily utilizes a data warehouse contained on a remote AS400 server, over which I have no control. |
|
|
|
maninder
Posting Yak Master
100 Posts |
Posted - 2008-06-30 : 11:20:38
|
| Many Factors to your issues and there can be many solutions to try.The Way you are pulling the Data from the warehouse is also a factor, in the server issue.Network can play a big Role here... Check you NIC settings/speed etc.Also.. If there are HEAP Tables in your most Utilized Databases, that can further Esclate your Issues... SO.. you/we will have to take a one issue one solutions at a time approach.(I Hope)Maninder |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-30 : 23:13:01
|
| >> For the heavily used databases, monthly. Should be more often than monthly. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2008-07-02 : 11:39:44
|
| rebuilding the indexes should only be done when needed...try to benchmark, monitor frequently fragmented indexes and schedule appropriately...we have a couple of tables that are heavily fragmented in 8 hours...so I schedule the rebuild daily during off peak hoursasumming a magic number is not advisable... different environment... different solutions--------------------keeping it simple... |
|
|
|
mdubey
Posting Yak Master
133 Posts |
Posted - 2008-07-02 : 11:57:01
|
| Try to find out top 10 selective tables from the DB and reindex on them. If possible create seprate filegroup for the huge tables. It may resolve your issue.ManojMCP, MCTS |
|
|
|
|
Server Slowdown