1

I have been troubleshooting an intermittent server performance issue for too long and I am running out of ideas. I am looking for any suggestions as to how I might be able to identify the cause of the issue.

We (my team and I) developed a client/server Windows Forms application using a SQL Server database for a client some years ago. The client recently started experiencing some performance issues and decided to upgrade their infrastructure. They migrated from a single physical SBS machine to a virtual environment with multiple VMs. We successfully migrated and application and SQL bits to the new environment. The client then requested application updates to fix some memory leaks and other performance issues / bugs that they had been running with for years. We made the updates and the system bench-marked well in our environment. We then deployed to their new production environment and the system seemed to run well.

A day or two after the deployment we received complaints about the system hanging or lagging when load/saving form data or generating reports. We connected with the client remotely and confirmed the issue. We analyzed the client environment and checked for possible memory leaks and other issues that might cause the symptoms. We found none. We then realized that the performance issue was affecting multiple machines on the network and must be environmental. The client then had their hardware support techs troubleshoot potential hardware/network configuration for a source. They found none.

During our rounds of troubleshooting with the client we stumbled onto ways of correcting the performance problem when it arises (which seems random). A server reboot fixes the issue but that is not an acceptable fix.

Another way, and the reason why I'm posting this, is when the client notices the performance degrading, they can open the "legacy" version of the application (which is still available on some client machines) and performance is restored. Existing client application instance restarts are not needed.

The system performs well between incidents and the issue seems to occur every 2 to 3 days on average but has run incident-free for as long as a week and has also has multiple incidents in a single day (one in the morning and then one in the afternoon).

We were thinking that the issue might be a SQL Server problem. So I've been profiling, saving traces and have also been monitoring SQL performance counters to look for clues. I'm no SQL performance expert and so I might not be looking into proper counters but the SQL Server does not seem to be pushed very hard. There are no persistent spikes in CPU, Memory, Batches/Second, Transactions/Second, Compilations/Second, Re-Compilations/Second and the paging and cache counters are generally static.

The application may have 10 to 20 active instances running at a time. The application was not originally written with the most efficient data retrieval practices but the load produced is nothing the server can't handle.

I have also been monitoring the Windows Event logs for errors and warning that might shine some light on the problem but haven't seen anything that is thrown just before or during an incident that points to the problem.

Another strange observation we found was that the application performs without degradation when executed directly on the server regardless the overall system performance. I have run the application directly on the server when other machines were experiencing the problem and had no slowness or lag.

Sorry for the book. I am going to keep digging for clues but any suggestions would be greatly appreciated.

Server: Windows Server 2012 R2 (VM with plenty of resources allocated) SQL: SQL Server 2014 Standard Clients: Mixed but Mostly Windows 7 Professional

  • "A Server reboot corrects the issue" What server? What servers do you have. You mention a DB server, but nothing else. Where are clients? Same network? – Sam Mar 17 '16 at 20:21
  • The Windows Server 2012 R2 machine is the SQL Server. The application assemblies run on the client and connect to SQL via ADO.net. All machines are on the same network. – Dusty Lau Mar 17 '16 at 20:43
  • Did you ever resolve this issue? I have a similar problem minus the local instance running fine. – Rex Bloom Aug 23 '16 at 2:07
1

As far as the database goes, I'd start logging activity to a table, like so. You'd need to adjust the stored proc to run for a longer duration so data keeps being logged (SET @numberOfRuns = 10), or discard that check altogether.

There are tools to make server performance log analysis easier. Here is one. Here is the authors blog.

You might try using a network monitor to see what is happening on a client when the issue happening. Also take a look at the NIC traffic counters in perfmon on the server. Check out tcp sessions when the problem is happening with netstat perhaps. I know little about networking, so this may be a case of the blind leading the blind :)

0

Did you ever figure this out? What kind of connection string does your application use? If it runs fine on the server but not on clients keep in mind the network connection. i.e. if your connection string uses datasource=computername then on the server it would use the loop back and on the clients, it would use name resolution and an IP address. Maybe try using the IP in the connection string instead of a DNS name to eliminate the DNS lookup.

  • We ended up setting up the application in Terminal Services as a RemoteApp. All the users are now running on the server with no problems. We never were able to isolate the cause of the problem. – Dusty Lau May 25 '17 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.