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.
Many organisations don’t realise it, but in our current environment Data has become the main differentiator in the market. Most…MORE INFORMATION
Professional services, one of the fastest growing sectors of the Australian economy, covers a broad group of companies and organizations…MORE INFORMATION
We experience an increasing polarisation in our political landscape with tribalism becoming a real issue. This is partially to be…MORE INFORMATION
Oracle’s introduction of the self-driving, self-securing, and self-repairing Autonomous Database draws upon its decades of expertise in automating databases and…MORE INFORMATION
In a recent blog post from Dataiku, the leading data science, machine learning, and AI platform, Lynn Heidmann explored ways…MORE INFORMATION
“With Great Power Comes Great Responsibility” One of the biggest ongoing responsibilities that comes after commissioning an Exadata appliance is…MORE INFORMATION
According to Constellation Research, a little more than half of traditional Fortune 500 companies have disappeared due to the lack…MORE INFORMATION
Fusion Professionals has signed a partnership agreement with Dataiku, one of the world’s leading machine learning platforms that moves companies…MORE INFORMATION
Statistical language models apply probability distributions to a sequence of words. These models are finding increasing use as natural language…MORE INFORMATION
Challenges The Company, one of Australia’s largest and fastest growing Telco companies had 2 primary SharePoint environments that had different…MORE INFORMATION
Containerization allows applications to run on any machine- anytime, anywhere so long as they are compatible. By virtualizing your OS,…MORE INFORMATION
So you’ve finally decided that the cloud is safer than corporate data centers and digital assets and you’ve chosen to…MORE INFORMATION
Building a system that houses your organisation’s data can be daunting, especially now that data acquisition is growing rapidly. The…MORE INFORMATION
Human-to-machine communication has not yet been perfected, but enterprises are already beginning to integrate this groundbreaking technology into their operations,…MORE INFORMATION
Fusion Professionals has signed a partnership agreement with MapR Technologies, provider of the industry’s leading data platform for AI and…MORE INFORMATION
“Big data is at the foundation of all of the megatrends that are happening today, from social to mobile to…MORE INFORMATION
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