February 5, 2009 at 8:54 am
Hello,
We're using SQL Server 2005 as a Microsoft Business Solutions Navision Data Server and we're experiencing several performance problems that we solve in 2 ways:
1) Rebooting the server. We've found that performance decreases over the time!! After two/three weeks, performance has decreased dramatically and, if we reboot the server, everything speeds up again. We didn't experience these sort of problems when we used Navision with the its own database server.
2) Running a maintenance plan weekly doing the following tasks: Integrity check -> Shrink database -> Reorganize indexes -> Rebuild indexes -> Update statistics -> Clean up history. We don't perform any backups because we use Navision backup's system to do it (it's much simpler to restore DB if we want to setup a test server).
Does anybody have any ideas why performance can degrade so much over the time and if there are any other ways to increase database performance.
Thanks a lot.
February 5, 2009 at 10:21 am
Try to put a cap on the Maximum Server Memory. It should be set about half of the total RAM you have for the system.
Hope it helps!
Brian
February 5, 2009 at 10:32 am
toni.sala (2/5/2009)
Hello,We're using SQL Server 2005 as a Microsoft Business Solutions Navision Data Server and we're experiencing several performance problems that we solve in 2 ways:
1) Rebooting the server. We've found that performance decreases over the time!! After two/three weeks, performance has decreased dramatically and, if we reboot the server, everything speeds up again. We didn't experience these sort of problems when we used Navision with the its own database server.
2) Running a maintenance plan weekly doing the following tasks: Integrity check -> Shrink database -> Reorganize indexes -> Rebuild indexes -> Update statistics -> Clean up history. We don't perform any backups because we use Navision backup's system to do it (it's much simpler to restore DB if we want to setup a test server).
Does anybody have any ideas why performance can degrade so much over the time and if there are any other ways to increase database performance.
Thanks a lot.
1) where is your Temp database file located ? this could be runnig out of room and when you restart the server this deletes the file, perhaps move this to a new disk.
2) It's not really recommended to Shrink your database weekly as this causes a lot of fragmentation and usually increases to the same size again, I would avoid doing this.. Also you shouldn't need to rebuild and re-organise the indexes do one or the other.
I don't know anything about Navision, but it would make me extremly nervous relying on another party for the database backups..
February 5, 2009 at 10:36 am
In addition to simply observing performance degrade, have you collected information from performance monitor to see if buffer cache hit ratio (just for one example) is maintaining over time or degrading with the rest of the system? Since a reboot fixes it, it sounds like you have a memory leak or something along those lines, but without more data it's hard to say.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2009 at 12:45 pm
It's not really recommended to Shrink your database weekly as this causes a lot of fragmentation and usually increases to the same size again, I would avoid doing this.. Also you shouldn't need to rebuild and re-organise the indexes do one or the other.
steve is absolutely right...first pls avoid this if u can...
also u can check from task manager or process manager(utility from microsoft) dat its sql server dat is actualy causing the performance issue...and if sql server is causing the issues...check wheather it's a cpu or a memory issue..u can use these counter
Server Buffer Manager Object: Cache Size (pages). (memory)
Server Buffer Manager Object: Buffer Cache Hit Ratio.
SQLServer: SQL Statistics: Batch Requests/Sec counter (cpu)
Memory: Page Faults/sec
also Consider watching these two counters: SQLServer:Memory Manager: Total Server Memory (KB) and SQLServer:Memory Manager: Target Server Memory (KB). if the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply