Global Airline
Scalability and Performance Analysis
Market Segment: Transportation Services
Introduction
This client success story involves one of America’s largest airlines that serves 172 cities in over 40 countries with more than 2,600 daily flights. The company has more than 60,000 employees and is in business to provide safe, dependable, and friendly air transportation to the customers, along with numerous related 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.
SE resources
Migrating from Oracle to SQL Server?
Learn more about Scalability Experts'
worldwide training 
Headed into the cloud?
Learn more about Scalability Experts'
cloud computing services
Extend your SQL Server 2008 Management Data Warehouse (MDW) with Scalability Experts Smart™ 
