I had a recent engagement with a well-known house hold supplier. This was my first arranged from start to finish consultancy project, whereby I was the only representative from DSP on the project (from pre sales to end delivery).
The project was a performance tuning exercise, whereby the customer had launched a new mobile application, where the back end database was being hosted on SQL Server 2012. This backend database was one that I built and configured on top of an AlwaysOn High Availability solution. The application had been launched for approximately 6 weeks, and the performance of the SQL and application was a lot less than what they had expected (considering the substantial investment they had made). Going into the project I had a number of performance tuning tools to hand including SQL Doctor; DTA and custom built scripts to detect issues and recommend improvements. Not to blow my own trumpet, I built this SQL Server environment using DSP’s methodology of using Microsoft best practice instilled within the build. So after running the tools and scripts, there was little surprise that the results did not bring back anything wrong with SQL Server.
So another approach had to be taken…the DBA approach. After my kick meeting, I clearly discovered that no one within the internal technical IT team knew end to end how and what the application does. Unfortunately the essence of time was not on my side, as the project had a very small window to complete all aspects of the work. I was not able to arrange anytime to discuss with 3rd party who had designed the application, to discover the internals of the processes used. I had to focus and discover the internal processes of the application interacting with SQL Server, understanding the infrastructure; the T-SQL code used; how the Server resources were being utilised and discovering any contention. The process for making these discoveries is not one to be taken for granted.
The end result was that the customer gained over 80% improved performance from the Operating System and SQL Server resources including:
• Memory
• CPU
• Disk I/O
• Page File
• Specific SQL counters (including wait stats)
Utilised the correct amount of cores licensed on the Server, which were being under used. Provide in depth documentation of the performance tuning exercise from start to end. Whereby they now had a thorough understanding of what and how the application interacts with SQL Server.