According to NPM requirements for SL2000, 4GB RAM for the SQL server should be enough. Which is why we are quite shocked to see these:
This has a direct impact to the performance of the web client. Loading pages takes a long time, especially for the NTA module.
We are monitoring only 603 elements, 23 nodes and 580 interfaces.
Any possible way to overcome this problem? Thanks.
I would recommend to open a support ticket to look into this issue.
This is normal for MSSQL - it will use all available memory for caching rather than having to hit the disk all the time.
Have you tried limiting memory?
The slowness of the web UI might be a different problem. I notice that you've got Microsoft's AV running - have you told it to ignore the SQL folders for realtime scanning? http://support.microsoft.com/kb/309422
Just to confirm the above post - SQL Server will use as much memory as it can for caching. If you're worried about SQL performance then monitor the performance counters (I reference http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64044 to remind me what counters to watch). If you have APM (or try the eval) you could also apply the SQL Server template for the stats.
What specific web pages are slow? Graphs? What retention settings do you have for storing data, and what is the default graphing period when you view graphs? You may also want to check for fragmented tables/indexes on the database - have a look here http://sqlserverperformance.wordpress.com/2011/06/30/july-2011-version-of-sql-server-2008-diagnostic-queries/ and download the diagnostic queries, a lot of them will point out problems for you.
Andy.
Thanks, smargh! I will try that and hopefully it helps.
Generally all NTA pages are quite slow, even when accessing the webclient on the server itself which has a 1gbps link to the sql server. However the most pressing matter is the loading of the graphs. For 24 hours plotting it will take a few minutes and half the time the graphs failed to load or stuck at loading. I will try APM eval and the links you posted, thanks!
Attached below is the stall time table from the SQL query I did on the Microsoft SWL 2008 R2 server for SolarWinds. I don't really know how to analyse this, can anyone help me with it? Thanks!
[View:/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.10.32.64/SQL-Server-2008-Diagnostic-Results-_2800_July-2011_2900_.xls]