topic Re: Monitoring SQL Server/SDE Memory Usage in Geodatabase Questions

Monitoring SQL Server/SDE Memory Usage

MikeSchonlau — Mon, 04 Jan 2016 14:57:30 GMT

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

Re: Monitoring SQL Server/SDE Memory Usage

ChrisSmith7 — Mon, 04 Jan 2016 15:18:57 GMT

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

Re: Monitoring SQL Server/SDE Memory Usage

JakeSkinner — Mon, 04 Jan 2016 15:39:46 GMT

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

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

Re: Monitoring SQL Server/SDE Memory Usage

ChadKopplin — Mon, 04 Jan 2016 15:40:24 GMT

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.

Re: Monitoring SQL Server/SDE Memory Usage

MikeSchonlau — Mon, 04 Jan 2016 16:07:40 GMT

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

Re: Monitoring SQL Server/SDE Memory Usage

MikeSchonlau — Mon, 04 Jan 2016 16:09:22 GMT

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?

Re: Monitoring SQL Server/SDE Memory Usage

VinceAngelo — Tue, 05 Jan 2016 17:15:57 GMT

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

Re: Monitoring SQL Server/SDE Memory Usage

MikeSchonlau — Tue, 05 Jan 2016 17:32:03 GMT

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.

Re: Monitoring SQL Server/SDE Memory Usage

MikeSchonlau — Tue, 05 Jan 2016 17:33:50 GMT

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?

Re: Monitoring SQL Server/SDE Memory Usage

VinceAngelo — Tue, 05 Jan 2016 17:59:16 GMT

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