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:
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!"
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:
Reduction in Disk IO: From 198 Gb per hour to 2Gb per hour - disk bottlenecks alleviated.
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.
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:
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
In case a follow-up on this topic is required, an updated datasheet and some details on what depths we get into in a Clinic, check out