System Design Strategies and Performance

773
2
08-23-2017 09:23 AM
MarkYerington
Occasional Contributor III

All

Looking for some help on guidelines for sizing database server to handle load.  We have just configured a completely new server environment and have ran into issues with our database server.  I figured this may be our bottleneck, but we have multiple different server sites and have never seen the performance hit that we are seeing on this machine.

All of our servers are running on VMWare

Server Setup

2 GIS ArcGIS servers

64GB Ram on both

8 Core Processors

Windows Server 2012 R2

1 Web Server with Web adaptor for Load balancing

4 Core Machine

Windows Server 2012 R2

1 Database Server 

SQL Server 2014

64GB Ram

4 Core Processors

Windows Server 2012 R2

1 Geoevent Server

64 GB Ram

4 Core Processor

Windows Server 2012 R2

We were running into resource problems on the old machine, so we built this system to be very robust.  The database server is completely dedicated to GIS data and GIS Applications.  We are streaming vehicles through the GeoEvent server with 50 vehicles that update a feature service on the Database Server every 2 minutes.  Not all vehicles come in at the same time usually it is 6 to 10 vehicles at the same time.  We have all the Electric, Water and Communications datasets on the same database server.  We had them as dynamic, but changing them to cached did not eliminate much load on the server.  We are also updating a table with outage information on the Database Server every 2 minutes.  This table is used in a Join only returning matching features in our applications.

The server load seems to be from the Real Time data layers:

Vehicle Updates

Outage Information

We have multiple Operations Dashboards setup for our system control center to view real time outage and vehicle locations.  We also have some web app builder web applications setup that are viewing this same information.  It really seems as though our dedicated database server is inundated easily.

We had 10 to 12 users open the Operations dashboards and the server cores were maxed out within seconds of everyone trying to access the information.

My question is do we simply need more cores to operate a system like this or does anyone feel that VMWare is not optimized for SQL server.  It seems as though the database server is inundated rather quickly.  The old Server on one machine sql server 2008 with ArcGIS server and Sql Server on the same machine.  We knew that the bottleneck on that server was the RAM as we were limited to 32GB because of windows 2008, but it would start failing after more ArcGIS instances could not spin up and Javew.exe process started storing all the log errors and growing exponentially.  This server only had 4 cores and multiple different business system databases on it.  At least we were able to view the applications for a while.  The only new load is the vehicle locations.

Any advise or suggestions would be great.

0 Kudos
2 Replies
ScottFierro2
Regular Contributor

You can review my post at bottom of page 3 on this thread dated August 27. If that's not what fixes things for you then there is a slew of other potential solutions in the thread. We jumped from 2012 to 2016 and skipped 2014 SQL Server but we applied this change back on SQL 2008 R2 servers and it was night and day difference in performance.

https://community.esri.com/message/693484-re-poor-performance-with-sde-on-sql-server-2012?messageTar...

0 Kudos
MarkYerington
Occasional Contributor III

Thanks Scott we are having IT check on this.

Mark Yerington, GISP

MAGIC GIS Systems Analyst

3205 CEDAR ST | MUSCATINE, IA 52761

PH: 563.262.3316 | CELL: 563.260.4525

0 Kudos