Client: Southwest Research Institute
COMPANYPROFILE: Southwest Research Institute (SwRI), headquartered in San Antonio, Texas, is one of the oldest and largest independent, nonprofit, applied research and development (R&D) organizations in the United States.
PROJECT DESCRIPTION: SQL Query and Performance Tuning
ENVIRONMENT: SQL Server, SQL Server Integration Services, Proprietary front-end (UI)
SUMMARY: SwRI was engaged in a long-term research initiative to test and capture the results of various performance scenarios on a variety of engine types. Their proprietary software recorded dozens of metrics in one-second cycles. Testing times would vary from hours to days. At the completion of each test, the data was migrated to a central database for archiving. The integration would often fail, or lag for extreme amounts of time, due to the amount of data being migrated. The Y&L team was brought in to eliminate the failures and help improve performance. Over a 2-week period, an in-depth analysis of the system logs was conducted, the server and network infrastructure was reviewed and validated to be up-to-date on patches and updates, the integration process and code was reviewed, and the data tables were analyzed for improvement opportunities. The team found a number of areas where changes were necessary. Table indexing and partitioning provided the most improvement, and queries were re-written to optimize load performance. Additionally, several automatic and dynamic maintenance plans were implemented to help with future data growth.