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.

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Fusion Insights

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

A data lab is a well-equipped environment that allows organisations to explore and examine new ideas by combining new data…

MORE INFORMATION