memory and sql server 2000

[fredphred]

Hello,

I've capped the memory usage on my SQL Server at around 512MB. When I startup my databases Sql Server initially uses only 125MB. Nice and steady.

Then as soon as I open Enterprise Manager on it.. my server slows.. memory usages spikes to ~530MB and enterprise manager takes 3 mins to become usable.

Is this normal or did somebody screw up the install/config?

Sql Server 2000 - 8.00.760
1.2 GHZ CPU
1 GB RAM

Thanks in advance.

 

[JayKusch]

Well I would suggest running Profiler trace and start it. Capture TSQL statements,
stored procedure events. Open Enterprise Manager and open the database list.. Stop
Profiler and look at the start and end times for each of the statements. If there is
one statement that seems to be taking a long time , you can test it by running it in
Query Analyser and see if it takes a long time returning the results as well . By
doing this you will atleast narrow down the problem statement and then you can
troubleshoot that further

Also
a) Check to see if ODBC tracing is on. If yes, then turn it off
b) Is it always slow from every client or is it specific to only some clients
c) Uncheck the AutoClose option for your databases. The autoclose option should not
be used for databases accessed by an application that repeatedly makes and breaks
connections to SQL Server. The overhead of closing and reopening the database
between each connection will impair performance


Thanks

J. Kusch