Case Study: MS SQL Server Performance Tuning and Best Practice Implementation at a NSW Government Department
The Client is one of major NSW government departments providing services to public. The Department had been experiencing performance issues with their CRM system that serves millions of NSW residents. The CRM system was built a few years ago and initially was perfectly fit for the existing workload. But as happens with most IT systems the requirements evolved over the years. The CRM added more valuable features and most importantly had a significant increase in the number of users. As a result, on various occasions, disruptions and performance issues were raised in a number of complaints by NSW residents.
Government Service owners and local IT support suspected that all the problems were caused by the poor performance of their MS SQL Server based databases.
IT Management engaged Fusion Professionals to help with troubleshooting the poor performance of the CRM MS SQL Server databases.
Fusion professionals were able to bring significant and varied experience in MS SQL Server Performance Tuning at all levels, implementing industry Best Practices to address not only ongoing performance issues but make the MS SQL Server scalable and performance predictable.
Fusion Professionals initial involvement consisted of “just Performance Tuning” of the departments CRM and its correlated BI MS SQL Server databases.
From our experience, we know that for large critical systems with a long “history” there cannot be just “one silver bullet” which solves all poor performance problems. The most likely circumstance is that all the current issues have their roots dated back in the past or probably even from the moment the system was initially built and commissioned. And over a long time the issues accumulated until recently a critical threshold was reached.
After an initial review our SQL Server experts identified the following key major problem areas, namely:
- Some basic SQL Server tuning routines (such as performance monitoring, identifying TOP resource consuming SQL statements, indexes review, etc.) were not conducted regularly, if at all.
- The SQL Server environment was not built and configured as per MS SQL Server Best Practices. This applied not only to the MS SQL Servers but also to the OS, Network and Storage layers.
- Unusually and extremely large numbers of deadlocks (avg 450/day) on the main PROD database. Whereby, it is a known fact that every deadlock situation causes a rollback of the transaction(s) which is the chosen deadlock victim. As a result, these deadlocks are likely to cause considerable disruption and inconvenience for users and other IT systems.
- No capacity planning and management were in place. As a result, the workload demands outgrew the MS SQL Server environment capacities.
Based on the discovery of these issues, we decided to take the following actions:
First of all, Fusion Professionals experts identified the TOP resource consuming transactions and using common DBA performance tuning methods managed to fix the most debilitating performance problems. The Execution time performance impact of those transactions reduced from 10 to 100 times depending on the transaction. The department users noticed the positive results immediately.
Secondly, Fusion Professionals experts together with the client IT Team started implementing the MS SQL Server Best Practices on the OS, Network and Storage layers. This also provided noticeable performance improvements as well as significant overall system stability. Unfortunately, not all best practice recommendations could be implemented, due to the system age, some of the recommendations were considered unfeasible for implementation. Fusion Professionals conducted some capacity planning for the MS SQL Server environment and combined those recommendations into a plan for the Departments new MS SQL Server environment.
Thirdly, the major task of addressing the clients large and diverse number of deadlocks. As is commonly known deadlocks are normally resolved by modifying the application code rather than by purely DBA available methods. Hence this piece of work required close cooperation with the client’s Development, Testing, App Support and Change Management teams. There were all sorts of diverse deadlocks identified and Fusion Professionals used all available techniques on the DB, objects and T-SQL levels to resolve them one-by-one. After completion, only 1 of initial 30 types of deadlocks still remained, that deadlock scenario was caused by the way application functions and was fixed by modifying the application.
Once all the major performance issues were resolved, the client IT teams started to receive positive feedback from the Service owners and users regarding System Performance and Stability.
Finally, the only remaining item was scalability, but that could not be resolved due to the hardware limitations of the existing systems. So, as part of the final set of recommendations, Fusion Professionals experts provided the client with a Hardware and Software upgrade plan that will make the departments future MS SQL Server environment more scalable and robust.
- The Government Department stakeholders are very satisfied as the End users, Business and Service owners are able to use the CRM effectively;
- Successful resolution of all CRM the performance problems and issues;
- Availability and Serviceability of the MS SQL Server environment increased dramatically despite the growth in users and functionality;
- The client adopted Performance monitoring and troubleshooting techniques that would allow them to prevent similar problems in the future;
- The client engaged Fusion Professionals again to help with the design, implementation and configuration of the new MS SQL Server environment;
To find out more about this implementation or to discover how to improve the efficiency and performance of your MS SQL Server environment, databases or dependent applications come and speak to the Fusion Professionals team.
In recent years data volumes have been increasing dramatically. This has created major challenges for traditional analytics platforms in terms…MORE INFORMATION
With the increasing volumes of data that can be cost effectively stored in the cloud, comes increasing responsibility. The current…MORE INFORMATION
With the advancement of technology and abundance of data your business receives on a daily basis, companies are now in…MORE INFORMATION
Fusion Professionals held its annual Fusion Summit last Thursday the 18th of October at the Rag and Famish Hotel in…MORE INFORMATION
The Client is one of major NSW government departments providing services to public. The Department had been experiencing performance issues…MORE INFORMATION
Though its conception dates back to 1979, containers made their mark as much needed, major technology assets in 2000. Digital…MORE INFORMATION
Objective The intelligent mobile app-based lending system is a new field, blending recent technical developments in mobile phones and Artificial…MORE INFORMATION
Our Client is a well-known Australian freight logistics company, operating in railway freight and shipping. The company embarked on a…MORE INFORMATION
Data warehouse management and data analytics always had the challenge to decide what data to store and for how long…MORE INFORMATION
Cloud computing is becoming a preferred storage platform for IT managers and organisations in general. In Australia alone, 31 percent…MORE INFORMATION
Serving your customer in the best possible, most efficient way should always be the major goal of any organisation. The…MORE INFORMATION
Moving out from proprietary software seems like a daredevil act, considering the possible data security issues some open source databases…MORE INFORMATION
The Challenge Complex IT environments can pose significant technical risk that, if not managed adequately, have the potential of major…MORE INFORMATION
Fusion Professionals has signed a partnership agreement with Waterline Data ( https://www.waterlinedata.com/ ) the leading provider of Information Catalogs and…MORE INFORMATION
Most people do not like change. As much as possible, they want things to stay the same that is why,…MORE INFORMATION
Regardless of your infrastructure whether you are running in the cloud or on-premise, there will always be a need to…MORE INFORMATION
Data Analytics tools provide Data Scientists and Data Engineers with the instruments to find patterns in data and provide business…MORE INFORMATION
Fusion Professionals held its Fusion Meld 2018 event last Thursday, the 17th of May at the Terrace Hotel in North…MORE INFORMATION
Over the past 10 years, data has grown into a behemoth that dominates business intelligence. A huge percentage of the…MORE INFORMATION