Please fill the form below up and receive immediately an automated email with the instructions to download and install the trial Virtual Appliance!

This Virtual Appliance will be valid for 15 days from the day the download link is sent.

About you

7 easy steps to fix the performance of database driven application

Here is a short story about how PerformanceVision helped a major Healthcare organization accelerate the diagnostic of slowdowns reported since many months by end users and help initiate the code optimization by their software vendor in no more than a few hours.

CUTTING MTTR (MEAN TIME TO RESOLVE) FOR PERFORMANCE DEGRADATIONS FROM WEEKS TO HOURS

CLASSICAL SOFTWARE PACKAGE GENERATING USER COMPLAINTS FOR SLOW RESPONSE TIMES

This group of hospitals use a specific application to handle the entrance / exits of patients; this application is based on a thick client hosted on a Terminal server, connecting to a Microsoft SQL database server to retrieve and update the patient data. This application is provided by an external software vendor.

Simplified Application Architecture
Simplified Database driven Application Architecture

FACING THE LIMITS OF TRADITIONAL TROUBLESHOOTING METHODS

Traditional monitoring and troubleshooting tools overloaded by data flows
Traditional monitoring and troubleshooting tools overloaded by data flows

The infrastructure team got repeated reports from the helpdesk team indicating the occurrence of user complaints regarding response time slowdown of the application. The team conducted multiple troubleshooting attempts to locate the existence of the degradation and its root cause.

  • SNMP monitoring revealed no loss of availability or resource outage
  • Database system monitoring showed normal values
  • Wireshark analysis failed due to the intermittence of degradations (never at the right time at the right place) and the little resources available compared to the volume of data which should be analysed.

They tested different configurations on the terminal servers as well as on the database server. They tried to compare the behaviour accessing the database server through the TSE infrastructure or directly from the client to the database server; they also opened multiple tickets at the vendor’s helpdesk and got little response due to the lack of concrete facts provided. After a few months, using their existing diagnostic and monitoring tools (mostly composed of SNMP tools, Wireshark and system based trackers), they conducted a trial of PerformanceVision.

ITOA to help fix performance degradations

 


PerformanceVision runs regular webinar to accelerate performance troubleshooting using network traffic analysis for all your users and applications at a time. In 30 minutes, you will learn clear and easy steps to identify if you face performance degradations, which users, applications and transactions are impacted and what is the root cause of the slowdown.  

  Webinar Accelerate performance troubleshooting


 

7 EASY STEPS TO IDENTIFY THE PAIN POINT FOR THIS APPLICATION

1. Understand how the application is structured

(see the chart hereabove) 

2. Define and implement a traffic capture

Use a SPAN port of the traffic sent from/to the TSE and the MS-SQL servers – more information about this.

3. Install PerformanceVision’s virtual appliance

Install on a server and connect it to the SPAN port (see this tutorial to install the virtual appliance in 5 minutes and this link to download the evaluation appliance). Let PerformanceVision capture traffic and analyse application transactions (time for a coffee or a quick lunch)

4. Check the network layer

Network Performance Chart
Network Performance Chart

PerformanceVision here shows no network latency variation and no sign of significant packet loss of TCP retransmission. 

5. Check the performance of the TSE farm

Terminal Server End User Response Times
Terminal Server End User Response Times

End user response times for the Terminal server layer are stable and are not impacted by any network or server processing slowdown. 

6. Check the overall Database Transaction Performance for all MS-SQL users

Overall MS SQL Database Transaction Performance
Overall MS SQL Database Transaction Performance

The overall database transaction performance shows fast sever processing and a very limited number of SQL errors. 
 

Database transaction performance trend
Database transaction performance trend

The trend chart show no variation based on time: database performance looks stable overall.

7. Narrow down to the performance of individual SQL Transactions looking for: 

A - QUERIES WITH SLOW SERVER PROCESSING 

Transactions with the longest processing times
Transactions with the longest processing times

Show the transactions with the slowest server response times

B- QUERIES WITH ABNORMAL FREQUENCY
 

Most frequent queries
Transactions with the highest number of occurencies

Some queries may happen too often for no reason (or for poor design); they may have a negative impact on the load and performance of your SQL servers. 



C- QUERIES WITH SLOW DATA TRANSFER

SQL queries generating long response transfer
SQL queries generating long response transfer

This screen shows the queries whose data transfer are the slowest: slow transfers are the combination of responses with large volume of data and degraded transfer conditions (retranmission, TCP errors - coming either from the network or the systems - client or server). 

D- QUERIES SENDING ABNORMAL VOLUME OF DATA 

Queries sending abnormal volume of data


In our case, nor the processing by the database server neither the transfer of data on the network were the cause of the slowdown. The actual root cause was poorly optimized SQL queries sending excessive data sets, which thick clients had difficulties to process within a reasonable time.



CONCLUSION: ENSURING BUSINESS USERS CAN MAKE THE MOST OUT OF THEIR APPLICATIONS

Applications are built to help business operations get to their optimal productivity. When slowdowns occur on a regular basis, the overall organization’s productivity is hit, as well as the quality of service provided to the patients.

By implementing capabilities to shorten the diagnostic time for response time degradations, this hospital is generating hundreds of thousands of Euros of savings each month.


To learn how to troubleshoot network and application performance degradations in 4 easy steps, you can download our Performance Troubleshooting Guide: 

Performance Troubleshooting Guide

Topics: performance troubleshooting, NPM, ITOA, network performance, performance diagnostic, application performance, response time, SQL Transaction, performance troubleshooting use case, Transaction performance, Stream analysis, APM, SQL query

Posted by Boris Rogier on 12 mai 2016
Boris Rogier
Find me on:

Receive our Blog Articles