Monitoring SQL Server/SDE Memory Usage

[MikeSchonlau]

Can anyone suggest some sql queries/scripts that could help me monitor SQL Server's system memory usage on an active enterprise geodatabase instance? This SQL instance is primarily supporting ArcGIS Server REST services. There are very few Desktop connections. I'm hoping to really examine my memory usage so I can right-size my database server going forward. Thanks

[ChrisSmith7 · Accepted solution]

Have you looked into any of the monitoring tools developed by Red Gate or SolarWinds? There are also some tools provided by SQL Server - Performance Monitoring and Tuning Tools

[MikeSchonlau]

Thanks, Chris. I'm trying out the SolarWinds DPA trial. Looks useful. I also stumbled across the DBCC MEMORYSTATUS command for SQL. Also provides some good info.

[JakeSkinner]

I would also recommend taking a look at the System Monitor tools:

http://www.arcgis.com/home/item.html?id=848f48b0f88e4de7a036377197453efe

[MikeSchonlau]

Thanks, Jake. I tried using the System Monitor tool a couple of years ago. I found it difficult to use and seemed to focus more on ArcGIS Server performance than on database performance. Have their been improvements in the last year or so?

[ChadKopplin]

Since this server is primarily used for serving rest services, you can go into the Task Manager, go to the processes and see the number of SOC services that are running.  By default ArcGIS server will make 2 available/service unless to change the settings.  We were consuming 7 gb of RAM out of 8 just to have these accounts available and ready when the service would be used.  We have changed to have a minimum of 0 in the service settings, now the account is created when the service is actually accessed, not we have 6 -7 gb available at any time. 

[MikeSchonlau]

Thanks, Chad. I'm actually referring to memory usage on the database server - not the ArcGIS Server.

[VinceAngelo]

If you're not using an ArcSDE Application Server (and you shouldn't be using one with ArcGIS Server at this point), then ArcSDE doesn't exist to utilize memory.  Generic database tools, provided by the RDBMS vendors, should be the only tools necessary to evaluate memory usage on the database server.

- V

[MikeSchonlau]

Thanks, Vince. Haven't used SDE app server in many years. I am looking for just what you describe - database tools that help me analyze specifically how SQL is using memory. Bottom line is do I need 8 or 16 Gb on my new database server?

[VinceAngelo]

While not "off the farm" as a geodatabase question, this is certainly on the "lower 40", mostly because the answer differs by RDBMS.  A google returns a number of native and 3rd-party memory logging tools for Microsoft SQL-Server; a Microsoft forum might be a better place to find those with strong opinions on their merits.

I nearly upgraded my 8Gb RAM home PC to 16Gb last night, and the last database server I purchased had 256Gb RAM, so I'd think that in a commercial GIS setting, the question should be "Do I need 32Gb or 64Gb on my new database server?"

- V