Call: 469-635-6200
Name *
Phone No. *
Email
Question
Security Code *
Refresh
 
Submit
Name *
Email *
Phone
Question *
Security Code *
Refresh
 
Submit

Case Studies

Global Airline

Market Segment: Transportation Services

Problem/Opportunity

Flight delays have a negative impact on this company’s image and financials.  The company asked its Operations Research and Decision Support team (ORDS) to design and prototype a solution to help mitigate the effects of ground delays. The company engaged Scalability Experts to analyze the Microsoft SQL Server ecosystem which underlies the ATC Ground Delay Advisor and other ORDS team prototype applications. The main goal of this engagement was to review the current application and database infrastructure in order to determine whether the current infrastructure is production-worthy. If not, evaluate if worthwhile to invest in a rewrite of the applications. Another integral part of the engagement was to review best practices with the ORDS team so that they would be self-servicing on SQL Server performance issues in the future.

Solution/Capabilities

In order to review the characteristics of the system under a load, a test load was generated by the Scalability Expert’s team. This pre-existing test scenario simulated data from many airports (stations) simultaneously and was used to represent a worst case scenario. Data analysis suggested several issues such as unnecessary table scans, locking, extensive calculations in user-defined functions and administration overheads. Based on the data collected and analysis performed, Scalability Experts provided several architectural recommendations. Some of these recommendations included the following:

  • Perform updates based on changed data only, instead of refreshing its own set of tables from all rows in few selected tables.
  • Review code that uses READ UNCOMMITTED query hints.
  • Consolidate the staging databases in order to improve schema and code manageability and minimize the likelihood of human errors.
  • Implement an external code layer to perform the calculations currently contained in the Staging database stored procedures.

Results

Several best practice recommendations were also provided to tune the database server, such as index de-fragmentation, segregating the transaction log files and Tempdb database on separate disks, implementing stored procedures instead of Ad-hoc queries and so on. During the knowledge transfer phase, Scalability Experts also went through a stored procedure tuning exercise where we identified an index that significantly improved the performance of that stored procedure. This served as a valuable engagement for the airlines technical team for using the tools supplied with SQL Server, such as Profiler and Query Analyzer (Show Execution Plan), to troubleshoot and tune stored procedures.  SE helped improve system performance, optimized the environment and equipped the team with the latest tools and best practices.