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.
OBJECTIVES
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.
OUR APPROACH
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:
RECTIFICATION
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.
ACHIEVEMENTS
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.
Comments