Monitoring SQL Server/SDE Memory Usage

3558
9
Jump to solution
01-04-2016 06:57 AM
MikeSchonlau
Occasional Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
ChrisSmith7
Frequent Contributor

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

View solution in original post

0 Kudos
9 Replies
ChrisSmith7
Frequent Contributor

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

0 Kudos
MikeSchonlau
Occasional Contributor III

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.

0 Kudos
JakeSkinner
Esri Esteemed Contributor

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

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

0 Kudos
MikeSchonlau
Occasional Contributor III

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?

0 Kudos
ChadKopplin
Occasional Contributor III

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. 

0 Kudos
MikeSchonlau
Occasional Contributor III

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

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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

0 Kudos
MikeSchonlau
Occasional Contributor III

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?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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

0 Kudos