Page life expectancy is at around 15k+ during normal use. That didn't increase performance in the slightest. I gave the SQL Server account the right to use Instant File Initialization.
![code on time sql 2016 code on time sql 2016](https://voiceofthedba.files.wordpress.com/2018/01/2018-01-27-09_28_29-sqlquery1-sql-local_sql2016-northwind-plato_steve-66_-microsoft-sql-se.jpg)
When I stress test, I get around max 5GB/s.īuffer cache size is at 60GB for the DB of our ERP system, 20GB for our financing software, 1GB for quality assurance software, 3GB for document archiving system. The IO of the db server is at max 20MB/s write speed during normal usage with the application (avg is 7-9MB/s). Latency of network connection is around 1-3ms. Most of the time CPU is around 26% on all machines (Client, appserver, db server). Neither of those are even remotely using their resources at maximum capacity. This could either mean a network issue, or performance issue with the application server or client. Yet people have to wait forever just to save anything in the application.Īccording to Paul Randal's " Tell me where it hurts" query, 50% of all wait events are ASYNC_NETWORK_IO. Only thing throttling is the client, because he can't answer fast enough.īut when there are barely any users, the SQL Server is barely doing anything. When I stress test the SQL Server with Microsoft's ostress using either many small queries or a big query, I get max performance. The users are performing single screen access via a C++ based ERP application. Program files, Log files, database files and tempdb are on separate partitions of the server.128 GB RAM (110GB Max memory for SQL Server).
![code on time sql 2016 code on time sql 2016](https://i.ytimg.com/vi/lbgv0-PrwxE/maxresdefault.jpg)
People testing the applications are reporting performance issues, though. Those applications are all on separate virtual servers. It contains 4 databases, each for a different application. We have a single instance of SQL Server 2016 SP1 running in a VMware virtual machine.