Optimising SQL Performance = Increased Revenue and Lower Costs

Optimising SQL Performance = Increased Revenue and Lower Costs

  • Comments 1
  • Likes

My name is Ishfaq Ahmed and I'm a SQL Server Dedicated Supportability Engineer (DSE). I work with Premier customers to ensure their SQL solutions are healthy, stable, provide maximum uptime, and are optimised for throughput.

SQL Server performance tuning and optimisation provides benefits ranging from enhanced user experience through to server stability, but in this article I want to look at the financial benefits of an optimised SQL solution. In this article I will show you how:

  1. Increased Throughput = Increased Revenue
  2. Optimised Systems = Lower Hardware Costs
  3. We Can Help You Do The Same

We regularly deliver Performance Tuning and Optimisation (PTO) engagements on SQL servers that customers have deployed. We've based this article on a real PTO  engagement that we conducted at a large investment bank running a key financial application. Data and usage growth over time had degraded performance and the customer was considering adding additional hardware resources (Memory and Disk). We set out to establish the root cause of the performance issues, find the worst performing procedures and tune them.

The rest of this article is based on the actual results of this exercise. The output from this exercise provided some interesting points when considering the financial benefits of the system improvements

1.  Increased Throughput = Increased Revenue

Transactional throughput directly affects the revenue generated by many financial applications. Each transaction has a monetary value attributed to it so any increase in "transactions per second" will also increase revenue.  Any performance boost in throughput and speed can have big implications on  revenue.

Here's the results from this engagement - interesting reading I'm sure!" 

Throughput

We were able to lower the duration of the longest running procedure from 31 minutes down to 4 minutes. This and similar improvements increased the capacity and scalability of their application not to mention improvements of the user experience.

The available headroom for further growth has now increased, for example based on an 8 hour trading day this procedure could only have been run 16 times it can now potentially run 120 times - that's an increase of 750%.  If you add a monetary value to each transaction, this achieves a huge growth in revenue.

Techy Detail (if you're interested)

We looked at their longest running procedure.  Each single execution was taking 31 minutes to complete. By adding an index we were able to lower duration to 4 minutes.

Along with the above example we were also able to lower the execution durations for several more procedures.

 

 2.  Optimised Systems = Lower Hardware Costs

Optimised systems have lower resource requirements. This improves the ROI of a SQL solution or can lower TCO through potential server consolidation or increased capacity.

Here's an example from the actual engagement:

Disk

Reduction in Disk IO:  From 198 Gb per hour to 2Gb per hour - disk bottlenecks alleviated.

RAM

Optimised database now runs on existing hardware - 32GB RAM upgrade no longer required.

The above 2 benefits has increased the shelf life of the existing hardware, therefore increasing the ROI. The cost of additional storage would have been very significant, especially considering it was running on a SAN. The cost of additional RAM may not seem very significant but don't forget to factor in the additional operational cost of replacing RAM.

Techy Detail (if you're interested)

We identified their worst performing procedure.  Initially each single execution of this procedure was reading in 3.3GB of data. After implementing our recommendations each single execution of this procedure now reads only 32k of data.

This procedure was executed approximately once a minute and each execution read in 3.3GB of data, this meant that 198GB of data was being read into memory from disk per hour for this procedure alone! Reading in large amounts of data was causing a memory bottleneck and disk performance issues. Our recommendations meant that we reduced the amount of data being read per hour down to about 2GB.

Along with the above example we were to make similar optimisations for several other procedures.

 

3.  We Can Help You Do The Same

If you would like to engage us to help with SQL "Performance Tuning and Optimisation" get in touch with your Technical Account Manager.  Engineers can then help with one of the 2 methods:

  • SQL Performance Tuning and Optimisation Clinic - a 4 day engagement aimed specifically at PTO.
  • SQL DSE - you can engage an engineer for a 3-12month engagement to work with your technical teams to help in all aspects of SQL server. As well as PTO the SQL DSE can work on knowledge transfer for your staff to enable them to better do this type of work for themselves. Your TAM can provide more details on the DSE offering.

If you’re not a Premier Support customer and would like to speak to someone about becoming one, and accessing these types of services contact UKSUPSAL@microsoft.com

I hope this helps, and thanks for reading 

Ishfaq Ahmed
Dedicated Supportability Engineer
Microsoft Premier Support UK

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment