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 |
|
83dons
Starting Member
3 Posts |
 Posted - 2010-08-12 : 05:17:37
|
| HiI work for the NHS and we have a server running Server 2005 R2 Standard x64 Edition on a Windows NT 5.2 (3790) PC. It currently has about 3 databases running on it and nothing else. Our clinical system's diary is taking forever to load up and change between dates, add appointments etc and our system support team have said it is nothing to do with the query times but is more likely a memory problem on the server (user query times in the diary are about 20secs and the query time using sql profiler was only about 2 secs).On looking into the Task Manager on the Server the following settings were there:CPU Usage fluctautes between 1 and 90% depending on usageTotal Physical Memory is 3931MBAvailable memory 418MB (Has been as low as 171MB at peak)System Cache 561MBPF Usage is around 3.11GBSome additional settings are:No AWEMin Server memory 0MBMax Server memory 2147483647MBIndex Creation memory 0KBMin memory for query 1024KBDoes any of this initially point towards why certain queries in our system should be painfully slow? Our 3rd party system guy thought available memory was too low and for diary queries we were probably using PF memory which is much slower. He thought available memory needed to be about 1-2GB.Any suggestions as I am a bit lost with this? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-12 : 05:23:32
|
| Has performance deteriorated compared to when first set up? (if the data volume has grown significantly then this may not apply)If so it may point to Housekeeping Maintenance )rebuild indexes / update statistics) not being done regularlyHas performance deteriorated since last reboot? does performance improve again shortly (albeit probably not IMMEDIATELY) after reboot? i.e. once Cache has had time to recreate for common queries, but before memory gets fullthat would suggest to me that the memory limit setting is not low enough and SQL is taking too much - SQL will take memory over time, rather than all-at-once on startup. Setting SQL to use a max leaving 2GB for the O/S is a starting point. There are more sophisticated recommendations based on number of processors etc. And allowing for anything else on the machine (although you say yours is a dedicated machine, so probably nothing else to allow for) |
 |
|
|
83dons
Starting Member
3 Posts |
Posted - 2010-08-13 : 06:48:59
|
| No the performance has been equally slow since the server was set up. It is only around diary queries, other aspects of the application are ok. I am guessing that the diary ones require more memory than the others and maybe thes server struggles to cope with them more perhaps using page file memory which is slower?We get server rebooted every weekend and it doesn reall ymake much difference to the speed.The max server memory looks to me very high and much higher than what is available in the settings but with the oprating system I think we are limited to 4GB max anyway. I think SQLsrvr.exe was using about 2.7GB or something last time looked at task manager.Our application runs off GUIs installed on each PC which link to the server using ODBC SQL Server connections. All PCs experience the same slowness around diary queries. Our 3rd paty support team have looked at the SQL Profiler logs and say the query times for the diary are only 2 secs whereas the user experiences 20secs so they though looking at the above that available memory was too low.Is it worth switching AWE on?is the best course to upgrade the operating system then add in more RAM and let SQL Server dynamically allocate more memory to avaialble? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-13 : 08:11:28
|
| Have you considered optimising the queries? Will likely have a much bigger impact than adding hardware. Have you looked at profiler and verified what the support team said? Have you looked at the wait types to see what's prevalent?--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Speeding Up System