Some people think that a database should be purely a data storage device. So much so that they don’t allow development of stored procedures and functions. But, the truth is that for a performance application you want to do as much manipulation as possible close to data - on the database server. With databases getting more and more sophisticated we can even implement asynchronous processes that truly push the edge. Don’t trust your SQL consulting to a team that doesn’t fully understand how vital your database is to all of your company’s other systems.
Case Study - Service Broker Queues as a Solution:
Here is a perfect example of burst performance optimization. This happened on an SQL server-based system that was used to process various events. Sometimes events would overwhelm the system, slowing down the entire platform. At other times, events would slow to a trickle, wasting capacity. What was our solution? Service Broker Queues. With a bit of tinkering, we were able to refactor the system to allow asynchronous handing off of events. Event ingestion rate went up to 1.5 million/s and all related components were no longer tied to SQL Server performance. As for the database itself, it was able to function at its theoretical peak throughput (approximately 40K transactions/s) to process all events without breaking a sweat!