By Insight Editor / 11 Nov 2021 / Topics: Microsoft Azure Analytics Data and AI
Facts at a glance
Client industry:
Manufacturing
Size of company:
1,500 employees, $490.5M in revenue
Challenge:
Five years after an initial migration to the cloud, significant data growth led to slow performance and escalating costs for a well-known beverage manufacturer. To overcome these issues, it needed to optimize its data environment by taking advantage of new features in Azure® Synapse Analytics.
Solution:
Leveraging a newly designed Performance Learning Utility for Synapse (PLUS) solution, Insight’s data architect provided a streamlined analysis of the Synapse pool, identifying and implementing numerous recommendations for improved performance.
Results:
- 301% performance gains over complete baseline query test set
- 612% performance gains over small baseline query test set
- 277% performance gains over medium baseline query test set
- 299% performance gains over large baseline query test set
- 33% reduction in consumption and cost of Data Warehouse Units (DWUs)
Solution area:
Digital Innovation
In 2015, a franchised branch of a well-known beverage company began shifting from its on-premises data environment to Azure SQL Data Warehouse, now known as dedicated SQL pool in Azure Synapse Analytics.
The company relied on its data to manage sales forecasting and inventory, as well as to keep stores and vending machines stocked with necessary product. The manufacturer also managed an Enterprise Resource Planning (ERP) system that housed financial, HR and accounting data.
Insight was selected as the preferred partner to lead the migration, ultimately helping the organization achieve a 71% increase in uptime and availability, a 375% increase in performance while processing and cost savings up to $8,000 per month. This laid the groundwork for more data-driven operations and established an ongoing relationship between the client and Insight.
While the migration fulfilled the client’s needs at the time, after several years, IT leaders began to recognize opportunities for optimization. In this time, Microsoft had made significant changes and improvements to the Azure Synapse environment. New tools and capabilities meant that the client’s original data structure had grown outdated and no longer aligned to Synapse best practices. Additionally, years of data growth had significantly increased workloads.
By 2019, the client’s small IT team found themselves spending an increasing amount of time troubleshooting performance issues within the data environment. In an attempt to overcome this issue, the team had steadily increased monthly investments in DWUs. But, despite the additional compute power, challenges persisted and costs began to escalate.
Due to the familiarity with the client’s data environment, leaders looked to Insight for a strategy to maximize performance while minimizing cost.
The Insight team began with a discovery session, performing an in-depth analysis of the database within Azure Synapse to evaluate how the client was currently using the platform’s functions and features. They identified significant opportunities for optimization by taking advantage of newly available capabilities that would improve processing speeds and reduce consumption costs.
As the scope of work was being agreed upon, an Insight data architect began documenting the various aspects of the database which would need to be corrected or aligned to best practices. He realized the required optimizations would call for a significant amount of repetitive, manual labor.
To improve efficiency, the architect began developing a utility tool that would automate a significant portion of the database analysis and provide recommended changes in a script-based format to improve the client’s data environment.
The tool, dubbed the Performance and Learning Utility for Synapse (PLUS), was designed to collect metadata from tables within the database, including table type, row and column definitions, indexes and partitioning. It could also analyze how data was being queried and stored. Based on this information, PLUS then programmatically applied Microsoft® best practices to make recommendations for improved efficiency.
By the time the client’s initiative was ready to begin, a preliminary version of the tool had been finalized.
PLUS enabled a single Insight data architect to perform the work of a team, reducing the time and costs associated with the optimization project. The architect used PLUS to collect and track ongoing table types, storage options, indexing columns and data volumes, as well as collecting and analyzing the query workload. The tool identified and provided recommendations for resolving the majority of the manufacturer’s most common database issues, allowing Insight’s architect to focus on the more challenging problems.
After the application of PLUS, the Insight team implemented recommended changes and best practices to optimize the client’s data environment. 80 changes to table types and storage were applied by Insight’s architect, while PLUS was used to compress 66 tables that had been identified as frequently having uncompressed rows. Maintenance scripts were also provided to the client’s IT team to enable them to address this issue long term.
The project ran smoothly from start to finish, thanks to the dedication and innovation of Insight’s architect. Within just four weeks, the improvements to the client database were complete.
Compared to baseline testing of the client workload, the optimizations recommended by the PLUS tool resulted in significant performance gains that allowed the organization to scale down DWU consumption and associated costs by 33%.
Following the success of this engagement, the beverage manufacturer has continued its close partnership with Insight. Insight teammates have worked to equip the organization’s IT team to take full ownership of database management while supporting other development efforts through Agile and Scrum training.
Furthermore, having recognized the dramatic cost- and time-saving results delivered through this initial deployment, Insight’s team has continued developing the PLUS solution. The tool is now being used by other Insight clients looking to optimize their Synapse database environments.
Innovating is the only way to stay relevant in today’s uber-competitive market. Our unique approach and deep knowledge put you on the path to true innovation.
Discover reports, stories and industry trends to help you innovate for the future.
